Tom Kyte

Hi, I discovered the powerful, not officially documented, hint OPT_ESTIMATE about 12 years ago. Recently I discovered the following officiel document about it: Oracle Support Document 2402821.1 (How To Use Optimizer Hints To Specify Cardinality For Join Operation) My question: is it now a documented hint or should it still not be used in SQL code? Regards Maurice

Hello, I'd like to write a query based on dba_source/all_identifiers/all_dependencies to find all the queries/cursors that have more than x joins involved. Can you help me ? Regards Hans

We have a unique auditing requirement where we want to know all updates to the system by a 5 digit terminal no for a given time period. Each user input coming through the legacy network always has an associated 5 digit terminal no. This user input will be processed by WebLogic application server which may do lots of SQL updates to 10g RAC database. We know that these SQL updates will be logged by Oracle in Redo and Archive logs, which can be mined using Log Miner by giving transaction id (XIDUSN + XIDSLT + XIDSQN). So we were thinking of creating a table that can relate 5 digit terminal no to a series of oracle transaction id like the following: TerminalNo timestamp XIDUSN XIDSLT XIDSQN ---------- --------- ------ ------ ------ However, in order to achive the above, We will need the transaction id of the current transaction (XIDUSN + XIDSLT + XIDSQN) before we commit all updates. Each transaction in the system will insert a row in this table just before commiting, which will enable us to get all updates by a terminal no for a given period. So, how can I get the current transaction id of the transaction? Do you have any other idea for implementing same functionality. Thanks

I would like to know your advice about migrating a database from WE8ISO8859P15 to AL32UTF8. We have a DB running WE8ISO8859P15 properly with a lot of applications, but a client suggests that it would be a good idea to change it so it is the same in their Java applications. These applications are not multilingual and they will never be, so do you think it would have some benefit? or won't be a good idea because of space consumption or other issues?

Hi, In below article https://oracle.readthedocs.io/en/latest/sql/basics/query-processing-order.html It is written that the order in which clauses are logically processed by Oracle is as follows: FROM -> CONNECT BY -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY In this order of execution when will analytical functions and MODEL clause will be applied. Is it applied after SELECT ? or how these will be executed ? Thanks, Girish

We have report grids showing 2 or more (maybe 6) columns of "Who" did what. To fetch and show their real name based on username: Is it better performance to have a subquery for each "Who" column or just return the username in the query column, and in that column Settings, select Based On 'Display Value List of Values' and have a Shared Component fetching all of the users? Grid query: <code> select <other things>, (select first_name || ' ' || last_name from security_users where user_name = vpd.Added_By ) as ADDED_BY, (select first_name || ' ' || last_name from security_users where user_name = vpd.Submitted_By ) as SUBMITTED_BY from VPD </code> OR <code> select <other things>, Added_By, SUBMITTED_BY from VPD </code> with each "Who" column using a Shared Component: <code> select First_Name||' '||Last_Name as Display_Val, USER_NAME AS Return_Val From security_users </code> When using Shared LOV, one thing I notice is that selecting the grid header shows a list of every user name returned from the Shared LOV for Filtering, even though they are not all in the grid results. Currently the number of users (the lookup table) are about 300. Same question but with a lookup table of say 10,000 rows.

Respected sir/Mem i am new in oracle database with apex can you please help me i want to insert data into table with Where clause, is it possible in oracle ?

Hi, When I start the ords server file I am getting following error (ORA-12514) TNS:Listner does not currently know of service requested in connect descriptor. I have tried connecting using SQL PLUS as sys SQL> conn/ as sysdba Connected to an idle instance. SQL> shutdown immediate Erro: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist I have tried STARTUP command SQL> startup ORA-00093 : pga_aggregate_limit must be between 6000M and 100000G ORA-01078 : failure in processing system parameters I have set my pga_aggreagte_limit to 4G

