Message-Id: <10701.123680@fatcity.com> From: Dara Vaughn Date: Tue, 05 Dec 2000 10:43:44 -0600 Subject: RE: Function Calling In View 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@fatcity.com [mailto:root@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@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@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