Tom Kyte

I understand how to use the online pragma before the invocation of a subprogram that should be inline, but based on the Oracle documentation (https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html#GUID-2E78813E-CF29-409D-9F8B-AA24B294BFA2) it should also be possible to use pragma inline to affect every invocation of the specified subprogram: <i>When the INLINE pragma immediately precedes a declaration, it affects: Every invocation of the specified subprogram in that declaration</i> Unfortunately when trying to do so it seems as if this would not work as expected when reading the documentation: <code> ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL'; CREATE OR REPLACE PACKAGE foo IS PROCEDURE bar; END foo; / CREATE OR REPLACE PACKAGE BODY foo IS PRAGMA INLINE (sub, 'YES'); PROCEDURE sub; PROCEDURE bar IS BEGIN dbms_output.put_line('bar'); sub; END bar; PROCEDURE sub IS BEGIN dbms_output.put_line('sub'); END sub; END foo; / SELECT * FROM user_errors; </code> PLW-05011: pragma INLINE for procedure 'SUB' does not apply to any calls

1) I am using oracle xe 21c database, apex 24.1 and deployed on tomcat 9.0.90 on my labtop 2) I try to connect my office server using Rest enabled sql service. Where I rest enabled entire schema 3) On my labtop when I try to connect it giving me error : The URL of this remote server does not point to a REST Enabled SQL service. Please check the details of your REST Enabled SQL service definition. 4) When I write same url in browser and ending with table name it showing the data. 5) Before 24.1 menas in 18.x to 20.x its working. 6) For rest enabled sql require same version of ords both side? Please guide because both side apex version and ords version are different (my labtop and my office server I am try using remotely ) 7) At my office I rest enabled schema from sql workshop->restfull service also run the following scripts BEGIN ORDS.enable_schema( p_enabled => TRUE, p_schema => 'SVM', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE ); COMMIT; END; / PLEASE REFERE THE ATTACHED VIDEO https://drive.google.com/file/d/1VONL-ngqD53xjnxKLWSzob63qtD-jSdy/view?usp=sharing same case appear on apex.oracle.com workspace :[redacted] username:[redacted] password :[redacted] Application id :[redacted] Rest enabled SQL service name : svm

Could you please kindly check and advise on the following. i need to find break hours between two dates. and also i need to find if this break mins is within night hours (10PM to 6AM next day). ex: employee work schedule 11-MAY-2012 16:00:00 and 12-MAY-2012 09:00:00. Break hour that fall between 10PM to 6 AM is as follows 8:00PM to 8:30PM - 1st Break 12:30 Am to 1:00 AM - 2nd Break 5:00AM to 05:30 AM - 3rd break two of the breaks are between 10PM and 6AM next day hence my night hours break time is 60mins. i am using following, but is there any better solution <code> (SELECT COUNT(1) FROM (SELECT TO_CHAR ((:SHIFT_START + ((val*4.5)/24)), 'YYYY-MM-DD HH24:MI:SS') TIME ,VAL FROM (select to_number(val,0) VAL from (select distinct regexp_substr('1,2,3,4,5','[^,]+',1,level) val from dual connect by level <=regexp_count('1,2,3,4,5',',')+1 order by val) WHERE ROWNUM <= ( FLOOR ( ( 24 * ( TO_DATE ( TO_CHAR (:SHIFT_END, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - TO_DATE ( TO_CHAR (:SHIFT_START, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi'))) / 4)) ) ) DT WHERE TO_DATE (DT.TIME,'YYYY-MM-DD HH24:MI:SS') BETWEEN TO_DATE(TRUNC(:SHIFT_START),'YYYY-MM-DD HH24:MI:SS')+22/24 AND TO_DATE(TRUNC(:SHIFT_START),'YYYY-MM-DD HH24:MI:SS')+24/24+6/24 )*0.5 ELSE 0 END NIGHT_BREAK_HOURS</code> Appreciated your kind help in check and advise on this

Hi, In order to improve reading performance of a LOB column in my table, I enable CACHE option for it. But when I check in the buffer cache using gv$bh (I did some SELECT queries on it so it can be cached), I cannot find any entries for my cached LOB. <code>SELECT * FROM gv$bh WHERE lobid = (SELECT object_id FROM dba_lobs lob, dba_objects obj WHERE lob.table_name = 'MY_TABLE' -- it contains only one lob column, so the returned result is unique AND lob.SEGMENT_NAME = obj.object_name AND lob.owner = obj.owner);</code> Is this the way to go? or I m missing something? Thanks.

our Daily job expdp has full=yes and worked OK till last night. last night the expdp log showed: ORA-31693: Table data object "AUDSYS"."AUD$UNIFIED":"SYS_P23021" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-08103: object no longer exists Attempt to manually backup the same using same user created to export full database - fails with: expdp $pcsbackup cluster=NO parallel=1 job_name=expdp_tbl DUMPFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs_%U.dmp LOGFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs.log directory=dir_dbexp exclude=statistics encryption=data_only ENCRYPTION_PASSWORD=***** ENCRYPTION_MODE=DUAL COMPRESSION=DATA_ONLY full=no tables=AUDSYS.AUD\$UNIFIED\:SYS_P23021 Export: Release 19.0.0.0.0 - Production on Wed Jun 19 09:03:27 2024 Version 19.20.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Starting "PCSBACKUP"."EXPDP_TBL": /********@gecdwp_pcsbackup cluster=NO parallel=1 job_name=expdp_tbl DUMPFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs_%U.dmp LOGFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs.log directory=dir_dbexp exclude=statistics encryption=data_only ENCRYPTION_PASSWORD=******** ENCRYPTION_MODE=DUAL COMPRESSION=DATA_ONLY full=no tables=AUDSYS.AUD$UNIFIED:SYS_P23021 ORA-39166: Object AUDSYS.AUD$UNIFIED was not found or could not be exported or imported. ORA-31655: no data or metadata objects selected for job Job "PCSBACKUP"."EXPDP_TBL" completed with 2 error(s) at Wed Jun 19 09:04:08 2024 elapsed 0 00:00:39 tried different combos for table name: AUDSYS.AUD\$UNIFIED:SYS_P23021 -- AND ABOVE - same error expdp $pcsbackup cluster=NO parallel=1 job_name=expdp_tbl DUMPFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs_%U.dmp LOGFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs.log directory=dir_dbexp exclude=statistics encryption=data_only ENCRYPTION_PASSWORD=***** ENCRYPTION_MODE=DUAL COMPRESSION=DATA_ONLY full=no tables=AUDSYS.AUD$UNIFIED:SYS_P23021 Export: Release 19.0.0.0.0 - Production on Wed Jun 19 09:00:37 2024 Version 19.20.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Starting "PCSBACKUP"."EXPDP_TBL": /********@gecdwp_pcsbackup cluster=NO parallel=1 job_name=expdp_tbl DUMPFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs_%U.dmp LOGFILE=expdp_gecdwp_full_2024_0618_222801_failedobjs.log directory=dir_dbexp exclude=statistics encryption=data_only ENCRYPTION_PASSWORD=******** ENCRYPTION_MODE=DUAL COMPRESSION=DATA_ONLY full=no tables=AUDSYS.AUD:SYS_P23021 ORA-39166: Object AUDSYS.AUD was not found or could not be exported or imported. ORA-31655: no data or metadata objects selected for job Job "PCSBACKUP"."EXPDP_TBL" completed with 2 error(s) ...

we see this error in the daily full=Y backup : Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA ORA-39127: unexpected error from call to TAG: SCHEDULER Calling: SYS.DBMS_SCHED_CLASS_EXPORT.GRANT_EXP obj: SYS.IDX_RB$CLASS_54724 - SCHEDULER CLASS ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 2601 ORA-06512: at "SYS.DBMS_SCHED_CLASS_EXPORT", line 41 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 2601 ORA-06512: at "SYS.DBMS_SCHED_CLASS_EXPORT", line 41 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 11144 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER But, when we checked ( as sysdba) - this object dosn't exist in the container that it runs: SYS.IDX_RB$CLASS_54724 no changes were made to the database/ shell script that runs the export datapump backup. we had seen the same issue on 10th june 2024, but without any changes the next days export backups ran good. yesterday - again the same error is seen. not sure what could be causing it - here is the export command that we use for daily backup: ( the $vars are location/filenames generated in script) expdp $pcsbackup job_name=${jobname}\ directory=DIR_DBEXP\ dumpfile=${dmpfile_name}\ logfile=${logfile_name}\ full=y\ cluster=no \ parallel=4\ exclude=STATISTICS\ exclude=SCHEMA:\" IN \(SELECT USERNAME FROM DBATOOL.EXPDP_EXCLUDE_DWP_BACKUP_TABLES\)\"\ ENCRYPTION=DATA_ONLY \ ENCRYPTION_PASSWORD=<OurProdEncPassowrd> \ ENCRYPTION_MODE=DUAL \ COMPRESSION=DATA_ONLY

Hi There, all the recent news about APEX AI Assistance for code generation ? doe sit comes out of the box ? or we need to configure and pay to AI/LLM provider like ChatGPT ? I am referring to your recent video Build AI-Powered Enterprise Apps Faster with Oracle APEX (https://www.youtube.com/watch?v=qZD8wtn7qoI ) Regards, Dr. Gyana

I need a example using DBMS_CRYPTO.SIGN. I'm need to sign a hash using RSA-SHA1 and I need to know if can i use DBMS_CRYPTO.SIGN?

Dear Tom, I am temporarily supporting a RAC, ASM database on 19c. I have to create a new tablespace. For the existing tablespaces I see that datafile names look like +IPAG_DATA/t5cnp1db/datafile/avos_dat.362.1067066741 I understand that +IPAG_DATA is the asm diskgroup name. t5cnp1db is database sid and avos_dat stands for the AVOS application whose data resides on this tablespace/datafile. What are the numbers 362 and 1067066741 ? for the new tablespace, I want to follow the same pattern, I can use my datafile name as +IPAG_DATA/t5cnp1db/datafile/odi_dat but how to get those numbers on my new datafile? Thank you for your help!

declare cursor cur_collection_detail is select rowid,'ABC' col4, 'ABC' col1, 'ABC2' action_id from TAB1 where COL1= 1286165; begin for dtl in cur_collection_detail loop dbms_output.put_line('Test inprogress..'); end loop; end; / Error report - ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind ORA-06512: at line 5 ORA-06512: at line 5 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints. ROWID - length(rowid) *BAAfXwIFxAIdPkIWQ1VSUkVOQ1lfRVhDSEFOR0VfUkFURRdNWE47MjAxOC0wOC0xNjs7Q0FTSEVYMf4 80 *BAAfXwIFxAIdPkIWQ1VSUkVOQ1lfRVhDSEFOR0VfUkFURRdNWE47MjAxOC0wOC0xNjs7Q0hFQ0VYMf4 80

I want to change the column size of a column from varchar2(100) to varchar2(50) on a non-partitioned table with 1138 Million records, yes 1.1 Billion. This project is not using Partitioned tables, so can't change the table structure. Could you please let me know a quicker approach other than alter statement to modify column and dbms_redefinition?

Good Morning, In the last year, I've started to support Mongo databases. Mongo stores data in BSON which is the binary form of JSON. JSON is just the field name followed by a value. This doesn't seem so different from Oracle since Oracle also its data in a series of columns with values. I'm curious to know how an Oracle row looks like. If a table has the following columns: -Fname string -Lname string -notes string If row has say, Fname='John' and Lname='Doe', does Oracle add the field names Fname and Lname to each row? Does the row look like this on disk: Fname='John', Lname='Doe', notes null or does it look like this: 'John','Doe', null My guess is that it looks like option 1. It would be nice if you could also provide what an Oracle row looks like on disk. Thank you John

We have two tables, ITEM table has more than 150 million records and ITEM_EVENT table has more than 400 millions. Because of the growing nature of the data, we want to perform periodic cleanup of the tables. Could not find a performant way to achieve the goal, select query was taking very long and eventually got ORA-01114. CREATED Columns in both tables are not indexed. We can do that if they can help to achieve our goal. So please give us some suggestions to achieve our goal. Thanks. Delete records is planned for: - older than some compliance date - with a batch size of say 50000 per iteration - split the deletion in two steps, delete 1st the foreign key records and then primary keys Our tables DDL: <code> CREATE TABLE "ITEM" ( "ID" VARCHAR2(255 CHAR) NOT NULL ENABLE, "CREATED" TIMESTAMP (6) NOT NULL ENABLE, "ITEM_TYPE" VARCHAR2(255 CHAR) NOT NULL ENABLE, "ITEM_ID" VARCHAR2(255 CHAR) NOT NULL ENABLE PRIMARY KEY ("ID") ) CREATE INDEX "ITEM_ID_NDX" ON "ITEM" ("ITEM_ID") CREATE TABLE "ITEM_EVENT" ( "ID" NUMBER(19,0) NOT NULL ENABLE, "CREATED" TIMESTAMP (6) NOT NULL ENABLE, "ITEM_EVENT_TYPE" VARCHAR2(255 CHAR) NOT NULL ENABLE, "ITEM_BID" VARCHAR2(255 CHAR) NOT NULL ENABLE, "ITEM_STATE" VARCHAR2(255 CHAR), "CHANGE_REASON" VARCHAR2(255 CHAR), "ITEM_ID" VARCHAR2(255 CHAR) NOT NULL ENABLE, PRIMARY KEY ("ID") ) alter table ITEM_EVENT add constraint ITEM_EVENT_FK_ITEM_BID foreign key (ITEM_BID) references ITEM; CREATE INDEX "ITEM_EVENT_BID_NDX" ON "ITEM_EVENT" ("ITEM_BID") CREATE INDEX "ITEM_EVENT_ID_NDX" ON "ITEM_EVENT" ("ITEM_ID") </code> Following query tried which was very slow and causing error: <code> DELETE FROM ITEM_EVENT WHERE ITEM_ID IN ( SELECT ITEM_ID FROM ITEM_EVENT WHERE CREATED < current_timestamp - NUMTODSINTERVAL(180, 'DAY') GROUP BY ITEM_ID HAVING MAX(ITEM_STATE) KEEP (DENSE_RANK LAST ORDER BY CREATED ASC)= 'DEACTIVATED' FETCH FIRST 50000 ROWS ONLY); DELETE FROM ITEM i WHERE NOT EXISTS (SELECT 1 FROM ITEM_EVENT ie WHERE ie.ITEM_BID = i.ID) AND CREATED < current_timestamp - NUMTODSINTERVAL(180, 'DAY'); </code>

Hi TOM: I have to copy an AS400 table, for that i have a DBLINK that connects the as400 database to my Oracle 11g Since it has several millions records, i tried with a bulk collect: <code> CREATE TABLE AS400_VPRA_ABONO ("ABON_NUM_CCTE" NUMBER(9,0) NOT NULL ENABLE, "ABON_FEC_COMPR_PAG" NUMBER(9,0) NOT NULL ENABLE, "ABON_CORR_COMPR" NUMBER(3,0) NOT NULL ENABLE, "ABON_CORRELATIVO" NUMBER(3,0) NOT NULL ENABLE, "ABON_FEC_CPBTE_ING_EGR" NUMBER(9,0) NOT NULL ENABLE, "ABON_TIPO_REND" NUMBER(2,0) NOT NULL ENABLE, "ABON_NUM_CPBTE_ING_EGR" NUMBER(8,0) NOT NULL ENABLE, "ABON_TIPO_COMPR" NUMBER(1,0) NOT NULL ENABLE, "ABON_TIPO_AVISO" NUMBER(2,0) NOT NULL ENABLE, "ABON_NUM_AVISO" NUMBER(8,0) NOT NULL ENABLE, "ABON_LINEA" NUMBER(5,0) NOT NULL ENABLE, "ABON_TIPO_ABONO" NUMBER(2,0) NOT NULL ENABLE, "ABON_TIPO_VIA" NUMBER(1,0) NOT NULL ENABLE, "ABON_RECAUDADOR" NUMBER(5,0) NOT NULL ENABLE, "ABON_MTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE, "ABON_MTO_PAG_PESOS" NUMBER(13,2) NOT NULL ENABLE, "ABON_FEC_PAGO" NUMBER(9,0) NOT NULL ENABLE, "ABON_MEDIO_PAGO" NUMBER(1,0) NOT NULL ENABLE, "ABON_AREA" CHAR(16 BYTE) NOT NULL ENABLE, "ABON_BCO_ADM" CHAR(10 BYTE) NOT NULL ENABLE, "ABON_MTO_DEV_MON" NUMBER(13,2) NOT NULL ENABLE, "ABON_MTO_PAG_MON_AJ" NUMBER(13,2) NOT NULL ENABLE, "ABON_MTO_PAG_PESOS_AJ" NUMBER(13,2) NOT NULL ENABLE, "ABON_MOTIVO" NUMBER(3,0) NOT NULL ENABLE, "ABON_SALDO" NUMBER(13,2) NOT NULL ENABLE, "ABON_STATUS" NUMBER(2,0) NOT NULL ENABLE, "ABON_STA_FACTUR" CHAR(1 BYTE) NOT NULL ENABLE, "ABON_EXENTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE, "ABON_AFECTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE, "ABON_DEREMI_PAG_MON" NUMBER(9,2) NOT NULL ENABLE, "ABON_IMPTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE, "ABON_TIPDOC" CHAR(2 BYTE) NOT NULL ENABLE, "ABON_NUMDOC" CHAR(8 BYTE) NOT NULL ENABLE, "ABON_FILLER" CHAR(14 BYTE) NOT NULL ENABLE ); CREATE TABLE AS400_VPRA_ABONO_ORIGIN ("ABON_NUM_CCTE" NUMBER(9,0) NOT NULL ENABLE, "ABON_FEC_COMPR_PAG" NUMBER(9,0) NOT NULL ENABLE, "ABON_CORR_COMPR" NUMBER(3,0) NOT NULL ENABLE, "ABON_CORRELATIVO" NUMBER(3,0) NOT NULL ENABLE, "ABON_FEC_CPBTE_ING_EGR" NUMBER(9,0) NOT NULL ENABLE, "ABON_TIPO_REND" NUMBER(2,0) NOT NULL ENABLE, "ABON_NUM_CPBTE_ING_EGR" NUMBER(8,0) NOT NULL ENABLE, "ABON_TIPO_COMPR" NUMBER(1,0) NOT NULL ENABLE, "ABON_TIPO_AVISO" NUMBER(2,0) NOT NULL ENABLE, "ABON_NUM_AVISO" NUMBER(8,0) NOT NULL ENABLE, "ABON_LINEA" NUMBER(5,0) NOT NULL ENABLE, "ABON_TIPO_ABONO" NUMBER(2,0) NOT NULL ENABLE, "ABON_TIPO_VIA" NUMBER(1,0) NOT NULL ENABLE, "ABON_RECAUDADOR" NUMBER(5,0) NOT NULL ENABLE, "ABON_MTO_PAG_MON" NUMBER(13,2) NOT NULL ENABLE, "ABON_MTO_PAG_PESOS" NUMBER(13,2) NOT NULL ENABLE, "ABON_FEC_PAGO" NUMBER(9,0) NOT NULL ENABLE, "ABON_MEDIO_PAGO" NUMBER(1,0) NOT NULL ENABLE, "ABON_AREA" CHAR(16 BYTE) NOT NULL ENABLE, "ABON_BCO_ADM" CHAR(10...

Hi TOM, Not long ago it was easy to determine if your DB is on premise or on cloud: their banners (from v$VERSION) were different. Now Entreprise and Standard original editions can be on premise or on cloud so we can no more determine in which environment we are just looking at the banner. Starting with 21c, V$PDBS contains a CLOUD_IDENTITY column which is not null if you are on cloud. So my question, in 12.2 to 19c, how to know, using SQL, if my oracle DB is on premise or cloud? Bonus: how to know if it is OCI (Oracle Cloud Infrastructure) or ACE (Authorized Cloud Environment) or even neither (and unsupported)? Regards Michel

From the following table , I want to build a query using analytical functions to retrieve the rows where there is a common document type for a Customer ID but not the mix. The output should be only ROW_NO 3,4,5 & 9,10 CREATE TABLE TAB_DOC_TYPES (ROW_NO NUMBER, CID NUMBER, DOC_TYPE VARCHAR2(5) ); INSERT INTO TAB_DOC_TYPES VALUES(1,101,'D1'); INSERT INTO TAB_DOC_TYPES VALUES(2,101,'DZ'); INSERT INTO TAB_DOC_TYPES VALUES(3,102,'DZ'); INSERT INTO TAB_DOC_TYPES VALUES(4,102,'DZ'); INSERT INTO TAB_DOC_TYPES VALUES(5,102,'DZ'); INSERT INTO TAB_DOC_TYPES VALUES(6,103,'D1'); INSERT INTO TAB_DOC_TYPES VALUES(7,103,'DZ'); INSERT INTO TAB_DOC_TYPES VALUES(8,103,'DZ'); INSERT INTO TAB_DOC_TYPES VALUES(9,104,'DZ'); INSERT INTO TAB_DOC_TYPES VALUES(10,104,'DZ'); ROW_NO CID DOC_TYPE 1 101 D1 2 101 DZ 3 102 DZ 4 102 DZ 5 102 DZ 6 103 D1 7 103 DZ 8 103 DZ 9 104 DZ 10 104 DZ Here CID 101 & 103 has both D1 and DZ and query output shouldn't bring these records. CID 102 & 104 has only one document type DZ and the query output should be bringing these record only.

I encountered a problem related to forcing the refresh procedure on the materialized view in a combined manner: - refresh_method = 'F' - out_of_place = true <code>DBMS_MVIEW.REFRESH('FOO_MV', out_of_place => true, atomic_refresh => false, method => 'F');</code> For the past few days, I have made many different attempts and tests to force a situation in which MV is refreshed using a combination of: refresh-method = FAST and out-of-place = TRUE but only succeeded in achieving the combinations: refresh-method = COMPLETE and out-of-place = TRUE refresh-method = FAST and out-of-place = FALSE Therefore, my main question is: <b>Are there any internal restrictions or conditions that must be met in order to perform FAST out-of-place refresh?</b> Because after reviewing the official documentation: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dwhsg/refreshing-materialized-views.html#GUID-51191C38-D52F-4A4D-B6FF-E631965AD69A I have not found anything that would prevent such a combination from succeeding in my case. It is even clearly stated that out-of-place should work with any refresh method, with FAST preferred first. Below I attach a script setting up and demonstrating the problem I am facing. Due to limited privileges in the LiveSQL tool, I recommend using the script on a local database <code> -- Clean Workspace DROP TABLE FOO; DROP MATERIALIZED VIEW FOO_MV; -- Create the Base Table FOO CREATE TABLE FOO ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100), product_price NUMBER(10, 2) ); -- Insert Sample Data into FOO table INSERT INTO FOO (product_id, product_name, product_price) VALUES (1, 'Widget A', 19.99); INSERT INTO FOO (product_id, product_name, product_price) VALUES (2, 'Gizmo B', 29.99); COMMIT; -- Create Materialized View Log CREATE MATERIALIZED VIEW LOG ON FOO WITH ROWID, PRIMARY KEY, SEQUENCE; -- Create simple Materialized View CREATE MATERIALIZED VIEW FOO_MV BUILD DEFERRED REFRESH FAST ON DEMAND AS SELECT product_id, product_name, product_price FROM FOO; -- Drop PK on MV prebuilt table to meet out-of-place refresh requirements ALTER TABLE FOO_MV DROP PRIMARY KEY; -- Enable Advanced statistics collection EXEC DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS ('FOO_MV','ADVANCED',30); -- Intial COMPLETE refresh of the Materialized View (out-of-place) EXEC DBMS_MVIEW.REFRESH('FOO_MV', out_of_place => true, atomic_refresh => false, method => 'C'); -- Insert incremental sample data into FOO table INSERT INTO FOO (product_id, product_name, product_price) VALUES (3, 'Gadget X', 49.99); INSERT INTO FOO (product_id, product_name, product_price) VALUES (4, 'Widget B', 24.99); COMMIT; -- Incremental FAST refresh of the Materialized View (HERE IS THE PROBLEM => despite the fact that out-of-place flag is true, the MV is refreshed in-place) EXEC DBMS_MVIEW.REFRESH('FOO_MV', out_of_place => true, atomic_refresh => false, method => 'F...

Hi, <b>There are several examples of hierarchal queries using the employees-manager example. Recently i came across hierarchal scenario where the table was storing multiple hierarchies. example data: </b> <code>CREATE TABLE example ( Title VARCHAR2(50), ID NUMBER, Link_id NUMBER );</code> <code>INSERT INTO your_table_name (Title, ID, Link_id) VALUES ('A', 1, NULL); INSERT INTO example (Title, ID, Link_id) VALUES ('B', 2, 1); INSERT INTO example(Title, ID, Link_id) VALUES ('C', 3, 2); INSERT INTO example (Title, ID, Link_id) VALUES ('D', 4, 3); INSERT INTO example (Title, ID, Link_id) VALUES ('E', 5, NULL); INSERT INTO example (Title, ID, Link_id) VALUES ('F', 6, 5); INSERT INTO example (Title, ID, Link_id) VALUES ('G', 7, 6); INSERT INTO example (Title, ID, Link_id) VALUES ('H', 8, NULL); INSERT INTO example (Title, ID, Link_id) VALUES ('I', 9, NULL); INSERT INTO example (Title, ID, Link_id) VALUES ('J', 10, 9);</code> Title |ID |Link_id A | 1 | null B | 2 | 1 C | 3 | 2 D | 4 | 3 E | 5 | null F | 6 | 5 G | 7 | 6 H | 8 | null I | 9 |null J | 10 | 9 and i wanted retrieve the whole hierarchy given any node i.e. passing ID 3 should return: A B C D i wrote the following function to get the root id: <code>create or replace FUNCTION find_root( p_id IN example.id%TYPE ) RETURN example.id%TYPE AS v_given_id example.id%TYPE := p_id; v_root_id example.id%TYPE; BEGIN LOOP SELECT Link_id INTO v_root_id FROM example WHERE id = v_given_id; IF v_root_id IS NULL THEN EXIT; -- Exit the loop if Link_id is null ELSE v_given_id := v_root_id; -- Update v_given_id with Link_id END IF; END LOOP; RETURN v_given_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; -- Return NULL if no record found for the given id WHEN OTHERS THEN RETURN NULL; -- Handle other errors by returning NULL END;</code> <b>The following stored procedure will then call the above function and return the hierarchy based on the id returned by the function:</b> <code>create or replace PROCEDURE get_hierarchy( p_given_id IN example.id%TYPE, hmm OUT SYS_REFCURSOR ) IS v_root_id example.id%TYPE; BEGIN SELECT find_root_id(p_given_id) INTO v_root_id FROM dual; -- Check if final case ID is not null IF v_final_case_id IS NOT NULL THEN -- Use explicit cursor declaration OPEN hmm FOR SELECT Title, ID, Link_id from example START WITH example.id = v_root_id CONNECT BY PRIOR example.id = example.link_id; ELSE -- Use RAISE_APPLICATION_ERROR for customized error messages RAISE_APPLICATION_ERROR(-20001, 'No record foun...

We are writing to discuss an operational challenge that we are currently facing with the integration of Oracle Integration Cloud (OIC) and Oracle Autonomous Database within our organization. Our setup utilizes OIC as the primary integration layer, in conjunction with the Autonomous Database for staging data, performing validations, and executing other data derivation tasks. Our infrastructure includes multiple OIC environments, each configured to connect to the same Autonomous Database but utilizing distinct database schemas. These schemas primarily contain custom tables and packages essential for enforcing our business rules. During the integration process, particularly when invoking subroutines, it is necessary to specify the database name along with the package or procedure name, as detailed in the Oracle documentation (https://docs.oracle.com/en/cloud/paas/integration-cloud/atp-adapter/invoke-stored-procedure-page.html). We encounter significant challenges when migrating integrations between different OIC environments due to the requirement of manually updating the schema name in each database activity to match the target environment's schema. This process is not only time-consuming but also prone to errors, impacting our efficiency and operational continuity. In previous discussions with the Oracle Support team, the suggestion was made to utilize separate databases with identical schema names to circumvent this issue. However, due to resource constraints, expanding beyond our current setup of one database for production and another for non-production environments is not feasible. Given these circumstances, we are reaching out to inquire if there might be an alternative solution or workaround that could facilitate a more streamlined migration process between OIC environments without the need for manual updates. Any suggestions or guidance you could provide would be greatly appreciated

Hi, I want to create a stored procedure / function which will call shell script and shell script will have command to copy the file from particular location of DB server to another location of DB server. I tried using the scheduler job same is working fine but i don't want to use scheduler job. I want to use procedure/function to call shell script. Request your help in how to call shell script in stored procedure/function. Regards GirishR