?
Solved

Oracle Help - Execute Stored Procedure

Posted on 2010-09-08
11
Medium Priority
?
623 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1800 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 200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

649 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