Friday puzzle 🙂. This isn’t CFML related, just a bit of a SQL puzzle, so I thought I’d throw it out here for some ideas. There is an Oracle database table that an application writes to, setting new records with a

processed

field set to

N

and

record_created_date

set to the current date/time. SAP PI (middleware software) polls the table for unprocessed records, creates SAP IDOCs from the data, then updates the

processed

flag to

Y

. Here is the kicker…the way the SAP PI JDBC sender works, there are 2 separate SQL statements that make this happen that have no knowledge of each other. The 1st is a SELECT to get the records to create the IDOCS. Then a totally separate UPDATE statement to update the records that have been processed. The polling interval can be set to whatever timeframe. So you might just say… 1.

SELECT * FROM table WHERE processed = 'N'

2. Process the records and create IDOCs 3.

UPDATE table SET processed = 'Y' WHERE processed = 'N'

The problem is, if processing the records takes 3 seconds (or 10ms, or 30s, etc), the records that are inserted in-between step 1 and 3 don’t get IDOCs created but are updated in the table as processed. Is there any way to make sure all records are processed?

When/after you select the records

where processed = 'N'

, can you set

processed

to

'P'

(pending), and then only update

where processed = 'P'

?

There is literally only 2 fields in the JDBC Sender in SAP XI - one for a SELECT statement (which is where the data is available to be processed) and one for an UPDATE statement. They are two totally separate fields that you plop SQL into and no data can be passed between.

Is there any way to tie a SAP IDOC to a particular record? Can you read those to update the table?

The Update doesn’t have any knowledge of the data that was processed. It is just a separate update statement.

and there's no way to make it aware of those updates? 🤔

I pulled this screenshot from the internet, but this is what they have to work with…

can the select statement be any valid sql, like a cte?

Yes. Including calling a function since that is allowed in a SELECT statement in Oracle. But functions in oracle can’t manipulate data.

Are you able to modify these tables or add a helper table?

yeah, i was going to say, what about a third table to stage records to be processed, then the update sql will do an UPDATE / INNER JOIN to that third table from the main one

Yes, to some extent, we could do that. I added the

record_created_date

field to try and help, for example.

Can you write a query to go with the select to update a helper table? If you could add a record_created_date, can you add an id column?

I could probably add an ID column. How would it be used? Tell me more about what you mean with “write a query to go with the select to update a helper table”.

