10 Replies Latest reply: May 10, 2007 6:23 AM by AlokKumar RSS

    How to spool to generate ~ character?

    AlokKumar
      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
          Can you give an example ?

          thanks & regards
          paragjp
          • 2. Re: How to spool to generate ~ character?
            175489
            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
              Hi,

              SET COLSEP '~' does the trick...


              Rgds,
              Guido
              • 4. Re: How to spool to generate ~ character?
                AlokKumar
                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
                  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
                    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
                      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
                        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
                          SQL> set serveroutput on size 1000000
                          Patrick
                          • 10. Re: How to spool to generate ~ character?
                            AlokKumar
                            Yes, It got worked! Thanks Laurant and PW.

                            hare krishna
                            Alok