This content has been marked as final. Show 11 replies
Do oyu have read/write privileges on the directory object granted directly to you or through a role? To use it in pl/sql you wuill need the privileges granted directly to the owner of the type.
The user that both (1) owns the object type and (2) invokes the procedure has read privileges on the directory object. It is the same user that successfully accesses the external table from sqlplus.
Can you give a try to reverse check the directory on the system .Because i find no issue with the approach but suspect due to the nature of the error you are getting.
Check the priveleges to the user and network access to the path and r/w access from the server.
In sqlplus do:
If you get the same error, then you have privileges through a role and not directy to the user.
set role none; select xo.household_id, lpad(xo.account01,14,'0') account01 from x_table xo;
Thanks, John. This does seem to be the case. But the statement,
grant read on directory XXX_bad_dir to USER
Maybe I need to read up on roles and users?
I believe you should give read,write both on your dir as
grant read,write on directory XXX_bad_dir to USER
This grant should work fine
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
Can you state for purpose you have roles here?
and why they are set to none ?
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.