Re: execute immediate and cursor- example

From: Tom Best <oracle_person_at_yahoo.com>
Date: Tue, 23 Jul 2002 09:03:24 -0400
Message-ID: <ahjgg8$9eh$1@news.bentley.com>

Mariusz:

See this below. It works. You cannot submit the string 'open cursor' as part of a dynamix SQL statement.... it is, rather, a PL/SQL statement.

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;
TableRowsCursor INTEGER := DBMS_SQL.OPEN_CURSOR; TableRowsValue CHAR(100) := NULL;
begin

    DBMS_SQL.PARSE(TableRowsCursor, 'SELECT '||pname||' FROM'||psome_table, DBMS_SQL.NATIVE);
    DBMS_SQL.DEFINE_COLUMN_CHAR(TableRowsCursor, 1, TableRowsValue,100);     TableRowsValue := DBMS_SQL.EXECUTE_AND_FETCH(TableRowsCursor);

    DBMS_SQL.COLUMN_VALUE(TableRowsCursor, 1, TableRowsValue);
    DBMS_SQL.CLOSE_CURSOR(TableRowsCursor);
    DBMS_OUTPUT.PUT_LINE(TableRowsValue);

 return(0);
end funkcja;

"mariusz" <on_at_wp.pl> wrote in message news:ahjbfk$7o8$1_at_news.tpi.pl...
> 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?
>
>
>
Received on Tue Jul 23 2002 - 08:03:24 CDT