Message-Id: <10703.123908@fatcity.com> From: "Azhar" Date: Thu, 7 Dec 2000 10:50:45 +0500 Subject: Re: Function Calling In View I have removed dbms_sql calls from the functions and it worked. However if i use pragma with dbms_Sql then it doesn't compile even. may be any method inside dbms_sql modifies databse state. Thank to every one who responded Azhar ----- Original Message ----- From: Dara Vaughn To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 05, 2000 9:46 PM 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----- > Sent: Tuesday, December 05, 2000 8:22 AM > To: Multiple recipients of list ORACLE-L > > > 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 > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Dara Vaughn > INET: dara.vaughn@wcom.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