Menu
I assume this information is available in Oracle metadata tables, but where exactly ?
I need the directory object for BFILENAME as in this answer: Using PL/SQL how do you I get a file's contents in to a blob?
I tried:
select * from all_objects where object_type ='DIRECTORY'; OWNER OBJECT_NAME ------------------------------ ------------------------------ SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ------------------- CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S ------------------- ------------------- ------------------- ------- - - - NAMESPACE EDITION_NAME ---------- ------------------------------ SYS ORACLE_OCM_CONFIG_DIR 12689 DIRECTORY 2010-03-30 10:16:30 2011-01-10 12:49:39 2011-01-10:12:49:39 VALID N N N 9 SYS DATA_PUMP_DIR 12764 DIRECTORY 2010-03-30 10:16:43 2011-01-10 12:49:38 2011-01-10:12:49:38 VALID N N N 9 SYS XMLDIR 57134 DIRECTORY 2010-03-30 10:29:37 2010-03-30 10:29:37 2010-03-30:10:29:37 VALID N N N 9 But based on the output timestamps these are not the dirs created for me. This also doesn't show what is the real OS filepath for the object.
497
You can find the directory objects in Oracle using data dictionary view DBA_DIRECTORIES and ALL_DIRECTORIES.
A DIRECTORY object specifies a logical alias name for a physical directory on the database server file system under which the file to be accessed is located. You can access a file in the server file system only if you have the required access privilege on the DIRECTORY object.
After you create a directory alias, grant users and groups access rights to the files contained in that directory, using the following PL/SQL syntax: GRANT permission ON DIRECTORY alias TO {user | role | PUBLIC};
Oracle object types are user-defined types that make it possible to model real-world entities, such as customers and purchase orders, as objects in the database. New object types can be created from any built-in database types and any previously created object types, object references, and collection types.
The ALL_DIRECTORIES data dictionary view will have information about all the directories that you have access to. That includes the operating system path
SELECT owner, directory_name, directory_path FROM all_directories If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
