i take complete database backup once daily with following command <code> backup as compressed backupset database plus archivelog; delete noprompt obsolete; </code> now i am planning to backup archive log every 5 mints with following command <code> backup archivelog all; </code> my archivelog deletion policy is 1 'times to DISK', so there will not be duplicate backups of same log files. i am doing this because i could not do complete recovery of one of my database due to missing archive log. my question is that, is this a good approch ? or there is any better way to be able to do complete recovery ? my RMAN settings are as folows <code> configure controlfile autobackup on; configure backup optimization on; configure retention policy to redundancy 5; configure archivelog deletion policy to backed up 1 times to DISK; configure retention policy to recovery window of 7 days; </code>

Hi Tom, I have this data <code>select 1 LineNumber, 1 mylevel, 10 id from dual union all select 2 , 2 , 11 from dual union all select 3 , 3 , 13 from dual union all select 4 , 3 , 14 from dual union all select 5 , 4 , 15 from dual union all select 6 , 2 , 20 from dual union all select 7 , 2 , 30 from dual union all select 8 , 3 , 31 from dual union all select 9 , 4 , 33 from dual union all select 10 , 3 , 32 from dual union all select 11 , 3 , 34 from dual union all select 12 , 4 , 35 from dual union all select 13 , 5 , 36 from dual</code> I'm look for a way to get - the child ids recursively of the id = 30. - the parent id of the id = 30

I just upgraded to Oracle 18c XE from 11g. I have an external table that worked fine on 11g, but I keep getting the following errors on 18c. ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-11604: no directory object specified for log file The directory does exist and I have the correct grants. <code>SELECT * FROM all_directories WHERE directory_name = 'MYDIR';</code> OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID SYS MYDIR C:\Users\sneuf\OneDrive\PLSQL\OracleDirectory 1 <code>SELECT * FROM all_tab_privs WHERE table_name = 'MYDIR' AND grantee = 'C##_SNEUF';</code> GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE INHERITED SYS C##_SNEUF SYS MYDIR EXECUTE YES NO NO DIRECTORY NO SYS C##_SNEUF SYS MYDIR READ YES NO NO DIRECTORY NO SYS C##_SNEUF SYS MYDIR WRITE YES NO NO DIRECTORY NO I'm pretty sure I'm missing a grant somewhere, but I can't figure out what. Here is my table: <code>CREATE TABLE C##_SNEUF.CHECKING_TBL_EXT2 ( DB_KEY NUMBER, CHECK_NUM VARCHAR2(10), TRANS_DATE TIMESTAMP (6), DESCRIPTION VARCHAR2(100), DEPOSIT_WITHDRAWAL VARCHAR2(1), AMOUNT VARCHAR2(12), MEMO VARCHAR2(200) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY MYDIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE MYDIR: 'checking.bad' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM MISSING FIELD VALUES ARE NULL ( DB_key CHAR, check_num CHAR(10), trans_date CHAR(21) DATE_FORMAT DATE MASK 'MM/DD/YYYY HH24:MI:SS', description CHAR(100), deposit_withdrawal CHAR(1), amount CHAR(12), memo CHAR(200) ) ) LOCATION ( MYDIR: 'checking.csv' ) ) REJECT LIMIT UNLIMITED ;</code> Thanks, Steve

This question is most common with no clear solution. I have 2 scenarios for dynamic pivoting and the solution requested could be using pivot xml(tried, but extracting is a task)/dynamic sql execution/arays if possible we could use.. Scenario 1: In the first case,I i need to pivot the data where data source is same table. However the pivot needs to be dynamic as the columns header would keep changing as per column app_id. So if app_id=1. The column header would be A,B,C,D, If app_id=2, column would be CDEF and so on. Also each set of value has an id. So for id, 120 and app_id=1 , column A,B,C,D, would display the values and so on. The current sample data has only 2 app_ids, but there could be many more, so app_id and labels would kepe changing thus i need to write a dynamic query. Table is DATA_1 <code>ID label value app_id --- ----- ----- ------ 120 A Alpha 1 120 B Beta 1 120 C Class 1 120 D Delta 1 120 C Alpha 2 120 D Beta 2 120 E Class 2 120 F Delta 2</code> And expected output would be something like this. P.S. Data is dummy and app_ids, and ids would keep getting inserted and removed with changing column headers,so a dynamic solution is needed. <code>SELECT * FROM data WHERE ID = 120 AND app_id = 1; app_id A B C D ID ------ ------ ----- ----- ----- ----- 1 Alpha Beta Class Delta 120 SELECT * FROM data WHERE ID = 120 AND app_id = 2; app_id C D E F ID ------ ------ ----- ----- ----- ----- 2 Alpha Beta Class Delta 120</code> Scenario 2: Here were had the data in one table. Now we have separate table containing the labels and current table with values. SO in table from scenario one we will focus on id, app_id and value columns only. Select app_id,id,value from data_1; The labels will come from another table DATA_Labels in column Header_Name: <code>APP_ID SEQ HEADER_NAME 1 1 A 1 2 B 1 3 C 1 4 D 1 5 E 1 6 F 1 7 G 1 8 H 1 9 I 1 10 J 2 1 P 2 2 Q 2 3 R 2 4 S 2 5 T 2 6 U 2 7 V 2 8 W 2 9 X 2 10 Y</code> So, for labels we would use header_names. ANd Data_1 and Data_Labels could be joined on basis of app_id. In case the values exceed column headers, we can assume that the headers would reach say maximum 20. And in case the header/name is not available, it could put some default header name like COL11,COL12 if the value stretches upto there. I did lot of research but most solutions are too complex and confusing. Any leads would be appreciated.

