Re: Dynamic SQL

>> I have a problem with function call from dynamic sql statement. When I
>> try to parse a statement that looks like this:
>>
>> DBMS_SQL.PARSE('insert into tab1 (col1,col2) select funct1(par1,par2),
>> funct2(par1) where ....');
>>
>> a following message is reported:
>> Function does not guarantee not to update database.
>>
>> We use Oracle 8.0.5.
>>
>> Thanks,
>> Filip
>
>You need to make it a procedure. Functions cannot do inserts, updates, and
>deletes.

Put the function into a package and use the PRAGMA RESTRICT_REFERENCES to specify what the function does.

A function that is used in a SELECT statement is not allowed to *potentially* change the database; DBMS_SQL *could* allow you to do this, so a function calling DBMS_SQL is never allowed in a function in a select statement. You can write a procedure that uses DBMS_SQL and use the SQLPLUS execute <procedure> to run it. If you really want a function, you need to write a "wrapper" procedure that uses dbms_output to show the result.

Dan Hekimian-Williams Received on Sat Jun 10 2000 - 00:00:00 CEST