Home » SQL & PL/SQL » SQL & PL/SQL » Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters
icon1.gif  Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters [message #285855] Wed, 05 December 2007 22:11 Go to next message
subhashsubramanyam
Messages: 2
Registered: December 2007
Location: INDIA
Junior Member
Hi Friends,
Could you please help me correct the syntax in the stored procedure below, I hae tried my level best to adjust things with hardly any materials oline:

CREATE OR REPLACE PROCEDURE SP_WEEKDAYS(START_DATE IN DATE, END_DATE IN DATE, CALENDARID IN CHAR(2), TOTALDAYS OUT NUMBER(20), WORKINGDAYS OUT NUMBER(20))
AS
SELECT COUNT(WORKDAY_FLG) INTO TOTALDAYS, SUM(WORKDAY_FLG) INTO WORKINGDAYS FROM RIP_CAL_FACTORY_CALENDAR
WHERE CALENDAR_DATE BETWEEN START_DATE AND END_DATE AND (CALENDAR_ID = CALENDARID) ;
END ;


I get the following errors on the Sqlplus Prompt:
PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.

PLS-00103: Encountered the symbol "INTO" when expecting one of the following:

. ( , % from
The symbol ". was inserted before "INTO" to continue

[Updated on: Wed, 05 December 2007 22:12]

Report message to a moderator

Re: Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters [message #285856 is a reply to message #285855] Wed, 05 December 2007 22:15 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I hae tried my level best to adjust things with hardly any materials oline:
Such as the whole documentation set found at http://tahiti.oracle.com; which include the PL/SQL Reference Manual?

http://asktom.oracle.com which contains many fine coding examples
Re: Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters [message #285858 is a reply to message #285855] Wed, 05 December 2007 22:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


1/ Your method of passing arguement is wrong
2/ You method of o/p nassignment is wrong

Thumbs Up
Rajuvan.
Re: Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters [message #285859 is a reply to message #285855] Wed, 05 December 2007 22:35 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
GOOGLE is your friend, but ONLY when you use it!
Results 1 - 10 of about 63,500 for Oracle Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters . (0.31 seconds
Re: Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters [message #286169 is a reply to message #285855] Thu, 06 December 2007 11:53 Go to previous messageGo to next message
DbSpa
Messages: 7
Registered: November 2007
Junior Member
why can't u try by passing parameters without mentioning size....
Re: Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters [message #286170 is a reply to message #285855] Thu, 06 December 2007 11:55 Go to previous messageGo to next message
DbSpa
Messages: 7
Registered: November 2007
Junior Member
Also, did you inserted BEGIN after AS?

[Updated on: Thu, 06 December 2007 11:56]

Report message to a moderator

Re: Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters [message #286209 is a reply to message #286169] Thu, 06 December 2007 21:32 Go to previous messageGo to next message
subhashsubramanyam
Messages: 2
Registered: December 2007
Location: INDIA
Junior Member
Atlast I got it working, Shame that we don't find a better reference. I've tried googling best ways using different combination of key words. Finally a friend helped me correcting the syntax. Thanks to Ayush and others who has attempted to answer here.

CREATE OR REPLACE PROCEDURE SP_WEEKDAYS(START_DT IN DATE, END_DT IN DATE, CALENDARID IN CHAR, TOTALDAYS OUT NUMBER, WORKINGDAYS OUT NUMBER)
IS
BEGIN
SELECT COUNT(WORKDAY_FLG)INTO TOTALDAYS FROM RIP_CAL_FACTORY_CALENDAR
WHERE CALENDAR_DATE BETWEEN START_DT AND END_DT AND (CALENDAR_ID = CALENDARID) ;
SELECT SUM(WORKDAY_FLG) INTO WORKINGDAYS FROM RIP_CAL_FACTORY_CALENDAR
WHERE CALENDAR_DATE BETWEEN START_DT AND END_DT AND (CALENDAR_ID = CALENDARID) ;

END ;

[Updated on: Thu, 06 December 2007 21:35]

Report message to a moderator

Re: Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters [message #286212 is a reply to message #285855] Thu, 06 December 2007 22:02 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Even you could have tried both sql into a single one like

SELECT COUNT(WORKDAY_FLG),  SUM(WORKDAY_FLG)
INTO ......


Thumbs Up
Rajuvan
Previous Topic: Listing triggers
Next Topic: join the result of two different queries
Goto Forum:
  


Current Time: Fri Dec 02 22:46:19 CST 2016

Total time taken to generate the page: 0.09786 seconds