Yes this is this is useful but it is not all of the functionality that we need.
Bradley Shockley, March 20, 2001 - 6:25 pm UTC
The functionality that we need is the following:
where_string_in := ' AND U.USER_PASSWORD = ''fishy''';
DBMS_SQL.PARSE(cursor_number_loc,
'OPEN cursor_in_out FOR SELECT
G.GROUP_NAME,
FROM
SW_GROUP G,
SW_USER_GROUP UG,
SW_USER U
WHERE
UG.SW_GROUP_GUID = G.SW_GROUP_GUID
AND
UG.SW_USER_GUID = U.SW_USER_GUID
:where_string_in
ORDER BY
GROUP_NAME',
DBMS_SQL.NATIVE);
Only we need this for use with a REF CURSOR. We are using Cold Fusion and need to be able to have a dynamic where clause added to a select that we are creating in a Stored Procedure. Then passing back to the caller as a REF CURSOR.
Any help that could be given would be great.
Thank you
My related question
John, March 10, 2003 - 10:20 am UTC
Tom: Can you return a REF CURSOR with the where clause built dynamically?
Something like this (use scott.emp as an example), but with one query and dyanamically-built where clause, or perhaps DECODE/CASE in select list:
if salary < 3000
select ename, job from emp where hiredate < to_date('mm/dd/yyyy', '01/01/1982')
if commission > 1000
select ename, mgr from emp where hiredate > to_date('mm/dd/yyyy', '01/01/1982')
if salary >= 3000 and commission <= 1000
select ename, sal, comm from emp where job = 'MANAGER' and sal >= 3000 and comm <= 1000
March 10, 2003 - 11:40 am UTC
Thanks Tom.
John, March 18, 2003 - 5:36 pm UTC
The example is used with dbms_session.set_context. I haven't got a chance to try it out. Bit I would think it should also work without using dbms_session.set_context, isn't it?
March 18, 2003 - 5:58 pm UTC
huh? what example exactly -- if you mean the linked to article above - well the answer to that would be "no, the entire example is predicated on dbms_session.set_context"
Yes, the example in the linked article.
John, March 25, 2003 - 2:19 pm UTC
So the only way to do dynamic where clause is to use dbms_session.set_context?
March 25, 2003 - 8:30 pm UTC
only way? no.
there is cursor_sharing
there are other "where clause tricks"
but this is the one I prefer.
Yes, the example in the linked article.
John, March 25, 2003 - 2:21 pm UTC
So the only way to do dynamic where clause is to use dbms_session.set_context?
Taking this a step further with a dynamic USING clause...
Debbie Hamilton, June 12, 2003 - 3:32 pm UTC
Super info!! I'm needing to take this all one step further, though. I'm having to translate a ton of logic that is currently using the DBMS_SQL into native dynamic sql since you can't transfer the data out of the DBMS_SQL cursor into a REF CURSOR (that I know of... or can you?). When you consider building a dynamic sql statement with a dynamic WHERE clause using bind variables, one would almost certainly need to build the USING clause dynamically as well. Could you elaborate on the syntax for creating a dynamic USING clause? All of this is to be utilized by a REF CURSOR that is being returned by a packaged procedure.
June 12, 2003 - 4:02 pm UTC
Maybe I'm just blind....
Debbie, June 12, 2003 - 5:36 pm UTC
I read the whole page (twice) and didn't see an example of a dynamically generated USING clause? Were you trying to suggest something else?
June 12, 2003 - 8:02 pm UTC
there is no such thing as a dynamically generated USING clause.
that page shows you how to BIND with native dynamic sql when you don't know the number of inputs to the query at compile time.
What is wrong with this
Prathap, December 15, 2004 - 6:25 am UTC
create or replace package x_pkg as
type numtype is record (var1 number);
type numcurtype is ref cursor return numtype;
procedure test_proc (var1 in number, var2 in out numcurtype);
end;
/
show err
create or replace package body x_pkg as
procedure test_proc (var1 in number, var2 in out numcurtype) is
begin
open var2 for select var1*var1 from dual;
end;
end;
/
Package body created.
DECLARE
varcur x_pkg.numcurtype;
var1 number ;
var2 number;
str1 varchar2(100);
BEGIN
var1:= 5;
str1 := 'begin x_pkg.test_proc(:b1,:b2); end;';
execute immediate str1 using in var1 , in out varcur ;
loop
exit when varcur%NOTFOUND;
fetch varcur into var2;
dbms_output.put_line('var2-->'||var2);
end loop;
close varcur;
end;
/
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
I am using 9.2.0.6. Is it a bug? If I don't use execute immediate and call the procedure directly, it is working fine. Some metalink threads suggested to open the REF cursor and close, which will do sort of initialize, but it still doesn't work.
Any help is appreciated.
December 15, 2004 - 1:58 pm UTC
it should fail with a compilation error -- that is the bug here. ref cursors should not supported with Native Dynamic SQL.
</code>
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems18.htm#33926 <code>
However, I did test in 10g -- and it worked, but the doc has the same limitations...
So, not sure if
a) it is a doc bug (well, it definitely is, just not sure which way...)
b) a database bug (well, same, in 9i/10g, it should raise a compilation error if this is not supported. If it is supported, then 9i is doing it wrong)
being a believer that smaller is better:
declare
x sys_refcursor;
dummy dual.dummy%type;
begin
execute immediate 'begin open :x for select * from dual; end;' using IN OUT x;
fetch x into dummy;
dbms_output.put_line( dummy );
close x;
end;
/
is the minimal test case you can use with support when filing this tar to get it bugged one way or the other...
What is wrong with this - TAR raised
Prathap, December 15, 2004 - 8:23 am UTC
TAR raised for the same - 4120523.996.
Oracle suggested to upgrade to 10g
Thx.
December 15, 2004 - 2:03 pm UTC
please point them to bug 2709343
it does say there "backport: yes" meaning -- it is backportable (the patch)
and -- if you don't mind, please have them file a doc bug as well....
What is wrong with this - TAR update
Prathap, December 16, 2004 - 3:30 am UTC
Oracle suggested workaround for 9i:
Declare the refcursor as sys_refcursor type,
open the refcurosr with a dummy select and do not close it.
Then, issue native dynamic sql. I have tested this and is working fine in 9.2.0.6.
Tom, can you foresee any side effects as the refcursor is going to be weak type in this case?
Thx.
December 16, 2004 - 8:17 am UTC
please tell support "but what about that backport: Yes" in the bug.
i only use weak ref cursors, I've never actually used the other kind at all.
Returning ref cursor from dynamic SQL
Matthew Wise, January 25, 2005 - 6:44 am UTC
I stumbled across this page when trying to figure out how to return a ref cursor from dynamic SQL.
"open :x for some_query USING some_bind_variable" works a treat! Thanks!
Matthew Wise
Aquila
Redhill, Surrey, UK
A reader, May 06, 2005 - 9:59 am UTC
Not clear on "open :x for some_query USING some_bind_variable"
Lawrence J. Sylvain, August 16, 2005 - 5:57 pm UTC
Would you mind giving a more complete example? I am trying to write a generic function that will call a stored procedure (name passed in as VARCHAR2 parameter) that returns a ref cursor as an out parameter. One example I saw here was close, but no joy. Here is my example:
FUNCTION CALL_PROC (proc_name IN VARCHAR2)
IS
v_ref_cursor sys_refcursor;
BEGIN
-- This works
-- proc_name(v_ref_cursor);
-- But not this...
execute immediate
'begin ' || proc_name || ' (:1); end;'
using out v_ref_cursor;
-- Then do something with the cursor...
END CALL_PROC;
Just how would I use the "open :x for some_query USING some_bind_variable" example posted on this topic in lieu of what I am doing above? Or, better, yet, how could I make the above work? Thanks?
August 17, 2005 - 12:17 pm UTC
I am trying to write a generic
function that will call a stored procedure
yuck.
but you cannot bind ref cursors, period, in plsql dynamic sql
native dynamic sql binds SQL types only -- period.
dbms_sql has no api to bind a ref cursor.
but -- funny thing, if all this does is this, WHY oh WHY would you make this "generic" routine (other than to make the system harder to understand, slower, less scalable, hard to maintain?)
why would not the person calling this just call the procedure, get the ref cursor and pass it to you?
that way, you avoid evil dynamic sql and suffer none of the problems.
Re: not clear....
Lawrence J. Sylvain, August 16, 2005 - 6:01 pm UTC
Sorry. I over-sanitized my example and removed the return type. Here is what I believe is a compilable example that gets a run-time error when called.
FUNCTION CALL_PROC (proc_name IN VARCHAR2)
RETURN sys_refcursor
IS
v_ref_cursor sys_refcursor;
BEGIN
-- This works
-- proc_name(v_ref_cursor);
-- But not this...
execute immediate
'begin ' || proc_name || ' (:1); end;'
using out v_ref_cursor;
RETURN v_ref_cursor;
END CALL_PROC;
Confued
geok, October 12, 2007 - 9:46 am UTC
This confuses me a little.
Thats a feature added in Oracle8i release 8.1 and up...
In that release you can code:
ops$tkyte@8i> variable x refcursor
ops$tkyte@8i> set autoprint on
ops$tkyte@8i>
ops$tkyte@8i> declare
2 some_query varchar2(255) default
3 'select ename, empno from emp where ename like :x';
4 some_bind_variable varchar2(255) default
5 '%A%';
6 begin
7 open :x for some_query USING some_bind_variable;
8 end;
9 /
1. declare a refcursor variable called x
2. declare a variable to hold the sql to be executed with your refcursor BOUND to the like statement???
3. you open your refcursor or bind variable - I am not exactly sure what you opened there, but it would have to be the refcursor, is that a typo?
to me, your query should come out thusly:
open (refcursor) for 'select ename, empno from emp where ename like (refcursor)';
-OR-
OPEN A% for 'select ename, empno from emp where ename like A%';
either way, it doesn't seem like it would work.
October 14, 2007 - 9:06 pm UTC
hah, I can see how this would be confusing - sorry about the use of :x like that, that was bad form.
change the example to be "where ename like :ENAME"
a matter of scope, the :x's are entirely different.