Home » SQL & PL/SQL » SQL & PL/SQL » How to create a directory object dynamically (Merged)
How to create a directory object dynamically (Merged) [message #217344] Thu, 01 February 2007 14:57 Go to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
I have a problem while creating directory object as dynamically.

Example:

declare

dirpath varchar2(50):= '/prodevel/workfile';

Begin

Create or replace directory test as || dirpath;

End;

While compiling the above code I am getting errors.

Actually I need to pass directory object to

UTL_FILE.FOPEN('TEST', 'UPDTGEO.TXT', 'R');

Please help me

For the following procedure I need to created dynamic directory object. That object is need to be pass.

create or replace
PACKAGE BODY UPDATEGEOAREA AS
PROCEDURE Update_Location_GeoArea(infile_location varchar2,outfile_location varchar2) is BEGIN
DECLARE
fHandler UTL_FILE.FILE_TYPE;
fileHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
delim Number;
len Number;
loc_val varchar2(20);
geo_val varchar2(20);
errmsg varchar2(50);
dtflag number := 0;
BEGIN
fHandler := UTL_FILE.FOPEN('TEST', 'UPDTGEO.TXT', 'R');
loop
UTL_FILE.GET_LINE(fHandler, buf);
len := length(buf);
delim := instr(buf,',');
loc_val := substr(buf,0,delim-1);
geo_val := substr(buf,delim+1,len);
UPDATE TEMPLOC set GEOAREA = geo_val where Location = loc_val;
IF SQL%NOTFOUND THEN
errmsg := 'LOCATION:'||loc_val||' IS INVALID';
fileHandler := UTL_FILE.FOPEN('TEST', 'UPDTGEOOUTPUT.TXT','A');
If dtflag = 0 Then
dtflag := dtflag +1;
UTL_FILE.PUTF(fileHandler, '************'||to_char(to_date(sysdate),'MON,DD,YYYY')||'*************\n\n');
End if;
UTL_FILE.PUTF(fileHandler, errmsg);
UTL_FILE.FCLOSE(fileHandler);
END IF;
end loop;
EXCEPTION
WHEN NO_DATA_FOUND then
UTL_FILE.FCLOSE(fHandler);
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
End;
END Update_Location_GeoArea;
END UPDATEGEOAREA;



NOTE: I am using unix environment plase help me

Thanks in advance..

[Updated on: Thu, 01 February 2007 14:59]

Report message to a moderator

Re: How to create a directory object dynamically [message #217345 is a reply to message #217344] Thu, 01 February 2007 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>While compiling the above code I am getting errors.
Error? what error? I don't see any error.

Report message to a moderator

Re: How to create a directory object dynamically [message #217349 is a reply to message #217345] Thu, 01 February 2007 15:09 Go to previous messageGo to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
Hi Sir,
I need to create directory object as dynamically.
Can I create procedure for the following state ment:
create or replace directory loctemp as '/prodevel/workfile';

Can you please help me?

I got the following error :

Error(11,9): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin case declare 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 <a single-quoted SQL string> pipe <an alternatively-quoted SQL string>

[Updated on: Thu, 01 February 2007 15:10]

Report message to a moderator

Re: How to create a directory object dynamically [message #217352 is a reply to message #217344] Thu, 01 February 2007 15:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Creating objects on the fly from PL/SQL is NOT a good idea.
Besides PL/SQL does not allow DDL to be directly run.
EXECUTE IMMEDIATE must be (ab)used.

Report message to a moderator

Re: How to create a directory object dynamically [message #217411 is a reply to message #217344] Fri, 02 February 2007 01:33 Go to previous messageGo to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
Thank you very much.

Now I am able to created directory object dynamicaaly using dynamic sql

[Updated on: Fri, 02 February 2007 01:34]

Report message to a moderator

insufficient privileges [message #217413 is a reply to message #217344] Fri, 02 February 2007 01:40 Go to previous messageGo to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
Hi While running the package I am getting the runtime error as

insufficient privileges.

Please help me.

The package is as follows:


create or replace
PACKAGE UPDATEGEOAREA AS
PROCEDURE Update_Location_GeoArea(infile_location varchar2,outfile_location varchar2);
END UPDATEGEOAREA;

create or replace
PACKAGE BODY UPDATEGEOAREA AS
PROCEDURE Update_Location_GeoArea(infile_location varchar2,outfile_location varchar2) is BEGIN
DECLARE
fHandler UTL_FILE.FILE_TYPE;
fileHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
delim Number;
len Number;
loc_val varchar2(20);
geo_val varchar2(20);
errmsg varchar2(50);
dtflag number := 0;
l_Directory varchar2(50):='TEST3';

BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || infile_location || '''';

EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || outfile_location || '''';

fHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEO.TXT', 'R');
loop
UTL_FILE.GET_LINE(fHandler, buf);
len := length(buf);
delim := instr(buf,',');
loc_val := substr(buf,0,delim-1);
geo_val := substr(buf,delim+1,len);
UPDATE TEMPLOC set GEOAREA = geo_val where Location = loc_val;
IF SQL%NOTFOUND THEN
errmsg := 'LOCATION:'||loc_val||' IS INVALID';
fileHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEOOUTPUT.TXT','A');
If dtflag = 0 Then
dtflag := dtflag +1;
UTL_FILE.PUTF(fileHandler, '************'||to_char(to_date(sysdate),'MON,DD,YYYY')||'*************\n\n');
End if;
UTL_FILE.PUTF(fileHandler, errmsg);
UTL_FILE.FCLOSE(fileHandler);
END IF;
end loop;
EXCEPTION
WHEN NO_DATA_FOUND then
UTL_FILE.FCLOSE(fHandler);
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
End;
END Update_Location_GeoArea;
END UPDATEGEOAREA;

The input for the above package is:


DECLARE
INFILE_LOCATION VARCHAR2(200);
OUTFILE_LOCATION VARCHAR2(200);
BEGIN
INFILE_LOCATION := '/prodevel/workfile';
OUTFILE_LOCATION := '/prodevel/workfile';

UPDATEGEOAREA.UPDATE_LOCATION_GEOAREA(
INFILE_LOCATION => INFILE_LOCATION,
OUTFILE_LOCATION => OUTFILE_LOCATION
);
END;

Error messge is:
ORA-01031: insufficient privileges
ORA-06512: at "CONSDB_DATABASE_PRODEVEL.UPDATEGEOAREA", line 16
ORA-06512: at line 8

Report message to a moderator

Re: insufficient privileges [message #217414 is a reply to message #217413] Fri, 02 February 2007 01:47 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Don't start a new thread if you have a follow-up question about the same topic.

Did you read anacedents remark?
Quote:
Creating objects on the fly from PL/SQL is NOT a good idea.

Why would you want to create the object dynamically?

Make sure that the owner of the package has the privilege to create a directory directly granted to him.

Report message to a moderator

Re: insufficient privileges [message #217417 is a reply to message #217414] Fri, 02 February 2007 01:54 Go to previous messageGo to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
I need to setup this package in different databases.
I have some databases like prodevel,cadevel,catest..etc.

In unix operating system I have folder like /prodevel/workfile, /cadevel/workfile, /catest/workfile etc..

For that reason I create dynamic directory object, and pass that object to UTL_FILE package.


Please help me.

Report message to a moderator

Re: How to create a directory object dynamically (Merged) [message #217479 is a reply to message #217344] Fri, 02 February 2007 08:10 Go to previous messageGo to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
Hi I am getting the runtime while running the following procedure.

CREATE OR REPLACE
PROCEDURE UPDATE_LOCATION_GEOAREA AS
BEGIN
Declare
fHandler UTL_FILE.FILE_TYPE;
fileHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
delim Number;
len Number;
loc_val varchar2(20);
geo_val varchar2(20);
errmsg varchar2(50);
dtflag number := 0;
l_Directory varchar2(50):='TEST';
infile_location varchar2(50):= '/prodevel/workfile';
outfile_location varchar2(50):= '/prodevel/workfile';
BEGIN
dbms_output.put_line(infile_location||outfile_location);
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || infile_location || '''';
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || outfile_location || '''';
EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY ' || l_Directory ||
' TO PUBLIC';
EXECUTE IMMEDIATE 'GRANT WRITE ON DIRECTORY ' || l_Directory ||
' TO PUBLIC';
fHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEO.TXT', 'R');
loop
UTL_FILE.GET_LINE(fHandler, buf);
len := length(buf);
delim := instr(buf,',');
loc_val := substr(buf,0,delim-1);
geo_val := substr(buf,delim+1,len);
UPDATE TEMPLOC set GEOAREA = geo_val where Location = loc_val;
IF SQL%NOTFOUND THEN
errmsg := 'LOCATION:'||loc_val||' IS INVALID';
fileHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEOOUTPUT.TXT','A');
If dtflag = 0 Then
dtflag := dtflag +1;
UTL_FILE.PUTF(fileHandler, '************'||to_char(to_date(sysdate),'MON,DD,YYYY')||'*************\n\n');
End if;
UTL_FILE.PUTF(fileHandler, errmsg);
UTL_FILE.FCLOSE(fileHandler);
END IF;
end loop;
EXCEPTION
WHEN NO_DATA_FOUND then
UTL_FILE.FCLOSE(fHandler);
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
End;
END UPDATE_LOCATION_GEOAREA;
/


IF I run the procedureexec UPDATE_LOCATION_GEOAREA;
ERROR AS

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "CONSDB_DATABASE_PRODEVEL.UPDATE_LOCATION_GEOAREA", line 18
ORA-06512: at line 1


IF I run the just script in sql *plus I am not getting any error its working fine.

The Script:

Declare
fHandler UTL_FILE.FILE_TYPE;
fileHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
delim Number;
len Number;
loc_val varchar2(20);
geo_val varchar2(20);
errmsg varchar2(50);
dtflag number := 0;
infile_location varchar2(50):= '/prodevel/workfile';
outfile_location varchar2(50):= '/prodevel/workfile';
l_Directory varchar2(50):='TEST';
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || infile_location || '''';
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
' AS ''' || outfile_location || '''';
fHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEO.TXT', 'R');
loop
UTL_FILE.GET_LINE(fHandler, buf);
len := length(buf);
delim := instr(buf,',');
loc_val := substr(buf,0,delim-1);
geo_val := substr(buf,delim+1,len);
UPDATE TEMPLOC set GEOAREA = geo_val where Location = loc_val;
IF SQL%NOTFOUND THEN
errmsg := 'LOCATION:'||loc_val||' IS INVALID';
fileHandler := UTL_FILE.FOPEN(l_Directory, 'UPDTGEOOUTPUT.TXT','A');
If dtflag = 0 Then
dtflag := dtflag +1;
UTL_FILE.PUTF(fileHandler, '************'||to_char(to_date(sysdate),'MON,DD,YYYY')||'*************\n\n');
End if;
UTL_FILE.PUTF(fileHandler, errmsg);
UTL_FILE.FCLOSE(fileHandler);
END IF;
end loop;
EXCEPTION
WHEN NO_DATA_FOUND then
UTL_FILE.FCLOSE(fHandler);
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
End;

Please help me.

Thanks in advance.

[Updated on: Fri, 02 February 2007 08:13]

Report message to a moderator

Re: How to create a directory object dynamically (Merged) [message #217498 is a reply to message #217344] Fri, 02 February 2007 09:37 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLEs do NOT apply within PL/SQL procedures.
GRANT to user must be explicitly given.

Report message to a moderator

Previous Topic: how fetch top 10 row from table
Next Topic: Error/Message handling
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 25 08:33:43 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.