12,557 reputation
12036
bio website ddbcinc.com
location Rochester Hills, MI
age 38
visits member for 3 years, 11 months
seen 2 days ago
Oracle ACE

Jul
2
answered Skip running trigger during cascade delete?
Jun
28
comment € Euro sign/symbol not showing up on Oracle 10gr2 and aix 6.1
What is your database character set? What does SELECT * FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET' return?
Jun
26
comment Oracle extents - How can I have so many with this small size?
@orbfish - Can you edit your post to include the output from user_extents. Also, post the results of querying user_indexes to get the tablespace_name and dba_tablespaces to get the extent_management and allocation_type for that tablespace. Is this a table that was created in a dictionary managed tablespace that was converted to locally managed?
Jun
26
comment Oracle extents - How can I have so few with this large size?
@orbfish - Can you edit your post to include the output from user_extents. Also, post the results of querying user_indexes to get the tablespace_name and dba_tablespaces to get the extent_management and allocation_type for that tablespace. Is this an index that was created in a dictionary managed tablespace that was converted to locally managed?
Jun
21
comment Oracle - capture row commit order in a table
@Ratnam - Your reader process can hit the change tables, get the SCN from the change table, move it to the sequence_id column of your existing table, and then do whatever else you need to do with the data in the change table to move it into your existing architecture. It's a bit messy but doable. Your reader process could also be a Streams consumer and do the same thing (grabbing the SCN from the Streams change record rather than the CDC change table).
Jun
21
answered delimited values in a column value
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.