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!

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'.

//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.

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;
/