Export a CSV file in Oracle database by using stored procedures

Question

I am using the following stored procedures (Oracle Database) to export the output of the query to a CSV file

    CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                            ,p_dir IN VARCHAR2
                                            ,p_file IN VARCHAR2
                                            ) IS
         v_finaltxt  VARCHAR2(4000);
         v_v_val     VARCHAR2(4000);
         v_n_val     NUMBER;
         v_d_val     DATE;
         v_ret       NUMBER;
         c           NUMBER;
         d           NUMBER;
         col_cnt     INTEGER;
         f           BOOLEAN;
         rec_tab     DBMS_SQL.DESC_TAB;
         col_num     NUMBER;
         v_fh        UTL_FILE.FILE_TYPE;
       BEGIN
         c := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
         d := DBMS_SQL.EXECUTE(c);
         DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
         FOR j in 1..col_cnt
         LOOP
           CASE rec_tab(j).col_type
             WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
             WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
             WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
           ELSE
             DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
           END CASE;
         END LOOP; 
         -- This part outputs the HEADER
         v_fh := UTL_FILE.FOPEN(upper(p_dir),p_file,'w',32767);
         FOR j in 1..col_cnt
         LOOP
           v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
         END LOOP;
         UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
         -- This part outputs the DATA
         LOOP
           v_ret := DBMS_SQL.FETCH_ROWS(c);
           EXIT WHEN v_ret = 0;
           v_finaltxt := NULL;
           FOR j in 1..col_cnt
           LOOP
             CASE rec_tab(j).col_type
               WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                           v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
               WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                           v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
               WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                           v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
             ELSE
               DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
               v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
             END CASE;
           END LOOP;
         --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
           UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
         END LOOP;
         UTL_FILE.FCLOSE(v_fh);
         DBMS_SQL.CLOSE_CURSOR(c);
       END;
       /

The above store procedure is working perfectly and I got the output by using the following script to run this stored procedure

exec run_query('select * from person_list','TEST_DIR','output.CSV);

Now here is my issue. As we are working in the big database so my Manager suggests I use the following command line

exec [stored_procedure_name] [table_name];

For example here stored_procedure_name is "run_query" and the table_name is "person_list"

exec run_query person_list; 

I don't understand how can I modify the stored procedure code so that I can run the above script.

Answer 1

Excel CSV can support only 60k rows max. Change the params in the stored procedure to accommodate the change. Remove the two params for dir and file name. Give the default dir and use the table name as the output file name.csv. Do a count on the table to make sure its less than 60k rows; if it's more, give a warning message and error out.

Given below is the stored procedure with the change you asked for. For DIR, I have assumed a value of UTL_FILE_DIR; change it to your directory name.

CREATE OR REPLACE PROCEDURE run_query1(p_tbl_name IN VARCHAR2
                                            ) IS
         v_finaltxt  VARCHAR2(4000);
         v_v_val     VARCHAR2(4000);
         v_n_val     NUMBER;
         v_d_val     DATE;
         v_ret       NUMBER;
         c           NUMBER;
         d           NUMBER;
         col_cnt     INTEGER;
         f           BOOLEAN;
         rec_tab     DBMS_SQL.DESC_TAB;
         col_num     NUMBER;
         v_fh        UTL_FILE.FILE_TYPE;
         p_dir       VARCHAR2(50);
       BEGIN
       p_dir:='UTL_FILE_DIR';
         c := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(c, 'select * from '||p_tbl_name, DBMS_SQL.NATIVE);
         d := DBMS_SQL.EXECUTE(c);
         DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
         FOR j in 1..col_cnt
         LOOP
           CASE rec_tab(j).col_type
             WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
             WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
             WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
           ELSE
             DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
           END CASE;
         END LOOP; 
         -- This part outputs the HEADER
         v_fh := UTL_FILE.FOPEN(upper(p_dir),p_tbl_name||'.csv','w',32767);
         FOR j in 1..col_cnt
         LOOP
           v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
         END LOOP;
         UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
         -- This part outputs the DATA
         LOOP
           v_ret := DBMS_SQL.FETCH_ROWS(c);
           EXIT WHEN v_ret = 0;
           v_finaltxt := NULL;
           FOR j in 1..col_cnt
           LOOP
             CASE rec_tab(j).col_type
               WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                           v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
               WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                           v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
               WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                           v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
             ELSE
               DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
               v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
             END CASE;
           END LOOP;
         --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
           UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
         END LOOP;
         UTL_FILE.FCLOSE(v_fh);
         DBMS_SQL.CLOSE_CURSOR(c);
       END;
       /