CODEWITHSUNDEEP
sqloracle-databasefiletoad
Lucky
Lucky

Reputation: 23

How to write a txt file on desktop using SQL script in Toad

I'm trying to write a simple txt file using SQL in Toad. Below is the SQL

create or replace directory dir_temp as 'C:\Users\name\Desktop\';
declare
  f utl_file.file_type;
begin
  f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
  utl_file.fclose(f);
end;
/

This results into error:

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4

I've verified the grants for Oracle user on directory. It has all permissions. Also, I'm able to create spool file in the same directory.

Upvotes: 1

Views: 988

Answers (1)

Littlefoot
Littlefoot

Reputation: 142715

Directory should be created by SYS, and has to be located on the database server (OK, it can be elsewhere, but it usually isn't). If database isn't installed on your own computer but on some database server, then directory object points to a directory on the server, not your own PC.

If you do it properly, it works. I use 11gXE, installed on my laptop. Here's how:

Connected as SYS, I'm creating a directory and granting privileges to user (SCOTT) who will use it.

SQL> show user
USER is "SYS"
SQL> create or replace directory dir_temp as 'c:\users\littlefoot\desktop';

Directory created.

SQL> grant read, write on directory dir_temp to scott;

Grant succeeded.

Connect as Scott; reuse code you wrote:

SQL> connect scott/tiger
Connected.
SQL> declare
  2    f utl_file.file_type;
  3  begin
  4    f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
  5    utl_file.fclose(f);
  6  end;
  7  /

PL/SQL procedure successfully completed.

Looks OK. Check whether the file was created:

SQL> $dir c:\users\littlefoot\desktop\*.txt
 Volume in drive C is OSDisk
 Volume Serial Number is 7635-F892

 Directory of c:\users\littlefoot\desktop

05.03.2020.  19:25                 0 something.txt
               1 File(s)              0 bytes
               0 Dir(s)  290.609.057.792 bytes free

SQL>

Right; the file is here (empty, though, but - it exists).

So, if you do it right, it works.

Upvotes: 1

Related Questions