Questions

Greetings,I have created a map in Oracle APEX where the user can click and get the lat and long and drop a pin there. Now, I want to find which congressional district the pin was dropped in. I have a table with all congressional districts, but that is geojson ...

I am facing issue of "Pooled connection request timed out" when I upgraded database to 19c and moved the application to cloud(TAS) from VM.There is no fixed scenario when this happens. No code changes has been done except replacing DataAccess.dll to ManagedDat...

We have a triggerCREATE OR REPLACE TRIGGER TRG_AFTER_LOGONAFTER LOGON ON DATABASEBEGIN LogonCheck;END;/withCREATE OR REPLACE PROCEDURE LogonCheckAUTHID current_userASBEGIN EXECUTE IMMEDIATE 'SET ROLE NONE';END;/This always results in error ORA-06565, if LogonC...

I need to move online a huge table with BLOBs (around 25 TB) to a new tablespace with bigger block size.Wich method is recommended? Is DBMS_REDEFINITION still the method recommended by oracle? Or is now ALTER TABLE MOVE ONLINE?...

Before understand question mind I have one table with 3 columns one is id second is description and third is sql query, i created one Apex page with two region one is static region and one is Interactive Report Region. In Static region i have one select list ...

Hi<code>select * from UNIFIED_AUDIT_TRAIL where event_timestamp >= to_date('20200827 1510','yyyymmdd hh24mi') order by event_timestamp desc</code>seems to take quite a lot of time.UNIFIED_AUDIT_TRAIL view has EVENT_TIMESTAMP as timestamp with local timezone.Th...

How do I return the values from a PL/SQL table(indexed by BINARY_INTEGER) into a ref cursor?The contents of the PL/SQL table are NOT returnable by a single SQL statement. AS it is a PL/SQL table ; I can't do a standard select (This doesn't work -open TunnelCr...

using the example data from https://oracle-base.com/articles/19c/sql-nested-clause-instead-of-json_table-19cIf I want the Address *and* the Contact Details, the intuitive thing would be to add another NESTED clause, like this<code>SELECT jt.*FROM json_docume...

Hi everyone!I'm working with 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionI'm trying to obtain json elements as columns in a view.When I saw the plan for the following sql I realized that the cost increases when json_value is used more than once...

Hi Tom,Below are the scripts for your reference wherein it works like if a row is not present in a table then insert it based on one column value.1.My query to you is simply looking at below scripts (without any alteration)which method do you think is better w...

Hello Team,I wanted to know if it is possible to run the same OLTP application on two different sites with databases on both sites being synced using the Golden Gate? Generally, do we need another dataguard enviornment(s) to cater as DR solution or above confi...

Hi Tom,I do have a large table millions of records. and the levels are much deeper it would vary case to caseIn order to achieve the results, whether hierarchical query is good or some other approach suits the purposeRegards,Jithesh...

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...

hi tomi have a databse with 125 tables. i can find the total number of tables of the database by the sql select * from tab; now i would like to know the number of rows in each table of my database with out executing select count(*) from...; each time.please...

Hi,We have a requirement to delete rows in chunks of say 100 from a large table and commit, then proceed for next 100 delete.Without looping delete written is as below<code>delete from large_table where code=888 and id in (select id from small_table ...

hi, there are queries which are performing slow from past 1 day, earlier same queries were giving output in 22 mins and not it takes 55 mins, this is basically the EOD process.Query 1:<code>SELECT DISTINCT DECODE(:B1 ,'C',IR.REASON_CODE,'D',IR.DESCRIPTION) ...

Hi,I am getting following error while migration.ORA-00054: RESOURCE BUSY AND ACQUIRE WITH NOWAIT SPECIFIEDMigration code is given below. I am not able to understand I have acquired exclusive lock on this table then why I am getting this error. <b>Please help A...

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...

My question: Is it possible to truncate multiple tables in a single line statement?Firstly, thanks for giving this opportunity to ask a question.I have developed a code to truncate multiple tables in a single line statement. Please refer the below code.----cre...

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...