CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
Pablo
Pablo

Reputation: 29519

How to pass query params to procedure?

PROCEDURE drawComponent (title IN VARCHAR2) IS
BEGIN
    htp.p('<tr>');
    htp.p('<td class="row-hdr"><strong>' || title || '</strong></td>');
    for rec in (SELECT CELLS.ID as CELLID, CELLS.NUM as CELLNUM, CELLS.A_ID as ID, TBL.REMOVED as REMOVED FROM CELLS LEFT OUTER JOIN TBL ON CELLS.A_ID = TBL.ID ORDER BY CELLS.NUM)  
...

I need to add CELLS, A_ID, TBL as params to above procedure. I am not sure on types and syntax.

Clarification:

Procedure will be called multiple times, for different tables, different columns. The body is same, only those params I mentioned are different.

Upvotes: 2

Views: 83

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You can (ab)use DYNAMIC SQL to pass the table name as parameter to the procedure.

SQL> CREATE OR REPLACE
  2  PROCEDURE p(
  3      table_name IN VARCHAR2)
  4  AS
  5  TYPE ref_typ
  6  IS
  7    REF
  8    CURSOR;
  9      var_ref ref_typ;
 10      emp_no NUMBER ;
 11    BEGIN
 12      OPEN var_ref FOR 'SELECT EMPNO FROM '||table_name;
 13      LOOP
 14        FETCH var_ref INTO emp_no ;
 15      EXIT
 16    WHEN var_ref%NOTFOUND;
 17      DBMS_OUTPUT.PUT_LINE('empno --> '||emp_no);
 18    END LOOP;
 19    CLOSE var_ref;
 20  END;
 21  /

Procedure created.

Let's execute the procedure and see.

SQL> set serveroutput on
SQL> BEGIN
  2     p('emp');
  3  END;
  4  /
empno --> 7369
empno --> 7499
empno --> 7521
empno --> 7566
empno --> 7654
empno --> 7698
empno --> 7782
empno --> 7788
empno --> 7839
empno --> 7844
empno --> 7876
empno --> 7900
empno --> 7902
empno --> 7934

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions