Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: execute immediate and cursor- example
OPEN curs FOR 'SELECT '||pname||' FROM '||psome_table
should do the trick. Since OPEN is not an SQL statement, you can't
execute it with EI the way you did. One way to do that (not
recommended, but seemingly valid) is like this:
execute immediate
'begin open :curs for select '||pname||' from '||psome_table||'; end;'
using in out curs;
but still OPEN cursor_var FOR 'dynamic statement' is clearer and should perform better.
Corrections and additions welcome.
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "mariusz" <on_at_wp.pl> wrote in message news:ahjbfk$7o8$1_at_news.tpi.pl...Received on Tue Jul 23 2002 - 14:14:07 CDT
> create or replace function funkcja (pName in varchar2,psome_table in
> varchar2) return integer is
> Result integer;
> TYPE EmpName IS RECORD (name VARCHAR2(100));
> TYPE cur_type IS REF CURSOR RETURN EmpName;
> curs cur_type;
> begin
> execute immediate 'OPEN curs for SELECT '||pname||' FROM'||psome_table;--
> this is Error!!!
> return(0);
> end funkcja;
> -- I want to write sql statement like this 'select sth,'||psth||'from
> '||ptable_name but in cursor!!!
> How can I do that?
>
>
>