CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Random guy
Random guy

Reputation: 923

How to pass the value of procedure to select statement in oracle?

I have a procedure where I am calling the procedure using 

exec ot.selector('regions');

But,I am getting error at this line:

 select * from ot."||p_table_name||";

So the procedure I tried to make was:

 create or replace procedure ot.selector(p_table_name varchar2)
    is
    cursor cur is
    select * from ot."||p_table_name||";
    begin
    for i in cur
    loop
    dbms_output.put_line(i.region_name);
    end loop;
    end;

The error I got is :

Error at line 1
ORA-06550: line 1, column 10:
PLS-00905: object OT.SELECTOR is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The error I got after making procedure is :

[Warning] ORA-24344: success with compilation error
4/19    PL/SQL: ORA-00903: invalid table name
4/1     PL/SQL: SQL Statement ignored
 (1: 0): Warning: compiled but with compilation errors

Upvotes: 0

Views: 96

Answers (3)

VBoka
VBoka

Reputation: 9083

Your line:

 select * from ot."||p_table_name||";

should look like this:

 select * from ot. || p_table_name;

This || is a concatenate sign.

Hope that this will help...

Upvotes: -1

Littlefoot
Littlefoot

Reputation: 142713

Here's a working alternative; see if it helps.

As you didn't provide test case, I'm creating the REGIONS table out of Scott's DEPT.

SQL> create table regions as
  2    select deptno, loc as region_name from dept;

Table created.

Function:

SQL> create or replace procedure selector(p_table_name in varchar2) as
  2    l_str varchar2(200);
  3    rc    sys_refcursor;
  4    l_rc  regions%rowtype;
  5  begin
  6    l_str := 'select * from ' || dbms_assert.sql_object_name(p_table_name);
  7
  8    open rc for l_str;
  9    loop
 10      fetch rc into l_rc;
 11      exit when rc%notfound;
 12      dbms_output.put_line(l_rc.region_name);
 13    end loop;
 14    close rc;
 15  end;
 16  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec selector('regions');
NEW YORK
DALLAS
CHICAGO
BOSTON

PL/SQL procedure successfully completed.

SQL>

If you wonder what's DBMS_ASSERT used for, it is here to prevent SQL injection (as mentioned within comments). If you pass anything that is not valid, you'd get

SQL> exec selector('nonexistent_table');
BEGIN selector('nonexistent_table'); END;

*
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SCOTT.SELECTOR", line 6
ORA-06512: at line 1


SQL>

Upvotes: 2

Popeye
Popeye

Reputation: 35900

You need to use dynamic sql for cursor.

create or replace procedure ot.selector(p_table_name varchar2)
    is
    cur sys_refcursor;
    V_sql varchar2(4000);
    v_rec SOME_TABLE%ROWTYPE; -- add variable table name here
    begin
    V_sql := 'select * from ot.'|| p_table_name;
    Open cur for v_sql;
    LOOP
  FETCH CUR INTO v_rec;
   dbms_output.put_line(v_rec.region_name);
  EXIT WHEN v_rec%NOTFOUND;
END LOOP;
    Close cur;
    end;
    end selector;
    /

Cheers!!

Upvotes: 0

Related Questions