← notes

Directory Objects in PL/SQL

Bino Kochumol Varghese |   2 March 2023

This view describes all directories accessible to the user.

SELECT * FROM ALL_DIRECTORIES;

This view describes all directories specified for the entire database.

SELECT * FROM DBA_DIRECTORIES;

To view all the tables details, look here.

References

Oracle Doc

pl-sql

What are your thoughts on this post?

I’d love to hear from you! Click this link to email me—I reply to every message!

Also use the share button below if you liked this post. It makes me smile, when I see it.

Read More

NOT IN vs NOT EXISTS in PL/SQL

19 June 2025

SELECT * FROM TABLE1 WHERE ID NOT IN ( SELECT ID FROM TABLE2 ) When I ran the above query I know the condition will give me the results, because the data exists in the TABLE1 which is not in TABLE2. But I didn’t go any results. I got surprised. After I a while I found out the issue.
Read more

To extract the value of a tag from an XML-like string in PL/SQL

12 January 2025

To extract the value of a tag from the given XML-like string in PL/SQL, you can use the EXTRACTVALUE function or XMLTABLE for XML processing.
Read more

Varchar datatype limits

30 November 2024

For NVARCHAR2 and VARCHAR2 maximum size is 4000 bytes, or 32767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDED. This is useful you have to allocate more data to a variable.
Read more