Re: Problem calling user defined function from Forms 4.0 Trigger

It's been my experience that when calling stored functions from forms, you need to have a synonym for the fuction if it's owned by another schema other than the one you are developing with.

Also, I assume you've done a "grant execute" to the account with which you are compiling the form?

Steve Coleman

In article <3jipkq$mhm_at_crocus.csv.warwick.ac.uk>, esuvl_at_csv.warwick.ac.uk (Richard James Tinker) writes:
>> Problem calling function from within Forms 4.0 Trigger
>> =====================================================
>>
>> I am trying to call a user defined SQL function from within a trigger in
>> Forms Version 4.0. The function is called preferred_name, and returns a
>> VARCHAR2 string (a name) given various input parameters. Testing the
>> function from the sqlplus command line yields
>>
>>
>> SQL> select csv_user_oracle.preferred_name('','','','Mr','Richard','James',
>> 'Rich','Tinker','N','N','N','N') from dual;
>> Press Return to continue...
>> CSV_USER_ORACLE.PREFERRED_NAME('','','','MR','RICHARD','JAMES','RICH','TINKER','
>> --------------------------------------------------------------------------------
>> Rich Tinker
>>
>> I am simply trying to assign this value to a variable. My trigger is as
>> follows;
>>
>> DECLARE
>> result VARCHAR2; /* Stores result of function call */
>> BEGIN
>> select csv_user_oracle.preferred_name('','','','Mr','Richard','James',
>> 'Rich','Tinker','N','N','N','N') into result from dual;
>>
>> :MEMBERSHIP.DISPLAY_1 := result;
>>
>> END;
>>
>> The error obtained is
>>
>> Error 356 at line 4, column 113
>> 'DUAL' must name a table to which the user has access.
>>
>> I have checked that there are no return characters in the returned text. I am
>> confused by the message. I have tried specifying sys.dual but to no avail.
>>
>> Can anyone help ?
>>
>> Thanks in advance
>>
>>
>> Richard
>> --
>> R.J.Tinker_at_csv.warwick.ac.uk
Received on Tue Mar 14 1995 - 21:28:08 CET