CODEWITHSUNDEEP
sqloracle-databaseplsql
Cyber Liebe
Cyber Liebe

Reputation: 41

PLSQL UTL FILE IS NOT GETTING CREATED ANY Solution

Hi guys IP am trying to use the utl_file package in PLSQL...

I have granted the user to create directory privilege and read and write privilege from sysdba...when I create a directory it says "directory created", but is physical harddrive local location no directory is created.

When I write a code to give output in file it gives some errors.

Code:

SQL> create or replace directory PLSQL as 'D:\PLSQL';
Directory created.

SQL>declare
2  fp utl_file.file_type;
3  begin
4  fp := utl_file.fopen('D:\PLSQL', 'Hello.txt','w');
5  utl_file.putf(fp,'HI THIS IS WRITTEN IN SQLPLUS');
6  utl_file.fclose(fp);
7  end;
8  /

declare
*
ERROR at line 1:
ORA-29280: invalid directory object
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4

if any solution help...

Upvotes: 0

Views: 659

Answers (2)

Atif
Atif

Reputation: 2210

1st create a logical directory using below syntax:

create directory logfile as 'D:\PLSQL';

and then refer the same in your code. Also please be sure you have read, write grant on this new logical directory.

GRANT READ,WRITE ON DIRECTORY logfile TO user_Name;

declare
2  fp utl_file.file_type;
3  begin
4  fp := utl_file.fopen(logfile , 'Hello.txt','w');
5  utl_file.putf(fp,'HI THIS IS WRITTEN IN SQLPLUS');
6  utl_file.fclose(fp);
7  end;
8  /

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

Two objections so far:

  • when you create a directory (an Oracle object), nothing is "automatically" created in filesystem. That directory (D:\PLSQL) has to exist, you have to create it manually (using e.g. Windows Explorer or Command Prompt or ...).

    • Note that directory usually resides on a database server. If D:\PLSQL is located on your own PC (while the database is elsewhere), it won't work. If you can't access the database server (because of lack of privileges), you'll have to talk to DBA to do it for you. Though, saying that you connected as sysdba, I presume that it shouldn't be a problem for you.

  • when referencing the directory in PL/SQL code, you have to use its name (PLSQL), not its path (D:\PLSQL):

    fp := utl_file.fopen('PLSQL', 'Hello.txt','w');

I tested your code, works just fine (file is created in my directory).

Upvotes: 2

Related Questions