Home » SQL & PL/SQL » SQL & PL/SQL » Prob : Whats the difference? (Oracle 9i)
Prob : Whats the difference? [message #329134] Tue, 24 June 2008 04:13 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Whats the difference for the below two cases if I use OUT (Case I) and IN OUT mode for (Case II) ?

Case I:
create or replace FUNCTION test(
    location_Id IN location.location_id%type DEFAULT NULL,
    RC1  OUT sys_refcursor)
    return integer
    is
    begin
    open rc1 for select LONG_NME from LOCATION ;
    return 1;
   end;
/


Case II:
create or replace FUNCTION test(
    location_Id IN location.location_id%type DEFAULT NULL,
    RC1 IN OUT sys_refcursor)
    return integer
    is
    begin
    open rc1 for select LONG_NME from LOCATION ;
    return 1;
   end;
/




Thanks in advance.

Regards,
Oli

[Updated on: Tue, 24 June 2008 04:15]

Report message to a moderator

Re: Prob : Whats the difference? [message #329136 is a reply to message #329134] Tue, 24 June 2008 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database PL/SQL User's Guide and Reference
Chapter 8 Using PL/SQL Subprograms
Section Passing Parameters to PL/SQL Subprograms
Subsection Specifying Subprogram Parameter Modes

Regards
Michel

[Edit: fix link]

[Updated on: Tue, 24 June 2008 04:32]

Report message to a moderator

Re: Prob : Whats the difference? [message #329138 is a reply to message #329136] Tue, 24 June 2008 04:23 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Thanks Michel..
Not being able to access the link mentioned above.What would happen if I use OUT parameter instead of IN OUT parameter in the above case? Would it make a difference? Thats what I want to know.

Regards,
oli

[Updated on: Tue, 24 June 2008 04:24]

Report message to a moderator

Re: Prob : Whats the difference? [message #329142 is a reply to message #329138] Tue, 24 June 2008 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Link is now fixed but you could find the section I mentioned with the information I gave.

Regards
Michel
Re: Prob : Whats the difference? [message #329144 is a reply to message #329138] Tue, 24 June 2008 04:35 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, when you tried it, what happened?

There is no logical reason why you would us an IN OUT, as you make no use of the ref cursor that you pass in.

A better way to write the code would be to either not use an OUT parameter, or to use a procedure rather than a function - functions with out parameters are pointless.
Previous Topic: procdure and function diff for this scenario
Next Topic: Joining of Table A & B
Goto Forum:
  


Current Time: Sat Dec 03 08:04:03 CST 2016

Total time taken to generate the page: 0.07787 seconds