Questions

hi,Refer to object-relational-developers-guide/Nested Table Locators‘For large child sets, the parent row and a locator to the child set can be returned so that the child rows can be accessed on demand; the child sets also can be filtered. Using nested table l...

Hi folks,I wrote a procedure that downloads spatial information for power lines via WFS from a web site and merges this into an existing table in my ADB. I wrote it in a way that it should use the parallel mode (pipelined table function, no triggers, jala jala...

How can you make an oracle table, index or partition 'lose' its storage definition and revert to tablespace defaults...I have a 'legacy' table in production that was built with storage parameters inherited from the 'consolidate storage' option on the old expor...

Hello i just wanted to ask you the (CASE WHEN THEN END;) is the ternary operator in oracle sql - pl/sql? thanks. I provided some example code above of java and pl/sql thanks!.<code>/*plsql code*/DECLARE myage NUMBER := 28;statusage VARCHAR(200) := NULL;BEGINst...

This query:<code>with T1 as (select 'A4' from dual unionselect 'A3' from dual unionselect 'A100' from dual unionselect 'A20' from dual )select * from t1;</code>produces the normal sort:<code>A100A20A3A4</code>In recent Oracle versions ( 19 and later ) is there...

Hello,I know PL/SQL package DBMS_OUTPUT and I know the procedure DBMS_OUTPUT.PUT_LINE.I see sometimes in code published on the web usage of DBMS_OUTPUT_LINE procedure.I would like to know if this procedure has ever existed and if yes in which Oracle version.Th...

Hello All,I encounter an error trying to use a SQL MACRO (TABLE) in freesql.com, in both versions 26ai and 23ai.The function top_n compiles successfully, but trying to call it fails:<code>create or replace function top_n (p_table DBMS_TF.TABLE_T, p_rows NUM...

I am implementing a 4-Schema SmartDB architecture for an Oracle APEX project to strictly enforce the principle of least privilege. My schemas are: DATA_LAYER (Tables), LOGIC_LAYER (Private Logic), API_LAYER (Public Interface), and APEX_UI_LAYER (Parsing Schema...

Hi,How can i connect to SQL developer using oracle wallet based security.from sql plus we can connect using the command like /@sid, but how can i connect to SQL developer (or) toad using the wallet based security.Thanks in advance. ...

Hi all,I have a problem with liveSQL. I can't create a sharable link. Even with FreeSQL, can't do so. Appreciate any help.Thnks...

<u></u>table has two rowsselect id,value from IIO_SYSTEM_PARAMETER;200 1027/1029/1000199 1027/1029/1000when I ran the below query, it is giving 7 records, instead of 3 records<b>query1:</b><code> SELECT REGEXP_SUBSTR(VALUE, '[^/]+', 1, LEVEL) DEST ...

I am troubleshooting a severe storage efficiency issue on a batch logging table in our Oracle 19c database on the below tableTable DDL: <code>CREATE TABLE APP_BATCH_LOGS( BATCH_ID VARCHAR2(100 CHAR) NOT NULL, -- Partition Key PROCESS_ID...

When querying a mounted database, an ORA-01219 occurs when querying anything besides instance views such as v$database. This is even the result when using a WITH clause that, itself, is an allowed query for a mounted database. For example:<code>SQL> select sta...

I'm trying to create a database link using the <b>CONNECT WITH</b> clause that's mentioned in the documentation for CREATE DATABASE LINK so that I can use a database credential in the DDL statement.The doc I'm referring to is : https://docs.oracle.com/en/datab...

A tuning book says window functions and the 12c row limiting clause often fail to trigger Top‑N Stopkey even with a perfect index, so I tried to reproduce that behavior and saw the same thing—ROWNUM uses stopkey, but ROW_NUMBER() and FETCH FIRST don’t. Here’...

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

To avoid unexpected execution plan changes some people say that we must set DBMS_STATS estimate_percent should always be to 100.I would like to know if you agree with this ?...

Hello,I work as a dba and get requests from developers to create tables which we often review and sometimes change for better design and/or implementation.One of the developers recently sent a request to create a table such as the following ;table_name:t1_rela...

How to activate the auto-index feature in Oracle 19c Enterprise Edition?To activate on PDB send me the error:<code>BEGIN dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT'); END;Informe de error -ORA-40216: función no soportadaORA-06512: en "SYS.DBMS_SYS_...

Hi Tom,First of all, I want to thank you for all the things which you have done and doing for Oracle community. It inspire me to learn more and more.Now my question is --Is there any way by which we can hide the procedures and functions code so the user can on...