Questions

Tom, I was working with External tables and Oracle came up with an error when I tried to insert, update or delete from external tables, as shown below.SQL> delete from student_ext;delete from student_ext *ERROR at line 1:ORA-30657: operation not su...

Hello Connor/Chris,I am in the middle of troubleshooting a slow-running application maintenance job for a database that has been migrated/upgraded from on-premise 12.1.0.2 on linux x64 to Exadata Cloud @ Customer setup. Many of the "Top SQLs" that have regress...

Hi,Please refer to the linked LiveSQL for the table, index, and data definition.I execute a query that uses ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC). When a partitioned index on SOME_DATE DESC is used, the result contains wrong row numbers. The order of row...

Tom,I am trying to POST the webservice all using UTL_HTTP function with the file from pl/sql procedures. The same processed working from Curl scripts.PL/SQL :<code>DECLARE req utl_http.req; resp utl_http.resp; l_value VARCHAR2(1024); l_url_import VARCHAR...

Hi,is it possible to audit (unified auditing) access to tables only once per session? I don't need to catch all selects from audited tables in a session, I just want to to know if a table was at querried at least once. Currently it generates huge amount of aud...

1) Platform and Oracle version i tried. OS platform Windows 10 & windows 11 Oracle version : Oracle 18CXE , Oracle 21c XE, Oracle 12c (on Oracle Linux 7) ORDS 22.2 TOMCAT 9.2) I tried it on oracle 18cXE and ords 21.4 to upgrade ords 22.2 but after inst...

Do you have a full example of writing a ddl_Even trigger. I want to audit on a schema level any creates, alters, or drops of any objects....and write the actual calling syntax to a table.Oracle8 has the new feature of DDL_EVENT, and their is an example in the...

Dear Tom,I am trying to tune a query (12.1.0.1 version, 4 node RAC).The database appears to use DBMS_RLS policies.I see one context defined as:<code>CREATE OR REPLACE CONTEXT CRAMERSESSION USING CRAMER.PKGSESSIONCONTEXT/</code>CRAMER.PKGSESSIONCONTEXT code is ...

Hello T[o|ea]m,Once, a long time ago, you gave an example of queuing a message asynchronously using PLSQL.https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8760267539329I implemented this example myself and got it working.(I think) I understan...

We have been trying to enable auditing on few DDL activities in autonomous database. As per the following code we created, enabled and verified the audit configs. Everything looks normal to us, however none of the table create/drop activities (listed at the en...

I am trying to write an sql, that takes two inputs at run time, if I am passing null then it looks at a database table and gets values from it.To make the NULL checks and then assign values from database, I implemented the logic as its given here https://askto...

I am trying to size my undo tablespace properly( requirement is 24 hours retention for flashback query), So I need historic undo usage per day for last 90 days. and I am using the query below but I suspect that it is giving me wrong answer. Autoextend is off f...

How to Extract the Tag value from xml which as tag name with colon.<code> <link>https://www.rba.gov.au/statistics/frequency/exchange-rates.html#JPY</link> <description>AUD/JPY representative rate as at 4.00 pm Eastern Australian time on 14 Nov 2023</descrip...

Hello,i am very new at Oracle licensing issues and i am facing a situation with an Oracle SE2 Licence NUP.i am very confused how to count the number of NUP's i will have to purchase.is a NUP an explicit Oracle user i have created? what about system Oracle use...

hi sir i got this message when i try to run my app"503 Service Unavailable2023-11-15T09:24:51.336389300Z | 8-GbuZcF3_cnIHgb1ul5Kg | ORDS was unable to make a connection to the database. This can occur if the database is unavailable, the maximum number of sessi...

Hi Tom,I have 5 tables in a query, Tab1, Tab2, Tab3, Tab4 and Tab5 and I have the following query below.Select * From Tab1, Tab2, Tab3, Tab4, Tab5 Where Tab1.x = Tab2.y(+) and Tab1.x = Tab3.y and Tab3.x = Tab4.y(+) and Tab3.x = Tab5.y;Question 1. Can a...

Hi Tom!ROWID works in a subselect: <code>SELECT * FROM(SELECT rowid, d.* FROM DUAL d)</code>and it also works with connect by: <code>SELECT rowid, LEVEL FROM DUAL CONNECT BY LEVEL < 2</code>But when used with both: <code>SELECT * FROM(SELECT rowid, LEVEL FROM ...

Hi TOM, We are planning on upgrading from Apex 22.1 to Apex 23.1. We are currently on Oracle 19c using Apex 22.2:SQL> <code>SELECT * FROM v$version;</code>BANNER--------------------------------------------------------------------------------BANNER_FULL-------...

Hi,how to create view with parameter?we want to create view definition as below,<code>create view test_item_vas select item_class,nvl(rev_id,v_rev_id),sum(total_cost),sum(total_resale),sum(margin)from test_item_classwhere rev_id = v_rev_id;</code>Please sugges...

Hello Tom,A colleague of mine is a big fan of service-oriented architectures and middle-ware. His view is that dblinks are 99.9% of the time a bad idea and that you should go via some sort of message layer in order to pass information between applications.In ...