Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Location
    VA
    Posts
    35

    Unanswered: How can I "CREATE DIRECTORY" in PL/SQL?

    The following statement works fine if I execute it interactively:

    create or replace directory DBA_DATABASE_DDL_DIR as '/usr/oracle/scripts/DBA_DATABASE_DDL_DIR';

    However, when I put it in a PL/SQL script it fails with:

    ERROR at line 18:
    ORA-06550: line 18, column 4:
    PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
    begin case declare end exit for goto if loop mod null pragma
    raise return select update while with <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall merge pipe

    Here is the PL/SQL script I'm using:

    declare
    RowCount_QTY number;

    begin
    select
    count(*) into RowCount_QTY
    from
    dba_directories
    where
    directory_name = 'DBA_DATABASE_DDL_DIR';

    if RowCount_QTY > 0
    then
    dbms_output.put_line ('DBA_DATABASE_DDL_DIR already exists');
    else
    dbms_output.put_line ('Creating DBA_DATABASE_DDL_DIR');

    create or replace directory DBA_DATABASE_DDL_DIR as '/usr/oracle/scripts/DBA_DATABASE_DDL_DIR';
    end if;
    end;
    /

    Any help is appreciated.

  2. #2
    Join Date
    Dec 2004
    Location
    VA
    Posts
    35
    Thanks for looking. I found something that works. Apparently, DDL must be executed with Dynamic SQL. For example:

    declare
    RowCount_QTY number;
    ExecImmediate_STR varchar (2000);
    begin
    select
    count(*) into RowCount_QTY
    from
    dba_directories
    where
    directory_name = 'DBA_DATABASE_DDL_DIR';

    if RowCount_QTY > 0
    then
    dbms_output.put_line ('DBA_DATABASE_DDL_DIR already exists');
    else
    dbms_output.put_line ('Creating DBA_DATABASE_DDL_DIR');

    ExecImmediate_STR := 'create or replace directory DBA_DATABASE_DDL_DIR as ''/usr/oracle/scripts/DBA_DATABASE_DDL_DIR''';
    dbms_output.put_line ('ExecImmediate_STR=' || ExecImmediate_STR);
    execute immediate (ExecImmediate_STR);
    end if;
    end;
    /

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DDL can not be invoked directly within PL/SQL.
    You must (ab)use EXECUTE IMMEDIATE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by shew01
    I found something that works. Apparently, DDL must be executed with Dynamic SQL.
    /
    This is true, however, as ana implies, it's really not the way that you want to go about it.
    Use the method that you used before i.e. simple create the directory outside any procedures. As an additional comment, your technique for trapping the potential error of an object already existing is a bit of a waste of resources. allow the exception to raise and handle it in the exception handler.
    From the looks of things, this is not intended as production code, rather, it looks like you are practicing, so it's obviously ok to play with the code, but I would strongly suggest that you do not implement this kind of practice in any live system.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •