CSV file from stored queries

User_70275

Hi, I have table with a lot of queries stored as a clob data.

So it looks like

create table z_queries (QUERY_NAME VARCHAR2(50 CHAR), 

QUERY CLOB,

CSV_GEN VARCHAR2(1 BYTE))

I need to generate csv file with results of every query which has status csv_gen='Y'. Can anyone tell me how can i do that? This could be done from ksh file or (if it's possible) from sql developer.

Thx

BEDE

Well, using sqldeveloper, after having run a query you may export its result to .csv or excel. But I think this is not what you actually want.

I understand you have N queries and the number and data types of the columns returned by each one of them varies. If it is so and you want to write some procedure to take the results of each query and write to .csv, then RTM on dbms_sql. Use the facilities of dbms_sql and utl_file to write to a .csv. It's not easy. I've once done something like that.

Gaz in Oz

one of the simplest ways to get csv output from an Oracle SQL statement would be to execute the query in sqlplus 12.2 or higher, have "set markup csv on" and "spool a_name.csv".

Viola! (or some other stringed instrument"), you have "a_name.csv" as (by default with set markup csv") a text file with the sql query results as rows comma separated.

User_70275

@BEDE thanks for advice :)

@Gaz in Oz thanks, I've tried to spool that but i had a problem with extracting query from clob column.. so I don't know if the way I did it is optimum but i spooled query to sql file and then spool @sqlfile to csv.

Gaz in Oz

if by "i had a problem with extracting query from clob column" you actually mean the SQL statement was truncated, that will be because you have "SET LONG nnnnn" too short or not at all. The default is 80. Set it to something large. For example:

SQL> set long
SP2-0267: long option 0 out of range (1 through 2000000000)
SQL> show long
long 80
SQL> set long 2000000000
SQL>

If it was some other problem then you'll need to share it, else how can anybody help?...

User_70275

I mean that in that clob column are queries, not their results so if i just spool it i have something like "select 'something' from dual" in spooled file, not 'something'

BluShadow

(trying to post this in pieces as the forum is not letting me post all in one go)...

drop table z_queries;
create table z_queries (query_name varchar2(25), query clob, csv_gen varchar2(1));
insert into z_queries
  select 'Employees', 'select * from emp', 'Y' from dual union
  select 'Departments','select * from dept','Y' from dual union
  select 'Freds','select * from fred','N' from dual
/
commit;
create or replace procedure run_query(p_query_name in varchar2, p_sql IN CLOB) is
  v_upperbound number;
  v_sql        dbms_sql.varchar2s;
  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_rowcount  number := 0;
  v_output    varchar2(32767);
begin
  -- Use p_query_name for the file name
  dbms_output.put_line('Opening File: '||p_query_name||'.csv'); -- here you would open your file - unless you collect the data and write it all out at the end
  -- split the clob into chunks and store in the dbms_sql varchar2s table
  -- this deals with query strings that are larger than normal varchar2 limit
  v_upperbound := ceil(dbms_lob.getlength(p_sql)/256);
  for i IN 1..v_upperbound
  loop
    v_sql(i) := dbms_lob.substr(p_sql
                               ,256 -- amount
                               ,((i-1)*256)+1 -- offset
                               );
  end loop;
  -- create a cursor
  c := dbms_sql.open_cursor;
  -- parse the SQL statement into the cursor
  dbms_sql.parse(c, v_sql, 1, v_upperbound, false, dbms_sql.native);
  -- execute the cursor
  d := dbms_sql.execute(c);
  --
  -- Describe the columns returned by the SQL statement
  dbms_sql.describe_columns(c, col_cnt, rec_tab);
  --
  -- Bind local return variables to the various columns based on their types
  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); -- Varchar2
      when 2 then dbms_sql.define_column(c,j,v_n_val);      -- Number
      when 12 then dbms_sql.define_column(c,j,v_d_val);     -- Date
    else
      dbms_sql.define_column(c,j,v_v_val,2000);  -- Any other type return as varchar2
    end case;
  end loop;

BluShadow

... continued...

  --
  -- column headers/names output (for this example dbms_output)
  v_output := null;
  for j in 1..col_cnt
  loop
    v_output := case when v_output is null then null else v_output||',' end||rec_tab(j).col_name;
  end loop;
  dbms_output.put_line(v_output); -- write this to file at this point as the first line
  --
  -- This part outputs the DATA
  loop
    -- Fetch a row of data through the cursor
    v_ret := dbms_sql.fetch_rows(c);
    -- Exit when no more rows
    exit when v_ret = 0;
    v_rowcount := v_rowcount + 1;
    -- Fetch the value of each column from the row
    v_output := null;
    for j in 1..col_cnt
    loop
      -- Fetch each column into the correct data type based on the description of the column
      case rec_tab(j).col_type
        when 1  then dbms_sql.column_value(c,j,v_v_val);
                     v_output := case when v_output is null then null else v_output||',' end||'"'||v_v_val||'"';
        when 2  then dbms_sql.column_value(c,j,v_n_val);
                     v_output := case when v_output is null then null else v_output||',' end||to_char(v_n_val); -- may want to apply specific formatting for numbers here
        when 12 then dbms_sql.column_value(c,j,v_d_val);
                     v_output := case when v_output is null then null else v_output||',' end||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'); -- adjust date format to required format
      else -- any other format treat as string
        dbms_sql.column_value(c,j,v_v_val);
        v_output := case when v_output is null then null else v_output||',' end||'"'||v_v_val||'"';
      end case;
    end loop;
    dbms_output.put_line(v_output); -- write this to file
  end loop;
  --
  -- Close the cursor now we have finished with it
  dbms_sql.close_cursor(c);
  -- depending on method used either close the file here, or if the data was collected in to a clob,
  -- write the clob out to a file and then close it all in one go here. e.g. using DBMS_XSLPROCESSOR.clob2file
  dbms_output.put_line('Closing File');
END;
/
declare
  cursor cur_queries is
    select * from z_queries where csv_gen = 'Y';
begin
  for q in cur_queries
  loop
    run_query(q.query_name, q.query);
  end loop;
end;
/
Opening File: Departments.csv
DEPTNO,DNAME,LOC
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"
50,"IT","SAN FRANCISCO"
Closing File
Opening File: Employees.csv
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,1000,30
7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,1000,30
7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10
Closing File


PL/SQL procedure successfully completed.

BluShadow

So, essentially what I've posted there is in agreement with BEDE in that using DBMS_SQL package is the best way to execute some dynamic SQL and then format the dynamic output, if you want to do it through PL/SQL and keep the processing in the database. This saves relying on different methods from different GUI tools, or writing some external scripting via ksh/bsh etc.

I haven't included the actual file writing part. You could do that with UTL_FILE package and write out the data line by line (as I've output it there) or you could collect all the data in to a resultant CLOB and then output that clob at the end of the procedure using something like DBMS_XSLPROCESSOR.CLOB2FILE

The actual writing to the file is the easy bit so I've left that for you.