CODEWITHSUNDEEP
oracle-databaseplsqlcursordynamicquery
niloofar rzi
niloofar rzi

Reputation: 11

Put variable tablename in cursor statement

Can someone help me how to write a dynamic query in PL/SQL, and define the table name as a variable in the dynamic query (in other words I need to write a dynamic query which accept table name as a variable)? I think it could be done by cursor, but I don't know how to put my table name as a variable in a cursor.

Upvotes: 0

Views: 74

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

This is one way to do that (if that's what you're looking for):

SQL> create or replace procedure p_test (par_tab in varchar2,
  2                                      par_out out sys_refcursor
  3                                     )
  4  is
  5  begin
  6    open par_out for 'select * from ' || dbms_assert.sql_object_name(par_tab);
  7  end;
  8  /

Procedure created.

SQL> var l_out refcursor
SQL> exec p_test ('dept', :l_out);

PL/SQL procedure successfully completed.

SQL> print :l_out

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Upvotes: 1

Related Questions