If I run this in Oracle SQL developer:
set serveroutput on
DECLARE
key_column varchar2(30) := 'Test';
BEGIN
dbms_output.put_line('key_col = ' || key_column );
SELECT cols.column_name INTO key_column
FROM all_constraints cons
INNER JOIN all_cons_columns cols ON cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
AND cons.constraint_type = 'P'
WHERE cons.owner = 'TAS'
AND cols.table_name = 'COMMENT_ALL_TXT';
DBMS_OUTPUT.PUT_LINE('1 key_column: ' || key_column);
END
;
The output is:
anonymous block completed
key_col = Test
1 key_column: COM_ALL_TXT_KEY
When I put the same code into a procedure, this is the output:
ORA-01403: no data found
Why does the procedure code not find the constraint when the code run in a SQL Worksheet does?
Here is the procedure code:
create or replace PROCEDURE MMW1164.USP_BATCH_CLEANUP_NEW
IS
key_column VARCHAR2(100);
BEGIN
SELECT cols.column_name INTO key_column
FROM all_constraints cons
INNER JOIN all_cons_columns cols ON cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
AND cons.constraint_type = 'P'
WHERE cons.owner = 'TAS'
AND cols.table_name = 'COMMENT_ALL_TXT';
DBMS_OUTPUT.PUT_LINE('1 key_column: ' || key_column);
END
;
