SELECT INTO variable in Procedure

Question

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 
; 

Solution

If you are running the procedure from the same schema, it should not give error.
Please check the attached screen shot. I just replicated your scenario.
Please cross check your code with mine on. Thanks.

Make sure, your schema name is right. I mean, you are checking both under the same schema. Then result will be same.

Comments

[Matthew Walk]

Thanks for the answer smshafiqulislam. When I run the direct query, I'm running it connected to the DB as "MMW1164" and it works when filtering for the schema "TAS". When I create the procedure, it's schema is "MMW1164" and it's attempting to query the schema "TAS" but apparently coming back with no value.

[smshafiqulislam]

What is the output of this query? SELECT cols.column_name 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';

[smshafiqulislam]

I shared the screen shot for your reference. It should work. :) Just share what is the output of query under "MMW1164"

[Matthew Walk]

Hi again smshafiqulislam. When I run that query in Oracle SQL developer, it returns "COM_ALL_TXT_KEY". When that same query runs in the procedure and tries to save the value into the variable, the variable is empty. It must be something to do with the fact that the procedure is in the schema "MMW1164" but the query is trying to filter for owner = 'TAS'. I just don't understand how it works for a direct query, but not in the procedure.

[smshafiqulislam]

Oh, I see. I got your problem. You need dba privilege if you want to see other schema's constraint information sitting in one schema. MMW1164 needs dba privilege. Check by just running "grant dba to MMW1164" once.

[Alex Poole]

@smshafiqulislam - that's massive overkill and dangerous, and goes completely against the principle of least privilege. The table privileges just need to be granted directly, or the procedure needs to be created with authid current_user.

[smshafiqulislam]

@AlexPoole yes. I agree. I mentioned just to make the things clear that, it is privilege issue. Yes. Solution is granting table privileges or using authid.

[Matthew Walk]

After asking the DBAs to create another version of the proc in the "TAS" schema, it worked. So, it does appear to be a schema permissions related issue.