Skip to Main Content
  • Questions
  • in and out variables - whats the difference

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, razvan.

Asked: June 19, 2000 - 9:36 am UTC

Last updated: February 01, 2012 - 7:36 am UTC

Version: 7.3.2

Viewed 10K+ times! This question is

You Asked

hello tom,

can you explain me what is the diference between variables in and out in pl/sql?

thanks a lot
razvan

and we said...

An IN parameter can be read but not written to in plsql. If I attempt to modify an IN parameter -- it will fail at compile time. For example:

ops$tkyte@8i> create or replace procedure p( x in number )
2 as
3 begin
4 dbms_output.put_line( 'x = ' || x );
5 x := 55;
6 end;
7 /

Warning: Procedure created with compilation errors.
ops$tkyte@8i> show err
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------
5/2 PLS-00363: expression 'X' cannot be used as an
assignment target
5/2 PL/SQL: Statement ignored


An IN OUT parameter can not only be READ but it can be WRITTEN to as well. It retains whatever value it had before coming into the subroutine. Consider:


ops$tkyte@8i> create or replace procedure p( x in OUT number )
2 as
3 begin
4 dbms_output.put_line( 'x = ' || x );
5 x := 55;
6 end;
7 /

Procedure created.

ops$tkyte@8i> declare
2 y number default 10;
3 begin
4 p(y);
5 dbms_output.put_line( 'y = ' || y );
6 end;
7 /
x = 10
y = 55

PL/SQL procedure successfully completed.


So, the value 10 was passed in and the value 55 was written to it on the way out.

An OUT parameter can be read and written however an OUT only parameter is always assigned NULL on the way into the routine. Consider:


ops$tkyte@8i> create or replace procedure p( x OUT number )
2 as
3 begin
4 dbms_output.put_line( 'x = ' || x );
5 x := 55;
6 end;
7 /

Procedure created.

ops$tkyte@8i> declare
2 y number default 10;
3 begin
4 p(y);
5 dbms_output.put_line( 'y = ' || y );
6 end;
7 /
x =
y = 55

PL/SQL procedure successfully completed.
on the way in, NULL -- not 10 -- was passed. OUT parameters always default to NULL.


Here is another example showing that an OUT parameter is *always* modified -- even if we don't directly modify it in the routine:


ops$tkyte@8i> create or replace procedure p2( x OUT number )
2 as
3 begin
4 dbms_output.put_line( 'x = ' || x );
5 -- x := 55; we do not assign 55
6 end;
7 /

Procedure created.

ops$tkyte@8i> declare
2 y number default 10;
3 begin
4 p2(y);
5 dbms_output.put_line( 'y = ' || y );
6 end;
7 /
x =
y =

PL/SQL procedure successfully completed.

Notice how y is set to NULL, even though we made no assignments to it


It is interesting to note that the ability to READ an OUT parameter is new with 7.3. In prior releases you would have gotten the error:

LINE/COL ERROR
-------- ----------------------------------------------------
4/2 PL/SQL: Statement ignored
4/34 PLS-00365: 'X' is an OUT parameter and cannot be read




Rating

  (85 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

examples using copy and nocopy

Debby, August 19, 2001 - 5:46 pm UTC


Please give more examples using copy and nocopy.
and also the example for inout.

Thx



Tom Kyte
August 19, 2001 - 9:08 pm UTC

Good point, COPY and NOCOPY (new with 8i) modify this behaviour somewhat.

Normally paramters are copied to the OUT values AFTER the succesful execution of a procedure, so for example when we compare the behaviour of a COPY and NOCOPY routine:

ops$tkyte@ORA8I.WORLD> create or replace procedure p1( x OUT number, y IN OUT number )
  2  as
  3  begin
  4          x := 55;
  5          y := 55;
  6          raise program_error;
  7  end;
  8  /

Procedure created.

ops$tkyte@ORA8I.WORLD> create or replace procedure p2( x OUT nocopy number, y IN OUT nocopy number )
  2  as
  3  begin
  4          x := 55;
  5          y := 55;
  6          raise program_error;
  7  end;
  8  /

Procedure created.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> declare
  2          l_x number default 0;
  3          l_y number default 0;
  4  begin
  5          p1( l_x, l_y );
  6  exception
  7          when others then
  8                  dbms_output.put_line( 'x = ' || l_x );
  9                  dbms_output.put_line( 'y = ' || l_y );
 10  end;
 11  /
x = 0
y = 0

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> declare
  2          l_x number default 0;
  3          l_y number default 0;
  4  begin
  5          p2( l_x, l_y );
  6  exception
  7          when others then
  8                  dbms_output.put_line( 'x = ' || l_x );
  9                  dbms_output.put_line( 'y = ' || l_y );
 10  end;
 11  /
x = 55
y = 55

PL/SQL procedure successfully completed.

<b>we see that x and y's values are different.  In the COPY routine -- p1 -- the values are COPIED to the out parameters upon successful completion.  In the nocopy routine, PLSQL is in effect sending a pointer to X and Y -- as soon as we modify them in the subroutine, their values are changed in the calling routine.  

So, that begs the question, why the heck would you want to do this?  The side effect seems to be not nice, whats the benefit?  Performance:</b>


ops$tkyte@ORA8I.WORLD> create or replace procedure p3( x OUT dbms_sql.varchar2s )
  2  as
  3  begin
  4          for i in 1 .. 20000 loop
  5                  x(i) := rpad( '*', 255, '*' );
  6          end loop;
  7  end;
  8  /

Procedure created.

ops$tkyte@ORA8I.WORLD> create or replace procedure p4( x OUT NOCOPY dbms_sql.varchar2s )
  2  as
  3  begin
  4          for i in 1 .. 20000 loop
  5                  x(i) := rpad( '*', 255, '*' );
  6          end loop;
  7  end;
  8  /

Procedure created.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> set timing on
ops$tkyte@ORA8I.WORLD> declare
  2          l_x dbms_sql.varchar2s;
  3  begin
  4          p3(l_x);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.47
ops$tkyte@ORA8I.WORLD> declare
  2          l_x dbms_sql.varchar2s;
  3  begin
  4          p4(l_x);
  5  end;
  6  /

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.35


<b>as you can see, avoiding the copy of that much data can shave some runtime off our execution. You should consider NOCOPY for all large variables (tables of anything) if it doesn't hurt your logic to do so.</b>
 

in what scenarios we need to use out and inout

Schesser, April 23, 2002 - 4:56 pm UTC

Tom, can you generalize as to when specifically we have to use out and inout parameter modes.

1.Actually instead of using an out parameter I can use a function.Isnt it? So what is the utility of out mode.

2.When do we have to or make use of inout mode. Are there any specific situations where we need to use this mode.

And lastly, and most interestingly, we use functions from within our sql as it returns a value. Now using out and inout parameter modes we can also return values from procedures, but why cant we use them in sql statements.

( thinking this way there is no difference between functions and procedures except that that the former can be used in sql , where as latter cannot be used)

Can you explain.

Thank you


Tom Kyte
April 23, 2002 - 7:38 pm UTC

1) some people just like to use procedures. a function can return a single value, a procedure -- many. You can have more then one OUT in a procedure.

2) there are never any cases where you HAVE to. You would use it when you
a) want to supply a value that
b) the procedure can modify and
c) you will recieve the modifications

3) because there is no way for you to declare a variable in a SQL query and in order to call an IN OUT -- you need a local variable (to supply a value to be read from and to supply a place to put the out value into)



NOCOPY and collection

kiro, April 24, 2002 - 3:10 am UTC

Hi Tom
NOCOPY is very useful for performance when we are working with collection. What do you mean?

Tom Kyte
April 24, 2002 - 7:27 am UTC

re-read the comments/followups. Look for "performance". NOCOPY causes the collection (big data) to be passed by REFERENCE (by a pointer) not by COPYING the data to a temp variable, working on the temp variable, and copying back out again.

Legal to select into an OUT parameter??

Pascal, April 24, 2002 - 5:19 am UTC

Hi Tom

Thanks very much for a Great Discussion on this...but all i know is that NOCOPY is great when there's an IN OUT/OUT pl/sql Table type parameter...


I have one doubt though, although i know it's working , but can we safely select into / (Bulk select) INTO an OUT/ IN OUT parameters

I was thinking may be only the Assignment is allowed when working with OUT/ IN OUT parameters , but couldn't find Documents to prove this..


Thanks




Tom Kyte
April 24, 2002 - 7:34 am UTC

NOCOPY is only useful with IN/OUT and OUT parameters actually. (not available with IN)

IN parameters -- already passed by reference.


You can only assign to OUT or IN/OUT parameters. Yes you can "safely" select into them.

You didnt get my poing

Schesser, April 24, 2002 - 11:51 am UTC

The below as you said

"2) there are never any cases where you HAVE to. You would use it when you
a) want to supply a value that
b) the procedure can modify and
c) you will recieve the modifications
"

Can be done even using a function( except that only one value will be returned using a function, and many using a procedure). Why would I use a procedure , I will use a function.

Further, even when I want to return multiple values out from a function, I CAN USE A REF CURSOR variable, and return multiple values out isnt it.

Kindly clarify.

Tom Kyte
April 24, 2002 - 3:22 pm UTC

As i always say -- there are an INIFINITE number of ways to accomplish most anything.

Yes, you can use a function (however, the function would have to allocate a temporary variable, copy the input parameter so it can change it and then return the temporary. Might be more efficient to use a procedure with nocopy)

Why would you use a procedure? Because you want to -- it is your choice.

A ref cursor is to return a result set. It would not be an appropriate technique for returning formal parameters.

Tianhua Wu, April 24, 2002 - 12:13 pm UTC

Everyday I can learn something new here. I use in and out for quite q while, but I never understand them btter than now, Thankk you!

Constant/Static variables

A reader, April 24, 2002 - 12:21 pm UTC

Hi, Tom,

Could you clarify and give us an example on
constant / static variables like in C/C++ please?

Many thanks,




Tom Kyte
April 24, 2002 - 4:21 pm UTC

easy

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package demo_pkg
  2  as
  3          x CONSTANT number := 5;
  4          y number;
  5  end;
  6  /

Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package body demo_pkg
  2  as
  3          a CONSTANT number := 5;
  4          b number;
  5  end;
  6  /

Package body created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 


X is like "#define X 5" in a .h file
Y is like

int y;

defined outside of a function (global variable, avaiable in all code).

A is like #define A 5 in the .c file
B is like 

static int b;

 

OUT parameters

Mo, August 31, 2002 - 10:36 am UTC

Tom:

1. I do understand that IN parameters are a must for a procedure that you want to pass values into. However, Why do I need to define OUT parameters to write to. Can I just declare variables in my PL/SQL program, and write values to those variables and then output the values using dbms_ouput.put_line.

Is there a benefit of using an OUT paraemter.

2. Also, in a web application you pass the values that you want to submit by incorporating it in the <form></form> tags. How do you do this in a client/server program.
My thinking is that when you want output of procedure A read into procedure B, you make a call to A within B procedure code. I think in this case you MUST use OUT parameters in A otherwise if you use variables they can not be accessed in B code. AM I correct?

Thank you,


Tom Kyte
August 31, 2002 - 10:47 am UTC

1) umm, say your client is a VB program and needs to get the results. dbms_output doesn't do anything in VB (and in fact, dbms_output just contains a procedure get_line(s) that has an OUT parameter that allows the client to get the output and display it - that is what sqlplus does)

OUT parameters are necessary for the language PLSQL to be a real programming language. Just like every other language in the world (Java, C, C++, etc)

2) Yes, you are accurate in your depiction -- although you could use globals instead of formal parameters hence the word "must" is a little strong.

nocopy - a hint ?

ram, September 09, 2002 - 5:28 am UTC

but i read in a book that nocopy is a 'hint' rather than a 'directive' and so the compiler is not obliged to make use of it everytime. the book also mentioned that if the compiler chooses to ignore the hint we will NOT get an error. in your example the values of x and y were different for a procedure with/without nocopy. however if we are not really sure if nocopy is being used then we cant really say that the value of x will be some_value, can we ? or, is it that if the compiler uses it once we can be sure it will be used in all the future calls ?
pls clarify.



Tom Kyte
September 09, 2002 - 8:31 am UTC

The only thing you can say is:

if you use NOCOPY, beware that the behaviour (eg: value) of your in out or out parameters may be different then when NOT using NOCOPY.

A Way to get Procedure Parameters ?

Christophe Parmentier, October 23, 2002 - 6:01 am UTC

Is there a Way to get procedure parameters from another procedure ?

E.G.
Calling a Trace Procedure that gets the :
1) The Calling Procedure Name?
2) Its parameters Names?
to UTL_FILE them...

Thanks !



Tom Kyte
October 23, 2002 - 7:21 am UTC

no, there is not.

search for DBMS_TRACE on this site or

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_tra.htm#1000263 <code>

to see the sort of debug stuff you can capture.

OUT and NOCOPY

J. Smits, April 24, 2003 - 6:54 am UTC

If an OUT parameter is not assigned to any value during a procedure, its value goes out as NULL (or an empty table) when the procedure completes.

However, what is the behavior for an OUT NOCOPY parameter that is not assigned during the procedure? Will it retain the value it had before the procedure call, or will it be nulled out?

In addition, although I don't expect you to explain how to predict whether the compiler will actually choose to implement NOCOPY, is there any way we can know after compilation that the compiler chose it? Knowing this would help to quantify the performance improvement that NOCOPY has on a particular process.

Tom Kyte
April 24, 2003 - 8:48 am UTC

the semantics of an OUT parameter is

a) set it to null
b) call procedure
c) upon success, copy the value back out 


NOCOPY only changes the behaviour of C


ops$tkyte@ORA920> create or replace procedure nocopy_demo( p1 out NOCOPY number, p2 out NOCOPY number )
  2  as
  3  begin
  4          p1 := 55;
  5          raise program_error;
  6  end;
  7  /

Procedure created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2          l1 number := 1;
  3          l2 number := 2;
  4  begin
  5          nocopy_demo( l1, l2 );
  6  exception
  7          when others then
  8                  dbms_output.put_line( 'l1 = ' || l1 );
  9                  dbms_output.put_line( 'l2 = ' || l2 );
 10  end;
 11  /
l1 = 55
l2 =

PL/SQL procedure successfully completed.


The variable is nulled out.....


The only way to quantify the performance increase/decrease in any case (even if you knew that it did the nocopy) would be to benchmark the procedure before and after. 

Thanks for the clarification

J. Smits, April 24, 2003 - 1:30 pm UTC

Yes, when doing performance testing we always do a before and after to see if a particular change made a difference.