Hi Tom, a fan of your work. Have a question: are there any operations in Oracle preserving row order? For example, can I expect that <code> select * from (select tag from test order by tag) </code> Will return in sorted order? Or if a pipelined table function produces a dozen rows in certain order, can I use "select * from table(f())" to see them in the same order? Will a cursor read rows from a pipelined function in the same order they are piped? Basically, looking for exceptions to the general rule "any operation destroys row order".

I have a clob column that as different tags in it, like the example below, I am trying to get the comments tag of all the rows, one of them is returning null, I am assuming it is because it has the word "comments" more than once, this is the query I am using: <b>select d.d1_activity_id, dbms_lob.substr(d.bo_data_area, dbms_lob.getlength(d.bo_data_area), 1) as DCLOB, extractValue(xmlparse(contentt d.bo_data_area),'comments' ) AS comnt from d1_activity d where dbms_lob.instr(d.bo_data_area,'comments') > 0 </b> This is an example of the data we have in that column: <code><comments>C2M Test Exchange Meter</comments><instructions>C2M Test Exchange Meter</instructions><replyToExternalSystem>D1YS</replyToExternalSystem><retryDetails><numberOfRetries>0</numberOfRetries><isToDoEntrySuppressed>false</isToDoEntrySuppressed></retryDetails><contactDetails/><connectNewDevice>D1CN</connectNewDevice><oldDeviceId>061840493997</oldDeviceId> <isFieldActivityCompleted>D1NO</isFieldActivityCompleted><isAppointmentNecessary>N</isAppointmentNecessary><appointmentWindow/><comments>C2M Test for M-Exchange Orch to PragmaCad</comments><instructions>C2M Test for M-Exchange Orch to PragmaCad</instructions><isMeasurementFound>D1NO</isMeasurementFound><replyToExternalSystem>D1YS</replyToExternalSystem><retryDetails><numberOfRetries>0</numberOfRetries><isToDoEntrySuppressed>false</isToDoEntrySuppressed></retryDetails><allowParentTransition>true</allowParentTransition><overrideRestrictions>D1NA</overrideRestrictions><fieldWorkSystemAddress><address1>3456 BOWDEN CIR W</address1><address4>15305034560000&gt;&lt;193954</address4><crossStreet>6249</crossStreet><city>JACKSONVILLE</city><county>DUVAL</county><postal>32216</postal><country>USA</country><state>FL</state><geocodeLatitude>0.000000</geocodeLatitude><geocodeLongitude>0.000000</geocodeLongitude></fieldWorkSystemAddress><contactDetails/> <updateSpecificActivity>D1YS</updateSpecificActivity><updateableItems><comments>Editing comments</comments><instructions>Editing comments</instructions><startDateTime>2020-10-27-00.00.00</startDateTime></updateableItems><isAppointmentNecessary>N</isAppointmentNecessary><appointmentWindow/><allowParentTransition>true</allowParentTransition><replyToExternalSystem>D1YS</replyToExternalSystem><retryDetails><numberOfRetries>0</numberOfRetries><isToDoEntrySuppressed>false</isToDoEntrySuppressed></retryDetails> </code>

I am building a package procedure that will accept a string of json as the input. In SQL, I can select a single value from the json object, but, using select x into var from ... in pl/sql throws an error: <code> ORA-06550: line 10, column 12: PL/SQL: ORA-19200: Invalid column specification </code> <code> with jdata as ( select treat('{"practitionerId":12345,"gender":"F"}' as json) as jrow from dual ) select j.jrow.practitionerId from jdata j; declare p_input_json varchar2(32767) := '{"practitionerId":12345,"gender":"F"}'; p_practitioner int; begin with jdata as ( select treat(p_input_json as json) as jrow from dual ) select j.jrow.practitionerId into p_practitioner from jdata j; end; / </code> Several hours of searching docs and ye olde internet, I cannot find a method of extracting scalars from json in pl/sql. Can you point me in the right direction?