Solved

Oracle Help - Execute Stored Procedure

Posted on 2010-09-08
11
615 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:StealthyDev
11 Comments
 
LVL 7

Expert Comment

by:sumit2906
ID: 33625593
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
 
LVL 1

Expert Comment

by:esmail1349
ID: 33625666
Hi
replace INT data type with number as follow
var iOut number;
INT is not defined for oracle.
0
 
LVL 15

Author Comment

by:StealthyDev
ID: 33625979
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
 
LVL 7

Expert Comment

by:sumit2906
ID: 33626039
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
 
LVL 15

Author Comment

by:StealthyDev
ID: 33626055
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:sumit2906
ID: 33626068
use ":" (colon) in dbms_output.put_line
DBMS_OUTPUT.PUT_LINE('iOut is: ' || :iOut);  
0
 
LVL 15

Author Comment

by:StealthyDev
ID: 33626127
@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
 
LVL 7

Accepted Solution

by:
sumit2906 earned 450 total points
ID: 33626152
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
 
LVL 15

Author Comment

by:StealthyDev
ID: 33626153
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 total points
ID: 33626193
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
 
LVL 15

Author Comment

by:StealthyDev
ID: 33626194
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now