However, when a test focusing on NOCOPY does not show a significant difference in performance, we don't know if it was because NOCOPY itself did not have much of an effect, or because the compiler chose not to implement NOCOPY for the particular code base.

out parameter

A reader, April 24, 2003 - 6:04 pm UTC

Hi otm,

I have procedure

create or replace p1(x out char,
y out varchar)
as
begin

select 'xyz'||myseq.nextval
into x
from dual;
y := success;
end;

create or replace p2(x in char,
y out varchar)
as
begin

insert into t values(x);
y := success;

end;


create or replace proceudre p(
p_1 out char,
p_2 in varchar,
p_3 out varchar,
p_4 out char)
is

begin

/**** calling another procedure p1 that will
generate a code(using seq.nexval) and return in
p_1 parameter */

p1(p_1,p_3);

/******* also use this generated code in the
another proceudres...

/**---->***/ dbms_output.put_lin(p_1);

p2(p_1,p_4);

end;

/*******************************************************/

this procedure works fine if I use sqlplus
but, if i use java/jsp --(app - tomcat )

it complains about can not bind LONG value to parameter and
p_1 parameter give problems it is some how get LONG ?? values, if called from java
but if I do that from sqlplus it is fine
they (java developer) uses registerparameter ??

can you please comment on that ?




Tom Kyte
April 24, 2003 - 6:22 pm UTC

without actually seeing the smallest possible test case that reproduces the error, I cannot really say what the java programmer is doing wrong in their code.

Call to procedure having more than 1 OUT variables...

Riaz Shahid, April 25, 2003 - 6:26 am UTC

Hello Tom!

How can we call a procedure having more than 1 OUT variables ?

create or replace procedure myproc (a OUt number, b OUT number)
as
begin
a:=10;
b:=15;
end;

Now how we will make call to this procedure ? Like

declare
a number;
b number;
begin
a,b):=myproc;
end;

But this is not valid. So how we'll do that?

Riaz



Tom Kyte
April 25, 2003 - 7:57 am UTC

you need to read the PLSQL programmers guide -- this is like "basic programming 101" stuff.

Just like you would in ANY 3gl language


begin
myproc( a, b );
end;
/




C

A reader, April 25, 2003 - 10:27 am UTC

In sum, nocopy works like passing parameters by reference, like in C. Shouldn't there be an "IN OUT NOCOPY" -- which is how it works in C ?

log

A reader, April 25, 2003 - 12:48 pm UTC


hi tom,

here is the log from tomcat40 and debug stmts.

Apr 22 15:47:47 [DEBUG]: Executing :{call p_mypkg.p (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) } - com.com.oracle.dataaccess.p.processInsertUpdate(Unknown Source)

Apr 22 15:47:48 [INFO]: p1 Message Code: DB-SUCCESS - com.oracle.dataaccess.p.processInsertUpdate(Unknown Source)
Apr 22 15:47:48 [ERROR]: p1 SQL Error Code: null - com.oracle.dataaccess.p.processInsertUpdate(Unknown Source)

/************************/

step 1: p calls p1 and get success and
generates a code in out parameter p_1

setp 2 : p calls p2 using new value in p_1 and passes in to
p2 as **IN** parametner
/*************************/


Apr 22 15:47:48 [INFO]: p2 Message Code: DB-GEN_FAIL_SYSTEM - com.oracle.dataaccess.p(Unknown Source)
Apr 22 15:47:48 [ERROR]: p2 SQL Error Code: ORA-01461: can bind a LONG value only for insert into a LONG column - com.oracle.dataaccess.p.processInsertUpdate(Unknown Source)

/*************************************/

setp 3: p2 gets fali system
setp 4: action as a whole fails...
/*************************************/




Tom Kyte
April 25, 2003 - 3:46 pm UTC

that is not java code...
test case -- small, concise, yet complete...

why nt default nocopy

Tanmoy Datta Choudhury, June 02, 2003 - 10:22 am UTC

tom ,
as u said that nocopy and copy is for performance that why oracle is not using that as a default..

Tom Kyte
June 02, 2003 - 10:45 am UTC

because

a) it was not that way in the beginning (so making it the default would affect tons of existing code)

b) it has undesirable side effects if applied arbitrarily (it would not be good to be the default)

so, to be backwards compatible AND to promote proper behavior of variables -- it is something you have to explicitly (and hopefull knowledgably) choose to use.

thanks tom

Tanmoy Datta Choudhury, June 04, 2003 - 2:48 am UTC

thanks for your reply ...i understood ..

IN OUT in Functions?

A reader, September 09, 2003 - 10:28 am UTC

Hi Tom!

I was wondering, can we also use IN OUT parameters in a function? I know we can use several IN OUT parameters in a procedure, but since a function already returns one value by default, is it ok to add another value it can return by using the IN OUT parameters?

Thanks and more power!

Tom Kyte
September 09, 2003 - 11:53 am UTC

yes, you can.

C External Routines

A reader, September 09, 2003 - 3:07 pm UTC

Hi Tom,
Can the NOCOPY be used for returing values from C External Routines? Do we have the same performance benifit?
Thanks

Passing Ref Cursor Result Set from one stored program to another

Justin, December 08, 2003 - 10:03 am UTC

Tom,

We are converting from SQL Server to Oracle.

In SQL Server, we have a procedure that calls a function.


Here is the SQL Server function:

ALTER FUNCTION dbo.fn_CEPrescriptionGetFamilyT071Ids (@intFamilyId int)
RETURNS @tblChild TABLE (t071_id int)
AS
BEGIN

INSERT @tblChild
SELECT DISTINCT t071_id
FROM t071_prescription t071 WITH (NOLOCK)
INNER JOIN t067_participant t067 WITH (NOLOCK)
INNER JOIN t033_fam_members t033 WITH (NOLOCK)
INNER JOIN t014_benefit_fam t014 WITH (NOLOCK)
ON t033.t014_id = t014.t014_id
ON t067.t069_id = t033.t069_id
ON t071.t067_id = t067.t067_id
WHERE family_id = @intFamilyId
AND fam_mbrship_typ_cd = 'P'

RETURN

END

Here is the SQL Server procedure that is calling the function:


ALTER PROCEDURE dbo.sp_CEPrescriptionGetPrescriptionAndContents_Child
@intFamilyId int,
@intT071Id int
AS

SET NOCOUNT ON

DECLARE @T table ( t071_id int )

IF @intFamilyId > 0
BEGIN
INSERT @T
SELECT t071_id FROM dbo.fn_CEPrescriptionGetFamilyT071Ids(@intFamilyId)
END
ELSE
BEGIN
INSERT @T ( t071_id )
VALUES ( @intT071Id)
END

SELECT T040_ID
,ABS(T040_ID) AS ID40
,t040.T071_ID
,FI_SEQ_NR
,T040_STATUS_CD
,t040.T093_ID
,NEWID() AS Id
, '' AS WEB_STATUS
FROM t040_FI_Spec t040 WITH (NOLOCK)
WHERE t040.t071_id IN (SELECT t071_id FROM @T)
ORDER BY t040.FI_SEQ_NR


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I didn't write the SQL Server stuff, but I am trying to duplicate it in Oracle utilizing Ref Cursors.

The application is a VB app and we have gotten this to work utilizing "sql types" (myscalartype & myscalartabletype), but can't seem to get Ref Cursors to work. We keep getting inconsistent datatypes errors. (Which I can understand why, but don't know how to remedy.)

Here is our Oracle translation attempt at ref cursors which fails from VB with the ORA-00932.

*Oracle Function:*

CREATE OR REPLACE FUNCTION FN_CEPRESCRIPTIONGETFAMILYT071(intFamilyId IN NUMBER DEFAULT NULL)

RETURN types.RC_NUM

AS
l_rc types.RC_NUM;
intFamilyId_ NUMBER(10,0) := intFamilyId;

BEGIN

OPEN l_rc FOR
SELECT DISTINCT t071_id
FROM wic_owner8.t071_prescription t071
,wic_owner8.t067_participant t067
,wic_owner8.t033_fam_members t033
,wic_owner8.t014_benefit_fam t014
WHERE (t033.t014_id = t014.t014_id)
AND (t067.t069_id = t033.t069_id)
AND (t071.t067_id = t067.t067_id)
AND (family_id = FN_CEPRESCRIPTIONGETFAMILYT071.intFamilyId_ AND fam_mbrship_typ_cd = 'P');

RETURN l_rc;

END FN_CEPRESCRIPTIONGETFAMILYT071;
/



CREATE OR REPLACE PROCEDURE SP_CEPRESCRIPTIONGETPRESCRIPTI ( intFamilyId int, intT071Id int DEFAULT NULL, l_rc OUT sys_refcursor )
AS

BEGIN

IF intFamilyId > 0 THEN
OPEN l_rc FOR
SELECT T040_ID
,ABS(T040_ID) AS ID40
,t040.T071_ID
,FI_SEQ_NR
,T040_STATUS_CD
,t040.T093_ID
,NEWID() AS Id
,'' AS WEB_STATUS
FROM t040_FI_Spec t040
WHERE t040.t071_id IN (select FN_CEPRESCRIPTIONGETFAMILYT071(intFamilyId) from dual )
ORDER BY t040.FI_SEQ_NR;

ELSE

OPEN l_rc FOR
SELECT T040_ID
,ABS(T040_ID) AS ID40
,t040.T071_ID
,FI_SEQ_NR
,T040_STATUS_CD
,t040.T093_ID
,NEWID() AS Id
,'' AS WEB_STATUS
FROM t040_FI_Spec t040
WHERE t040.t071_id IN (intT071Id)
ORDER BY t040.FI_SEQ_NR;
END IF;
END;
/

I have copied both the function code and the procedure code directly from this window into sqlplus, and both compile fine, but I know the procedure is *wrong*.

This:
select FN_CEPRESCRIPTIONGETFAMILYT071(1001) from dual;

Results in this:

FN_CEPRESCRIPTIONGET
====================
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

T071_ID
=================
1001
1002
486562
11189949

4 rows selected.

1 row selected.

This:

exec SP_CEPRESCRIPTIONGETPRESCRIPTI (1001,NULL);

Results in this:

PLS-00306: wrong number or types of arguments in call to 'SP_CEPRESCRIPTIONGETPRESCRIPTI'





So if you could go through - step by step - passing the result set of a stored program in the form of a Ref Cursor, from one stored subprogram to another.

I am working on a more simplistic test case right now...

Thanks much for looking at this!



Tom Kyte
December 09, 2003 - 5:24 am UTC

looks like it worked to me - given the select f(x) from dual worked.

You just want to (in sqlplus) declare a cursor variable to return the value into and then print it:

variable x refcursor
exec sp_.....( 1001, null, :x )
print x


thats all -- you, you just needed to "get" the last parameter and print it.

follow up goof

Justin, December 08, 2003 - 10:34 am UTC

By the way, in case you want to reference page numbers,
I have all 3 of your books:

Beginning Oracle Programming
Effective Oracle by Design
Expert One on One...

I am looking at pg 572-575 in EOBD.


Goof:

The Oracle procedure listing in my previous post should have been:

CREATE OR REPLACE PROCEDURE SP_CEPRESCRIPTIONGETPRESCRIPTI ( intFamilyId int, intT071Id int DEFAULT NULL ) --, l_rc OUT sys_refcursor )

Without the refcursor parameter declaration.

I know we need to have a ref cursor in the parameter list to pump the final result set out to VB, but when I have the ref cursor commented out, then I get the:

exec SP_CEPRESCRIPTIONGETPRESCRIPTI(1001);

ORA-00932: inconsistent datatypes: expected - got CURSER

I was talking about.




PS. Why the "got "CURSER""?

Is CURSOR simply misspelled CURSER??



I have found a link on your site which may help me

Justin, December 08, 2003 - 12:59 pm UTC

Just wanted to let you know, I found (interestingly enough) by typing in simply "ref cursor" the very first note that comes up is
"Returning a refcursor and use it in IN clause"
which made me sick, as I've tried so many ways to find it, and it was staring me in the face...

I'll try it, and see if I can figure it out with a YAF.

Sorry for the thread continuations...

Internal Implementation

Dhrubo, March 08, 2004 - 9:07 am UTC

Hi Tom
I have some basic questions regarding IN OUT implementation
My understanding says --
OUT
---
"Inside the program, an OUT parameter acts like a variable that has not been initialized. In fact, the OUT parameter has no value at all until the program terminates successfully (without raising an exception, that is). During the execution of the program, any assignments to an OUT parameter are actually made to an internal copy of the OUT parameter. When the program terminates successfully and returns control to the calling block, the value in that local copy is then transferred to the actual OUT parameter. That value is then available in the calling PL/SQL block. "
IN
--
"IN Parameter is basically a protected pointer."

So why is this kind of implementation?Because in case of OUT I feel that has memory overhead and is a complex way of doing thing.In case of IN we could have just passed by value.Please explain

Tom Kyte
March 08, 2004 - 9:46 am UTC

in -> pointer passed, data is not copied

in out -> value is copied to a temporary, temporary is available read write in the procedure, temporary copied to value upon successful return

out -> temporary is assigned NULL, temporary is used in routine, upon success temporary is copied to output.


ops$tkyte@ORA9IR2> create or replace procedure p( p_in in number,
  2                                 p_in_out in out number,
  3                                 p_out out number,
  4                                 p_fail in boolean )
  5  is
  6  begin
  7          dbms_output.put_line
  8          ( 'on the way in ' || p_in || ',' || p_in_out || ',' || p_out );
  9          p_in_out := p_in;
 10          p_out    := p_in;
 11          if ( p_fail )
 12          then
 13                  raise program_error;
 14          end if;
 15          dbms_output.put_line
 16          ( 'on the way out ' || p_in || ',' || p_in_out || ',' || p_out );
 17  end;
 18  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_in     number := 55;
  3          l_in_out number := 100;
  4          l_out    number := 200;
  5  begin
  6          dbms_output.put_line
  7          ( 'before ' || l_in || ',' || l_in_out || ',' || l_out );
  8          begin
  9                  p( l_in, l_in_out, l_out, TRUE );
 10          exception
 11                  when others then null;
 12          end;
 13          dbms_output.put_line
 14          ( 'after call 1 ' || l_in || ',' || l_in_out || ',' || l_out );
 15          p( l_in, l_in_out, l_out, FALSE );
 16          dbms_output.put_line
 17          ( 'after call 2 ' || l_in || ',' || l_in_out || ',' || l_out );
 18  end;
 19  /
