Forum Stats

  • 3,734,282 Users
  • 2,246,937 Discussions
  • 7,857,218 Comments

Discussions

CSV file from stored queries

User_70275
User_70275 Member Posts: 1 Green Ribbon

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

Tagged:

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,234 Gold Trophy

    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
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited Mar 17, 2021 9:16AM

    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
    User_70275 Member Posts: 1 Green Ribbon

    @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
    Gaz in Oz Member Posts: 3,776 Bronze Crown

    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
    User_70275 Member Posts: 1 Green Ribbon

    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
    BluShadow Member, Moderator Posts: 40,989 Red Diamond

    (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
    BluShadow Member, Moderator Posts: 40,989 Red Diamond

    ... 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
    BluShadow Member, Moderator Posts: 40,989 Red Diamond

    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.

Sign In or Register to comment.