First off thank you guys for all your help thus far, it is greatly
appreciated. They did switch it to IN OUT this morning for me and I had
the same results.
I checked the permissions using the queries Janet supplied:
For roles my user has CONNECT and CTXAPP.
For table privileges I ran
select * from user_tab_privs
where privilege = 'EXECUTE'
and the none of the results had the package or procedure name in the
TABLE_NAME field (which is where I assumed i'd see it). None of the
TABLE_NAME's where the table I know the procedure runs against either.
Scott
-----Original Message-----
From: Janet Schmitt [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 14, 2004 1:14 PM
To: CF-Talk
Subject: RE: Oracle Stored Procedures
Scott -
I think you need the IN OUT that Adam mentioned.
When you connect via SQL*Plus and successfully run the procedure, are
you
using the same username and password that is defined in the CF data
source? If so, then I do not think it is a permissions problem but I
could
be wrong. If not, it might be a permissions problem. You could test
this
by changing your CF code to specify the username and password in the
stored
procedure call that you use when you successfully run the procedure in
SQL*Plus. I think this will override what is stored in the CF data
source
definition (but I am not sure).
The only permission you need is EXECUTE on the package in Oracle. If
you
can connect to Oracle via SQL*Plus as the user running the stored
procedure, you can execute 'select * from user_tab_privs' to see what
privileges that user is granted. If the user is granted execute
directly
on the package, you will see an entry in that table. If the user is
granted execute via role, it is harder to figure out because you need to
look at the roles granted to the user (select * from user_role_privs)
and
then what object privileges are granted to the roles (not a single SQL
statement).
HTH.
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
