| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Functions using DBMS_SQL
A copy of this was sent to clint.scott_at_bosquenetwerks.com
(if that email address didn't require changing)
On Tue, 02 Jun 1998 21:36:32 GMT, you wrote:
>I need some help.
>I have a function within a package that uses dbms_sql. I set PRAGMA
>RESTRICT_REFERENCES (find_ids, WNDS) in the package declaration like so:
>
>create or replace package doit AS
> function find_ids(where_clause in varchar2) return varchar2;
> PRAGMA RESTRICT_REFERENCES (find_ids, WNDS);
>end doit;
>
>When I compile the package body, I get an error stating that I've violated my
>pragma. How can I use DBMS_SQL inside a function to return data? Please
>help.
>
You cannot. DBMS_SQL (which can execute create, alter, insert, update, delete, etc) does not gaurantee to not update the database. One of the reqirements of a procedure/function in pl/sql that is to be called from sql is that is will not write to the database -- dbms_sql cannot make this promise..
>
>The package body is:
>
>function find_ids( where_clause in varchar2) return varchar2 is
>begin
>......
> l_theCursor integer;
> s_idList varchar2(30000);
> l_status integer;
> l_counter integer;
> BEGIN
>
> l_counter := 0;
> l_theCursor := dbms_sql.open_cursor;
>
> dbms_sql.parse( l_theCursor,
> 'SELECT to_char(subscriber_id) s_load_id
> FROM subscribers
> WHERE '||where_clause,
> dbms_sql.native );
>
>TIA,
>
>Please reply via email as I don't get to read this group very often:
>clint.scott_at_bosquenetwerks.com
>
>
>blah, blah, blah about fetching the rows.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jun 03 1998 - 11:46:34 CDT
 |
 |