Re: Challenge? dynamic query in store procedure

I find a work around of my problem,

in sql plus, I have done

create or replace package types
as

        type cursortype is ref cursor;
end;

then

create or replace procedure test_it (fieldname1 in varchar2, fieldname2 in varchar2, tablename in varchar2, p_cursor in out types.cursortype) as
begin

        open p_cursor for 'select ' || fieldname || ',' || filedname2 || ' from ' || tablename;
end;

finally I did following

variable c refcursor;
MyTest('user_name','emails', 'infotable',:c); print c;

This works, however it has some problems:

1. I do not understand why sqlplus complains when I tried to do

p_cursor in out ref cursor

in the procedure declaration. I have to create a type packages to work around this: create a type called cursortype, and use types.cursortype in the procedure.

2. I have to use print(c) to print out the data. I want more control of out put, such as

  DBMS_output.Put_line('This is filed one' || c.filed1 || ' This is filed 2' || c.filed2);

I serached the archive and did not find any answears to this kind of questions.

Thanks,

Alan

On Sun, 08 Dec 2002 19:10:15 GMT, non (Alan) wrote:

>In a store procedure, I try to pass in fileld and table name to
>construct a dynamic query and return the cursor, here is the code:
>
>create or replace procedure TestIt(fieldname in varchar2, tablename in
>varchar2, p_cursor in out types.cursorType )
>
>is
>
>begin
>
> open p_cursor for select fieldname from tablename;
>
>end;
>
>
>But it encounters compilation error and I believe the reason is that
>there only be static query in the store procedure.
>
>Maybe I can get my way by using DBMS_SQL package, such as
>DBMS_SQL.Parse();
>DBMS_SQL.Define_Column();
>DBMS_SQL.EXECUTE();
>DBMS_SQL.Fetch_Rows();
>
>However I do not know how to return the cursor back to the caller.
>
>Thanks,
>
>Alan
>
Received on Sun Dec 08 2002 - 20:11:10 CST