RE: Function Calling In View

From: Dara Vaughn <dara.vaughn_at_wcom.com>
Date: Tue, 05 Dec 2000 10:43:44 -0600
Message-Id: <10701.123680@fatcity.com>

Azhar,

In the function definition, you have to use a compiler directive. Look up the following in the Oracle7 documentation for more information:

pragma restrict_references(your_function_name,WNDS);

This tells the PL/SQL compiler (at compile time) that you are guaranteeing that this function will not update the database. The WNDS stands for Writes No Database State; i.e. Does not update the database.

This compiler directive is no longer required in Oracle 8i.

Dara Vaughn
Oracle DBA
Quality Software Engineering

-----Original Message-----

From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Azhar Sent: Tuesday, December 05, 2000 8:22 AM To: Multiple recipients of list ORACLE-L Subject: Function Calling In View

hi all,

I have sql script which connect two databases and create views on local database which select data from remote database. All data is on the remote databse. Beside selecting data , some user-defined functions (which also make decisions on the basis of remote database tables) are also being called in select statements. DBMS_SQL package is used to create views on the local side.

"The script works fine on oracle 8i , but it gives following error on the oracle 7.3 "

SETDISTANCEUOM('newlink'), NULL, fh.record_changed *
ERROR at line 23:
ORA-06571: Function SETDISTANCEUOM does not guarantee not to update database

Since function call fails , view is not created on the local database.

Any ideas ,Suggestions Please Help.
Azhar

---

--Sample code is given below.View and functions are created as
CREATE OR REPLACE FUNCTION SETDISTANCEUOM (pdblink varchar2 default NULL) RETURN VARCHAR2
IS
 vdistance VARCHAR2(100);
 vcurs number;
 vsqltxt varchar2(300);
 vstatus number;
BEGIN
 vsqltxt:='SELECT default_value FROM project_defaults@' ||pdblink||' WHERE default_name = ''HORIZONTOL_UOM''';
 vcurs := dbms_sql.open_cursor;
 dbms_sql.parse(vcurs,vsqltxt,dbms_sql.v7);  dbms_sql.define_column(vcurs,1,vdistance,100);  vstatus := dbms_sql.execute(vcurs);
 vstatus:=dbms_sql.fetch_rows(vcurs);
 dbms_sql.column_value(vcurs,1,vdistance);  dbms_sql.close_cursor(vcurs);
  RETURN vdistance;
EXCEPTION WHEN OTHERS THEN
 RETURN '';
END; Create or Replace View
oe_ldv_vc_company(company_name,parent_company_name,company_abbrev, company_code,remark,create_date,update_date) AS
(Select bs.SETDISTANCEUOM('&&linkname'),null,substr(bs.assoc_id,1,7), bs.assoc_id,bs.source,null,null
from business_assoc@&&linkname bs)

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Azhar
  INET: azhar_at_mathtech-pk.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Tue Dec 05 2000 - 10:43:44 CST