use the helper table as a holding cell. (I'm a Sql Server user, so this may require some translation to oracle)

select ID

(from origTable)

into helperTable where processed = 'N'

. Then

Select * from helperTable

for processing IDOCs.

Update origTable SET processed = 'Y' WHERE id in (select id from helperTable).

Truncate helperTable

so the next batch starts from scratch.

INSERT INTO helpertable ( col1, col2 ) 
SELECT col1, col2 
FROM tablename 
WHERE processed = 'N'; 

SELECT col1, col2
FROM tablename 
WHERE processed = 'N';

-------------------------

UPDATE t 
SET t.processed = 'Y' 
FROM tablename t 
	INNER JOIN helpertable h ON t.col1 = h.col1
WHERE t.processed = 'N';

you'd have to update the update sql to match how oracle does update/inner joins, of course. you'd also want to have something that cleans up helpertable although if the above works you could forego that (but have lots of leftover useless rows)

But how does the insert into the helpertable happen? It’s kinda like wanting an “in process” flag (e.g. P instead of just Y or N). Where does that event take place?

Where does

select * from table where processed = 'n'

take place?

it assumes you can have multiple queries in the select item

Why can't it go just before that?

Ok, I’ll ask about multiple statements in that little box. Not sure. If that is possible, then all sorts of stuff opens up. I’m not sure if it is.

or can you call some sort of oracle-equivalent to a stored procedure?

perhaps you can wrap all this up in a stored proc and invoke that

I don’t know if it has the “I saw a certain character and will continue to run the next statement” ability.

@websolete, it only encourages me that I might be on the right track. 😁

Yeah, I tried that route first. Evidently it has to be valid SQL, not PL/SQL.

which is why capturing the complex select/flag/update in a stored proc and 'simply' calling the stored proc makes the most sense. the update sql should be easy

Right. In Oracle I think it is EXECUTE proc;, or something like that. Maybe I need to have them show me that so I can see it not working with my own eyes, ha.

it can, at least in normal rdbms's

Maybe I just never do that and it can in Oracle too? 🤔

anything's possible. i hear they have auto-incrementing numeric identifiers in oracle now, so there's hope

Oh, the triggers and sequences I’ve created in my day. Whew.

Regardless...this is an interesting challenge!

Yeah, I don’t think you can return data with an

EXECUTE sproc;

statement, right? Sorry if I’m too Oracle-ized and this sounds ridiculous. But Returning data = SELECT statements = SQL. Stored procedures are in the PL/SQL realm. Right?

fwiw, you can return a resultset from a stored proc in sql server in a 'flat' exec proc statement, but only a single resultset, not multiple like if you actually invoke the stored proc

Single result set, meaning could be the results of one query?

yes

Hmm. Looks like I’m on 11g for this table and resultsets hadn’t been dreamt up yet. Still googling though…

i don't believe so, if you mean altering data in tables. you can always massage what's returned from the function, but i don't think you can affect data changes at the storage level

Yeah, that’s what I mean. Ok, that’s consistent with Oracle too.

does oracle have computed columns?

I might need a translation or example of what that means.

Ah, gotcha. So yeah, it looks like it exists, but I’ve usually seen that type of thing solved by an insert/update trigger that manipulates the data before committing.

similar yes, a bit more lightweight than triggers i'd say, again at least in sql server

Ok, I’ve reread that a couple of times. I think it will be susceptible to the same issue I explained here… https://cfml.slack.com/archives/C082RPGTZ/p1651249243704979?thread_ts=1651248315.509289&cid=C082RPGTZ

ok, so you set the processAfter time to add 20 mins, and you still poll on five min schedule. assuming the batch finishes quickly, your update sql will simply be

WHERE processed = 'N' AND processAfter < now()

any newly inserted records that have occurred will be flagged for 20 mins in the future

Ok, that’s interesting (the batch idea). Ideally, within minutes. Right now it polls every 20 seconds. But it also potentially leaves records hanging every 20 seconds, so that’s not exactly ideal either. 😄

if you base it on the insert timestamp rather than a padded timestamp 'in the future' that is beyond your schedule window

Trying to think through that to figure out if you still have the problem at the polling interval…

it's not clear to me if that truly avoids the problem you're describing about getting close to the threshold of the schedule time, but i think it dos

Does the

processAfter

become the same thing as

record_create_date

, but just 10 minutes later?

yes

Just trying to rubber duck this a bit. It might not be clicking yet, but I think my problem still exists. The statement below would give me would give me 1:00pm if run right now (1:04pm). It will give me 1:00pm anytime from 1:00pm until 10959pm.

trunc(sysdate, 'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 5) / (24 * 60)

you're rounding down, i think you need to round up

But anytime a SELECT then UPDATE combo rolls over the time when the period that returns changes, I’m losing records.

perhaps

Yeah, I think it has the same problem. If one batch of updates is set for 4:15pm in the processAfter field and the next is set at 4:30pm, if the SELECT runs at 41459, it wouldn’t pick up the 4:15pm batch. But if the correlated UPDATE then runs at 41501pm, it would update the 4:15pm batch as processed. Right?

what about this: let's say you're ok with an hour lag time for processing. when inserting records in to the table, you could have a column called

hourBatch

or batchId or whatever whose value will be the hour part of a 24 hour clock + 1.

id	col1	col2	processed 	hourBatch	timestamp
1	92	hello	N		12		11:01:33 am
1	93	blah	N 		12		11:11:43 am
1	94	fred	N 		13		12:32:00 pm
1	95	mary	N 		15		02:01:33 pm

then, your SELECT will target only records from the current hour:

SELECT col1, col1
FROM tablename
WHERE processed = 'N' AND hourBatch = hour(now())  -- let's say it's 11:02am right now, so this would be 11

you process those records (which were inserted an hour ago) and then your UPDATE statement targets the same set

UPDATE tablename
SET processed = 'Y'
WHERE processed = 'N' AND hourBatch = hour(now()) -- 11

the trick would be finding the smallest time increment (quarter hour, half hour, hour, whatever) you can get away with and not risk any records slipping into the current batch. in this exaggerated scenario of one hour lag, you're fine as long as any one batch doesn't take more than an hour to complete. it's the disconnect between the select and update and the forced simplicity of the sql statements you can use that are going to make any solution that does not include a third agent getting involved to try and bridge the gap really difficult

Ok - thinking aloud based on what you said. There’s something there about the polling interval and the batches, I think. Thinking about it at an hour length is maybe sparking something. Really, the problem is if the processing takes longer than the polling duration. Said another way, the problem of a separate SELECT/UPDATE is only problematic if a new batch is picked up in the UPDATE but wasn’t in the SELECT. If the SELECT runs at 41459, it wouldn’t pick up the 4:15pm batch. But if the correlated UPDATE then runs at 41501pm, it would update the 4:15pm batch as processed. But what if you poll every 5 minutes to update the batches marked at every 15 minutes? So you will have had two tries at updating that batch already by the time the 3rd poll comes around. Lessening your chance that 3rd and final one is problematic.

is the update on a separate schedule too or is it called at the completion of the processing, however long that takes?

It is called on completion.

does oracle have global temp variables? e.g., in sql server that would be a @@varname rather than a @varname

Not sure about variables. Yes on temp tables.

if so, could you do something like

SELECT col1, col2, @@lastprocessed = now() FROM tablename WHERE processed = 'N'

do the processing and then

UPDATE tablename SET processed = 'Y' WHERE processed = 'N' AND record_date < @@lastprocessed

?

How would

@@lastprocessed

get set?

inline in the sql. the last row that is collected in the select would have the latest timestamp at that time, so any new records inserted would be explicitly after that time

Ah, ok, I missed that in the SELECT. Yeah, I haven’t seen anything like that in Oracle. I don’t think that is available. That would certainly work, it seems.

12. Dislike Oracle more than disliked previously DONE.

You would do something like

SELECT bleh INTO myVariable

with Oracle and you can use it within the same function/procedure, but that would only be in PL/SQL, not normal ’ol SQL.

i can't think of another way other than pre-flagging rows to be processed. can that interface/process you posted a screenshot of have three steps?

At this point, changing DBs might actually be the easiest way to accomplish this. 😂

It cannot have three steps. I just had them send me the error when they tried to have two statements in the first select field.