Oracle PL/SQL Exception for Dynamic SQL

Question

I have a problem with my code (bellow), with assumption the path 'E:\Oracle\test' doesn't exist (the psychical folder doesn't exist), so it should go to the error exception handler and write an error log, but it doesn't. Instead it successfully create the Oracle Directory, why does this happen? where do my code went wrong?

Can someone please help me, thanks a lot The database is Oracle 11g

DECLARE
var1 VARCHAR2(2000) := 'somepath';
var2 VARCHAR2(2000) := 'directoryname';
err  VARCHAR2(2000);

BEGIN
file_path_ := 'E:\Oracle\test\' || var1 || '\';
temp_dir_ := 'TEST_DIR_' || var2;
plsql_block_ := 'CREATE OR REPLACE DIRECTORY ' || temp_dir_ || ' AS ' || '''' ||file_path_ || '''';
EXECUTE IMMEDIATE plsql_block_;

SAVEPOINT Start_Trans;

<some code here>

EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO Start_Trans;
err := substr(SQLERRM,1,2000);
Write_Log('ERROR', var1, err_);

END;

diederikh · Accepted Answer · 2014-05-20 06:47:36Z

1

The created directory in the Oracle database is just an alias for the real thing on disk. Oracle doesn't verify that the directory you specify actually exists.

answered May 20, 2014 at 6:47

diederikh

25.3k5 gold badges39 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

erwin Over a year ago

But, when I simply execute CREATE OR REPLACE TEST_DIR AS 'E:\Oracle\Test\' (which the physical folder doesn't exist) it does returns an error

diederikh Over a year ago

Strange, should not make a difference. Does the exception occur when the directory is first created (so no replace)?

Jon Heller Over a year ago

@erwin Can you add the "bad" code and the error message it generates? On my test database I can certainly create a directory with a bad path and get no error messages.

Collectives™ on Stack Overflow

Oracle PL/SQL Exception for Dynamic SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related