Call stored procedure from sqlplus
42,069
The second parameter of your procedure is an OUT
parameter -- its value will be assigned to the variable passed when the procedure completes. So you can't use a literal value for this parameter.
You can declare a bind variable at the SQLPlus prompt and use that:
-- Declare bind variable
VARIABLE x NUMBER
-- If necessary, initialize the value of x; in your example this should be unnecessary
-- since the value of the second parameter is never read
EXEC :x := 1
-- Call the procedure
EXEC testproc(12, :x)
-- Print the value assigned to the bind variable
PRINT x
Alternatively, you can use an anonymous PL/SQL block:
-- Activate client processing of dbms_output buffer
SET SERVEROUTPUT ON
-- In anonymous block, declare variable, call procedure, print resulting value
DECLARE
x NUMBER;
BEGIN
testproc(12, x);
dbms_output.put_line( x );
END;
/
Author by
Rupesh
I have expeirence is Product Development using Microsoft Technologies. Proficinet in programming languages such as C# VB.Net. Proficient in SSIS, SSAS , SSRS, SQL Server 2008+ to Azure Passionate about learning new technology.
Updated on June 24, 2020Comments
-
Rupesh almost 4 years
How to call a stored procedure from sqlplus?
I have a procedure:
Create or replace procedure testproc(parameter1 in varachar2,parameter2 out varchar2) begin Do something end;
I tried exec
testproc(12,89)
::Returns error