Re: PL/SQL block, won't accept accept

On Jan 29, 8:34 pm, "Mitch" <spudtheimpa..._at_gmail.com> wrote:
> So far this is my work around:
>
> [code]
> CREATE OR REPLACE PROCEDURE TTT_Move_Proc(aTTT_PlayerMoveColumn IN
> VARCHAR2, aTTT_PlayerMoveRow IN VARCHAR2)
> AS
> BEGIN
> DBMS_OUTPUT.PUT_LINE('You chose cell: '||aTTT_PlayerMoveColumn||
> aTTT_PlayerMoveRow);
> END;
> /
>
> ACCEPT TTT_PlayerMoveColumn PROMPT 'Please select a Column for your
> move: ';
> ACCEPT TTT_PlayerMoveRow PROMPT 'Please select a Row Number for your
> move: ';
> EXECUTE TTT_Move_Proc(TTT_PlayerMoveColumn, TTT_PlayerMoveRow);
> [/code]
>
> With an error as such:
>
> [output]
> SQL> @TTT_Move
>
> Procedure created.
>
> Please select a Column for your move: A
> Please select a Row Number for your move: 2
> BEGIN TTT_Move_Proc(TTT_PlayerMoveColumn, TTT_PlayerMoveRow); END;
>
> *
> ERROR at line 1:
> ORA-06550: line 1, column 21:
> PLS-00201: identifier 'TTT_PLAYERMOVECOLUMN' must be declared
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> [/output]
>
> Now I'm mostly comfused as the line it claims to error on isn't in my
> file. I don't know if execute does some substitution but that has
> stumped me a bit. Secondly again I think it's the variables causing
> me trouble.
>
> Does anyone know a workaround? Basically I'm just after using
> variables passed to a procedure but it is giving me grief.
>
> Cheers!
> Mitch.

Yes, EXECUTE in SQL*Plus wraps its arguments into BEGIN ... END; block automatically. And SQL*Plus variables, when used as arguments, should be prefixed with '&' or whatever character you defined with SET DEFINE command:

EXECUTE TTT_MoveProc('&TTT_PlayerMoveColumn', '&TTT_PlayerMoveRow');

(note they are surrounded by quotes - SQL*Plus simply replaces the variables with whatever you typed in, so it's up to you to typecast the input properly, in this case - to strings.) And you don't need to create the procedure again for each call - once compiled it will stay in the database until you drop it.

Again, more reading (assuming you're on 10g R2): http://www.oracle.com/pls/db102/to_toc?pathname=server. 102%2Fb14357%2Ftoc.htm

Please RTFM, at least those referenced, before posting more questions.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Mon Jan 29 2007 - 13:06:35 CST