Questions

Dear Team,I have created stored procedure which return output in refcursor.If I run procedure by "right click => Run", then I can see output in tab "Log => output variables".But, I can't export output from there.Then I tried below command: --------------------...

Hello All ,can we able to find the select statement which is used for sys_refcursor ?<code>function f_my (id in number ) return sys_refcursor is l_rc sys_refcursor; begin open l_rc for select * from emp where empno=id; return l_rc; en...

Hello,I wanted to ask about new 23C feature - Schema-level Privileges: is it possible to combine it with "WITH GRANT OPTION"?According to documentation https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/configuring-privilege-and-role-authoriza...

Hi Tom,The company I work for has quite a number of Oracle databases,on the order of 200 or so, including development, test, preprod,production, etc, all hosted on Solaris, currently. We have a pretty large number of unix clients of various flavors, mostly So...

We have an application in Oracle APEX where we do not have a large database and the number of pages used in the application would be max 250. However, there is a 500 Internal Server error that we are encountering since the last 3-4 days and we have not made an...

While we are working on dbms_cloud API in ATP DB, we are facing some issues. When we are calling the API directly from SQL Developer, it works perfectly fine and data is getting loaded into the tables.<code>BEGIN dbms_output.put_line('Start Time : '||TO_CHAR(...

Hi Tom,What plan do you suggest to purge a table which is of size 377Gb approx?We are currently following this plan,- Creating a temporary table with the required data(Approx 86 crore records).- Drop the constraints(15 constraints).- Truncate the original tabl...

In Oracle SQL Developer, 'Application Express' section, we can export DDL for an APEX app by context menu, 'Quick DDL' > 'Save to Worksheet' menu. I'd like to do the same by executing some SQL or PL/SQL code. I know we have DBMS_METADATA.GET_DDL, but that does...

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

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

Hi,Could you please have a look at below table structure<code>drop table child purge/drop table parent purge/create table parent (id number(11,0) ,dt date ,constraint pk_parent primary key (id))partition by range (dt) (par...

Hi Tom,In one of the interview, interviewer asked me about the scenario where bitmap index is useful in OLTP system.and I was totally clueless.....I wasn't able to think any of the scenario.Later I googled every possible combination to get some info about the ...

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

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

Hi,In below article https://oracle.readthedocs.io/en/latest/sql/basics/query-processing-order.htmlIt is written that the order in which clauses are logically processed by Oracle is as follows: FROM -> CONNECT BY -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDE...

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

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, andin that column ...

Respected sir/Memi am new in oracle database with apexcan 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 sysdbaConnected to an idle instance.SQL> s...

connect system password : password. i got following errorsORA-01034: ORACLE NOT AVAILABLEORA-27101: shared memory realm does not exist...