before 55,100,200
on the way in 55,100,
after call 1 55,100,200
on the way in 55,100,
on the way out 55,55,55
after call 2 55,55,55
 
PL/SQL procedure successfully completed.


<b>so, IN is passed by reference (no copy, fast).

OUT is returned by VALUE (copy upon success)

IN OUT is passed by VALUE (copy on way in, copy on way out)

and OUT/IN OUT can be modified sometimes with the NOCOPY hint</b>
 

Why OUT is implemented in that way?

Dhrubo, March 08, 2004 - 11:54 pm UTC

Hi TOM,
I now understand why we implemented IN as a protected pointer but you gave no explanation about why OUT is implemented in that fashion.Isn't copying slow and that method entails higher memory overhead ( though memory is cheap these days) am i too curious?
Will be highly enlightened by your explanations

Tom Kyte
March 09, 2004 - 11:59 am UTC

it is the very defintion of the langage (borrowed from Ada).

your OUT parameter is done conceptually like this:

      set out_parameter to null
      call procedure and get temporary back
      if success then out_parameter := temporary;

it makes for predicable code, if procedure "fails", your data is in a known state.


see this example:

ops$tkyte@ORA9IR2> create or replace package demo_pkg
  2  as
  3          procedure p1( x out number, fail in boolean, fail2 in boolean );
  4          procedure p2( x out NOCOPY number, fail in boolean, fail2 in boolean );
  5  end;
  6  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body demo_pkg
  2  as
  3
  4  procedure p1( x out number, fail in boolean, fail2 in boolean )
  5  is
  6  begin
  7          x := 1;
  8          if (fail) then raise program_error; end if;
  9          x := 2;
 10          if (fail2) then raise program_error; end if;
 11          x := 3;
 12  end;
 13
 14  procedure p2( x out number, fail in boolean, fail2 in boolean )
 15  is
 16  begin
 17          x := 1;
 18          if (fail) then raise program_error; end if;
 19          x := 2;
 20          if (fail2) then raise program_error; end if;
 21          x := 3;
 22  end;
 23
 24  end;
 25  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          x number  := 55;
  3  begin
  4          begin demo_pkg.p1( x, false, false ); exception when others then null; end;
  5          dbms_output.put_line( 'p1 f,f ' || x );
  6          x:=55;
  7          begin demo_pkg.p1( x, true, false ); exception when others then null; end;
  8          dbms_output.put_line( 'p1 t,f ' || x );
  9          x:=55;
 10          begin demo_pkg.p1( x, false, true ); exception when others then null; end;
 11          dbms_output.put_line( 'p1 f,t ' || x );
 12          x:=55;
 13
 14          begin demo_pkg.p2( x, false, false ); exception when others then null; end;
 15          dbms_output.put_line( 'p2 f,f ' || x );
 16          x:=55;
 17          begin demo_pkg.p2( x, true, false ); exception when others then null; end;
 18          dbms_output.put_line( 'p2 t,f ' || x );
 19          x:=55;
 20          begin demo_pkg.p2( x, false, true ); exception when others then null; end;
 21          dbms_output.put_line( 'p2 f,t ' || x );
 22          x:=55;
 23  end;
 24  /
p1 f,f 3
p1 t,f 55
p1 f,t 55
p2 f,f 3
p2 t,f 1
p2 f,t 2
 
PL/SQL procedure successfully completed.


Now, p2 (which uses the "unsafe" nocopy -- pass by reference) returns "unpredicable" results where as the p1 procedure returns consistent, expected results. 

nocopy???

NOTNA, May 30, 2004 - 10:52 pm UTC

Hi Tom,

I was playing with the NOCOPY option with my small db (9204). and here is my code

create or replace package pkg1 as

type g_dflt_val_tbls is table of varchar2(400) index by varchar2(400);
g_dflt_vals g_dflt_val_tbls;

procedure test1;
procedure test2;

end pkg1;
/



create or replace package body pkg1     as

procedure test1 is
begin

g_dflt_vals('VALUE1') := 'String Val1';
g_dflt_vals('VALUE2') := 1;
g_dflt_vals('VALUE3') := null;
g_dflt_vals('VALUE4') := 'String Val2';
g_dflt_vals('VALUE5') := 3;
g_dflt_vals('VALUE6') := 'String Val3';
g_dflt_vals('VALUE7') := 'String Val4';
g_dflt_vals('VALUE8') := null;

pkg2.tst1(g_dflt_vals);
test2;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('exception INSIDE PKG1 VALUE 8 =>'||g_dflt_vals('VALUE8'));
end test1;



procedure test2 is
begin

dbms_output.put_line('INSIDE PKG1 VALUE 1 =>'||g_dflt_vals('VALUE1'));
dbms_output.put_line('INSIDE PKG1 VALUE 2 =>'||g_dflt_vals('VALUE2'));
dbms_output.put_line('INSIDE PKG1 VALUE 3 =>'||g_dflt_vals('VALUE3'));
dbms_output.put_line('INSIDE PKG1 VALUE 4 =>'||g_dflt_vals('VALUE4'));
dbms_output.put_line('INSIDE PKG1 VALUE 5 =>'||g_dflt_vals('VALUE5'));
dbms_output.put_line('INSIDE PKG1 VALUE 6 =>'||g_dflt_vals('VALUE6'));
dbms_output.put_line('INSIDE PKG1 VALUE 7 =>'||g_dflt_vals('VALUE7'));
dbms_output.put_line('INSIDE PKG1 VALUE 8 =>'||g_dflt_vals('VALUE8'));
pkg2.tst1(g_dflt_vals);
g_dflt_vals('VALUE8') := 'I MODIFIED VALUE 8 pkg1.test2 here';
pkg2.tst2(g_dflt_vals);
dbms_output.put_line('INSIDE PKG1 VALUE 8 (AFTER MODIFICATION)=>'||g_dflt_vals('VALUE8'));

end test2;

end pkg1;
/





create or replace package pkg2 as

procedure tst1(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls);
procedure tst2(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls);

end pkg2;
/



create or replace package body pkg2     as

procedure tst1(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls) is
begin

g_dflt_vals('VALUE1') := g_dflt_vals('VALUE1')||'New String Val1';
g_dflt_vals('VALUE2') := g_dflt_vals('VALUE2')+1;
g_dflt_vals('VALUE3') := null;
g_dflt_vals('VALUE4') := g_dflt_vals('VALUE4')||'New String Val2';
g_dflt_vals('VALUE5') := g_dflt_vals('VALUE5')+1;
g_dflt_vals('VALUE6') := g_dflt_vals('VALUE6')||'New String Val3';
g_dflt_vals('VALUE7') := g_dflt_vals('VALUE7') ||'New String Val4';
g_dflt_vals('VALUE8') := g_dflt_vals('VALUE8')||'MODIFIED';
tst2(g_dflt_vals);

--raise_application_error(-20001,'SAMPLE ERROR');
end tst1;



procedure tst2(g_dflt_vals in out pkg1.g_dflt_val_tbls) is
begin

dbms_output.put_line('INSIDE PKG2 VALUE 1 =>'||g_dflt_vals('VALUE1'));
dbms_output.put_line('INSIDE PKG2 VALUE 2 =>'||g_dflt_vals('VALUE2'));
dbms_output.put_line('INSIDE PKG2 VALUE 3 =>'||g_dflt_vals('VALUE3'));
dbms_output.put_line('INSIDE PKG2 VALUE 4 =>'||g_dflt_vals('VALUE4'));
dbms_output.put_line('INSIDE PKG2 VALUE 5 =>'||g_dflt_vals('VALUE5'));
dbms_output.put_line('INSIDE PKG2 VALUE 6 =>'||g_dflt_vals('VALUE6'));
dbms_output.put_line('INSIDE PKG2 VALUE 7 =>'||g_dflt_vals('VALUE7'));
dbms_output.put_line('INSIDE PKG2 VALUE 8 =>'||g_dflt_vals('VALUE8'));
g_dflt_vals('VALUE8') := g_dflt_vals('VALUE8') ||' ASSIGNED A VALUE AT TEST2';
RAISE_APPLICATION_ERROR(-20001,'SAMPLE ERROR');
end tst2;

end pkg2;
/


When I declared pkg2.tst2 with NOCOPY the output is the following :

SQL> exec pkg1.test1
INSIDE PKG2 VALUE 1 =>String Val1New String Val1
INSIDE PKG2 VALUE 2 =>2
INSIDE PKG2 VALUE 3 =>
INSIDE PKG2 VALUE 4 =>String Val2New String Val2
INSIDE PKG2 VALUE 5 =>4
INSIDE PKG2 VALUE 6 =>String Val3New String Val3
INSIDE PKG2 VALUE 7 =>String Val4New String Val4
INSIDE PKG2 VALUE 8 =>MODIFIED
exception INSIDE PKG1 VALUE 8 =>MODIFIED ASSIGNED A VALUE AT TEST2


But when I modified the pkg2.tst2 to remove the NOCOPY option, i get this :

SQL> set serverout on
SQL> exec pkg1.test1
INSIDE PKG2 VALUE 1 =>String Val1New String Val1
INSIDE PKG2 VALUE 2 =>2
INSIDE PKG2 VALUE 3 =>
INSIDE PKG2 VALUE 4 =>String Val2New String Val2
INSIDE PKG2 VALUE 5 =>4
INSIDE PKG2 VALUE 6 =>String Val3New String Val3
INSIDE PKG2 VALUE 7 =>String Val4New String Val4
INSIDE PKG2 VALUE 8 =>MODIFIED
exception INSIDE PKG1 VALUE 8 =>MODIFIED ASSIGNED A VALUE AT TEST2


look at the "exception INSIDE PKG1 VALUE 8" the value is still the same as without the NOCOPY option. what I am expecting is :

"exception INSIDE PKG1 VALUE 8 =>MODIFIED", 

since I did not specify the NOCOPY option. Can you enlighten me with this one?


Cheers,
NOTNA

 

Tom Kyte
May 31, 2004 - 12:58 pm UTC

really hard time following this as there appears to be pkg1/pkg2/test1/test2, ugh confusing. 

small, concise, clear, yet 100% complete (the ingredients for a good test case).


look at your spec for pkg2.  I see nocopy in there?



ops$tkyte@ORA9IR2> create or replace package pkg2
  2  as
  3     procedure tst1(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls);
  4     procedure tst2(g_dflt_vals in out <b>nocopy</b> pkg1.g_dflt_val_tbls);
  5  end pkg2;
  6  /
 
Package created.
 
ops$tkyte@ORA9IR2> exec pkg1.test1
INSIDE PKG2 VALUE 1 =>String Val1New String Val1
INSIDE PKG2 VALUE 2 =>2
INSIDE PKG2 VALUE 3 =>
INSIDE PKG2 VALUE 4 =>String Val2New String Val2
INSIDE PKG2 VALUE 5 =>4
INSIDE PKG2 VALUE 6 =>String Val3New String Val3
INSIDE PKG2 VALUE 7 =>String Val4New String Val4<b>
INSIDE PKG2 VALUE 8 =>MODIFIED
exception INSIDE PKG1 VALUE 8 =>MODIFIED ASSIGNED A VALUE AT TEST2
</b> 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> create or replace package pkg2
  2  as
  3     procedure tst1(g_dflt_vals in out nocopy pkg1.g_dflt_val_tbls);
  4     procedure tst2(g_dflt_vals in out        pkg1.g_dflt_val_tbls);
  5  end pkg2;
  6  /
 
Package created.
 
ops$tkyte@ORA9IR2> exec pkg1.test1
INSIDE PKG2 VALUE 1 =>String Val1New String Val1
INSIDE PKG2 VALUE 2 =>2
INSIDE PKG2 VALUE 3 =>
INSIDE PKG2 VALUE 4 =>String Val2New String Val2
INSIDE PKG2 VALUE 5 =>4
INSIDE PKG2 VALUE 6 =>String Val3New String Val3
INSIDE PKG2 VALUE 7 =>String Val4New String Val4
INSIDE PKG2 VALUE 8 =>MODIFIED<b>
exception INSIDE PKG1 VALUE 8 =>MODIFIED
 </b>
PL/SQL procedure successfully completed.
 

Where to put the NOCOPY in a Package.

Bob Lyon, July 15, 2004 - 11:35 am UTC

By running the following test, it appears that in Packages,
the NOCOPY must be in the package spec.  Putting a NOCOPY solely in the package body shows no performance improvement.  I couldn't find anything in Oracle's documentation really covering this, so I ran the following tests...
CREATE OR REPLACE PACKAGE pkgNO_NOCOPY
AS
   procedure p3( x OUT dbms_sql.varchar2s );
END;
/

CREATE OR REPLACE PACKAGE BODY pkgNO_NOCOPY
AS

   procedure p3( x OUT dbms_sql.varchar2s )
   as
   begin
           for i in 1 .. 20000 loop
                   x(i) := rpad( '*', 255, '*' );
           end loop;
   end;
END;
/

CREATE OR REPLACE PACKAGE pkgSpec_NOCOPY
AS
   procedure p3( x OUT NOCOPY dbms_sql.varchar2s );
END;
/


CREATE OR REPLACE PACKAGE BODY pkgSpec_NOCOPY
AS

   procedure p3( x OUT dbms_sql.varchar2s )
   as
   begin
           for i in 1 .. 20000 loop
                   x(i) := rpad( '*', 255, '*' );
           end loop;
   end;
END;
/



CREATE OR REPLACE PACKAGE pkgBody_NOCOPY
AS
   procedure p3( x OUT dbms_sql.varchar2s );
END;
/


CREATE OR REPLACE PACKAGE BODY pkgBody_NOCOPY
AS

   procedure p3( x OUT NOCOPY dbms_sql.varchar2s )
   as
   begin
           for i in 1 .. 20000 loop
                   x(i) := rpad( '*', 255, '*' );
           end loop;
   end;
END;
/

CREATE OR REPLACE PACKAGE pkgBoth_NOCOPY
AS
   procedure p3( x OUT NOCOPY dbms_sql.varchar2s );
END;
/


CREATE OR REPLACE PACKAGE BODY pkgBoth_NOCOPY
AS

   procedure p3( x OUT NOCOPY dbms_sql.varchar2s )
   as
   begin
           for i in 1 .. 20000 loop
                   x(i) := rpad( '*', 255, '*' );
           end loop;
   end;
END;
/

set timing on
SQL> declare
  2          l_x dbms_sql.varchar2s;
  3  begin
  4          for i in 1 .. 1000 loop
  5             pkgNo_NOCOPY.p3(l_x);
  6             l_x.DELETE;
  7          end loop;
  8
  9  end;
 10  /