Hello, The below query does not return any value when the value of the counter is equal or greater than 110000000. NOTE: It works well for 6100 or 19 000 000 as example. <code>select to_char(to_number(counter)) from (select level counter from dual connect by level <= 120000000) where counter =(SELECT SUM(peevf.screen_entry_value) FROM pay_element_entries_f peef, pay_element_entry_values_f peevf, pay_input_values_f pivf, pay_sub_classn_rules_f pscrf, pay_ele_classifications_tl pect WHERE peef.element_entry_id = peevf.element_entry_id AND peef.effective_start_date BETWEEN TO_DATE('01/01/2021','DD/MM/YYYY') AND TO_DATE('31/12/2021','DD/MM/YYYY') AND peef.person_id = <REPLACE with PersonID> AND peevf.input_value_id = pivf.input_value_id AND pivf.base_name = 'Amount' AND peef.element_type_id = pscrf.element_type_id AND pscrf.classification_id = pect.classification_id AND pect.LANGUAGE = 'US' AND pect.classification_name = 'TOTAL_VARIABLE_COMPENSATION')</code> Is this a limitation from the "select level counter from dual connect by level" ? NOTE: to replace with personID in the query: <REPLACE with PersonID>

Howdy, Just curious what (if any) deleterious effects we would see in the standby databases in an active data guard set up from switching redo logs every 2-3 minutes on the primary? I?m also curious what would be the best diagnostic methods to troubleshoot issues on read only standby databases. Thanks !

I'll summarise this query with a (hopefully easy to follow) scenario: We have two tables: TableOne TableTwo Both have identically defined ID columns ("ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE). We perform these actions: We insert a row into each. TableOne's highest ID is now 28. TableTwo's highest ID is now 1620. We commit. We wait four months. We insert a new row into each. TableOne skips several IDs (ie. the sequence goes from 28 directly to 41) TableTwo does not skip any IDs (ie. the sequence goes from 1620 to 1621) And my question is, why do the two tables behave differently?

Hi Experts, I am fairly new to Oracle Apex. My req was basically to make a OCI instance with a program which lets me upload files to it. I was able to succesfully do that, but then i needed to make a Simple UI. I was asked to use oracle Apex for that. Thinking it worked easily in postman i tried to implement it with the help of chat gpt and a few mentors. but no matter what we do. it does not work. i know im not providing clarity but i dont know what else to say. Without much knowledge in PL/SQL, I have created a simple code to upload the file. which i will post here. My OCI program keeps giving me errors regarding the headers and 'Required part 'File' is missing'. It should be a minor error and i will appreciate any help. incase u need more data, feel free to ask <code> DECLARE l_response CLOB; l_file_content CLOB; BEGIN l_file_content := :P8_FILE; -- apex_web_service.g_request_headers(1).name := 'Content-Type'; -- apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded'; apex_web_service.g_request_headers(1).name := 'Content-Type'; -- apex_web_service.g_request_headers(2).Value := 'multipart/form-data'; apex_web_service.g_request_headers(1).Value := 'multipart/form-data; boundary=' || 'APEX' || DBMS_RANDOM.STRING('X', 10); l_response := apex_web_service.make_rest_request( p_url => 'http://<MY_IP_ADDRESS>:8080/uploadfile', p_http_method => 'POST', -- p_body => 'file='||l_file_content p_body => 'file=' || UTL_RAW.CAST_TO_VARCHAR2(Utl_encode.text_encode( UTL_FILE.FREAD(UTL_FILE.FOPEN('UPLOAD_DIRECTORY', 'DataLoader.xlsx', 'R')), 'UTF-8', Utl_encode.BASE64)) || '&other_param=value' ); -- Process the response or set a page item with the response :P8_RESPONSE := l_response; EXCEPTION WHEN OTHERS THEN -- Handle errors :P8_RESPONSE := 'Error at file upload: ' || SQLERRM; END; </code> NOTE: i have hidden my ip address. I apologize for any mistakes or improper way in communication, im not very social. Please consider

I want to create an editable grid using sql query. Like this https://drive.google.com/file/d/1ubj4zPNuIyt3iC1VJBEB1k1OqIYeJlJp/view?usp=sharing in the rows there are files and in the columns there are the package names. If we tick a cell then the file will be included in the corresponding package. For example in the image above if I tick the cell corresponding row test1.txt and column 22000 then test1.txt file will be included in the package 22000. Similarly if we untick any cell then the file will be removed from the package. I have files and packages table. I have tried an interactive report for pivoting but there I am not able to edit the cell.(Tick and untick can be done using 1 and 0 too where 1 means tick, 0 means untick). And if i use an interactive grid then i am able to edit the cell but i am not able to pivot. Any guidance will be highly appreciated. Thanks Hritik

