Function Calling In View

From: Azhar <azhar_at_mathtech-pk.com>
Date: Tue, 5 Dec 2000 19:22:35 +0500
Message-Id: <10701.123652@fatcity.com>

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 Received on Tue Dec 05 2000 - 08:22:35 CST