12,472 reputation
12036
bio website ddbcinc.com
location Rochester Hills, MI
age 38
visits member for 3 years, 10 months
seen 26 mins ago
Oracle ACE

Jun
20
comment Oracle - capture row commit order in a table
@Ratnam - Normal CDC involves the reader process controlling a window that shows one change set at a time, consuming that change set, and then advancing the window. The reader consumes data in commit order and never needs to know specifically what the SCN of a change is. You can hit the change tables directly as well to get the actual SCN if you want to complicate the reader's pull process. I'm not sure what "let us know what the maximum sequence_id on its side" means here-- I'm not sure what "us" refers to and I'm not sure what the sequence_id column has to do with anything.
Jun
19
comment Oracle - capture row commit order in a table
@Ratnam - Both CDC and Streams will send the subscriber the data in commit order. The CDC change table will also store the commit SCN and the commit timestamp if you really want them (though I suspect you don't really care so long as the reader is sent every change in order).
Jun
19
comment Error calling stored procedure from trigger in Oracle Database 11g
@notfed - In the normal case, you have to type both 'BEGIN' and 'END' (8 characters). In the CALL case, you just have to type CALL (4 characters) so you save 4 characters. Not, of course, that avoiding typing 4 characters is a meaningful benefit. But the CALL syntax is marginally shorter.
Jun
19
comment Error calling stored procedure from trigger in Oracle Database 11g
@notfed - Putting the actual logic in a stored procedure rather than having lots of code in trigger bodies is definitely a good idea. Using the paleolithic CALL syntax rather than the standard PL/SQL block to actually execute the stored procedure is where I have a dispute. CALL is valid, it's just extremely unusual and sufficiently different from every other PL/SQL block you'll ever encounter that I can't see any benefit to using it (well, it does save typing 4 characters, so I guess there is some benefit).
Jun
19
comment Error calling stored procedure from trigger in Oracle Database 11g
@notfed - The CALL syntax hasn't changed in forever-- what works in 10.1 works in 11.2 just as well. You don't have a BEGIN or an END when you are using the CALL syntax (see my second example). In general, though, I'd strongly suggest avoiding the CALL syntax entirely.
Jun
19
revised Error calling stored procedure from trigger in Oracle Database 11g
added 633 characters in body
Jun
19
answered Error calling stored procedure from trigger in Oracle Database 11g
Jun
17
comment Oracle - capture row commit order in a table
@Ratnam - I'm not sure I understand. Some process needs to generate the XML in response to a HTTP request (i.e. some process is implementing the web service). That process is reading data from the database. That process should be the CDC subscriber.
Jun
16
answered Oracle - capture row commit order in a table
Jun
15
comment Oracle - capture row commit order in a table
Are you trying to capture the order in which rows are inserted? Or the order in which rows are committed? If I insert row A at time 1, row B at time 2, commit B at time 3, and commit A at time 4, does A come before B? Or does A come after B?
Jun
15
comment How do I grant execute permission to an Oracle database user?
@notfed - Yes. If you can lock it down to one script, that would be much safer
Jun
15
comment How do I grant execute permission to an Oracle database user?
@notfed - I updated my answer with an example. If you're calling shell scripts, you'll also likely need the RuntimePermission.
Jun
15
revised How do I grant execute permission to an Oracle database user?
added 627 characters in body
Jun
15
comment How do I grant execute permission to an Oracle database user?
In order to call a stored procedure, you would either need to type EXEC before the stored procedure in SQL*Plus or surround it in a BEGIN and END block.
Jun
15
answered How do I grant execute permission to an Oracle database user?
Jun
14
awarded  Good Answer
Jun
12
revised Oracle - query by two column combination
edited tags
Jun
12
answered Oracle - query by two column combination
Jun
12
comment Oracle - query by two column combination
Do you really not care which three products you get for each market & category?
Jun
10
comment Locking order in cursors (Oracle)
@a1ex07 - No more dangerous than, say, an UPDATE statement. It's entirely possible that if you have two sessions trying simultaneously to update the same set of rows that the two sessions would get a different query plan and that they would affect rows in a different order leading to a deadlock. But while it's possible, it's relatively unlikely.