| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: OK... maybe I'm being stupid... HELP
You will need to add a
pragma restrict_references( HOWMANYCUSTOMERS , WNDS,WNPS );
in your package spec the WNDS tells oracle that the function will not
write to the database. The WNPS tells oracle that the function will not
change values in your procedure. There are other options you may add.
WNDS is the only required option. There are several other postings
about this around Deja if you want more details.
In article <378A1818.87ADE733_at_bayer.co.uk>,
Chris Edge <chris.edge.ce1_at_bayer.co.uk> wrote:
>
> --------------780685DAA8CB9B58C37D5A16
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Hi guys,
>
> I've been struggling with using cursors inside functions with the
> DBMS_SQL command. So have looked thru the newsgroups and found the
topic
> under discussion here.
>
> I've tried your sql (with a slight correction to make it compile) and
> still get the same error in SQL*PLUS which is...
>
> ERROR at line 1:
> ORA-06571: Function HOWMANYCUSTOMERS does not guarantee not to update
> database
>
> > (BankName IN VARCHAR2, SURNAME IN VARCHAR2) RETURN NUMBER IS
> > v_query varchar2(50);
> > c_cursor integer;
> > v_count integer;
> > chris1 integer;
> > BEGIN
> > v_query := 'select count(*) from ' || BankName ||
> > 'where CustomerSurname = :surname';
> > c_cursor := dbms_sql.open_cursor;
> > dbms_sql.parse(c_cursor, v_query, dbms_sql.v7);
> > dbms_sql.bind_variable(c_cursor, ':surname',Surname);
> > dbms_sql.define_column(c_cursor,1,v_count);
> > chris1 := dbms_sql.execute(c_cursor);
> > chris1 := dbms_sql.fetch_rows(c_cursor);
> > dbms_sql.column_value(c_cursor,1,v_count);
> > return v_count;
> > END;
> >
>
> Nick Bull wrote:
>
> > Cool,
> >
> > Cheers guys.
> >
> > I'll give them a try.
> >
> > Thanks,
> >
> > nick.
>
> --------------780685DAA8CB9B58C37D5A16
> Content-Type: text/html; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
> <html>
> Hi guys,
> <p>I've been struggling with using cursors inside functions with the
DBMS_SQL
> command. So have looked thru the newsgroups and found the topic under
discussion
> here.
> <p>I've tried your sql (with a slight correction to make it compile)
and
> still get the same error in SQL*PLUS which is...
> <p>ERROR at line 1:
> <br>ORA-06571: Function HOWMANYCUSTOMERS does not guarantee not to
update
> database
> <br>
> <blockquote TYPE=CITE>
> <pre>(BankName IN VARCHAR2, SURNAME IN VARCHAR2) RETURN NUMBER IS
>
v_query varchar2(50);
>
c_cursor integer;
>
v_count integer;
> chris1 integer;
> BEGIN
> v_query := 'select count
(*) from ' || BankName ||
>
'where CustomerSurname = :surname';
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Tue Jul 13 1999 - 21:30:11 CDT
 |
 |