Oracle Help - Execute Stored Procedure

Dear Experts,

I am a Rookie in Oracle SP.

I have a SP with 1 in param and 1 out param.
I want to execute in sqlplusw

I have tried the attached procedure, but it throws "Bind variable "IOUT" not declared."

Best Regards.
SET SERVEROUTPUT ON;
VARIABLE iOut INT;
DECLARE
	vVar1 VARCHAR2(100) := 'Test';
BEGIN
	PROC2 (vVar1, :iOut);
	--PRINT :iOut;
	--DBMS_OUTPUT.PUT_LINE('iOut is: ' + iOut);
END;

Open in new window

LVL 15
StealthyDevAsked:
Who is Participating?
 
sumit2906Connect With a Mentor Commented:
hey, you are missing something again, you need to use :iOut in call to proc:
DECLARE  
  2  vVar1 VARCHAR2(100):='Test';  
  3  BEGIN  
  4  SENTHUR.PROC2(vVar1, :iOut);  
  5  DBMS_OUTPUT.PUT_LINE('iOut is: ' || :iOut);  
  6  END;
0
 
sumit2906Commented:
the code is fine, however print can not be used inside the block. try this:
VARIABLE iOut INT;  
DECLARE  
        vVar1 VARCHAR2(100) := 'Test';  
BEGIN  
        PROC2 (vVar1, :iOut);  
       
        DBMS_OUTPUT.PUT_LINE('iOut is: ' || :iOut);  
END;
/

print iOut
0
 
esmail1349Commented:
Hi
replace INT data type with number as follow
var iOut number;
INT is not defined for oracle.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
StealthyDevAuthor Commented:
still the same problem
SQL> ed
Wrote file afiedt.buf

  1  SET SERVEROUTPUT ON;
  2  VARIABLE iOut NUMBER;
  3  DECLARE
  4     vVar1 VARCHAR2(100) := 'Test';
  5  BEGIN
  6     EXEC SENTHUR.PROC2 (vVar1, :iOut);
  7* END;
  8  /
SP2-0552: Bind variable "IOUT" not declared.

Open in new window

0
 
sumit2906Commented:
you don't need EXEC here, the code should be like this:
  2  VARIABLE iOut NUMBER;  
  3  DECLARE  
  4     vVar1 VARCHAR2(100) := 'Test';  
  5  BEGIN  
  6     SENTHUR.PROC2 (vVar1, :iOut);  
  7* END;  
  8  /  
0
 
StealthyDevAuthor Commented:
Ya, its working fine now, But yet, am not able to use the VARIABLE...
I like to know how to use it?

The below code works fine..
DECLARE
	vVar1 VARCHAR2(100) := 'Test';
	iOut NUMBER;
BEGIN
	EXEC SENTHUR.PROC2 (vVar1, iOut);
	--PRINT :iOut;
	DBMS_OUTPUT.PUT_LINE('iOut is: ' || iOut);
END;
/

Open in new window

0
 
sumit2906Commented:
use ":" (colon) in dbms_output.put_line
DBMS_OUTPUT.PUT_LINE('iOut is: ' || :iOut);  
0
 
StealthyDevAuthor Commented:
@Sumit, sorry, am not able to figure out.

I use the below statements, but it throws the same error.


Thanks
SQL> SET SERVEROUTPUT ON;
SQL> VARIABLE iOut NUMBER;
SQL> DECLARE
  2  vVar1 VARCHAR2(100):='Test';
  3  BEGIN
  4  SENTHUR.PROC2(vVar1, iOut);
  5  DBMS_OUTPUT.PUT_LINE('iOut is: ' || :iOut);
  6  END;
  7  /
SENTHUR.PROC2(vVar1, iOut);
                     *
ERROR at line 4:
ORA-06550: line 4, column 22:
PLS-00201: identifier 'IOUT' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored


SQL>

Open in new window

0
 
StealthyDevAuthor Commented:
Sorry, I was in a bit hurry !

That worked !

Thanks...
SQL> SET SERVEROUTPUT ON;
SQL> VARIABLE iOut NUMBER;
SQL> DECLARE
  2  vVar1 VARCHAR(100) := 'Test';
  3  BEGIN
  4  SENTHUR.PROC2(vVar1, :iOut);
  5  DBMS_OUTPUT.PUT_LINE('iOut is:' || :iOut);
  6  END;
  7  /
iOut is:100

PL/SQL procedure successfully completed.

SQL> 

Open in new window

0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
You are mixing two different ways to call this.

See if this helps clear it up.
--create dummy proc for testing
create or replace procedure PROC2 ( var1 in varchar2, var2 out number)
is
begin
	var2 := 1;
end;
/


--two ways to do this.  SQL*Plus variables and anonymous PL/SQL block

--sql*plus

SET SERVEROUTPUT ON;
VARIABLE vVar1 varchar2(10);
VARIABLE iOut NUMBER;

exec :vVar1 := 'Test';
exec PROC2(:vVar1, :iOut);

print iOut is :iOut


--pl/sql block

DECLARE
  vVar1 VARCHAR2(100):='Test';
  iOut number;
BEGIN
  PROC2(vVar1, iOut);
  DBMS_OUTPUT.PUT_LINE('iOut is: ' || iOut);
END;
/

Open in new window

0
 
StealthyDevAuthor Commented:
The problem is I gave:
SET SERVEROUTPUT ON;
and
VARIABLE iOut NUMBER;

in the single execution.
Actually, I have used ed to edit the statements and gave all at one go, it should be as separate statements.

Regards.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.