Link to home
Start Free TrialLog in
Avatar of StealthyDev
StealthyDev

asked on

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

Avatar of sumit2906
sumit2906
Flag of India image

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
Hi
replace INT data type with number as follow
var iOut number;
INT is not defined for oracle.
Avatar of StealthyDev
StealthyDev

ASKER

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

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

use ":" (colon) in dbms_output.put_line
DBMS_OUTPUT.PUT_LINE('iOut is: ' || :iOut);  
@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

ASKER CERTIFIED SOLUTION
Avatar of sumit2906
sumit2906
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.