1

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;
/
Improve this question
7
  • 1
    4000 is a the size limit in SQL/DDL (and can be increased in later versions); but in PL/SQL the limit is 32k anyway. If you need to go large than that you can use a CLOB, but likely to be overkill here? Commented Apr 6, 2020 at 15:57
  • Thanks Alex, what about printing table ? the problem with the table I have is a portion table it's name change each month billing202001@billingdb next month billing202002@billingdb,after many research I figure out I have to use dynamic SQL but I don't know how fetch the data into a variable and print it in a for loop . Commented Apr 6, 2020 at 16:02
  • Are the column names always the same? Commented Apr 6, 2020 at 16:03
  • column name always the same but the table name is changing each month . Commented Apr 6, 2020 at 16:04
  • Why are you creating a different table each month? Why not just create a partition on one BILLING table? Commented Apr 6, 2020 at 16:04

2 Answers 2

Reset to default
3

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;
/
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Alex is killing me today...keeps submitting answers while I'm typing them. The big difference is the BULK COLLECT into a collection to limit context switching.
Bulk collect can reduce network round trips at the expense of more memory allocated to hold the results (hence the limit); though recent versions optimise to do bulk transfers under the hood anyway. (And I feel your pain... I have many days like that! I'll probably be quiet for a while now *8-)
@Del thanks for your answer I will try to learn your script as well,actually I sow Alex answer first .
1

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.

Improve this answer

9 Comments

Thanks Alex it worked for me , I will try to apply the same concept on this question stackoverflow.com/questions/61027238/…
can you please tell me what sys_refcursor does is this a data type or function that fetch sample data to detect the datatype ?
@OsamaAl-Banna - it's a weak cursor variable - read more in the docs. You can define your own ref cursor variable, but using the built-in sys_refcursor saves a bit of typing.
Yes, the 'error-prone' warning isn't really relevant to your situation.
@OsamaAl-Banna - Yes
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.