This discussion is archived
10 Replies Latest reply: May 10, 2007 4:23 AM by AlokKumar RSS

How to spool to generate ~ character?

AlokKumar Explorer
Currently Being Moderated
Hi All,

I need to know that is there any way by whcih I can spool a file with a '~' character
in between rows values. If not then, is there any workaround for the same?


hare krishna
Alok
  • 1. Re: How to spool to generate ~ character?
    57043 Newbie
    Currently Being Moderated
    Can you give an example ?

    thanks & regards
    paragjp
  • 2. Re: How to spool to generate ~ character?
    175489 Newbie
    Currently Being Moderated
    SQL> set colsep ~
    SQL> select ename,job from scott.emp;
    
    ENAME     ~JOB
    ----------~---------
    SMITH     ~CLERK
    ALLEN     ~SALESMAN
    WARD      ~SALESMAN
    JONES     ~MANAGER
    MARTIN    ~SALESMAN
    BLAKE     ~MANAGER
    CLARK     ~MANAGER
    SCOTT     ~ANALYST
    KING      ~PRESIDENT
    TURNER    ~SALESMAN
    ADAMS     ~CLERK
    JAMES     ~CLERK
    FORD      ~ANALYST
    MILLER    ~CLERK
    
    14 rows selected.
    Patrick
  • 3. Re: How to spool to generate ~ character?
    388131 Explorer
    Currently Being Moderated
    Hi,

    SET COLSEP '~' does the trick...


    Rgds,
    Guido
  • 4. Re: How to spool to generate ~ character?
    AlokKumar Explorer
    Currently Being Moderated
    I don't need a space in between rows. I am pasting some sample data on the board.

    628211183~Alessio~Rinaldi~25/01/2007~29/01/2007~ 9160.00~ 12950.48~CA~~SPLD I~INR~LT~AZ~00829595~AGRTR~E~
    600087656~Avneesh~Chadha~04/01/2007~06/01/2007~ 8810.38~ 10110.12~CA~~PKGIHH02~INR~PK~ ~ ~AGRTR~E~
    434958457~Robert C~Peterson~24/01/2007~25/01/2007~ 5708.25~ 10558.21~VA~~HNL-FFR~INR~FI~ ~ ~AGRTR~E~

    hare krishna
    Alok
  • 5. Re: How to spool to generate ~ character?
    57043 Newbie
    Currently Being Moderated
    16:22:46 SQL> select rownum n, empno||'~'||ename string1
    16:22:56 2 from emp
    16:22:57 3 /

    N~STRING1
    ----------~---------------------------------------------------
    1~7369~SMITH
    2~7499~ALLEN
    3~7521~WARD
    4~7566~JONES
    5~7654~MARTIN
    6~7698~BLAKE
    7~7782~CLARK
    8~7788~SCOTT
    9~7844~TURNER
    10~7876~ADAMS
    11~7777~FORD
    12~7934~MILLER

    12 rows selected.

    1 select replace(ltrim(max(sys_connect_by_path(string1,'@')),'@'),'@',null) x1
    2 from
    3 (
    4 select rownum n, empno||'~'||ename string1
    5 from emp
    6 )
    7 start with n = 1
    8* connect by prior n = n - 1
    16:22:44 SQL> /

    X1
    --------------------------------------------------------------------------------
    7369~SMITH7499~ALLEN7521~WARD7566~JONES7654~MARTIN7698~BLAKE7782~CLARK7788~SCOTT
    7844~TURNER7876~ADAMS7777~FORD7934~MILLER

    I hope you are using oracle 9 and above.

    thanks & regards
    paragjp
  • 6. Re: How to spool to generate ~ character?
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    CREATE OR REPLACE procedure CSV(query varchar2, separator char default ';') IS
      curid NUMBER; 
      desctab DBMS_SQL.DESC_TAB; 
      colcnt NUMBER; 
      namevar VARCHAR2(4000); 
    begin
      curid := dbms_sql.open_cursor;
      dbms_sql.parse(curid, query, dbms_sql.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
      FOR i IN 1 .. colcnt LOOP
        DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
      END LOOP;
      IF DBMS_SQL.execute(curid) = 0 THEN
        WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
          FOR i IN 1 .. colcnt LOOP
            IF (i>1) then
              dbms_output.put(separator);
            end IF;
            DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
            IF (namevar IS NOT NULL) 
            then
              dbms_output.put(namevar);
            end IF;
          END LOOP;
          dbms_output.new_line;
        END LOOP;
      END IF;
    end;
    /
    try it !
    SQL> execute csv('select * from emp','~');
    7369~SMITH~CLERK~7902~17-DEC-80~800~~20
    7499~ALLEN~SALESMAN~7698~20-FEB-81~1600~300~30
    7521~WARD~SALESMAN~7698~22-FEB-81~1250~500~30
    7566~JONES~MANAGER~7839~02-APR-81~2975~~20
    7654~MARTIN~SALESMAN~7698~28-SEP-81~1250~1400~30
    7698~BLAKE~MANAGER~7839~01-MAY-81~2850~~30
    7782~CLARK~MANAGER~7839~09-JUN-81~2450~~10
    7788~SCOTT~ANALYST~7566~19-APR-87~3000~~20
    7839~KING~PRESIDENT~~17-NOV-81~5000~~10
    7844~TURNER~SALESMAN~7698~08-SEP-81~1500~0~30
    7876~ADAMS~CLERK~7788~23-MAY-87~1100~~20
    7900~JAMES~CLERK~7698~03-DEC-81~950~~30
    7902~FORD~ANALYST~7566~03-DEC-81~3000~~20
    7934~MILLER~CLERK~7782~23-JAN-82~1300~~10
  • 7. Re: How to spool to generate ~ character?
    57043 Newbie
    Currently Being Moderated
    Hi Laurent,

    Excellent. But Oracle versions less than 10g we mostly face this problem

    ERROR at line 1:
    ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
    ORA-06512: at "SYS.DBMS_OUTPUT", line 35
    ORA-06512: at "SYS.DBMS_OUTPUT", line 198
    ORA-06512: at "A.CSV", line 25
    ORA-06512: at line 1

    Kindly suggest best way to avoid this problem.

    thanks & regards
    paragjp
  • 8. Re: How to spool to generate ~ character?
    AlokKumar Explorer
    Currently Being Moderated
    Thanks Laurant, but that procedure doesen't return the values with ~ character.

    SQL> CREATE OR REPLACE procedure CSV(query varchar2, separator char default ';') IS
    2 curid NUMBER;
    3 desctab DBMS_SQL.DESC_TAB;
    4 colcnt NUMBER;
    5 namevar VARCHAR2(4000);
    6 begin
    7 curid := dbms_sql.open_cursor;
    8 dbms_sql.parse(curid, query, dbms_sql.NATIVE);
    9 DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
    10 FOR i IN 1 .. colcnt LOOP
    11 DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
    12 END LOOP;
    13 IF DBMS_SQL.execute(curid) = 0 THEN
    14 WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
    15 FOR i IN 1 .. colcnt LOOP
    16 IF (i>1) then
    17 dbms_output.put(separator);
    18 end IF;
    19 DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
    20 IF (namevar IS NOT NULL)
    21 then
    22 dbms_output.put(namevar);
    23 end IF;
    24 END LOOP;
    25 dbms_output.new_line;
    26 END LOOP;
    27 END IF;
    28 end;
    29 /

    Procedure created.

    SQL> execute csv('select * from emp','~');

    PL/SQL procedure successfully completed.

    this is what I get, when I run the procedure suggested by you.

    hare krishna
    Alok
  • 9. Re: How to spool to generate ~ character?
    175489 Newbie
    Currently Being Moderated
    SQL> set serveroutput on size 1000000
    Patrick
  • 10. Re: How to spool to generate ~ character?
    AlokKumar Explorer
    Currently Being Moderated
    Yes, It got worked! Thanks Laurant and PW.

    hare krishna
    Alok