Elapsed: 00:05:18.02
SQL>
SQL> declare
  2          l_x dbms_sql.varchar2s;
  3  begin
  4          for i in 1 .. 1000 loop
  5           pkgSpec_NOCOPY.p3(l_x);
  6             l_x.DELETE;
  7          end loop;
  8  end;
  9  /
Elapsed: 00:03:51.05
SQL>
SQL> declare
  2          l_x dbms_sql.varchar2s;
  3  begin
  4          for i in 1 .. 1000 loop
  5           pkgBody_NOCOPY.p3(l_x);
  6             l_x.DELETE;
  7          end loop;
  8  end;
  9  /
Elapsed: 00:05:07.09
SQL>
SQL> declare
  2          l_x dbms_sql.varchar2s;
  3  begin
  4          for i in 1 .. 1000 loop
  5           pkgBoth_NOCOPY.p3(l_x);
  6             l_x.DELETE;
  7          end loop;
  8  end;
  9  /
Elapsed: 00:03:51.06

 

Tom Kyte
July 15, 2004 - 1:25 pm UTC

the spec should match the body.

the SPEC is what is visible.

pass in variable

A reader, July 26, 2004 - 10:27 pm UTC

Tom,
a newbie question.
I have a procedure inside the P2PA_EXPENDITURES_IMPORT_PK package:
PROCEDURE expenditures_import_main (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_cefms_foa_code IN VARCHAR2,
p_project_number IN VARCHAR2
);

I want to run it for p_cefms_foa_code='M2' and all the p_project_number. I did as :
APPS@S0P2DBD5> set serveroutput on size 1000000
APPS@S0P2DBD5> declare
2 v_errbuf varchar2(2000);
3 v_retcode number;
4 v_project_number varchar2(30) := ' ';
5 begin
6 P2PA_EXPENDITURES_IMPORT_PK.expenditures_import_main (
7 errbuf=> v_errbuf
8 ,retcode=> v_retcode
9 , p_cefms_foa_code => 'M2'
10 , p_project_number => v_project_number
11 );
12 dbms_output.put_line(v_errbuf);
13 dbms_output.put_line(to_char(v_retcode));
14 end;
15 /

PL/SQL procedure successfully completed.

APPS@S0P2DBD5>
It finished in a couple of seconds. I am not sure it ran for all the project number or not.

Tom Kyte
July 27, 2004 - 7:12 am UTC

and there is no way we could tell either from the above?

I mean, you passed in ' ' for the project number, does your code process all project numbers if it is passed that? if so, it did, else it did not.

C like pass by copy or reference

A reader, March 09, 2005 - 5:28 pm UTC

Hi

I am a beginner C Programmer starting to do some PL/SQL tasks.

In C we can pass parameters by value or reference, to pass by reference we use pointers. Char for example are always passed by reference AFIAK.

I am reading about this IN - IN/OUT - OUT - NOCOPY theory.

IN as you said is passed by reference since no writing is needed

IN/OUT is passed by value

OUT by value

IN/OUT with NOCOPY is passed by reference correct

Can OUT passed by reference as well? If so how does it work? Does the procedure simply return the memory address?

In the documentation it says this:

Because NOCOPY is a hint, not a directive, the compiler can pass NOCOPY parameters to a subprogram by value or by reference. So, if the subprogram exits with an unhandled exception, you cannot rely on the values of the NOCOPY actual parameters

It says that if the program exits with an unhandled exception then I cannot rely the values of parameters but how do you handle an exception for this situation?

Tom Kyte
March 09, 2005 - 6:38 pm UTC

out can be done with nocopy.


basically, if you use NOCOPY - you are saying "if the procedure I call raised an exception, the values of the OUT or IN OUT nocopy parameters are garbage, unreliable and I won't use them"

Just like in C if you called a routine and passed an address and the routine longjump()'ed back to you - the contents of the pointers you passed -- what they point to, would be in an unknown state and you just wouldn't use them.


As a C programmer myself, you will hate plsql for a little while but come to really like it over time ;)

how to call a function with out parameter

riyaz, March 10, 2005 - 2:32 am UTC

This is excellent thread, cleared all my doubts regarding the arguments in procedure. You have said, it is possible to use out paramter in funciton also.
I created a function:
create or replace function f_ex(l1 in out number)
return number
is
begin
dbms_output.put_line(l1);
return 1;
end;

1 declare
2 y number(5) default 1;
3 z number(5) default 2;
4 begin
6 select f_ex(z) into y from dual;
7 dbms_output.put_line(y);
8* end;
ems0405@ORCL> /
select f_ex(z) into y from dual;
*
ERROR at line 6:
ORA-06550: line 6, column 9:
PL/SQL: ORA-06572: Function F_EX has out arguments
ORA-06550: line 6, column 2:
PL/SQL: SQL Statement ignored

But how to call the same. Please help.


Tom Kyte
March 10, 2005 - 7:36 am UTC

variable n number
exec f_ex(:n)
print n


you cannot have out parameters in a function called from sql, and normally you would not call a function from sql -- only if you had to (and there an OUT parameter would simply not make sense)

it is working

Riyaz, March 10, 2005 - 4:48 am UTC

1 declare
2 y number(5) default 1;
3 z number(5) default 2;
4 u number(5) default 3;
5 begin
6 u := f_ex(z);
7* end;
ems0405@ORCL> /
2

So the function is working similar to procedure when out parameter is used.
But at the same time, function with "out" parameter cannot be used in sql. Please confirm.



Tom Kyte
March 10, 2005 - 7:44 am UTC

a function always works similar to a procedure, a function is a procedure that returns a value as well.

examples using copy and nocopy

veera, March 10, 2005 - 8:57 am UTC

Tom,

Excellent and interesting. In your example in the very beginning for COPY & NOCOPY, you are raising exception and displaying values in the exception. Then the values are not changed and showing x = 0 and y= 0.
But if you take out the exception and displaying the values of x and y they show as 55.

CREATE OR REPLACE procedure p1( x OUT number, y IN OUT number ) as
begin
x := 55;
y := 55;
--raise program_error;
end;

declare
x number default 0;
y number default 0;
begin
p1(x, y );
dbms_output.put_line( 'x = ' || x );
dbms_output.put_line( 'y = ' || y );
exception
when others then
dbms_output.put_line( 'x = ' || x );
dbms_output.put_line( 'y = ' || y );
end;

x = 55
y = 55

Why is the difference in values in raising exception and without raising exception.

For NOCOPY option the values are same with or without raising exception.


Tom Kyte
March 10, 2005 - 7:05 pm UTC

that is the material difference between copy and nocopy?

using the defaults, x out number and y in out number -- the values of x and y can only be modified by p1 IF p1 returns "successfully", without an exception. Because the values are passed by copy.

IF you use nocopy, the values of x and y are directly modified by p1, they are not copied on the way in and out -- they are just sent as pointers. So, whatever changes are made to them in p1 before the failure -- are visible.

OUT parameters returned in Java

Alex, March 22, 2005 - 4:00 pm UTC

Hi,

I'm having a problem executing stored procedures from Java that return OUT parameters. I keep getting

"PLS-00306: wrong number or types of arguments in call." when I've supplied all the IN parameters.

So basically, I'm calling a method and passing in my SP name and params that calls ct.execute() (ct for CallableStatement).

I am not sure how and where to handle the OUT parameters coming from the stored procedure. Perhaps I am not assigning the results properly, I'm trying something like
ResultSet rs = ct.execute(). Could you tell me how to do this correctly, thank you.

Tom Kyte
March 22, 2005 - 6:12 pm UTC

well, on this site there are lots of examples, in my book there are lots of examples, in the documentation there are lots of examples

but you give us no simple small test case to work from so it is hard to say where you went wrong.

To Alex

Menon, March 22, 2005 - 6:37 pm UTC

"I am not sure how and where to handle the OUT parameters coming from the stored
procedure. Perhaps I am not assigning the results properly, I'm trying something like
ResultSet rs = ct.execute(). Could you tell me how to do this correctly, thank
you. "

As Tom points out, actual code would help people help you!:)
But I believe you need to do
ct.execute();
ResultSet rs = (ResultSet) cstmt.getObject( 2 );
instead of "ResultSet rs = ct.execute()" as you
say above.

Here is a quick and dirty sample
of CallableStateament usage...
The procedure callable_stmt_demo.get_emp_details_proc() takes two parameters:
the first one is an in parameter representing
the employee number and the second one is the an out parameter - a ref cursor containing
three columns (as seen in the while loop of ResultSet
later)

---
String sql92Style =
"{ call callable_stmt_demo.get_emp_details_proc(?,?) }";
// create the CallableStatement object
cstmt = conn.prepareCall( sql92Style );
// bind the input value
cstmt.setInt(1, inputEmpNo );
// register the output value
cstmt.registerOutParameter( 2, OracleTypes.CURSOR );
// execute the query
cstmt.execute();
rset = (ResultSet) cstmt.getObject( 2 );
// print the result
while (rset.next())
{
int empNo = rset.getInt ( 1 );
String empName = rset.getString ( 2 );
String empJob = rset.getString ( 3 );
}
---
Hope this helps.

The purpose

Alex, March 23, 2005 - 11:08 am UTC

Ok I think I should take a step back and explain more, then I'll post the code.

To conduct database performance testing for our application the plan is to create Java programs that will take input files that will be logs, and strip our the dynamic sql and SPs and then execute them. The idea is to have our client send us there server logs so we can run them through these Java programs, and test the database for performance issues.

I don't think this will accurately simulate what they experience because the server logs will be spitting out queries with the hard coded values. I don't think that's them same as choosing things on the fly in a real production enviornment, I would enjoy your thoughts on that.

So in the Java program i'm trying to pass in the DML statements and the SPs and execute them accordingly. I can grab the input parameters to pass to the SP but I'm getting the error as I mentioned above. He's the code:

public static void executeSP(String param, String type) {

int pastpointer, curpointer = 0;
try {
for (int i = 0; i < param.length(); i++) {
char c = param.charAt(i);
if (c == '=') {
pastpointer = i;
for (int j = curpointer; j >= pastpointer; j--) {
char d = param.charAt(i);
if (d == curpointer) {
param.replace(param.charAt(curpointer), ';');
pastpointer = curpointer;

}
}

}
System.out.println("The parameter string:" + " " + param);
StringTokenizer st = new StringTokenizer(param, ";");
String[] paramArray = new String[0];

for (int x = 0; x < paramArray.length; x++) {
paramArray[x] = st.nextToken();
}

DatabaseUtility.executeSpUpdate(type, paramArray);
}
} catch (Exception e) {
System.out.println(e.toString());
}
}
}

