Solved

Oracle Help - Execute Stored Procedure

Posted on 2010-09-08
11
616 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

932 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

8 Experts available now in Live!

Get 1:1 Help Now