Re: PLS-00103 error in sqlPLus

Hi Arvid Mestdagh, thanks for writing this:
> Hi I want to create a simple stored procedure but stumble across the
> syntax.
>
> I get the following error message (after the create...)
> SQL> CREATE OR REPLACE PROCEDURE TEST (arvid in FLOAT) AS begin SELECT
> pkMNCLNTref from MNCLNT WHERE
> MNCLNT.pkMNCLNTref = TEST.arvid;end;
> 2 /
>
> Warning: Procedure created with compilation errors.
>
> SQL> show error
> Errors for PROCEDURE TEST:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 1/42 PLS-00428: an INTO clause is expected in this SELECT
> statement
> 1/42 PL/SQL: SQL Statement ignored
>
>
> I tried something of the following :
> SQL> CREATE OR REPLACE PROCEDURE TEST (arvid in varchar) AS
> 2 begin
> 3 declare
> 4 testclient MNCLNT%ROWTYPE;
> 5 vartestclient OUT testclient ;
> 6 SELECT * into vartestclient from MNCLNT WHERE MNCLNT.pkMNCLNTref
> = to_NUMBER(arvid);
> 7 end
> 8 /
>
> Warning: Procedure created with compilation errors.
>
> SQL> show error
> Errors for PROCEDURE TEST:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 5/19 PLS-00103: Encountered the symbol "TESTCLIENT" when expecting
> one
> of the following:
> := . ( @ % ; not null range default character
>
> 7/3 PLS-00103: Encountered the symbol "end-of-file" when
> expecting
> one of the following:
> ; <an identifier> <a double-quoted delimited-identifier>
> delete exists prior <a single-quoted SQL string>
> The symbol ";" was substituted for "end-of-file" to continue.
>
> but still with errors.
>
> Can someone tell me what I am doing wrong?
>

A couple of things:

• The use of the OUT clause is legal only in the formal arg list of the procedure.
• You don't need the DECLARE statement (it's implicit) in a procedure (unless you're coding an internal block).
• If your SELECT returns more than one row, you cannot put the result set in a simple, scalar variable. Instead you need to look at how a REF CURSOR is implemented.

You might want to either look over the PL/SQL manual at http://docs.oracle.com or search for ref cursor at http://asktom.oracle.com.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]