This content has been marked as final. Show 11 replies
That's the way these directories are set up originally (which led to my original statement that I believed the USER had permissions set correctly).
As I wasn't the one who set these up originally (and the one who did was something of a cowboy), I went ahead and executed the grants again. Then following John's suggestion, I tried the following:
SQL> set role none;
SQL> select count(1) from x_acct_hhld;
select count(1) from x_acct_hhld
ERROR at line 1:
ORA-06564: object CBLAND_DAT_DIR does not exist
I'm not using roles. The reason they are set to none is based on John Spencer's suggestion to determine whether the grants were to roles or users farther up in the thread.
Based on John's question, I'm hoping that if I can get this simple statement to work, my problem with the type body will be solved.
Ok, it is clear that you have privileges through a role and not directly to your user. As the owner of the type, try:
If that returns no rows, then you, or a privileged user, will need to do:
SELECT * FROM user_tab_privs_recd WHERE table_name = 'XXX_DAT_DIR'
Then you should be good to go unless there is something really odd about types that I am not aware of.
GRANT READ, WRITE ON DIRECTORY xxx_date_dir TO <type_owner>
Here is my dilemma/confusion.
assume that the user is XYZZY. We run a pretty simple shop here - no roles, just different schemas.
When I log in to sqlplus to execute the simple external table access that works, I log in as XYZZY.
When I log in to compile my type code, I log in as XYZZY.
When I log in to execute the stored procedure that uses the Object type, I log in as XYZZY.
As mentioned above, I recently logged in and re-executed all the grants to the XYZZY user.
when I ran the little test that included the set role to none (after re-executing all the grants), I logged in as the XYZZY user, and even after this user had the grants, the test failed.