Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103 in Procedure using ref cursor
icon9.gif  PLS-00103 in Procedure using ref cursor [message #397763] Mon, 13 April 2009 16:30 Go to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Hi,

I have a superclass table called StockItem and subclass tables called Magazine and Book, the PK in all tables is called PartNo. Here I am trying to get the information on either book or magazine depending on the StockItem.PartNo.

The error i get is:

Error at line 17: PLS-00103: Encountered the symbol "JOIN" when expecting one of the following:, ; for group having intersect minus order start union where connect

This is the SP:

create or replace PROCEDURE Get_Item_Details_SP (var_partNo IN NUMBER,results_cursor OUT ref cursor) AS 
DECLARE
var_Category StockItem."Category"%TYPE;
BEGIN 

Select "Category" 
INTO var_Category 
FROM StockItem
WHERE "PartNo" = var_partNo;

IF var_Category = "Book" THEN
	
open results_cursor for
Select b."Title", si."Description" , b."ISBN",b."Authors",b."Editors", b."Hardback",b."NumOfPages" as "Pages",
b."Publisher",to_char(b."PublishDate",'DD MONTH YY') as "Publish Date", si."QuantityIn" as "Remaining", si."Price"
FROM StockItem si
JOIN Book b
ON si."PartNo" = b."PartNo"
WHERE b."PartNo" = var_partNo;

ELSE IF var_Category = "Magazine" THEN

open results_cursor for
Select m."Title", si."Description" , m."Publisher",m."IssueNumber",to_char(m."IssueDate",'DD MONTH YY') as "Publish Date",
si."QuantityIn" as "Remaining", si."Price"
FROM StockItem si
JOIN Magazine m
ON si."PartNo" = m."PartNo"
WHERE m."PartNo" = var_partNo;
END IF;

END;


Any help appreciated.
Smile
Re: PLS-00103 in Procedure using ref cursor [message #397767 is a reply to message #397763] Mon, 13 April 2009 16:39 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
Quote:
IF var_Category = "Book" THEN

Use single quote....

IF monthly_value <= 4000 THEN
     ILevel := 'Low Income';

ELSIF CONDITION
     ILevel := 'Avg Income';

ELSIF CONDITION
     ILevel := 'Moderate Income';

ELSE
     ILevel := 'High Income';

END IF;

Re: PLS-00103 in Procedure using ref cursor [message #397768 is a reply to message #397767] Mon, 13 April 2009 16:41 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Thanks for the quick reply!
Tried it, didn't work Sad
Re: PLS-00103 in Procedure using ref cursor [message #397770 is a reply to message #397768] Mon, 13 April 2009 16:48 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
just compiled it in APEX object browser and the error is:

Compilation failed,line 2 (22:47:24)
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue. Compilation failed,line 17 (22:47:24)

PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: , ; for group having intersect minus order start union where connect
Re: PLS-00103 in Procedure using ref cursor [message #397784 is a reply to message #397770] Mon, 13 April 2009 23:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DECLARE does not exist in procedure, remove it.

Use SQL Formatter to make your code more readable.
Check (and fix) each statement OUTSIDE pl/sql procedure.

Also always post your Oracle version (4 decimals).

Regards
Michel
Re: PLS-00103 in Procedure using ref cursor [message #397837 is a reply to message #397784] Tue, 14 April 2009 02:43 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
my Oracle version is 11.1.0.6.0

I have removed DECLARE and I still get error:

Compilation failed,line 31 (08:38:52)
PLS-00103: Encountered the symbol ";" when expecting one of the following: if

SQL formatter wont work because there are errors in the code Sad


Im not sure what you mean when you say

Quote:
Check (and fix) each statement OUTSIDE pl/sql procedure.



Thanks
Re: PLS-00103 in Procedure using ref cursor [message #397840 is a reply to message #397837] Tue, 14 April 2009 03:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have you really given your columns mixed case names?
Why would you want to do that?

You want to use ELSIF rather than ELSE IF.
Re: PLS-00103 in Procedure using ref cursor [message #397843 is a reply to message #397837] Tue, 14 April 2009 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Im not sure what you mean when you say

Quote:
Check (and fix) each statement OUTSIDE pl/sql procedure.

Try to execute the query outside the procedure.
Copy the query text and paste it in SQL*Plus or whatever.

Regards
Michel
Re: PLS-00103 in Procedure using ref cursor [message #397844 is a reply to message #397840] Tue, 14 April 2009 03:20 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Because I thought that using a column to determine where the entry is in a sublass table would be easier than getting all the rows from a subclass table and seeing if the PK entry is in the subclass table as well as StockItem.

Ive changed my code to use ELSIF. the SP now reads

create or replace PROCEDURE Get_Item_Details_SP (var_partNo IN NUMBER,results_cursor OUT ref cursor) AS 
var_Category StockItem."Category"%TYPE;
BEGIN

Select "Category" 
INTO var_Category 
FROM StockItem
WHERE "PartNo" = var_partNo;

IF var_Category = 'Book' THEN
	
open results_cursor for
Select b."Title", si."Description" , b."ISBN",b."Authors",b."Editors", b."Hardback",b."NumOfPages" as "Pages",
b."Publisher",to_char(b."PublishDate",'DD MONTH YY') as "Publish Date", si."QuantityIn" as "Remaining", si."Price"
FROM StockItem si
JOIN Book b
ON si."PartNo" = b."PartNo"
WHERE b."PartNo" = var_partNo;

ELSIF var_Category = 'Magazine' THEN

open results_cursor for
Select m."Title", si."Description" , m."Publisher",m."IssueNumber",to_char(m."IssueDate",'DD MONTH YY') as "Publish Date",
si."QuantityIn" as "Remaining", si."Price"
FROM StockItem si
JOIN Magazine m
ON si."PartNo" = m."PartNo"
WHERE m."PartNo" = var_partNo;
END IF;

END;


and I get this error

Compilation failed,line 1 (09:17:00)
PLS-00201: identifier 'CURSOR' must be declaredCompilation failed,line 0 (09:17:00)
PL/SQL: Compilation unit analysis terminated

I dont see why id have to declare the cursor anywhere. Confused Confused

Thanks
Re: PLS-00103 in Procedure using ref cursor [message #397845 is a reply to message #397844] Tue, 14 April 2009 03:23 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
I ran them separately and they work fine. Smile

Re: PLS-00103 in Procedure using ref cursor [message #397846 is a reply to message #397763] Tue, 14 April 2009 03:29 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
create or replace PROCEDURE Get_Item_Details_SP (var_partNo IN NUMBER,results_cursor OUT ref cursor) AS 

What type is "ref cursor" (nearly in the end of the line)? It should probably be SYS_REFCURSOR instead.
By the way, you should also read the error messages. As there is only one 'CURSOR' identifier in the code you posted, you could easily spot it just searching for it.
Previous Topic: Exceptions
Next Topic: Modifications required in existing Procedure
Goto Forum:
  


Current Time: Tue Dec 06 10:41:08 CST 2016

Total time taken to generate the page: 1.07704 seconds