Re: PLS-00103 error in sqlPLus

On 1 Aug 2003 06:56:46 -0700, am_at_cipal.be (Arvid Mestdagh) wrote:

>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?

Apparently you think you defined testclient as a type. You didn't, you did define it as a variable.
Your use of OUT is also only allowed in a formal parameter declaration.
Apparently you know T-SQL and you think PL/SQL is T-SQL sold by a different vendor. If you do know T-SQL please look up REF CURSOR in your pl/sql manual before going any further.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Aug 01 2003 - 10:27:33 CDT