Forum Stats

  • 3,828,121 Users
  • 2,260,865 Discussions
  • 7,897,483 Comments

Discussions

SELECT CLOB in SQLPLUS is too slow

User_FOA6U
User_FOA6U Member Posts: 1 Green Ribbon

Hi I have to fetch around 1.5 million records from the DB and spool it to a text file. Below is my code, I had to use to_clob as there are about 1.5 million records in the DB and it gives an error (ORA-01489: result of string concatenation is too long) without using to_clob. However, using to_clob affects the performance. It takes several days to finish running. Please suggests any improvements in my code. I would need the data to be in the txt file in couple of hours. SQL developer takes only 3 hours to export all the data.


set heading off 

SET NEWPAGE NONE

set linesize 20000 pagesize 10000

set feedback off verify off trimspool on trimout on

set long 2000000000

SET LONGCHUNKSIZE 10000

SELECT '"'||to_clob(ID)||'"'||'|'||'"'||to_clob(NUMBER)||'"'||'|'||'"'||to_clob(Department)||'"'||'|'||'"'||to_clob(NAME)||'"'  FROM table;

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,084 Red Diamond

    Hi, @User_FOA6U

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Note tahat TABLE is not a good table name, and NUMBER is not a good column name. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    If you can show what you need to do using commonly available tables (such as the tables in the SCOTT schema) then you don't need to post any sample data; just the results, explanation and version).

    Here's an example of a reasonable question:

    "I need to SPOOL the output of a query into a pipe-delimited file (that is, a CSV, but using '|' as the delimiter rather than ','.) Using the scott.emp table, I tried doing something like this:

    SELECT  '"' || empno  || '"|"' 
     	    || deptno || '"|"' 
    	    || job    || '"|"' 
    	    || ename  || '"' AS data
    FROM	 scott_emp
    WHERE	 ename < 'F' -- just to keep the result set small
    ORDER BY ename
    ;
    

    and that worked pretty well, but in my real table there are many more columns, and some of them can be long, so the total length of the concatenated string can be too big for a VARCHAR2, and using TO_CLOB is very slow. Is there a better way to produce the delimited output?"


    If that is your question, then one answer is to use the SQL*Plus SET MARKUP CSV command. like this

    SET	MARKUP	CSV ON   DELIMITER |  
    SET	HEADING	 OFF
    
    SPOOL	data.csv
    
    SELECT   empno, deptno, job, ename
    FROM	 scott_emp
    WHERE	 ename < 'F' -- just to keep the result set small
    ORDER BY ename
    ;
    
    SPOOL	OFF
    
    -- Undo the CSV markup if necessary
    SET	MARKUP	CSV OFF
    SET	HEADING	 ON
    

    Note that this automatically quotes the strings, but not the NUMBERs, so the output is actually:

    7876|20|"CLERK"|"ADAMS"
    7499|30|"SALESMAN"|"ALLEN"
    7698|30|"MANAGER"|"BLAKE"
    7782|10|"MANAGER"|"CLARK"
    

    If you really, really want quotes around the NUMBERs, too, then use TO_CHAR in the query, so that those columns are strings and will be automaticallly quoted.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond


    Does your table actually contain any CLOB columns, or are they just regular datatypes like VARCHAR, NUMBER, DATE etc?

    If it's just regular datatypes, then you could generate the CSV data to a file on the server using something like below...

    As SYS user:
    
    
    CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
    /
    GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
    /
    
    
    As user:
    
    
    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;
    /
    
    Example:
    
    SQL> exec run_query('select * from emp','TEST_DIR','output.csv');
     
    PL/SQL procedure successfully completed.
    
    
    Output.csv file contains:
    
    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,,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,,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
    
    
    Adapt to output different datatypes and styles are required.
    
    


    The way you are doing it from SQL*Plus usually implies you're doing the export to a file on a client machine rather than the server. Is that the case? If you're doing that, you have to consider that you're transferring all the data from the database server to your client where the SQL*Plus tool is then 'rendering' the data output and putting it to a file locally. That can introduce delays due to network traffic and the intervention of the SQL*Plus tool itself.

    Using code like above, will generate the data directly to a file on the database server and directly from PL/SQL which is typically faster.

    Also, for most companies, you have to consider data security/data protection. Transferring data (and certainly large amounts of data, especially if contains personal information) to a file stored locally on a client machine rather than inside the secure database/server, is often a breach of data protection laws. Usually, transfer of data from one database to a destination should ensure that both end-points are secure and that the data doesn't appear 'in-the-clear' at any point in between, or if it does, it's maintained in a secure environment (i.e. remains on secure servers that typically aren't connected to the internet).