public static void executeSpUpdate(String spName, String[] parameters) {

// Construct Callable Statement
StringBuffer buffer = new StringBuffer("{call " + spName + "(");
boolean isFirst = true;
for (int i = 0; i < parameters.length; i++) {
buffer.append( isFirst ? "" : ",");
buffer.append("?");
isFirst = false;
}
buffer.append(")}");
String sql = buffer.toString();
System.out.println(sql);

try {
// Get a connection
Connection cn = getConnection();

// Get a Callable Statement
CallableStatement st = cn.prepareCall(sql);

// Set the input parameters
for (int i = 0; i < parameters.length; i++) {
st.setString(i+1, parameters[i]);
}

// Execute the query
st.executeUpdate();
// resultSet rs = st.execute();


// Extract a string array from the resultset
//resultSet = toStringArray(rs);

// Close
//rs.close();
st.close();
cn.close();

} catch (Exception e) {
System.out.println("Error in DatabaseUtility:
--" + e);
System.exit(0);
}
}

I hope this is more what you need. Thanks.

Tom Kyte
March 23, 2005 - 6:10 pm UTC

you seem to be making the assumption all parameters will be strings.


so, what is the signature of the plsql routine you are trying to call.

a little progress...

Alex, March 24, 2005 - 9:03 am UTC

Tom,

I've made some progress, I no longer get the "wrong number of parameters" error. I found a class in our application code that does all the handling of a stored procedure for me.

However now I get java.lang.NumberFormatException on the strings I'm passing as parameters. I think this means somewhere in the code I it's trying to perform a number only function on my alphanumeric string.

Also I don't know what you mean by my plsql routine in this case. Do you mean the SP? or the method executing them?

Tom Kyte
March 24, 2005 - 3:40 pm UTC

a stored procedure is a plsql routine.


You are binding all strings to things that are perhaps not strings and getting an example due to the string not converting to the base type properly....

if you have an in/out type of NUMBER, you should be using numbers and so on.

The code casuing the problem

Alex, March 24, 2005 - 11:00 am UTC

Tom here is the code the throws the NumberFormatException when it hits the parseInt block. For some reason characters and strings are making it into that block and I have no idea why. Parameters that caused errors "N" and random alphanumeric "gh90kb" stuff. I'm hoping you can see an error in the logic.

for (int i = 0; i < parameterTypes.length; i++) {
// If the parameter type is IN, set the parameter value.
if ((parameterTypes[i][0] == DatabaseMetaData.procedureColumnIn || parameterTypes[i][0] == DatabaseMetaData.procedureColumnInOut)
&& parameterTypes[i][1] != OracleTypes.CURSOR) {
if (inParameters[inParameterIndex].equals("")) {
callableStatement.setNull(i + 1, parameterTypes[i][1]);
inParameterIndex++;
} else {
switch (parameterTypes[i][1]) {
case java.sql.Types.NUMERIC:

if (inParameters[inParameterIndex].indexOf(".") != -1) {
callableStatement
.setDouble(
i + 1,
Double
.parseDouble(inParameters[inParameterIndex++]));
} else {
callableStatement
.setInt(
i + 1,
Integer
.parseInt(inParameters[inParameterIndex++]));
}

break;

I forgot to add this

Alex, March 24, 2005 - 11:38 am UTC

At the end of the case statements there's also

default:

callableStatement.setString(i + 1,
inParameters[inParameterIndex++]);

break;
I didn't see it at the end because I didn't want to post every case statement for every type, just where the problem is occuring. But there is code for strings.

Problem solved

Alex, March 24, 2005 - 3:40 pm UTC

You can disregard my previous posts. The problem was the log file that I was reading in the SP parameters was printed out in a different order from what the SP takes.

If you have a sec, I still would like to hear you thoughts on this approach on stress testing the database I mentioned above. Thanks.

Tom Kyte
March 24, 2005 - 4:08 pm UTC

stands a chance of working somewhat.

you won't have the concurrent mix you would have in real life. so it'll test "how does this perform in isolation" but maybe not "under concurrent load". if you can mix it up and have a concurrent load, it'll be even better as a testing tool



How the sql is being passed to the DB

Alex, March 25, 2005 - 10:06 am UTC

I was thinking this is a seriously flawed test because it won't show the poorly coded dynamic sql. It will show up as

select dmp
from emp
where dmp = 'developer'

so we won't know if that was passed using a PreparedStatement or just strings in which case the strings will be hard parsed every time. The hard coded selects from the log won't be assuming the test is conducted more than once.
Also, stored procedures are called from many different places. There could be a problem with how the code behind the window calls the SP, not necessarily the SP itself. Are these valid arguements?

Tom Kyte
March 25, 2005 - 6:26 pm UTC

depends, I think that is the purpose of the log -- to record lots of sessions and replay them.

Not out of the woods yet....

Alex, March 28, 2005 - 12:20 pm UTC

Tom,

Well now that I know my problem I can't fix it. Somewhere in our code the IN parameters are not being sorted to fit how they are called by the SP. We are getting results from using databaseMetaData.getProcedureColumns but after that , I think there's a break somewhere.

Do you know how to get the SP params ordered correctly? I hoping if you can point me to some correct code I may be able to pick out the problem in our code. Thanks a lot.

Tom Kyte
March 28, 2005 - 1:39 pm UTC

all_arguments (you can query it)

or dbms_describe, to describe it.


or just build the block using named notation in any order you want

begin p( a => ?, b => ?, c => ?...... ); end;

maybe



Brenda Tinker, April 03, 2005 - 1:03 am UTC

TOM, help I am new to Oracle9i and trying to create a package named mm_rentals_pkg with two procedures and one function. The error message is not clear to me what I am doing wrong?


CREATE OR REPLACE PACKAGE mm_rentals_pkg
IS
PROCEDURE movie_rent_sp
(p_movie_id IN mm_movie.movie_id%TYPE,
p_member_id IN mm_rental.member_id%TYPE,
p_payment_id IN mm_pay_type.payment_methods_id%TYPE);

PROCEDURE movie_return_sp
(p_rental_id mm_rental.rental_id%TYPE);

FUNCTION movie_stock_sf
(p_movie_id IN mm_movie.movie_id%TYPE)
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY mm_rentals_pks
IS
PROCEDURE movie_rent_sp
(p_movie_id IN mm_movie.movie_id%TYPE,
p_member_id IN mm_rental.member_id%TYPE,
p_payment_id IN mm_pay_type.payment_methods_id%TYPE)
IS
v_count NUMBER;
v_movie NUMBER;
v_member NUMBER;
v_payment NUMBER;
BEGIN
SELECT MAX(rental_id) INTO v_count
FROM mm_rental;

SELECT COUNT(movie_id) INTO v_movie
FROM mm_movie
WHERE movie_id = p_movie_id;

SELECT COUNT(member_id) INTO v_member
FROM mm_member
WHERE member_id = p_member_id;

SELECT COUNT(payment_methods_id) INTO v_payment
FROM mm_pay_type
WHERE payment_methods_id = p_payment_id;
If v_movie = 0 THEN
DBMS_OUTPUT.PUT_LINE('Movie ID is invalid');
ELSE
IF v_member = 0 THEN
DBMS_OUTPUT.PUT_LINE('Member ID is invalid');
ELSE
IF v_payment = 0 THEN
DBMS_OUTPUT.PUT_LINE('Payment ID is invalid');
ELSE
INSERT INTO mm_rental
VALUES(v_count + 1, p_member_id, p_movie_id, SYSDATE, NULL, p_payment_id);

UPDATE mm_movie
SET movie_qty = movie_qty - 1
WHERE movie_id = p_movie_id;
END IF;
END IF;
END IF;
END movie_rent_sp;
END;
PROCEDURE MOVIE_RETURN_SP
(p_rental_id mm_rental.rental_id%TYPE)
IS
v_movie_id mm_movie.movie_id%TYPE;
v_rental_id mm_rental.rental_id%TYPE;
BEGIN
SELECT rental_id INTO v_rental_id
FROM mm_rental
WHERE rental_id = p_rental_id;

UPDATE mm_rental
SET checkin_date = SYSDATE
WHERE rental_id = p_rental_id;

UPDATE mm_movie
SET movie_qty = movie_qty + 1
WHERE movie_id=v_movie_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Rental ID is invalid');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected Error');
DBMS_OUTPUT.PUT_LINE('Error Code = ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Error Message = ' || SQLERRM);

END MOVIE_RETURN_SP;
END;
/
CREATE OR REPLACE FUNCTION movie_stock_sf
(p_movie_id IN mm_movie.movie_id%TYPE)
RETURN VARCHAR2
IS
v_title mm_movie.movie_title%TYPE;
v_qty mm_movie.movie_qty%TYPE;
lv_stock_info VARCHAR2 (50);
BEGIN
SELECT movie_title, movie_qty INTO v_title, v_qty
FROM mm_movie
WHERE p_movie_id = movie_id;

IF v_qty = 0 THEN
lv_stock_info := v_title || ' is currently not available';
ELSE
lv_stock_info := v_title || ' is available: ' || v_qty || ' on the shelf';
END IF;
RETURN lv_stock_info;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid Rental ID');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected Error');
DBMS_OUTPUT.PUT_LINE('Error Code = ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Error Message = ' || SQLERRM);
END movie_stock_sf;
/
SHOW ERROR



Package created.

Warning: Package Body created with compilation errors.
Function created.

No errors.




Tom Kyte
April 03, 2005 - 9:22 am UTC

put show error AFTER the package body if you want to see it's errors. show error without any other parameters shows the errors from the last compilation. the last one in your case worked.

or

show errors package body mm_rentals_pks


I feel compelled to comment on the code.

SELECT MAX(rental_id) INTO v_count
FROM mm_rental;

Oh no, trying a do it yourself "gap free sequence". Uh Oh. Big time troubles for you. I say that based on the insert:


INSERT INTO mm_rental
VALUES(v_count + 1, p_member_id, p_movie_id, SYSDATE, NULL, p_payment_id);


what happens in this database when two people call this at the same time??? they get the same count and insert the same rental_id. Either

a) one will get an error, thats a bummer.
b) both will work, thats a bigger bummer.

USE SEQUENCES. So, lose the select max() and the insert would be so far:

INSERT INTO mm_rental
VALUES( rental_id_seq.nextval, p_member_id, p_movie_id, SYSDATE, NULL,
p_payment_id);




SELECT COUNT(movie_id) INTO v_movie
FROM mm_movie
WHERE movie_id = p_movie_id;

....
If v_movie = 0 THEN
DBMS_OUTPUT.PUT_LINE('Movie ID is invalid');
ELSE

we would generally handle this data rule that you can only rent a movie with RI, when you create the table, make movie_id in mm_rental a foreign key to mm_movie.

so, you lose the count(movie_id) and you lose the procedure code to check.


Same with member and payment.

Now, once you set up the forieng keys and let the database do its job, this procedure becomes simply:

PROCEDURE movie_rent_sp
(p_movie_id IN mm_movie.movie_id%TYPE,
p_member_id IN mm_rental.member_id%TYPE,
p_payment_id IN mm_pay_type.payment_methods_id%TYPE)
IS
BEGIN
INSERT INTO mm_rental (Please,List,Column,Names)
VALUES(rental_id_seq.nextval, p_member_id, p_movie_id,
SYSDATE, NULL, p_payment_id);

UPDATE mm_movie
SET movie_qty = movie_qty - 1
WHERE movie_id = p_movie_id;
END movie_rent_sp;


and make sure that mm_movie also has a check constraint on it to "check (movie_qty >= 0)" -- a missing check in your code (but you cannot check it in your code without locking the table so the check constraint is the ONLY right answer)

One more question?

brenda, April 03, 2005 - 8:13 pm UTC

Thanks for the help I keep changing things.
After putting the show errors After the Create or Replace Package Body my error are:

Create [ OR REPLACE] package body package_name
IS|AS
this is the body of the syntax why is (IS) not a iSQL*Plus

Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW
| TYPE | TYPE BODY | DIMENSION
| JAVA SOURCE | JAVA CLASS } [schema.]name]

SP2-0850: Command "IS" is not available in iSQL*Plus

PROCEDURE movie_rent_sp
*

ERROR at line 1:
ORA-00900: invalid SQL statement
Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW
| TYPE | TYPE BODY | DIMENSION
| JAVA SOURCE | JAVA CLASS } [schema.]name]

SP2-0850: Command "IS" is not available in iSQL*Plus

PROCEDURE movie_rent_sp
*

ERROR at line 1:
ORA-00900: invalid SQL statement
Errors for PACKAGE D00772CCC:
LINE/COL ERROR
1/19 PLS-00103: Encountered the symbol "MM_RENTALS_PKG" when expecting one of the following: is authid as compress compiled wrapped The symbol "authid" was substituted for "MM_RENTALS_PKG" to cont inue.







Tom Kyte
April 03, 2005 - 8:46 pm UTC

after the "/" that runs the create or replace

create or replace package body .....
....
...
...
end;
/
show errors



I am not getting it !!

brenda, April 03, 2005 - 9:30 pm UTC

Tom I have the (create to !!!)after the
/
CREATE OR REPLACE PACKAGE BODY mm_rentals_pks
IS
PROCEDURE movie_rent_sp

My error message is :
Package created.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY MM_RENTALS_PKS:


IF I us the show errors my message is
/
SHOW ERRORS CREATE OR REPLACE PACKAGE BODY mm_rentals_pks
IS
PROCEDURE movie_rent_sp

ERROR MESSAGE IS:

Package created.

Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW
| TYPE | TYPE BODY | DIMENSION
| JAVA SOURCE | JAVA CLASS } [schema.]name]

SP2-0850: Command "IS" is not available in iSQL*Plus

PROCEDURE movie_rent_sp
*

ERROR at line 1:
ORA-00900: invalid SQL statement
No errors.

The ending looks like this

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid Rental ID');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected Error');
DBMS_OUTPUT.PUT_LINE('Error Code = ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Error Message = ' || SQLERRM);
END;
/
SHOW ERRORS







Tom Kyte
April 03, 2005 - 9:35 pm UTC


create or replace package body mm_rentals_pks
as
procedure......
....
end mm_rentals_pks;
/


show errors package body mm_rentals_pks;



they are separately commands


Fixed one of my errors one to go.

brenda, April 03, 2005 - 9:39 pm UTC

Tom, the lights turn on!!

I am looking back at some of your posting trying to figure this one out



SP2-0850: Command "IS" is not available in iSQL*Plus

PROCEDURE movie_rent_sp
*

ERROR at line 1:
ORA-00900: invalid SQL statement


Tom Kyte
April 04, 2005 - 6:49 am UTC

CREATE OR REPLACE PACKAGE mm_rentals_pkg
as
PROCEDURE movie_rent_sp
(p_movie_id IN mm_movie.movie_id%TYPE,
p_member_id IN mm_rental.member_id%TYPE,
p_payment_id IN mm_pay_type.payment_methods_id%TYPE);

PROCEDURE movie_return_sp
(p_rental_id mm_rental.rental_id%TYPE);

FUNCTION movie_stock_sf
(p_movie_id IN mm_movie.movie_id%TYPE)
RETURN VARCHAR2;
END;
/
show errors

CREATE OR REPLACE PACKAGE body mm_rentals_pkg
as
PROCEDURE movie_rent_sp (p_movie_id IN mm_movie.movie_id%TYPE,
p_member_id IN mm_rental.member_id%TYPE,
p_payment_id IN mm_pay_type.payment_methods_id%TYPE)
is
begin
null;
-- your code here
end;


PROCEDURE movie_return_sp
(p_rental_id mm_rental.rental_id%TYPE)
is
begin
null;
-- your code here
end;

FUNCTION movie_stock_sf
(p_movie_id IN mm_movie.movie_id%TYPE)
RETURN VARCHAR2
is
begin
null;
-- your code here
end;

END;
/
show errors

CREATE OR REPLACE FUNCTION movie_stock_sf
(p_movie_id IN mm_movie.movie_id%TYPE)
RETURN VARCHAR2
as
begin
null;
--your code here
end;
/
show errors


Can we use IN parameters in proc as direct assignment target.

San_Mat, May 20, 2005 - 6:37 am UTC

Hi Tom,
Thanks for the great service. ( i read your blog everyday).
My query is very simple.>>
I have a proc. like
CREATE OR REPLACE PROCEDURE mytest(PIE_ID IN number) IS
l_id number(5) := 0;
v_eonly_pages number(5) := 0;
begin
dbms_output.put_line('id'||pie_id);
--l_id := PIE_ID;
select nvl(sum(no_pages),0)
into v_eonly_pages
from pts_items itm,
pts_items_in_issue pii
where itm.id = pii.itm_id
and pii.pie_id = PIE_ID and ind_e_only = 'Y';
dbms_output.put_line('v_eonly_pages'||v_eonly_pages);
dbms_output.put_line('id'||PIE_ID);
end;
/

If i execute it
exec mytest(PIE_ID => 7681);
exec mytest(PIE_ID => 7682);
The results i get for 'v_eonly_pages' is 535 which is wrong.
If i assign the value of IN parameter to a local variable here
l_id := PIE_ID (which is commented in the proc.)
then the results are as per the expectations.
Whats wrong ?? Does that mean we can't use IN parameters as direct assignment to any SELECT statements in the proc.

Please help.
Regards
San_mat

Tom Kyte
May 20, 2005 - 8:06 am UTC

you'll need to give me a full example i can run, i cannot run this.

Re: Can we use IN parameters in proc as direct assignment target

Jonathan Taylor, May 20, 2005 - 8:57 am UTC

I suspect this problem is due to scoping rules.

The PIE_ID in the SQL is referring to the COLUMN in pts_items_in_issue, NOT the PARAMETER PIE_ID.

Therefore the line :-

and pii.pie_id = PIE_ID and ind_e_only = 'Y';

is identical to:-

and pii.pie_id = pii.PIE_ID and ind_e_only = 'Y';

(i.e. it matches itself).

Solution:
1. rename the parameter to something like p_Pie_ID (avoids confusion ) or
2. prefix the parameter in the SQL with the procedure name => mytest.PIE_ID (not my preferred method).




Continuation of the IN variable with example.

San_Mat, May 20, 2005 - 9:27 am UTC

Thanks for the prompt response Tom.
Ok. here is the complete example. I have Oracle 8i client on my machine.

SQL> create table test1
  2  ( id number(5)
  3    ,description varchar2(20)
  4  , epages number(5));


SQL> insert into test1(id,description,epages)
 values(1,'test1',20)
