Solved

Oracle Help - Execute Stored Procedure

Posted on 2010-09-08
11
620 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

734 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