I am running a PL/SQL and during its execution the connection to the database simply disappears leaving the process unfinished. There are no error messages that make me think PL/SQL failed, it just stops. Checking the session browser I see that it is in ACTIVE status and at some point it simply disappears. Do you know what may be causing this abrupt disconnection?

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Sep 1 20:34:56 2023 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Enter user-name: SYSTEM/sys ERROR: ORA-12560: TNS: protocol adapter error Enter user-name: I Started Oracle Service no Use. Can anyone help me? To find solution.

I have written PL/SQL code to store XML into cursor. Refer the below code Requirement: As of line at line 83, I am inserting the result into another table (XML_GEN) as XMLTYPE datatype. Line: insert into xml_gen (fxdoc) values (i.FXML); But I don't want to that, I need this result to get stored as file on Oracle DB Server. (each row to be stored as a file .xml) I have searched on google, but I am unable to do it. Can someone provide suggestion or guidance, how I can achieve it. I have attached the file TableDetail.xls (export of data) and PL/SQL Code I am using. Below code and table are available at below link as well https://drive.google.com/drive/folders/1d7VPlY0ooR8jr3Xrvd1la1BiEIwFrEhv?usp=drive_link Code: <code> declare cursor c_fdoc is SELECT id, XMLElement("SDPSyncMessage", XMLAttributes('http://www.emeter.com/energyip/syncinterface/v8' AS "xmlns"), XMLElement("header", XMLElement("verb",'SDPSync'), XMLElement("noun",'SDPSync'), XMLElement("revision",'1'), XMLElement("dateTime",SYSTIMESTAMP), XMLElement("source",'CIS'), XMLForest(loc_badge "messageID"), XMLElement("syncMode",'sync'), XMLElement("optimizationLevel",'Optimistic') ), XMLElement("payload", XMLElement("servicePoint", XMLForest(loc_badge "mRID"), XMLElement("type",'ServiceDeliveryPoint'), XMLElement("serviceType",'Electric'), XMLElement("className",'Electric'), XMLElement("status",'Active'), XMLElement("premise", XMLForest(phx_loc "mRID")), XMLElement("servicePointStatus", XMLElement("powerStatus",'Y'), CASE WHEN TO_DATE IS NOT NULL THEN XMLElement("loadStatus",'N') ELSE XMLElement("loadStatus",'Y')END, XMLElement("billingHoldStatus",'N'))), XMLElement("device", XMLForest(phx_badge "mRID", modeltype "model"), XMLElement("className",'Electric'), XMLElement("status",'Installed'), XMLElement("type",'Meter'), XMLElement("deviceFunctionType",'N'), XMLElement("parameter", XMLElement("name",'Configuration ID'), XMLForest(CONFIGURATIONID "value", EFFECTIVE_DATE "startDate")), XMLElement("parameter", XMLElement("name",'Program ID'), XMLForest(PROGRAMID "value", EFFECTIVE_DATE "startDate"))), ...

Oracle 19c, Primary and Active Dataguard standby database. Not having luck to automatically change the session parameter STANDBY_MAX_DATA_DELAY when a user logs in to the database: We want users connected in read only mode to the Standby DB to get an error when synchronization lag is greater than a certain threshold (5min). Tried this logon trigger created on primary and replicated to standby: <code>CREATE OR REPLACE TRIGGER STANDBY_MAX_DATA_DELAY AFTER LOGON ON database WHEN (SYS_CONTEXT('USERENV','DATABASE_ROLE') in ('PHYSICAL STANDBY') and SYS_CONTEXT('USERENV','SESSION_USER')!='SYS' and SYS_CONTEXT('USERENV','SERVICE_NAME')='serv_name.sn.vcn.oraclevcn.com') BEGIN execute immediate 'ALTER SESSION SET STANDBY_MAX_DATA_DELAY=300'; END;</code> However, whenever a non SYS user connects to the standby using the service specified, the trigger executes and fails with this error: <code>ORA-04088: error during execution of trigger 'SYS.STANDBY_MAX_DATA_DELAY' ORA-00604: error occurred at recursive SQL level 1 ORA-03174: STANDBY_MAX_DATA_DELAY does not apply to SYS users ORA-06512: at line 204088. 00000 - "error during execution of trigger '%s.%s'" *Cause: A runtime error occurred during execution of a trigger. *Action: Check the triggers which were involved in the operation. Vendor code 4088</code> By testing different ways and parameters to use the logon trigger, I concluded the trigger is always doing the execute immediate as SYS, no matter who is logging in. I also tried changing the CURRENT_SCHEMA session parameter on the same ALTER SESSION statement but that does not work, the session needs to change or some variable needs to be set to target the SESSION_USER environment to change the parameter.

