Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    57

    Unanswered: Spooling a Cursor

    Is there a way to spool the output of cursor contained within a SQL script?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    More detail needed on this question. Maybe you mean something like this:
    Code:
    SQL> set serverout on
    SQL> spool emp.txt
    SQL> begin
      2    for r in (select * from emp) loop
      3      dbms_output.put_line('Employee '||r.empno||' is called '||r.ename);
      4    end loop;
      5  end;
      6  /
    Employee 7369 is called SMITH
    Employee 7499 is called ALLEN
    Employee 7521 is called WARD
    Employee 7566 is called JONES
    Employee 7654 is called MARTIN
    Employee 7698 is called BLAKE
    Employee 7782 is called CLARK
    Employee 7788 is called SCOTT
    Employee 7839 is called KING
    Employee 7844 is called TURNER
    Employee 7876 is called ADAMS
    Employee 7900 is called JAMES
    Employee 7902 is called FORD
    Employee 7934 is called MILLER
    
    PL/SQL procedure successfully completed.
    
    SQL> spool off
    Or perhaps:
    Code:
    SQL> var rc refcursor
    SQL> begin
      2    open :rc for select ename from emp;
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> spool emp.txt
    SQL> print rc
    
    ENAME
    ----------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER
    
    14 rows selected.
    
    SQL> spool off
    Or...?

  3. #3
    Join Date
    Nov 2004
    Posts
    57

    DBMS_OUTPUT Package not Installed

    I read up on this issue. Exporting to an ASCI file is available with the UTL_FILE package. Unluckily this package was not installed with the database when it was created; nor do I have the option of installing it afterwards due to support issues from our vendor with modifications to the database.

    Likewise the package DBMS_OUTPUT (dbmsoutp.sql) is also no where to be found. Without these two packages I think I have my hands tied using PL/SQL to output to ASCI file.

    I think I have to go about it the way that my predicessor had with using an intermediate table and then reading from that table using SQL*Plus and doing a simple SPOOL.

  4. #4
    Join Date
    Nov 2004
    Posts
    57
    Here's a couple links from Oracle that is attempting to do something similiar to what I'm attempting and their suggestion.

    http://asktom.oracle.com/pls/ask/f?p...D:241814624807

    http://asktom.oracle.com/pls/ask/f?p...14293414688590

    http://asktom.oracle.com/~tkyte/Misc/csv.html

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Can you not just extract the sql in the cursor and run it in sqlplus with spooling on? Much better than using a temp table unless you really are doing a lot of processing of the data from the cursor.

    Alan

  6. #6
    Join Date
    Nov 2004
    Posts
    57

    Question

    Well I gave up on attempting writing the ASCI file all in one step with PL/SQL and opted for doing this in 2 scripts. The first script containing a cursor will populate a intermediate table. The second script will simply spool the desired table columns to a ASCI file.

    When I run my first script with the cursor I get the error message of:


    Code:
    "PLS-00201: identifier 'W100.VARIAL_KORE' must be declared
       ORA-06550: line 374, column 3:
       PL/SQL: SQL Statement ignored"

    The line refered to is simply a insert statement that comes at the end of my script after my cursor. The line reads:


    Code:
    insert into w100.varial_kore values (schreib_string,'N',belegnummer,
    		substr(konto_nr,2,8),substr(gegenkonto_nr,2,8),zahlungs_kond_nr,
    		kst_ktr1_nr,bs_bezeichnung);
    			
    		commit;

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There should be a comma (,) behind "zah". Or is it "zah_lungs_kond_nr"?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The error message "PLS-00201: identifier 'W100.VARIAL_KORE' must be declared" is what you would get if there was no such table as VARIAL_KORE in the W100 schema, or if your session didn't have the privileges to see it:
    Code:
    SQL> begin
      2    insert into no_such_table values ('x');
      3  end;
      4  /
    begin
    *
    ERROR at line 1:
    ORA-06550: line 2, column 15:
    PLS-00201: identifier 'NO_SUCH_TABLE' must be declared

  9. #9
    Join Date
    Nov 2004
    Posts
    57
    The error message that I was getting was due to the fact that the table I was attempting to update was in another instance. The problem was corrected when I switched to the correct instance.

    The script works now and I'd like to tank Tony and Littlefoot for their help again. Thanks guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •