Home » SQL & PL/SQL » SQL & PL/SQL » Can a nested select be passed as a parameter? (11i)
Can a nested select be passed as a parameter? [message #448338] Mon, 22 March 2010 17:02 Go to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
Hello,

Is it possible to pass a nested select statement as a parameter to a procedure? In other words, I am currently successfully doing this:
SELECT BASE_PAY INTO N FROM EMPLOYEES WHERE ID=p_ID;
SetPackageValueProc(PkgID,'YearlyBasePay',N);

And I'd like to do something along the lines of this:
SetPackageValueProc(PkgID,'YearlyBasePay',(SELECT BASE_PAY FROM EMPLOYEES WHERE ID=p_ID));

Tia,
Cheers
Re: Can a nested select be passed as a parameter? [message #448339 is a reply to message #448338] Mon, 22 March 2010 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Is it possible to pass a nested select statement as a parameter to a procedure?

Why bother us with your question instead of actually testing for yourself?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted code.

What datatype is the 3rd parameter defined to be?
Re: Can a nested select be passed as a parameter? [message #448340 is a reply to message #448339] Mon, 22 March 2010 17:32 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
re: Duck
so sorry, I was assuming intelligence on the part of the reader.
re: use guidelines: 1. Be polite: Doctor heal thyself. What part of the guidelines are you suggesting that I have not followed?

The first code works. Therefore N is compatible. You don't need my tables and data to figure that one out, and I'm not asking you to improve my code.

The second code doesn't work. Of course I tried it. Hence the statement: I would like to do something along the lines of...

If you don't understand the question, there are other possibilities than the OP is a lazy idiot.

This was my first and probably last post here I guess.

Sheesh.


Re: Can a nested select be passed as a parameter? [message #448341 is a reply to message #448340] Mon, 22 March 2010 18:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What you are looking for is either REF CURSORS (that's a poiner to a cursor that can be passed between stored program units, or dynamic SQL, which is executing a string as a SQL statement.

icon7.gif  Re: Can a nested select be passed as a parameter? [message #448345 is a reply to message #448338] Mon, 22 March 2010 18:58 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
@Frank:
Excellent, Thanks! I'll check it out.
-Jeff
Re: Can a nested select be passed as a parameter? [message #448359 is a reply to message #448338] Tue, 23 March 2010 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also put your query in a string as parameter and execute it inside the procedure.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Tue, 23 March 2010 00:43]

Report message to a moderator

Re: Can a nested select be passed as a parameter? [message #448379 is a reply to message #448359] Tue, 23 March 2010 01:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That's what I was hinting at when I mentioned dynamic SQL. Probably should have explained it better Smile
Re: Can a nested select be passed as a parameter? [message #448380 is a reply to message #448379] Tue, 23 March 2010 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, missed that.

Regards
Michel
Re: Can a nested select be passed as a parameter? [message #448423 is a reply to message #448338] Tue, 23 March 2010 05:38 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
if it is a scalar variable then "Yes" it can be done, But you have to consider the datatypes one from the Select statement and one that is accepting the value into the procedure.
Re: Can a nested select be passed as a parameter? [message #448425 is a reply to message #448423] Tue, 23 March 2010 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
sen.suvro wrote on Tue, 23 March 2010 05:38
if it is a scalar variable then "Yes" it can be done, But you have to consider the datatypes one from the Select statement and one that is accepting the value into the procedure.


What can be done exactly?
Re: Can a nested select be passed as a parameter? [message #448427 is a reply to message #448425] Tue, 23 March 2010 06:00 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member

If the output of the select statement is only a single value then than can be passed into the procedure provided the variable accepting the value is a scalar variable.
Re: Can a nested select be passed as a parameter? [message #448428 is a reply to message #448338] Tue, 23 March 2010 06:04 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You mean you can pass a variable containing the result of a query to a procedure?
I'm fairly sure the OP knows that and it's not what he's asking.
Re: Can a nested select be passed as a parameter? [message #448430 is a reply to message #448428] Tue, 23 March 2010 06:15 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Quote:
Hello,

Is it possible to pass a nested select statement as a parameter to a procedure?


well, i thought that was what the OP was asking...
Re: Can a nested select be passed as a parameter? [message #448432 is a reply to message #448430] Tue, 23 March 2010 06:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Actually, it was what the OP showed he already did. Instead of passing the result of the query, he wanted to send the query itself.
Re: Can a nested select be passed as a parameter? [message #448464 is a reply to message #448338] Tue, 23 March 2010 10:30 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
@All:
Thanks for all the discussion.
re Dynamic sql: In other words pass the select statement as a string to the procedure which would in turn execute it. That would definitely work, but in my particular case I think it is less elegant, bad for performance, you lose compile time error checking and potentially open myself up to sql injection. But there could be design scenarios where this might be a viable option.

If I understand Frank's suggestion, I could also: Perform the first query and put the result in the refcursor, then refer to that refcursor in the proc. That too would work but doesn't seem any more elegant than doing it the way that is already working, since I am dealing with a scalar value. It might be a cool way to deal with passing result sets around though.

Anyway, thanks again to all for the ideas Smile

Cheers
Jeff
Re: Can a nested select be passed as a parameter? [message #448467 is a reply to message #448464] Tue, 23 March 2010 10:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the set of results is relatively small (a few thousand rows) then you could execute the original query, store the results in a collection, and pass the collection around.

As long as the collection is defined in SQL, you can then use the collection as a table in other queries:
Script:
create or replace type ty_154_obj as object(col_1  number);
/

create or replace type ty_154 as table of ty_154_obj;
/

create table t_154 (col_1   number);

create table t_155 (col_1 number, col_2 varchar2(10));

insert into t_154 (select level from dual connect by level <=20);

insert into t_155 (select level,'Row '||level from dual connect by level <=20);

declare
  t_numbers   ty_154 := ty_154();
begin

  select ty_154_obj(col_1)
  bulk collect into t_numbers
  from   t_154
  where  mod(col_1,2)=0;
  
  for rec in (select t.col_2
              from   t_155 t
                    ,table(t_numbers) n
              where  t.col_1 = n.col_1) loop
    dbms_output.put_line(rec.col_2);
  end loop;
end;
/


Output:
SQL> declare
  2    t_numbers   ty_154 := ty_154();
  3  begin
  4  
  5    select ty_154_obj(col_1)
  6    bulk collect into t_numbers
  7    from   t_154
  8    where  mod(col_1,2)=0;
  9    
 10    for rec in (select t.col_2
 11                from   t_155 t
 12                      ,table(t_numbers) n
 13                where  t.col_1 = n.col_1) loop
 14      dbms_output.put_line(rec.col_2);
 15    end loop;
 16  end;
 17  /
Row 2
Row 4
Row 6
Row 8
Row 10
Row 12
Row 14
Row 16
Row 18
Row 20

PL/SQL procedure successfully completed.
Re: Can a nested select be passed as a parameter? [message #448470 is a reply to message #448464] Tue, 23 March 2010 10:57 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
May be I misunderstand Your question, but You can pass NT from/to procedures;
CREATE OR REPLACE TYPE 
 numlist IS TABLE OF NUMBER;
/

CREATE OR REPLACE PROCEDURE listman (liste IN numlist)
AS
BEGIN
  FOR i IN liste.first .. liste.last LOOP
    dbms_output.put_line('i='||liste(i));
  END LOOP;
END;
/

SHOW ERRORS

SET SERVEROUTPUT ON SIZE 1000000;

EXEC listman(numlist(1,2,3,4));
/

i=1
i=2
i=3
i=4

DECLARE
  l1 numlist;
BEGIN
  SELECT LEVEL lv BULK COLLECT INTO l1 FROM dual CONNECT BY LEVEL<=4;
  listman(l1);
END;

i=1
i=2
i=3
i=4
Re: Can a nested select be passed as a parameter? [message #448471 is a reply to message #448467] Tue, 23 March 2010 11:07 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
@Jum, JR: Shocked Wow Thats some nifty stuff. Beyond the scope of the Original question, but it illustrates that PL/SQL can do some cool things that my tSQL experience hasn't prepared me for.

Thanks Again!
Cheers,
Jeff
Re: Can a nested select be passed as a parameter? [message #448474 is a reply to message #448464] Tue, 23 March 2010 11:30 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
jagilbert wrote on Tue, 23 March 2010 10:30

If I understand Frank's suggestion, I could also: Perform the first query and put the result in the refcursor, then refer to that refcursor in the proc.


I think you misunderstand ref cursors. They're not result sets, they're cursors (well pointers to cursors). When you open a ref cursor, oracle doesn't query anything, it only does that when you fetch the results. What you pass around is a pointer to the query.
icon7.gif  Re: Can a nested select be passed as a parameter? [message #448476 is a reply to message #448338] Tue, 23 March 2010 11:55 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
@Cookie: Yes, I was misunderstanding. ...So passing around a ref cursor actually could be quite efficient because I'm just passing a pointer and not a big wad of data. And the expense of performing the query is only incurred when the consumer of the ref cursor actually performs the fetch.

Thanks for the clarification.

Cheers,
Jeff
Re: Can a nested select be passed as a parameter? [message #448482 is a reply to message #448476] Tue, 23 March 2010 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is correct (if we except the parsing phase).
For instance:
SQL> var c refcursor
SQL> set timing on
SQL> begin open :c for select count(*) from dba_objects, dba_objects; end;
  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14

SQL> print c
  COUNT(*)
----------
2367017104

1 row selected.

Elapsed: 00:02:54.81

As you can see opening is immediate when fetching the count takes time.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Tue, 23 March 2010 12:05]

Report message to a moderator

Re: Can a nested select be passed as a parameter? [message #448485 is a reply to message #448338] Tue, 23 March 2010 12:09 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
That said ref cursors are primarily used for passing complicated queries back to client programs - web front ends for example.

You don't normally use them to pass stuff between procedures - you can do of course but usually there are better ways, depends what you're trying to do.
Previous Topic: How to do a conditional query
Next Topic: sysdate performance
Goto Forum:
  


Current Time: Fri Dec 02 16:31:46 CST 2016

Total time taken to generate the page: 0.28432 seconds