Re: Problem calling user-defined SQL function from Forms 4.0 trigger

I had the same error message the other day compiling a trigger select from dual. It went away after I connected to the database ;-)

In article <1995Mar7.232727.17785_at_dcs.warwick.ac.uk>, R.J.Tinker_at_dcs.warwick.ac.uk (Richard) 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 Wed Mar 08 1995 - 23:58:39 CET