Pass PL/SQL parameter as SCHEMA NAME-CodePudding

I'm trying to send variable schema name to cursor via procedure input Here is my lame try, but you can see what I want to do:

CREATE OR REPLACE PROCEDURE HOUSEKEEPING 
(SCHEMANAME in varchar2)

IS

CURSOR data_instances  IS  select table_name 
from SCHEMANAME.table_name where TYPE='PERMANENT' and rownum<200 ;

BEGIN
    DBMS_OUTPUT.PUT_LINE(SCHEMANAME);
END;
/

it throws expected

PL/SQL: ORA-00942: table or view does not exist

is there lawful way to make schema name work as variable? thanks

CodePudding user response：

There is a way; you'll need some kind of dynamic SQL because you can't use schema (or object) names like that. For example, you could use refcursor instead.

Sample table:

SQL> create table table_name as
  2  select 'EMP' table_name, 'PERMANENT' type from dual union all
  3  select 'DEPT'          , 'TEMPORARY'      from dual union all
  4  select 'BONUS'         , 'PERMANENT'      from dual;

Table created.

Procedure; note the way I composed SELECT statement first (so that I could display it and check whether it is correct), and then used it in OPEN. Loop is here to ... well, loop through the cursor. I'm just displaying table names I found - you'd probably do something smarter.

SQL> create or replace procedure housekeeping (par_schemaname in varchar2)
  2  is
  3    l_str        varchar2(500);
  4    l_rc         sys_refcursor;
  5    l_table_name varchar2(30);
  6  begin
  7    l_str := 'select table_name from ' ||
  8              dbms_assert.schema_name(upper(par_schemaname)) ||
  9             '.table_name where type = ''PERMANENT'' and rownum < 200';
 10    open l_rc for l_str;
 11
 12    loop
 13      fetch l_rc into l_table_name;
 14      exit when l_rc%notfound;
 15
 16      dbms_output.put_line(l_table_name);
 17    end loop;
 18    close l_rc;
 19  end;
 20  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec housekeeping('SCOTT');
EMP
BONUS

PL/SQL procedure successfully completed.

SQL>