SQL> /

1 row created.
SQL> insert into test1(id,description,epages)
  2  values(1,'test2',10)
  3  /

1 row created.

SQL> insert into test1(id,description,epages)
  2  values(1,'test3',12);

1 row created.

SQL> CREATE OR REPLACE PROCEDURE mytest(PIE_ID IN number)  IS
  2   l_id number(5) := 0;
  3   v_eonly_pages number(5) := 0;
  4   begin
  5   dbms_output.put_line('id'||pie_id);
  6   l_id := PIE_ID;
  7   select  nvl(sum(epages),0)
  8     into v_eonly_pages
  9     from  test1
 10   where id = PIE_ID ;
 11     dbms_output.put_line('v_eonly_pages'||v_eonly_pages);
 12     dbms_output.put_line('id'||PIE_ID);
 13   end;
 14  /

Procedure created.

SQL> exec mytest( PIE_ID => 1);
id1
v_eonly_pages42
id1

See the above results, v_eonly_pages should be 20 but it is displaying 42. One more thing i noticed in this example is i am not getting the value of id1 whereas in my project code , i can see the value of pie_id(as mentioned in the previous post)
Please help.
Thanks 

Tom Kyte
May 20, 2005 - 10:33 am UTC

why should it be 20?  

SQL> insert into test1(id,description,epages)
 values(1,'test1',20)
SQL> /

1 row created.
SQL> insert into test1(id,description,epages)
  2  values(1,'test2',10)
  3  /

1 row created.

SQL> insert into test1(id,description,epages)
  2  values(1,'test3',12);

20+10+12 = 42??? 

Continuation of IN variable

San_Mat, May 20, 2005 - 9:35 am UTC

Sorry Tom,
It actually worked in the test example. My mistake in this example.
But when i use the actual query in my project its not working.
One more thing >> why it is not displaying the ID in this test example ??

Regards

Thanks Jonathan Taylor & Tom

San_Mat, May 20, 2005 - 9:41 am UTC

Jonathan
Thank you very much. You are correct. I did try it with the way you mentioned & it returns the same results. It is due to scoping rules only.
Tom can you please let us know about Scoping rules. It will be of great help to us.
Thanks again
Regards

Re: Continuation of IN variable

Jonathan Taylor, May 20, 2005 - 9:45 am UTC

All three rows have an ID of 1. The query is summing three rows. SO the answer of 42 is correct.

The id is being shown. "id1" is the concatenation of "id" and "1" (the in parameter).

Thanks Jonathan.

San_Mat, May 20, 2005 - 10:10 am UTC

Thanks Jonathan

parameter datatypes

A reader, June 13, 2005 - 11:13 am UTC

Tom,
How come when you declare a parameter based on a column's datatype (%TYPE), the data dictionary shows the datatype and length, but it doesn't raise an error when a longer string is passed in. Does Oracle ignore the data length? It seems a bit confusing that it would ignore the length, but show it in the data dictionary.

Example:
CREATE TABLE t (id VARCHAR2(1))
/

Table created.

CREATE OR REPLACE PROCEDURE p (p_id IN t.id%TYPE)
AS
BEGIN
NULL;
END p;
/

Procedure created.

desc p
PROCEDURE p
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2(1) IN

EXEC p ('abc')

PL/SQL procedure successfully completed.


Tom Kyte
June 13, 2005 - 12:04 pm UTC

formal parameters are always unconstrained types at runtime.

I don't know "why" it shows the varchar2(1), I concurr it appears misleading.

I WANT SQL SOLUTION ABOUT QUESTION BELOW

MORAD MANSOUR YEMEN, July 23, 2005 - 10:08 am UTC

i have example ANY YABLE_NAME
NAME GRADE
ALI 20
ALI 30
ALI 50
I WANT SQL STATEMENT TO DO
ALI 20 30 50

Tom Kyte
July 23, 2005 - 10:38 am UTC

SEARCH SITE FOR PIVOT OR STRAGG

please give me solution

morad anam, July 23, 2005 - 11:30 am UTC

I HAVE THIS EXAMPLE
TABLE NAME ST
ST_NAME ST_GRADE
ALI 10
ALI 30
ALI 50
I WANT SELECT STATEMENT IN SQL GAVE US
ALI 10 30 50

Tom Kyte
July 23, 2005 - 12:45 pm UTC

wow.

search for pivot

search for stragg


on this site. really, it works.


you have too many assumptions I don't know about. Is there always just three? just ALI in the table? by the way, where is the create table and insert into's? I don't see those (but yet the page you used to add this text said

<quote>
If your followup requires a response that might include a query, you had better supply very very simple create tables and insert statements. I cannot create a table and populate it for each and every question. The SMALLEST create table possible (no tablespaces, no schema names, just like I do in my examples for you)
</quote>

Remember, this is not about "get tom to write my code".


if just a single student
if just three grades

then

select name, max(decode(r,1,grade)),
max(decode(r,2,grade)), max(decode(r,2,grade))
from (select name, grade, rownum r from t where name = :x )
group by name

would do it, if some of those very much unspecified assumptions are inaccurate, then row_number() and more columns might work


but again, a simple search will reveal *a lot*

Pass by value and Reference

Kamal, August 11, 2005 - 6:25 am UTC

Hi Tom,

I read in a Article that

IN Pass by Reference

OUT
and IN OUT Pass by Value

so if IN parameter is Pass by Reference, why it is not allowed to change in the procedure?

Regards
kamal

Tom Kyte
August 11, 2005 - 10:07 am UTC

because we passed by reference (a pointer), if you changed the IN variable, it would CHANGE THE REFERENCED variable.

We pass the other two by value just to make sure you cannot change the pointed to thing.

Size constraint on procedure parameter

Arindam Mukherjee, August 31, 2005 - 8:13 am UTC

Hi Tom,

I am facing a problem while using size constraint on procedure parameter. Following is the quote from Oracle documentation for PL/SQL User's Guide and Reference, Release 2 (9.2), Chapter 8, PL/SQL Subprograms:

<quote>
You cannot constrain the datatype of a parameter. For example, the following declaration of acct_id is illegal because the datatype CHAR is size-constrained:

PROCEDURE reconcile (acct_id CHAR(5)) IS ...  -- illegal

However, you can use the following workaround to size-constrain parameter types indirectly:

DECLARE 
   SUBTYPE Char5 IS CHAR(5);
   PROCEDURE reconcile (acct_id Char5) IS ...
</quote>

With this information, I tried to execute the following procedure:

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 24 14:51:54 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Release 9.2.0.4.0 - 64bit Production
JServer Release 9.2.0.4.0 - Production

SQL> set serveroutput on
SQL> declare
  2      SUBTYPE str10 IS VARCHAR2(10);
  3      PROCEDURE proc(x IN str10) AS
  4      BEGIN
  5          dbms_output.put_line(x);
  6      END proc;
  7  begin
  8      proc('This string is of length more than 10');
  9  end;
 10  /
This string is of length more than 10

PL/SQL procedure successfully completed.

SQL>

I was expecting an exception like
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
as I got below:

SQL> ed
Wrote file afiedt.buf

  1  declare
  2      SUBTYPE str10 IS VARCHAR2(10);
  3      str str10;
  4      PROCEDURE proc(x IN str10) AS
  5      BEGIN
  6          dbms_output.put_line(x);
  7      END proc;
  8  begin
  9      proc('This string is of length more than 10');
 10     str := 'This string is of length more than 10';
 11* end;
SQL> /
This string is of length more than 10
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 10

Can you please tell me what is going on here? Am I missing out something or I misunderstood the Oracle documentation?

Thanks and Regards,

Arindam Mukherjee 

Tom Kyte
August 31, 2005 - 1:55 pm UTC

plsql only has non-constrained datatypes for parameters, so I believe the documentation to be saying it 'wrong' here, i'll add to the list.

NOCOPY WITH REF CURSOR

Vincent, September 21, 2005 - 2:45 pm UTC

Hi Tom,

I have the code below which should return me all employees record regardless if it's successfully inserted or not. The problem occurs when insert throws an exception(say unique violation error). When I try to loop through the all_employees ref cursor, all i got was "ORA-01001: invalid cursor". Any Ideas..


CREATE OR REPLACE PACKAGE TEST_PACKAGE AS
TYPE REF_CURSOR IS REF CURSOR;

PROCEDURE ADD_EMPLOYEE(EMP_NAME IN VARCHAR2, ALL_EMPLOYEES OUT NOCOPY REF_CURSOR);

PROCEDURE GET_ALLEMPLOYEES(ALL_EMPLOYEES OUT NOCOPY REF_CURSOR);
END TEST_KBASE;
/

CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE AS

PROCEDURE ADD_EMPLOYEE(EMP_NAME IN VARCHAR2, ALL_EMPLOYEES OUT NOCOPY REF_CURSOR)
AS
BEGIN
INSERT INTO EMPLOYEE (EMPLOYEE_NAME, EMP_DATE)
VALUES(EMP_NAME, SYSDATE);
GET_ALLEMPLOYEES(ALL_EMPLOYEES);
EXCEPTION
WHEN OTHERS THEN
GET_ALLEMPLOYEES(ALL_EMPLOYEES);
RAISE_APPLICATION_ERROR(-20000, 'Error Message');
END ADD_EMPLOYEE;


PROCEDURE GET_ALLEMPLOYEES(ALL_EMPLOYEES OUT NOCOPY REF_CURSOR)
AS
BEGIN
OPEN ALL_EMPLOYEES FOR
SELECT EMP_NAME, EMP_DATE FROM EMPLOYEE;

END GET_ALLEMPLOYEES;
END TEST_PACKAGE;

Tom Kyte
September 21, 2005 - 8:06 pm UTC

you'll have to show me how to reproduce withg as small a test case as possible (create table, insert, run code...)

NOCOPY WITH REF CURSOR

Vincent, September 22, 2005 - 6:34 am UTC

Hi Tom,
Here's the complete test case.. Hope you can help me on this.. I have been pondering on this for days..

CREATE TABLE EMPLOYEE (EMPLOYEE_NAME VARCHAR2(30), EMP_DATE DATE);
ALTER TABLE EMPLOYEE ADD UNIQUE(EMPLOYEE_NAME);
/

----- START OF PACKAGE ------------
CREATE OR REPLACE PACKAGE TEST_PACKAGE AS
TYPE REF_CURSOR IS REF CURSOR;

PROCEDURE ADD_EMPLOYEE(EMP_NAME IN VARCHAR2, ALL_EMPLOYEES OUT NOCOPY
REF_CURSOR);

PROCEDURE GET_ALLEMPLOYEES(ALL_EMPLOYEES OUT NOCOPY REF_CURSOR);
END TEST_PACKAGE;
/
-------- END OF PACKAGE ------------
--------- START OF PACKAGE BODY --------
CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE AS

PROCEDURE ADD_EMPLOYEE(EMP_NAME IN VARCHAR2, ALL_EMPLOYEES OUT NOCOPY
REF_CURSOR)
AS
BEGIN
INSERT INTO EMPLOYEE (EMPLOYEE_NAME, EMP_DATE)
VALUES(EMP_NAME, SYSDATE);
GET_ALLEMPLOYEES(ALL_EMPLOYEES);
EXCEPTION
WHEN OTHERS THEN
GET_ALLEMPLOYEES(ALL_EMPLOYEES);
RAISE_APPLICATION_ERROR(-20000, 'Error Message');
END ADD_EMPLOYEE;


PROCEDURE GET_ALLEMPLOYEES(ALL_EMPLOYEES OUT NOCOPY REF_CURSOR)
AS
BEGIN
OPEN ALL_EMPLOYEES FOR
SELECT EMPLOYEE_NAME, EMP_DATE FROM EMPLOYEE;

END GET_ALLEMPLOYEES;
END TEST_PACKAGE;
/
------------ END OF PACKAGE BODY -------
--------- START TEST CODE ---------
SET SERVEROUTPUT ON;
DECLARE
ALLEMPLOYEES TEST_KBASE.REF_CURSOR;
EMPLOYEENAME EMPLOYEE.EMPLOYEE_NAME%TYPE;
HIREDATE EMPLOYEE.EMP_DATE%TYPE;

BEGIN
TEST_PACKAGE.ADD_EMPLOYEE('Vincent', ALLEMPLOYEES);
LOOP
FETCH ALLEMPLOYEES INTO EMPLOYEENAME, HIREDATE;
EXIT WHEN ALLEMPLOYEES%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS -- ' || EMPLOYEENAME || ' HIRE DATE IS -- ' || HIREDATE);
END LOOP;
CLOSE ALLEMPLOYEES;
EXCEPTION
WHEN OTHERS THEN
LOOP
FETCH ALLEMPLOYEES INTO EMPLOYEENAME;
EXIT WHEN ALLEMPLOYEES%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS -- ' || EMPLOYEENAME ||' HIRE DATE IS -- ' || HIREDATE);
END LOOP;
CLOSE ALLEMPLOYEES;
END;

---------------------- END TEST CODE -----------

The test code might not seem elegant but I am trying to access the package thru Java jdbc where you have try catch finally block.
try{
TEST_PACKAGE.ADD_EMPLOYEE('xxxx', outcursor);
}catch(SQLException sqex){
... handle the exception
}finally{
... handle and retrieve outcursor resultset...
}
cheers,

Vincent


Tom Kyte
September 22, 2005 - 1:57 pm UTC

but I don't get what the *problem* you are trying solve here is.

(when others, without a RAISE is a bug almost everytime)


What is the goal here, I'm not getting it from this code.

(nocopy isn't really sensible with a ref cursor, a ref cursor is pretty small, it is already a pointer and that is what nocopy tries to do with "big" things)



NOCOPY WITH REF CURSOR

Vincent, September 22, 2005 - 6:45 am UTC

Hi Tom,

Just a follow-up information.. The code above is used in a web application where the add employee and list all employees functionality is located on a single page. I just wanted to have a single trip to the database when inserting an employee entry and list all employees regardless if it's inserted successfully or not. Each name inserted must be unique(of course this not the best criteria for uniqueness but it's just a hypothetical test case to highlight the real problem).

Cheers,

Vincent

Tom Kyte
September 22, 2005 - 1:59 pm UTC

That would simply be:


procedure add_emp( new_emp_info, refcursor OUT sys_refcursor, bad_way_to_handle_errors_msg, bad_way_to_handle_errors_cd )
is
begin
begin
insert into emp values ( ... );
exception
when others then
bad_way_to_handle_errors_msg := sqlerrm;
bad_way_to_handle_errors_cd := sqlcode;
end;
open refcursor for .....;
end;


