Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-29280: invalid directory path

708091Jun 25 2009 — edited Jun 25 2009
am facing a problem,

my requirement is i've to brought some procedure,packages,functions' scripts from a remote database's schema to my current schema and compile them here in my current schema.
Basically in straight word what I've to do is, whatever functions,procedures,packages are there in the remote schema(which i can access through a datbase link) needs to be created in my current schema.
so what i was trying to do is i just access the source codes from all_sources and creating files in a directory.giving the code here,

what I did is first created an Oracle directory.

First I've created a directory named 'PROC_PKG_FUNC' in D drive then I ran the following command.

SQL> create directory 'PROC_PKG_FUNC' as 'D:\'PROC_PKG_FUNC';
Directory created.

CREATE OR REPLACE PROCEDURE Get_Db_Ddl_Scripts AS
v_file Utl_File.FILE_TYPE;
v_file_dir VARCHAR2(50);
i_first_line NUMBER := 1;
BEGIN

v_file_dir := 'PROC_PKG_FUNC';

FOR REC_OBJ IN
(SELECT DISTINCT NAME,TYPE,DECODE(TYPE,'FUNCTION','FUNCTIONS','PACKAGE','PACKAGES','PACKAGE BODY','PACKAGES_BODY','PROCEDURE','PROCEDURES','TYPE','TYPES','TYPE BODY','TYPES_BODY') v_file_dir
FROM ALL_SOURCE@FRISKDEVI41B_ORCL WHERE OWNER='FRISKDEVI41B'
AND TYPE IN ('FUNCTION, PROCEDURE','PACKAGE','PACKAGE BODY','TYPE'))
LOOP
v_file := Utl_File.FOPEN(location => REC_OBJ.v_file_dir,
filename => REC_OBJ.NAME || '.sql',
open_mode => 'w',
max_linesize => 32767);
i_first_line := 1;
FOR REC IN (SELECT TEXT FROM ALL_SOURCE@FRISKDEVI41B_ORCL WHERE NAME = REC_OBJ.NAME AND TYPE=REC_OBJ.TYPE AND OWNER='FRISKDEVI41B' ORDER BY LINE)
LOOP
IF i_first_line = 1 THEN
Utl_File.PUT_LINE(v_file,'CREATE OR REPLACE ' || REPLACE(REC.TEXT,CHR(10),NULL));
ELSE Utl_File.PUT_LINE(v_file, REPLACE(REC.TEXT,CHR(10),NULL));
END IF;
i_first_line := i_first_line + 1;
END LOOP;
Utl_File.FCLOSE(v_file);

END LOOP;

END;
/


but when I'm trying to run this am getting error,
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "SYS.GET_DB_DDL_SCRIPTS", line 15
ORA-06512: at line 2

i know probbaly am getting this error because i'm trying to create different directories like functions, packages etc under the external directory using that record loop,but how would that be possible,please help.

regards,
debalina
This post has been answered by Karthick2003 on Jun 25 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 23 2009
Added on Jun 25 2009
6 comments
1,094 views