Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unanswered: newbie question on output

    I am using the sqlplus worksheet to pull content out of an oracle db. I'm using the following code:
    VARIABLE query_output REFCURSOR
    BEGIN
    open :query_output for
    select p.title, t.toc
    from mpd.toc t,
    mpd.product p
    where p.project_number = t.project_number
    and isbn = '0072822015';
    end;

    For this I get: PL/SQL procedure successfully completed. But I'm not sure what that means, it doesn't show me the contents of the toc or title and I'm not sure where I can get them from.


    When I use this code:
    select p.title, t.toc
    from mpd.toc t,
    mpd.product p
    where p.project_number = t.project_number
    and isbn = '0072822015';

    I only get the first line in the output, but I checked the db and there are many lines in the toc field, is there some setting I need to change or something I need to add to my code to get all of the content that's in the field I am querying?

    Thank you for any help!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: newbie question on output

    Use:

    PRINT :query_output

    to see the results for the ref cursor variable.

    I cannot explain why your SELECT returns only 1 row, but it won't be a problem with SQL Plus. You say "there are many lines in the toc field": I'm not sure what you mean by that exactly (many records in the toc TABLE I presume?), but the query says that there is only 1 toc record linked to a project with isbn = '0072822015'.

  3. #3
    Join Date
    Jan 2003
    Posts
    2
    //Thank you for replying. When I use print query_output I get this:

    print query_output
    *
    ERROR at line 9:
    ORA-06550: line 9, column 1:
    PLS-00103: Encountered the symbol "PRINT"

    //What I mean by a lot of lines is that this is what is in the field in the db for the toc that goes with that isbn:
    <h1>Hall-Mercer, Beginning & Intermediate Algebra: The Language and Symbolism of Mathematics.</h1>

    <h2>DETAILED TABLE OF CONTENTS</h2>

    <h3>Chapter One OPERATIONS WITH REAL NUMBERS</h3>

    </blockquote>This chapter reviews the basic operations with real numbers. Applications are used to present further practice with these operations. These applications include: evaluating algebraic expressions, checking possible solutions to equations, and calculating the terms of a sequence. The properties of the real numbers are introduced as needed. Calculator usage and estimation skills are developed as the operations are presented. The material is driven by concepts and based on technology.</blockquote>




    //But when I run

    select p.title, t.toc
    from mpd.toc t,
    mpd.product p
    where p.project_number = t.project_number
    and isbn = '0072822015';

    //The output I get is:
    TITLE
    --------------------------------------------------------------------------------
    TOC
    --------------------------------------------------------------------------------
    Beginning & Intermediate Algebra with SMART CD
    <h1>Hall-Mercer, Beginning & Intermediate Algebra: The Language and Symbolism of

    ** I don't get the entire field

    Thank you.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by kelly_l_brown
    When I use print query_output I get this:

    print query_output
    *
    ERROR at line 9:
    ORA-06550: line 9, column 1:
    PLS-00103: Encountered the symbol "PRINT"
    Sorry, I should have said: the PRINT command is a SQL Plus command, not a PL/SQL command:

    SQL> VARIABLE query_output REFCURSOR

    SQL> BEGIN
    open :query_output for
    select p.title, t.toc
    from mpd.toc t,
    mpd.product p
    where p.project_number = t.project_number
    and isbn = '0072822015';
    end;
    /

    PL/SQL procedure successfully completed.

    SQL> PRINT :query_output

    (results shown here)

    Originally posted by kelly_l_brown
    //The output I get is:
    TITLE
    --------------------------------------------------------------------------------
    TOC
    --------------------------------------------------------------------------------
    Beginning & Intermediate Algebra with SMART CD
    <h1>Hall-Mercer, Beginning & Intermediate Algebra: The Language and Symbolism of

    ** I don't get the entire field

    Right, I see what you mean now! You need to format the output in SQL Plus. Something like this:

    SQL> COLUMN toc FORMAT A80 WRAP

    Then run the SQL again.

  5. #5
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107
    Originally posted by andrewst
    Sorry, I should have said: the PRINT command is a SQL Plus command, not a PL/SQL command:

    SQL> VARIABLE query_output REFCURSOR

    SQL> BEGIN
    open :query_output for
    select p.title, t.toc
    from mpd.toc t,
    mpd.product p
    where p.project_number = t.project_number
    and isbn = '0072822015';
    end;
    /

    PL/SQL procedure successfully completed.

    SQL> PRINT :query_output

    (results shown here)


    Right, I see what you mean now! You need to format the output in SQL Plus. Something like this:

    SQL> COLUMN toc FORMAT A80 WRAP

    Then run the SQL again.

    Hi,
    Since the variable is of refcursor type, you cannot print it.
    In SQL Plus, you have to set autoprint on.

    Try this, it will work.

    SQL> set autoprint on

    SQL> VARIABLE query_output REFCURSOR

    SQL> BEGIN
    open :query_output for
    select p.title, t.toc
    from mpd.toc t,
    mpd.product p
    where p.project_number = t.project_number
    and isbn = '0072822015';
    end;
    /
    nn

Posting Permissions

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