NOCOPY WITH REF CURSOR

Vincent, September 22, 2005 - 4:32 pm UTC

Hi Tom

The problem i was anticipating was if the user enters the same data(e.g. by clicking the same button twice)from the page, an error message will be presented to the user saying the data has already been entered while still showing all the current employees from the database table. As you can see from above, I was trying to simulate the problem by running my test code. Initially it runs successfully since no entry has been inserted. If you run it the second time, it throws a unique constraint violation since the entry already exists. In the package body, I was trying to fill the ALL_EMPLOYEES REF CURSOR(using NOCOPY--since it passes by-reference)with data before the raise_application_error occurs. So in my test code after catching the exception, I was hoping to retrieve the data but I get an invalid cursor error. In short, I was trying to catch the error code, error message and retrieving the ALL_EMPLOYEES REF cursor all at the same time. Would that be possible? It's more of like the try, catch and finally block behavior in Java.

Tom Kyte
September 22, 2005 - 9:21 pm UTC

but a ref cursor "isn't big", it isn't like the data is "copied out" - the ref cursor is small - nocopy - big "nothing" for it. so, don't get sidetracked on that.

a refcursor that "points" to 10,000,0000,000,000 rows is as big as a ref cursor that points to "select * from dual"


try my logic -- put the insert in a begin block -- CATCH ONLY the duplicate error (not when others -- when DUP_VAL_ON_INDEX!!) and open the ref cursor, it'll work


How to call my procedure

Pinaki Bhattacharya, December 07, 2005 - 5:28 am UTC

Hello Tom,
I have written this procedure.

create or replace procedure names_tog
(first_name in out varchar2,
last_name in out varchar2,
name_format in varchar2 default 'FIRST,LAST',
full_name out varchar2) as

begin

first_name:=UPPER(first_name);
last_name:=UPPER(last_name);

if name_format = 'FIRST,LAST' then
full_name:=first_name||' '||last_name;
else
full_name:=last_name||' '||first_name;
end if;
end;
/
sho err


This is a standalone procedure and for learning purpose.I am not very sure how am I going to call this procedure.My intention is to pass the First Name, Last Name and the Name Fomrat to the procedure and depending on the Name Format I want a concatenated value returned.This could have done with a function but I am keen on doing it via a procedure.

Thanks for your immense help...

Neo


Tom Kyte
December 07, 2005 - 7:15 am UTC

to call in sqlplus:

variable fn varchar2(100)
exec names_tog( 'Tom', 'Kyte', full_name => :fn );
print fn

Another way...

Pinaki Bhattacharya, December 08, 2005 - 12:02 am UTC

Hello Tom,
           Thank you for such a quick response.I tried your solution.

SQL> variable fn varchar2(100)
SQL> exec names_tog( 'Tom', 'Kyte', full_name => :fn );
BEGIN names_tog( 'Tom', 'Kyte', full_name => :fn ); END;

                 *
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00363: expression 'Tom' cannot be used as an assignment target
ORA-06550: line 1, column 25:
PLS-00363: expression 'Kyte' cannot be used as an assignment target
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The problem I think is that first_name and last_name being IN OUT variables also try to return a value to the enviornment from where they are called(being SQL Plus in this scenario).I may not be printing them from SqlPlus but they are returning values as in 'TOM' and 'KYTE'.Am I correct in my thinking here? 

I tried it in another way.

SQL>variable fn varchar2(500)
SQL>variable ln varchar2(500)
SQL>variable ful_name varchar2(500)


 SQL> begin
  2  :fn := 'Tom';
  3  :ln := 'Kyte';
  4  names_tog(:fn, :ln, full_name => :ful_name);
  5  dbms_output.put_line('Out String is : '|| :ful_name);
  6  end;
  7  /
Out String is : TOM  KYTE

PL/SQL procedure successfully completed. 

Thanks a lot,

Neo

 

Tom Kyte
December 08, 2005 - 2:04 am UTC

did not noticed you made fn, ln in out - probably shouldn't be in "real life"

do you really want to have modified your input parameters in this case, only you can answer that.

Pinaki Bhattacharya, December 08, 2005 - 2:42 am UTC

I had actaully used IN OUT because I wanted to pass values into the procedure and at the same time write into them as I have done here.

first_name:=UPPER(first_name);
last_name:=UPPER(last_name);

I am converting them into uppercase whatever format they can be sent in(uppercase or lowercase or mixed case).

1) Isin't it correct using IN OUT variable when you try to do something like this.I mean when you want to read from as well as write into them?

2) Is there any other way of doing this thing?It would be really helpful if you could give an example where an INOUT variable would be used appropriately.

Thank you Tom, You make learning Oracle very easy and fun with your test case examples.I really learnt a lot from the test cases that you give.Thanks a bunch.

Tom Kyte
December 08, 2005 - 7:02 am UTC

just put them into local variables......


suggest you use p_ for parameters, l_ for local variables to, just get in that habit, it'll save you hours in the future when trying to figure out why:

create procedure update_emp( ename in varchar2 )
as
begin
update emp set sal = sal*1.1 where ename = ename;
end;

updates every row, whereas


create procedure update_emp( P_ename in varchar2 )
as
begin
update emp set sal = sal*1.1 where ename = P_ename;
end;


works....


1) you might:

create or replace procedure names_tog
(first_name in varchar2,
last_name in varchar2,
name_format in varchar2 default 'FIRST,LAST',
full_name out varchar2)
as
l_first_name varchar2(30) := upper(first_name);
l_last_name varchar2(30) := upper(last_name);
begin
if name_format = 'FIRST,LAST' then
full_name:=l_first_name||' '||l_last_name;
else
full_name:=l_last_name||' '||l_first_name;
end if;
end;
/


2) see #1 IN OUT is used when you want to modify the parameters, it is useful - but here I don't think you want to modify the parameters "permanently"






PL/SQL IN and OUT parameters

Sanjeev Vibuthi, December 24, 2005 - 1:03 am UTC

Hi Tom,

This thred is excellent and it help a lot to me in writing my Procedures and understand the difference between COPY and NOCOPY


Thanx



VARCHAR2 versus CHAR

Arindam Mukherjee, February 21, 2006 - 5:10 am UTC

Tom,

Is there any difference between VARCHAR2(1) and CHAR(1) in any way? If I use CHAR(1) in column definition of a table, then how it's different from VARCHAR2(1) so far as table size is concerned? Is there any difference if we store NULL in VARCHAR2(1) or CHAR(1)?

Thanks and Regards,

Arindam Mukherjee

Tom Kyte
February 21, 2006 - 7:49 am UTC

my answer will always be:

just use varchar2(1)


but char(1) and varchar2(1) are for all intents and purposes identical (this is true for 1 only)

they are physically stored the same and would logically behave the same.

Procedures with unknown number of parameters

Vikram Romeo, July 03, 2006 - 12:55 pm UTC

Hi Tom,

Is it possible to write a stored procedure with unknown number of parameters in Oracle - for example, it should execute with any number of parameters which is not fixed at compile time. I know we can always define a large number of parameters and ignore the ones which are not passed. Other than this , is there any other way?

If yes, can you please demonstrate an example?

Regards,
Vikram Romeo

Tom Kyte
July 07, 2006 - 7:19 pm UTC

In a "sense", yes... 

ops$tkyte@ORA10GR2> create or replace type varargs
  2  as table of varchar2(4000)
  3  /

Type created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace procedure p( p_inputs in varargs default varargs() )
  2  as
  3  begin
  4          for i in 1 .. p_inputs.count
  5          loop
  6                  dbms_output.put_line( 'arg('||i||') = ' || p_inputs(i) );
  7          end loop;
  8          dbms_output.put_line( '----------------' );
  9  end;
 10  /

Procedure created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec p();
----------------

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec p( varargs( 1, 2, 3 ) );
arg(1) = 1
arg(2) = 2
arg(3) = 3
----------------

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec p( varargs( 'a', 'b', 'hello', 'world', 'c', 'd' ) );
arg(1) = a
arg(2) = b
arg(3) = hello
arg(4) = world
arg(5) = c
arg(6) = d
----------------

PL/SQL procedure successfully completed.


Of course, your invoking plsql code would just have variables of type varargs - not literals like this demo... 

In parameter gets cleared

Bart, October 03, 2006 - 1:36 am UTC

I encountered that parameters can unexpectedly (well, not anymore) get cleared. It happened to me and it took a while before I found out the 'bug'.


declare

some_id number;

procedure do_something (p_number in number)
is
begin
dbms_output.put_line ('do_something 1 (expect 13): p_number='||nvl(to_char(p_number), 'NULL'));
/*
This assignment null's out parameter p_number also (apparently)
*/
some_id := null;
dbms_output.put_line ('do_something 2 (expect 13): p_number='||nvl(to_char(p_number), 'NULL'));
end do_something;

begin
some_id := 13;
do_something (some_id);
end;
/

Although I presume parameter and variable share the same memory I didn't expect this behavior. Even more because Oracle explicitly allows to share memorylocation with NOCOPY.

Of course a workaround is not a problem.



Tom Kyte
October 03, 2006 - 6:26 am UTC

I think this is definitely a bug, I've pinged the plsql guys to confirm...

IN parameters are passed by reference

Andy, October 03, 2006 - 9:26 am UTC

I didn't know that IN parameters were passed by reference, so the above behaviour surprised (shocked, actually) me. However, a quick check in PL/SQL User’s Guide and Reference (9i) confirms that this is the intended behavior. Specifically, "Summary of Subprogram Parameter Modes" section confirms that "IN" are indeed passed by ref. The "Understanding Subprogram Parameter Aliasing" section even gives an example of this kind of behaviour.

Counter-intuitive, definitely. Bug? I don't know!

This is what I learned new today

Thomas Kyte, October 05, 2006 - 8:26 am UTC

In fact, this is the expected behaviour - believe it or not. I was not expecting that.

However, that said, I never encountered it because

a) I tend never to use globals
b) I would never access the "global" in the subprogram like
that, just seems a bad idea/bad practice.

Interesting, another nugget to file away.

A trivial question

Sumit Thapar, January 04, 2007 - 12:23 pm UTC

Hi Tom,
An undoubtedly treasure of information is what I found in this thread. Absolutely very much useful. I got to know a lot of things about IN,OUT,IN/OUT parameters which were not highlighted earlier.
Thanks for sharing all this.

However, there are questions which boils down to the very basics.
> What is the basic criteria that one should use to decide upon using a function or a procedure?
> What is the difference in essence between a function and a procedure ? (apart from the usual ones saying a function returns a value, but a procedure does not).

Would really appreciate if you could throw some light on this.

Thanks a lot in advance.

Tom Kyte
January 05, 2007 - 9:15 am UTC

function vs procedure

which one is easier to use for you?


if ( check_balance( p_account_id ) > 5000 )
then
....


might be "easier" than:

check_balance( p_account_id, l_balance );
if ( l_balance > 5000 )
then
....


a function in plsql is simply a procedure that returns a value.

Thanks a lot..

Sumit Thapar, January 07, 2007 - 3:23 am UTC


IN OUT returns.....

Sagar, June 14, 2007 - 3:54 am UTC

SQL> ed
Wrote file afiedt.buf

1 declare
2 n number ;
3 procedure p1(n1 in out number, n2 in out number) is
4 begin
5 dbms_output.put_line('n1 is ' || n1 || ' and n2 is ' || n2 );
6 n2 := 200 ;
7 dbms_output.put_line('n1 is ' || n1 || ' and n2 is ' || n2 ) ;
8 n1 := 100;
9 dbms_output.put_line('n1 is ' || n1 || ' and n2 is ' || n2 ) ;
10 end;
11 begin
12 n:= 10;
13 p1(n,n);
14 dbms_output.put_line('n is ' || n);
15* end;
SQL> /
n1 is 10 and n2 is 10
n1 is 10 and n2 is 200
n1 is 100 and n2 is 200
n is 200

PL/SQL procedure successfully completed.


Why n is not showing as 100 which the latest value set for it in the procedure p1 ?.




Tom Kyte
June 14, 2007 - 7:47 am UTC

because plsql works with copy on return - not pointers.

so the routine is more like:

begin
   n := 10;
   BEGIN
      tmp1 := n;
      tmp2 := n;
      p(tmp1,tmp2);
      n := tmp1;
      n := tmp2;
   END;
end;


But precedence

Sagar, June 14, 2007 - 8:03 am UTC

Thanks for the reply Tom

You said its like...
begin
n := 10;
BEGIN
tmp1 := n;
tmp2 := n;
p(tmp1,tmp2);
n := tmp1;
n := tmp2;
END;
end;

Does that mean copying takes place from left to right manner?. This is resulting in n not being latest value as set in the procedure as seen in above example.





Tom Kyte
June 14, 2007 - 9:54 am UTC

N is not being set in the procedure at all.

N1 and N2 are.

And they are assigned to N after p executes

and the order of assignment is not defined - it would be a horribly BAD idea to rely on this working in some prescribed fashion.

It is really bad code, do not do this.

Thanks Tom.....

Sagar, June 14, 2007 - 1:27 pm UTC

Thanks Tom...
I got it...

Alessandro Nazzani, January 07, 2008 - 9:41 am UTC

Hi Tom.

I was working on a code snippet like the following:

procedure bulk_load is
TYPE ImpTab IS TABLE OF IMP_TBL%rowtype;
imp_recs ImpTab;

CURSOR c1 IS SELECT * FROM IMP_TBL FOR UPDATE;
begin
OPEN c1;
FETCH c1 BULK COLLECT INTO imp_recs;
CLOSE c1;
For i in 1 .. imp_recs.count Loop
do_checks(imp_recs(i));
.
.etc.
.

procedure do_checks(imp_rec in out nocopy IMP_TBL%rowtype) is
.
.etc.
.

I suspect NOCOPY in this case is ignored, because I'm passing only a single element, not the entire collection: could you please confirm (or reject) this?

Thank you.

Alessandro
Tom Kyte
January 07, 2008 - 11:24 am UTC

suggestion:

use lock table imp_tbl in exclusive mode; INSTEAD OF for update.

it'll be less resource intensive (much much much less) than select for update every row.

nocopy is always a hint - it never needs to be used, ever - and can change from release to release.

but here is some food for thought:

ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          type rec is table of all_objects%rowtype;
  4
  5          procedure p1( l_rec in out nocopy all_objects%rowtype );
  6          procedure p2( l_rec in out all_objects%rowtype );
  7  end;
  8  /

Package created.