Hi Tom, we run an application that writes thousands of inserts per minute to a table. The database runs in a 2-node RAC. This insert generates a very high system load due to the global block wait events, since the block into which an insert is to take place is potentially currently in use at the other instance. And even if this block belongs to the current instance, there are index blocks that are potentially not active on the instance performing the insert. In particular, how can we speed up the insert so that the global block waits evens are reduced? Thanks a lot! Kay.

Hi All, I am a new developer to Oracle SQL and PL/SQL. I have the below scenario, if you could please help me find the most optimal solution for this I would be very grateful. If there is any other thread already which shows the solution to similar scenario please let me know. Thanks in advance! I have a Table with the below set of Rows (Link for Create table and Insert data into table present in the LiveSQL link). Post Insertion table will look as shown below: ---------------------------------------------- <code>Asset_ID Item_Name Category Vendor Location_ID Unit_Price Quantity Parent_Or_Child -------- --------- -------- ------ ----------- ---------- -------- ---------------- 100.000 CPU Hardware 10001 12345 1000 1 Parent 100.100 Mouse Hardware 10001 12345 50 2 Child 100.100 Keyboard Hardware 10001 12345 100 1 Child 100.100 OS Software 10001 12345 200 1 Child 100.100 Assemble Install 10001 12345 150 1 Child 100.100 Shipping Freight 10001 12345 100 1 Child 101.000 Laptop Hardware 10002 14567 800 1 Parent 101.101 Mouse Hardware 10002 14567 50 2 Child 101.101 Keyboard Hardware 10002 14567 100 1 Child 101.101 OS Software 10002 14567 200 1 Child 102.000 CPU Hardware 10001 12345 1000 1 Parent 102.102 Mouse Hardware 10001 12345 50 2 Child 102.102 Keyboard Hardware 10001 12345 100 1 Child 102.102 OS Software 10001 12345 200 1 Child 102.102 Assemble Install 10001 12345 150 1 Child 102.102 Shipping Freight 10001 12345 100 1 Child 103.000 Laptop Hardware 10001 12345 1000 1 Parent 103.103 Mouse Hardware 10001 12345 50 2 Child 103.103 Keyboard Hardware 10001 12345 100 1 Child 103.103 OS Software 10001 12345 200 1 Child 103.103 Assemble Install 10001 12345 150 1 Child 103.103 Shipping Freight 10001 12345 100 1 Child</code> Any row having an Asset ID ending with .000 is a Parent and items ending with .100, .101 etc. are the child records. So in the above data set, 100.000 is the Parent and 100.100 are its Child records. There is also another column 'Parent_Or_Child' which identifies whether the row is a Parent or Child. The Asset_Id column indicates the row is a child of which parent (ex 100.100 is a child of 100.000, 101.101 is a child of of 101.000, etc.). All this setup is done by another system and we get the table with these details filled. There will be only one level of Parent - Child, i.e. a child wont have another Child record of its own. The requirement is to combine multiple Parent and Child rows into a single Parent and its corresponding Child lines where 1. The Parents have same values of: Item_Name Category Vendor Location_ID Unit_Price 2. The Parents should have the same number of Child records. 3. The Child record under one Parent should have a corresponding Child record in the other Parent having the same values of the below columns: Item_Name Cate...

Please check the LiveSQL. I'm getting error with the clob column, but it works with the varchar2. So my question is how to properly handle the clob column with dbms_sql?