How to show the sql statement result in the screen when it is executed using PL SQL?

Question

Good morning,

I want to execute a query using PL SQL code and I want the result to be shown to the user like he was executing the SQL by himself. In short I want the result to be shown in the screen like usual. It's the same problem reported in this other topic:

How to output result of SELECT statement which is executed using native dynamic SQL?

I tried to apply the solution suggested in the link bellow but it didn't work. Here my code that is not working properly:

DECLARE

    cursor_ID  NUMBER;
    tbl_name VARCHAR2(256);
    record_count INTEGER;
    numRows  INTEGER;

    sql_instruction VARCHAR(10000);
    interface_table_name VARCHAR2(50);
    interfaces_record ALL_TABLES%rowtype;
    CURSOR interfaces_cursor IS 
      SELECT table_name, ROW_NUMBER()OVER(ORDER BY 1) record_number,
               COUNT(*)OVER() total_records FROM ALL_TABLES where table_name like '%TBL_%';

    BEGIN

      FOR interfaces_record in interfaces_cursor
      LOOP
        interface_table_name := interfaces_record.table_name;
        IF interfaces_record.record_number = 1 THEN
          sql_instruction := sql_instruction || ' SELECT ''' || interface_table_name || ''' AS TABELA, COUNT(*) AS QTDE_REGISTROS FROM ' || interface_table_name;
        ELSE
          sql_instruction := sql_instruction || ' UNION ALL SELECT ''' || interface_table_name || ''' AS TABELA, COUNT(*) AS QTDE_REGISTROS FROM ' || interface_table_name;
        END IF;
      END LOOP;
      dbms_output.put_line('SQL: ' || sql_instruction);
      --EXECUTE IMMEDIATE sql_instruction; --this don't show the result on the screen

      -- THIS PART BELLOW IS WHAT I DID TO TRY SHOW THE RESULT ON THE SCREEN
      cursor_ID := DBMS_SQL.OPEN_CURSOR;

      DBMS_SQL.PARSE(cursor_ID, sql_instruction, DBMS_SQL.native);
      DBMS_SQL.DEFINE_COLUMN(cursor_ID, 1, tbl_name, 256);
      DBMS_SQL.DEFINE_COLUMN(cursor_ID, 2, record_count);

      numRows := DBMS_SQL.EXECUTE(cursor_ID);
      LOOP
            IF DBMS_SQL.FETCH_ROWS(cursor_ID) = 0 THEN
                 EXIT;
            END IF;

            DBMS_SQL.COLUMN_VALUE(cursor_ID, 1, tbl_name);
            DBMS_SQL.COLUMN_VALUE(cursor_ID, 2, record_count);

            --DBMS_OUTPUT.PUT_LINE(tbl_name || ' ' || record_count);
      END LOOP;

      EXCEPTION
            WHEN OTHERS THEN
                      RAISE;
            DBMS_SQL.CLOSE_CURSOR(cursor_ID);
    END;

Do you have any idea why my code does not show the result on the screen? I will appreciate any help. Thanks.

Answer 1

Use DBMS_OUTPUT.PUT_LINE in order to get the result on Screen :) or use / at the end of you script thts it ... If your script is having any error then also sometimes it will not display result or show any error in this case better to use Exeption..

Answer 2

If executing the PL/SQL code in SQL*Plus you need to SET SERVEROUTPUT ON and uncomment/include DBMS_OUTPUT.PUT_LINE statements.

You can get the same result in a single SQL statement

SELECT table_name
      ,to_number
         (extractvalue
            (xmltype
               (dbms_xmlgen.getxml
                  ('SELECT count(*) c FROM ' || owner || '.' || table_name)
               )
            ,'/ROWSET/ROW/C'
            )
         ) Count
  FROM all_tables
 WHERE table_name LIKE '%TBL_%'