CODEWITHSUNDEEP
sqloracle-databaseplsql
Osama Al-Banna
Osama Al-Banna

Reputation: 1515

How to print all table rows in dynamic SQL via PL/SQL procedure and table name as a variable?

I'm trying to print a table via DBMS_OUTPUT.PUT_LINE with dynamic SQL and loop, because my table name must be a variable because it will change each month, below, is my code,

I'm also confused about how how to store the data and how to put it inside the variable? shall the variable need to be a CURSOR? for testing purposes in my code below I'm using the demo employee table in HR schema.

Also, the is there is another data type bigger than varchar2(4000) because I need to store my SQL statement into a variable to use execute immediately and that variable needs to store a very big complex SQL query .

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
v_TBL_NAME varchar2(200):='EMPLOYEES';
v_sql varchar2(200):='IS select * from ' ||v_TBL_NAME;
CURSOR C1;
BEGIN
EXECUTE IMMEDIATE v_sql INTO C1;
FOR REC IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(REC.EMPLOYEE_ID||' '||REC.FIRST_NAME);
END LOOP;
END;
/

Upvotes: 1

Views: 4307

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

You could use the open ... for cursor syntax:

declare
  v_tbl_name varchar2(30) := 'EMPLOYEES'; -- assume this will be a parameter later
  v_sql varchar2(32767) := 'select employee_id, first_name from ' || v_tbl_name;
  -- if there is a static table you could use %rowtype, or your own type with column%type
  type t_rec is record (employee_id number, first_name varchar2(20));
  v_rec t_rec; 

  c1 sys_refcursor;
begin
  open c1 for v_sql;
  loop
    fetch c1 into v_rec;
    exit when c1%notfound;
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.first_name);
  end loop;
end;
/

100 Steven
101 Neena
102 Lex
103 Alexander
104 Bruce
...
205 Shelley
206 William


PL/SQL procedure successfully completed.

Hopefully you are specifying the columns you actually want, and not using *...

Depending on your application/client you could access the ref cursor directly; this syntax works in SQL*Plus, SQL Developer, SQLcl and possibly some third-party clients:

var rc refcursor;

declare
  v_tbl_name varchar2(200) := 'EMPLOYEES'; -- assume this will be a parameter later
  v_sql varchar2(200) := 'select * from ' || v_tbl_name;
begin
  open :rc for v_sql;
end;
/

print rc

which produces output like:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE  JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ---------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         1987-06-17 AD_PRES         24000                                      90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         1989-09-21 AD_VP           17000                       100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         1993-01-13 AD_VP           17000                       100            90
...
        205 Shelley              Higgins                   SHIGGINS                  515.123.8080         1994-06-07 AC_MGR          12000                       101           110
        206 William              Gietz                     WGIETZ                    515.123.8181         1994-06-07 AC_ACCOUNT       8300                       205           110

107 rows selected.

You can access the ref cursor from JDBC etc.

Upvotes: 1

Del
Del

Reputation: 1599

You can use a reference cursor and bulk collect into a nested table. Then loop over the table like this:

DECLARE
  TYPE lt_record IS RECORD
  (
    empID INTEGER,
    fname VARCHAR2(100)
  );
  TYPE lt_recordTable IS TABLE OF lt_record;
  l_cTableCursor SYS_REFCURSOR;
  l_sTableName VARCHAR2(1000) := 'TABLE NAME';
  l_tRecords lt_recordTable;
BEGIN
  OPEN l_cTableCursor FOR ('SELECT employee_id, first_name FROM '||l_sTableName);
  LOOP
    FETCH l_cTableCursor
    BULK COLLECT INTO l_tRecords
    LIMIT 1000;

    EXIT WHEN l_tRecords.COUNT = 0;

    FOR i IN 1..l_tRecords.LAST LOOP
      dbms_output.put_line(l_tRecords(i).empID||' '||l_tRecords(i).fname);
    END LOOP;
  END LOOP;
  CLOSE l_cTableCursor;
END;
/

Upvotes: 3

Related Questions