Re: Stored Procedure from Form

From: Grant Donohoe <grant_at_abbi.demon.co.uk>
Date: 1995/07/02
Message-ID: <804708362snz_at_abbi.demon.co.uk>#1/1


In article <3sv4m7$hd7_at_xenon.brooks.af.mil>

           sdowns_at_guardian.brooks.af.mil "Steve Downs" writes:

> I seem to be having some kind of privilege problem calling a stored
> procedure within a package from a form (v3). The form generates fine
> when I connect as app_owner, but not from my development account. I
> am using a public synonym to reference the package in the form, and
> EXECUTE on the package has been granted to PUBLIC.
>
> Simplified:
>
> CREATE PACKAGE app_owner.mypackage AS
> PROCEDURE myprocedure (arg1 app_owner.table1.column1%TYPE)
> PROCEDURE simpleprocedure ;
> END mypackage;
>
> CREATE PACKAGE BODY app_owner.mypackage AS
> PROCEDURE myprocedure (arg1 app_owner.table1.column1%TYPE) AS
>
> BEGIN
> NULL;
> END myprocedure ;
>
> PROCEDURE simpleprocedure AS
> BEGIN
> NULL ;
> END simpleprocedure ;
>
> CREATE PUBLIC SYNONYM mypackage_syn FOR app_owner.mypackage ;
>
> GRANT EXECUTE ON app_owner.mypackage TO PUBLIC ;
>
> Then, in the PRE-INSERT trigger of a form, I try to call the procedure
> using the following syntax:
>
> MYPACKAGE_SYN.SIMPLEPROCEDURE ;
>
> When I try to compile this trigger, I will get these errors:
>
> Qualifier 'table1' must be declared
>
> and
>
> Qualifier 'mypackage_syn' must be declared
>
>
> I am scouring over the Application Developer's Guide, but haven't
> been able to find anything yet. Am I missing something simple?
> Can anyone suggest what I might be doing wrong? Thanks...Steve
>
>

Hi,
I may be misreading what you've written but Ironically aren't you complicating this a bit.

Why create your package as app_owner.my_package.

I would expect to do the following

create a package called say .... package1 (without an owner prefix) with procedures called say... procedure1

                              procedure2 etc'

then grant execute to public and a synonym package1 for owner.package1 (are you using the same name for the synonym, that might me a foible)

then call it using

begin

    package1.procedure1
end;

Or is that what you did , if so did you grant access to all the necessary tables to the user executing the procedure. Have you tried executing the procedure outside the form ?  

Grant Donohoe
Oracle Contractor.
Reigate
Surrey
U.K
grant_at_abbi.demon.co.uk Received on Sun Jul 02 1995 - 00:00:00 CEST

Original text of this message