Problem with Accepting user input in a procedure within a package...

[lokesh123]

I have a procedure in a package where i need to accept a user input. However while i compile this package, the user input is being prompted which is not my requirement. While executing this procedure the value of the input variable is that which has been enterd during compilation time.
Is there anyway by which i can avoid the i/p being prompted at compilation time and request for user input only when that procedure is called ?

 

[Triton46]

Procedures cannot accept input...only functions.

 

[Triton46]

Sorry, I meant only functions can return values (aside from insert, update, delete).

Are you compiling in SQLPlus?

 

[lokesh123]

I'm not trying to return a value. Let me explain with a hypothetical case. Assume that i have a SQl statement , Select * from emp where empno = &empno; . This SQL statement is a part of a procedure or a function (say 'x')in a package (say 'y'). When i compile this package 'y', the compiler is prompting me to enter the value for 'empno', which according to my requirement should be prompted when i execute this procedure/function 'x' at the SQL prompt using 'y.x'. However, when i key in 'y.x' at SQL prompt, the variable takes the value of 'empno' that was prompted for at compilation time. I hope i have made my requirement clear now.

 

[sem]

The value of lexical variable is substituted before compile time so you have no chance to create stored procedure with &-style variable. Lexical variables are used in Sql*plus, not in Oracle stored procedures. The only way is to create some procedure, possibly using dynamic sql, with parameter. You may pass this parameter from some "outer" script.

 

[JamesGordon]

Any function or procedure can accept input just pass the value in as a parameter.

procedure get_data(p_empno) is
begin
select * from emp where empno = p_empno;
end;

I know that the syntax is bad as it really does nothing but shows how to pass in a value.
From SQL*Plus you can run:
exec get_data(&empno);
and it will ask you for the value of empno. Or even put he exec call into a SQL file, change &empno to &1 and call
@get_data.sql 12
It will take the first parameter and replace &1 with the number 12.