Home » SQL & PL/SQL » SQL & PL/SQL » procedure parameter
procedure parameter [message #241516] Tue, 29 May 2007 19:16 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
What is the problem with this ?

Can't seem to understand

create procedure proc1
(
pHICN IN VARCHAR2(12),
pCurrentPaymentDate IN TIMESTAMP(3),
pEligible out NUMBER(1,0),
pExceptionCode out NUMBER(38,0)
)
as
begin
....

end;
/

Its throwing an error
S2712 Expecting ] , := DEFAULT WITH

Can't understand what is the error ?

Thanks
Yog
Re: procedure parameter [message #241517 is a reply to message #241516] Tue, 29 May 2007 19:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Since you refuse to use CUT & PASTE so we can really see what is happening, I refuse to guess at the problem or possible solution.

Please read & FOLLOW the posting guidelines in the #1 STICKY post at the top of this forum.

[Updated on: Tue, 29 May 2007 19:26] by Moderator

Report message to a moderator

Re: procedure parameter [message #241519 is a reply to message #241517] Tue, 29 May 2007 19:41 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
ok the formatted version of code:

CREATE OR REPLACE PROCEDURE PROC1
(
	pHICN          IN     VARCHAR2(12),
	pCurrentPaymentDate IN     TIMESTAMP(3),
	pEligible  OUT    VARCHAR2(1),
	pExceptionCode  OUT    INTEGER
) 
IS  
BEGIN

   BEGIN

    pMEPE_ELIG_IND := 'Y';
    pExceptionCode := 0;

    SELECT 
     ...
    INTO
       ...
    BLAH BLAH ;

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         pExceptionCode := 0;
         NULL;         
      WHEN OTHERS THEN 
         pExceptionCode := SQLCODE;
         NULL;
   END;
  
END;
/
Re: procedure parameter [message #241520 is a reply to message #241516] Tue, 29 May 2007 19:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
error? what error?
I don't see any error.
Re: procedure parameter [message #241521 is a reply to message #241520] Tue, 29 May 2007 19:58 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
thats what i don't understand

PROC1
CREATE OR REPLACE PROCEDURE PROC1
(
	pHICN          IN     VARCHAR2(12),
	pCurrentPaymentDate IN     TIMESTAMP(3),
	pEligible  OUT    VARCHAR2(1),
	pExceptionCode  OUT    INTEGER
) 
IS  
BEGIN

   BEGIN

    pMEPE_ELIG_IND := 'Y';
    pExceptionCode := 0;

    SELECT 
     ...
    INTO
       ...
    BLAH BLAH ;

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         pExceptionCode := 0;
         NULL;         
      WHEN OTHERS THEN 
         pExceptionCode := SQLCODE;
         NULL;
   END;
  
END;
/


When I compile I get an error :
NO CREATE OR REPLACE STMT FOUND TO EXECUTE
Expecting } ; := CHARACTER DEFAULT

Blah Blah...

However when I change it as below, it works. Strange ? The difference is the number of characters specified in the datatype. See changes below. Can't seem to understand ?
CREATE OR REPLACE PROCEDURE PROC1
(
	pHICN          IN     VARCHAR2,
	pCurrentPaymentDate IN     TIMESTAMP,
	pEligible  OUT    VARCHAR2,
	pExceptionCode  OUT    INTEGER
) 
IS  
BEGIN
 
END;
/
Re: procedure parameter [message #241522 is a reply to message #241516] Tue, 29 May 2007 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>The difference is the number of characters specified in the datatype.
>Can't seem to understand ?
What make you think that "(12)" is valid in the argument list?
Re: procedure parameter [message #241537 is a reply to message #241522] Tue, 29 May 2007 22:27 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
Are you saying that it is invalid.

How do you limit the length of DATATYPEs in parameters then ?

Thanks
Yog
Re: procedure parameter [message #241539 is a reply to message #241516] Tue, 29 May 2007 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Here is a free clue.
Answers to questions about PL/SQL can be found in the PL/SQL Reference Manual at
http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
Rather than practicing the Ready, Fire, Aim school of programming; you should RTFM listed above.
Re: procedure parameter [message #241888 is a reply to message #241516] Wed, 30 May 2007 19:00 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
Thank you for your response anacedent.

Anyone know in terms of memory, how much memory will be used for the parameters in this case ?

When we just say VARCHAR2 in the parameter of the procedure , and the field is only a 5 character length, how much memory will oracle allocate during execution ?

Thank you,
Yog
Re: procedure parameter [message #241893 is a reply to message #241516] Wed, 30 May 2007 19:23 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>When we just say VARCHAR2 in the parameter of the procedure , and the field is only a 5 character length, how much memory will oracle allocate during execution ?
Why are you obsessing over 5 bytes when it is likely running on a box with 512MB or MORE?
Since you can NOT really control how Oracle allocates memory, I doubt that knowing a definitive answer will get you closer to Nirvana or even help you do your job better.
FWIW - the maximum single of any input arguement is limited/governed by the size of the variable which is being passed into it.
Calling routines could have different sized variables used as input arguments which is why it is WRONG to declare any size in the called routine.
Previous Topic: CASE help
Next Topic: passing value in a pl/sql block
Goto Forum:
  


Current Time: Sat Dec 10 18:59:12 CST 2016

Total time taken to generate the page: 0.14028 seconds