UTL_FILE no Oracle XE

[ceduardosilva 0]

Estou tentando ler um arquivo texto através do PL/SQL mas não estou conseguindo. No manual que eu consegui encontrar, diz para configurar no INI.ORA. Como não achei este arquivo no XE, tentei configurar pelo C:\oraclexe\app\oracle\product\10.2.0\server\dbs\SPFILEXE.ORA, da seguinte forma UTL_FILE_DIR='c:\tmp'. Desta forma forma, ao iniciar o oracle, ele inicializa de um modo bem rapido (sem dar erro nenhum), mas quando eu vou acessar pelo sql plus eu não consigo.

 

Alguém tem alguma sugestão?

 

Atenciosamente

 

Carlos Eduardo

[Vanderlei Filho 0]

Segue o exemplo de utilizaçao UTL_FILE do guia DEVOLOPER PLSQL da documentação do XE:

 

Example 5–18 Setting up a Directory for Use With UTL_FILE

-- first connect as SYS to perform the necessary setups

-- when you run the following to connect as SYS, use your password for SYS

CONNECT SYS/ORACLE AS SYSDBA

-- the following grants access on the UTL_FILE package to user HR

GRANT EXECUTE ON UTL_FILE TO HR;

-- the following sets up directory access for /tmp on a Linux platform

CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp';

-- you could use 'c:\temp' for temp_dir on a Windows platform, note that

-- c:\temp must exist on the Windows computer

-- the following grants the user read and write access to the directory

GRANT READ, WRITE ON DIRECTORY temp_dir TO HR;

-- now connect as user HR/HR to check directory setup

-- when you connect as HR, use your password for HR

CONNECT HR/HR

-- the following SELECT query lists information about all directories that

-- have been set up for the user

SELECT * FROM ALL_DIRECTORIES;

-- if TEMP_DIR is listed, then you are ready to run UTL_FILE procedures as HR

After the SQL statements in Example 5–18 are executed, you can connect as the user

HR and run UTL_FILE procedures. Some simple examples are shown in Example 5–19.

Example 5–19 Using the UTL_FILE Package

-- connect as user HR and run UTL_FILE procedures

DECLARE

string1 VARCHAR2(32767);

file1 UTL_FILE.FILE_TYPE;

BEGIN

file1 := UTL_FILE.FOPEN('TEMP_DIR','log_file_test','A'); -- open in append mode

string1 := TO_CHAR(SYSDATE) || ' UTL_FILE test';

UTL_FILE.PUT_LINE(file1, string1); -- write a string to the file

UTL_FILE.FFLUSH(file1);

UTL_FILE.FCLOSE_ALL; -- close all open files

END;

/

DECLARE

string1 VARCHAR2(32767);

Oracle Provided Packages

Using Procedures, Functions, and Packages 5-31

file1 UTL_FILE.FILE_TYPE;

BEGIN

file1 := UTL_FILE.FOPEN('TEMP_DIR','log_file_test','R'); -- open in read mode

UTL_FILE.GET_LINE(file1, string1, 32767); -- read a string from the file

DBMS_OUTPUT.PUT_LINE(string1); -- display the string

UTL_FILE.FCLOSE_ALL; -- close all open files

END;

/

 

 

Obrigado,

 

Vanderlei Silva

[alphamek 2]

Carlos,

 

Qual o valor do parâmetro UTL_FILE_DIR na instância do XE?

 

Abraços,

 

Rodrigo Almeida