-- query_report.sql

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE query_report(query VARCHAR2)
AS
   TYPE tab_type IS TABLE OF VARCHAR2(30);
   tables tab_type;
   c INTEGER;
   col_count INTEGER;
   desc_t DBMS_SQL.DESC_TAB;
   r INTEGER;
BEGIN
   DBMS_OUTPUT.ENABLE(99999999999); 
   c := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(c, query, DBMS_SQL.NATIVE);  
   r := DBMS_SQL.EXECUTE(c);
   DBMS_SQL.DESCRIBE_COLUMNS (c, col_count, desc_t);
   DBMS_OUTPUT.PUT_LINE('Table has the following columns: ');
   FOR i IN 1..col_count LOOP
     DBMS_OUTPUT.PUT(desc_t(i).col_name || ', ');
   END LOOP;
   DBMS_OUTPUT.NEW_LINE();
   DBMS_SQL.CLOSE_CURSOR(c);
EXCEPTION
WHEN OTHERS THEN
   DBMS_SQL.CLOSE_CURSOR(c);
END;
/

BEGIN
  query_report('SELECT * FROM store');
END;
/