ops$tkyte%ORA10GR2> create or replace package body my_pkg
  2  as
  3          type rec is table of all_objects%rowtype;
  4
  5          procedure p1( l_rec in out nocopy all_objects%rowtype )
  6          is
  7          begin
  8                  l_rec.object_name := l_rec.object_id;
  9          end;
 10
 11          procedure p2( l_rec in out all_objects%rowtype )
 12          is
 13          begin
 14                  l_rec.object_name := l_rec.object_id;
 15          end;
 16  end;
 17  /

Package body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_recs my_pkg.rec;
  3          l_rec  all_objects%rowtype;
  4          l_start number;
  5  begin
  6          select * bulk collect into l_recs from all_objects where rownum <= 10000;
  7          l_start := dbms_utility.get_cpu_time;
  8          for j in 1 .. 50
  9          loop
 10          for i in 1 .. l_recs.count
 11          loop
 12                  my_pkg.p1( l_recs(i) );
 13          end loop;
 14          end loop;
 15          dbms_output.put_line( (dbms_utility.get_cpu_time-l_start) || ' p1, array hsecs...' );
 16
 17          select * bulk collect into l_recs from all_objects where rownum <= 10000;
 18          l_start := dbms_utility.get_cpu_time;
 19          for j in 1 .. 50
 20          loop
 21          for i in 1 .. l_recs.count
 22          loop
 23                  my_pkg.p2( l_recs(i) );
 24          end loop;
 25          end loop;
 26          dbms_output.put_line( (dbms_utility.get_cpu_time-l_start) || ' p2, array hsecs...' );
 27
 28
 29          select * bulk collect into l_recs from all_objects where rownum <= 10000;
 30          l_start := dbms_utility.get_cpu_time;
 31          for j in 1 .. 50
 32          loop
 33          for i in 1 .. l_recs.count
 34          loop
 35                  l_rec := l_recs(i);
 36                  my_pkg.p1( l_rec );
 37          end loop;
 38          end loop;
 39          dbms_output.put_line( (dbms_utility.get_cpu_time-l_start) || ' p1 scalar, hsecs...' );
 40
 41          select * bulk collect into l_recs from all_objects where rownum <= 10000;
 42          l_start := dbms_utility.get_cpu_time;
 43          for j in 1 .. 50
 44          loop
 45          for i in 1 .. l_recs.count
 46          loop
 47                  l_rec := l_recs(i);
 48                  my_pkg.p2( l_rec );
 49          end loop;
 50          end loop;
 51          dbms_output.put_line( (dbms_utility.get_cpu_time-l_start) || ' p2 scalar, hsecs...' );
 52  end;
 53  /
120 p1, array hsecs...
120 p2, array hsecs...
85 p1 scalar, hsecs...
155 p2 scalar, hsecs...

PL/SQL procedure successfully completed.

Alessandro Nazzani, January 10, 2008 - 4:34 am UTC

Hi Tom and apologies for the late reply.

> suggestion:
>
> use lock table imp_tbl in exclusive mode; INSTEAD OF for update.

The depth of my ignorance always amazes me. :(

Thanks for the tip!

> here is some food for thought:

Much appreciated: will try and make the most out of it.

Cheers.

Alessandro

Nocopy with BLOB

A reader, January 15, 2008 - 10:38 am UTC

thank you Tom for your to-the-point answers..
you commented earlier -"IN parameters -- already passed by reference."
Is it a generic statement for all large objects? I have a function that uses IN BLOB. Is it also passed by reference? We are running a 9i database.
Thank you for your response.
Tom Kyte
January 15, 2008 - 3:16 pm UTC

nocopy only applies to in out and out parameters - so it doesn't really matter :)


blobs are tiny, they are just pointers to begin with :)

Record type as OUT parameter

Kari, March 03, 2008 - 6:46 pm UTC

Hi Tom

Wondering why you can't use a record type as an out parameter? You seem to have to declare each out parameter individually.

For example, if you have a package:

CREATE OR REPLACE PACKAGE TEST_PKG AS

TYPE test_type IS RECORD (
test_a VARCHAR2(60)
,test_b VARCHAR2(60)
);

PROCEDURE test_case(p_a IN VARCHAR2
,p_key OUT test_type);

END TEST_PKG;

END;
/

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS


PROCEDURE test_case(p_a IN VARCHAR2
,p_key OUT test_type) IS
BEGIN
--do something here
p_key.test_a :='test a';
p_key.test_b :='test b';

END;

END TEST_PKG;
/

You compile it and it doesn't raise any errors. However, then when you make a call to this procedure you will get an error similar to:

ORA-06550: line 10, column 1:
PLS-00306: wrong number or types of arguments in call to 'TEST_CASE'
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored

Your insight would be much appreciated.
Tom Kyte
March 03, 2008 - 9:33 pm UTC

check out the error, it has nothing to do with the code you POSTED

it has only to do with the code you DID NOT post, you know, the block if plsql that called this.



ops$tkyte%ORA10GR2> declare
  2          l_rec   test_pkg.test_type;
  3  begin
  4          test_pkg.test_case( 'hello world', l_rec );
  5          dbms_output.put_line( l_rec.test_a );
  6          dbms_output.put_line( l_rec.test_b );
  7  end;
  8  /
test a
test b

PL/SQL procedure successfully completed.



You probably redefined the type - did not use the PACKAGE.TYPE_NAME like you should - and that was the cause.

clarification

Ranjith, March 04, 2008 - 6:38 am UTC

Tom,

You wrote:

In the COPY routine -- p1 -- the values are COPIED 
to the out parameters upon successful completion.  In the nocopy routine, PLSQL is in effect 
sending a pointer to X and Y -- as soon as we modify them in the subroutine, their values are 
changed in the calling routine.

if so, why is the below procedure pr executing till the end;

SQL>drop table t
  2  /

Table dropped.

SQL>
SQL>create table t (t varchar2(100))
  2  /

Table created.

SQL>
SQL>create or replace procedure pr(x in out nocopy number)
  2  is
  3  begin
  4    insert into t values ('bef '||x);
  5    commit;
  6    x := 10;    -- <<---Shouldn't it fail here?
  7    insert into t values ('aft '||x);
  8    commit;
  9  end;
 10  /

Procedure created.

SQL>
SQL>declare
  2    n number(1);
  3  begin
  4    pr(n);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4


SQL>
SQL>select * from t
  2  /

T
----------
bef
aft 10

2 rows selected.


Tom Kyte
March 04, 2008 - 7:31 am UTC

why should it fail necessarily, nocopy is a "HINT", does not need to happen.

ops$tkyte%ORA10GR2> create or replace procedure pr(x in out nocopy number)
  2  is
  3  begin
  4     x := 1;    -- <<---Shouldn't it fail here?
  5     raise program_error;
  6  end;
  7  /

Procedure created.

ops$tkyte%ORA10GR2> declare
  2     n number(1) := 0;
  3  begin
  4     pr(n);
  5  exception
  6          when others then dbms_output.put_line( 'error ' || n );
  7  end;
  8  /
error 0

PL/SQL procedure successfully completed.




once you used the constrained type, it knew it would not be SAFE to nocopy it


ops$tkyte%ORA10GR2> create or replace procedure pr(x in out nocopy number)
  2  is
  3  begin
  4     x := 1;    -- <<---Shouldn't it fail here?
  5     raise program_error;
  6  end;
  7  /

Procedure created.

ops$tkyte%ORA10GR2> declare
  2     n number := 0;
  3  begin
  4     pr(n);
  5  exception
  6          when others then dbms_output.put_line( 'error ' || n );
  7  end;
  8  /
error 1

PL/SQL procedure successfully completed.


You should have tested it like above to see if it was doing a nocopy...


Will the values of OUT variables are not copied when using NOCOPY Hint?

Mukund Nampally, January 16, 2012 - 11:42 am UTC

DECLARE
n NUMBER := 10;
PROCEDURE do_something (
n1 IN NUMBER,
n2 IN OUT NUMBER,
n3 IN OUT NOCOPY NUMBER) IS
BEGIN
n2 := 20;
dbms_output.put_line(n1); -- prints 10
n3 := 30;
dbms_output.put_line(n1); -- prints 30
END;
BEGIN
do_something(n, n, n);
dbms_output.put_line(n); -- prints 20
END;
/
Why does the last dbms_output print 20?
Why not 30?
Tom Kyte
January 23, 2012 - 6:05 am UTC

nocopy is a hint, it does not need to be obeyed, ever - we decide if and when.

Your programming construct is a horribly bad idea, I hope you were just playing around.


But it looks like the nocopy hint was obeyed, we passed a pointer to N to the procedure - we do that for IN parameters and when we use nocopy - the in out as well. So, both N1 and N3 pointed to the same thing.

And when we returned, we have to copy the OUT parameter N2 into N - which overwrote the value in N.


The code above is sort of like (using a bit of C syntax here)

begin
   temporary_n2 := n;
   do_something( &n, &temporary_n2, &n );    -- &n is "address of N", a pointer
   n := tempory_n2;
   dbms_output.put_line( n );
end;


if you left the nocopy off - we might see 20 or we might see 30 - it depends on the order the compiler decided to pop the temporary values off of the return stack.


Regardless, if you wrote code like this - you should stop right here, right now and never ever do anything remotely similar. The behavior is going to be erratic and could change as the compiler changes or even just with different optimization levels/patch releases and so on...

Yes ... not a serious thought!

Mukund Nampally, January 22, 2012 - 5:15 am UTC

Hi Tom,
Yeah this is not a serious code from the Batch process.Was just trying to see the effects of NOCOPY.
Actually, this code snippet is form Oracle Documentation.
But Oracle doesn't say firmly/concretely what happens in this scenario.
But my main question is -
The OUT parameter(without the NOCOPY) has been copied to the variables of calling procedure and hence resulted in overwriting the parameter.
The NOCOPY will not COPY the results AGAIN when the control is passed from Called subroutine to calling subroutine?
How should I go about this?
Tom Kyte
January 23, 2012 - 6:03 am UTC

But Oracle doesn't say firmly/concretely what happens in this scenario.


because it is not firm, it is not concrete, it is a really bad construct that leads to ambiguous results.

The OUT parameter(without the NOCOPY) has been copied to the variables of
calling procedure and hence resulted in overwriting the parameter.


I don't know what you mean by that exactly. If you mean:

an IN OUT parameter is treated like this:

a) copy input value into temporary
b) pass temporary into procedure
c) if the execution of the procedure was successful, then copy the value of the temporary back into the IN OUT parameter.

then yes.


The NOCOPY will not COPY the results AGAIN when the control is passed from
Called subroutine to calling subroutine?


this is true, but NOT RELEVANT, since the value will already be there. We do not use a temporary, we just pass the pointer to the IN OUT variable - any change to the parameter in the procedure (regardless of success or not) is immediately reflected in the variable, there is no need to copy a thing, it is ALREADY THERE



How should I go about this?


go about what???? you stated some facts, you didn't ask how to do something?


DO NOT pass the same formal argument as an OUTPUT from a procedure, the results would be very unreliable.

nocopy bug

biswaranjan, February 01, 2012 - 3:49 am UTC

Hi Tom,
I was doing research on the procedure formal parameter constraints and found something which confused me a lot.

I created a procedure.
create or replace procedure raiseerror(p_parameter in out nocopy number) as
begin
p_parameter:=345;
raise program_error;
end raiseerror;

Then I ran the below code.

declare
v_num number(3) :=123;
begin
dbms_output.put_line(v_num);
raiseerror(v_num);
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error occured '||v_num);
end;

and found the output like this

123
error occured 123

I was expecting the output should be
123
error occured 345.

but when I ran like this
declare
v_num number :=123;
begin
dbms_output.put_line(v_num);
raiseerror(v_num);
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error occured '||v_num);
end;

it gives me the correct output.
I also tried by giving max precision(38) as below but not got the actual output.

declare
v_num number(38) :=123;
begin
dbms_output.put_line(v_num);
raiseerror(v_num);
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error occured '||v_num);
end;

**************

Please tell me whether it is a bug or I have understood wrongle.
for varchar or varchar2 data taye it works both of the case (I mean nocopy works fine),but for the number data type it behaved strangely.
Please explain the mismatch.

thanks and long live tom.
biswaranjan.

Tom Kyte
February 01, 2012 - 7:35 am UTC

I was expecting the output should be
123
error occured 345.


why? it is explicitly stated that NOCOPY is a hint, one that we may decide to use or not - it is entirely up to us, it does not force any behavior, it just allows us to do it if we so choose.

If you do use it, you have to be aware of the possible side effects, but the side effects are POSSIBLE not assured.


it gives me the correct output.


it gives you correct output in all cases.


*we* decide when to do it.


when you start using constrained types with unconstrained types - you sort of lose the ability to point at things. If you wanted the number(3) to be possibly (remember POSSIBLY - not guaranteed) passed by reference with nocopy, you'd have to use the same types so we can use a pointer:




ops$tkyte%ORA11GR2> create or replace package my_types
  2  as
  3          subtype small_number is number(3);
  4  end;
  5  /

Package created.

ops$tkyte%ORA11GR2> create or replace procedure raiseerror(p_parameter in out nocopy my_types.small_number ) as
  2  begin
  3  p_parameter:=345;
  4  raise program_error;
  5  end raiseerror;
  6  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2  v_num number(3) :=123;
  3  begin
  4  dbms_output.put_line(v_num);
  5  raiseerror(v_num);
  6  dbms_output.put_line(v_num);
  7  exception
  8  when others then
  9  dbms_output.put_line('error occured '||v_num);
 10  end;
 11  /
123
error occured 345

PL/SQL procedure successfully completed.




but please remember - nocopy is just a hint and can be ignored at will. The side effect MAY or MAY NOT appear.

The side effect might appear in version X.Y, the side effect might not appear in X.Z. If you rely on the side effect happening - it is your code that has the bug in it - you cannot rely on side effects.

continue to nocopy bug.

biswaranjan, February 01, 2012 - 4:20 am UTC

Hi Tom,
I got the login by doing more research on it.
The trick I found is "tHE ACTUAL PARAMETER IS CONSTRAINED by a
precision,scale or not null constraint then nocopy will not work but any how the restriction doed not apply to a character parameter constrained by a maximun length.

thanks,
Biswaranjan.


Tom Kyte
February 01, 2012 - 7:36 am UTC

that is not true either, as demonstrated above.

You just have to use the same type.

continue to nocopy bug

A reader, February 02, 2012 - 12:04 am UTC

Hi Tom,

I am glad that I got reply from you with nice explanation.
I will ask later something about nocopy , i will try to solve it myself ,if not then will definitely ask you.

great long live.
regards,
Biswaranjan.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library