Solved

Oracle Help - Execute Stored Procedure

Posted on 2010-09-08
11
621 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

724 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