Menu de navegação

[jaugustoParticipante]

Pessoal,

Tentei executar esta package:

CREATE OR REPLACE PACKAGE utility IS
FUNCTION change_datatype(prm_value IN VARCHAR2,
prm_data_type IN VARCHAR2) RETURN VARCHAR2;

PROCEDURE generate_stmt(prm_table_name IN VARCHAR2,
prm_where_clause IN VARCHAR2,
prm_output_folder IN VARCHAR2,
prm_output_file IN VARCHAR2);

PROCEDURE generate_all(prm_output_folder IN VARCHAR2);
END utility;

CREATE OR REPLACE PACKAGE BODY utility IS

— VARIABLES USED by PROCEDURE generate_stmt
— File Related PACKAGE Variable
cmn_file_handle UTL_FILE.file_type;
—
—

PROCEDURE close_file IS
BEGIN
UTL_FILE.FCLOSE(cmn_file_handle);
EXCEPTION

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20003, ‘File handle was invalid’);
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20004, ‘Invalid path for file’);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20005,
‘CLOSE_FILE Error in creating file. Message: ‘ ||
SQLERRM);
END close_file;

PROCEDURE open_file(prm_output_folder IN VARCHAR2,
prm_output_file IN VARCHAR2) IS

BEGIN
cmn_file_handle := UTL_FILE.FOPEN(prm_output_folder,
prm_output_file,
‘a’,
32767);
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
close_file;
RAISE_APPLICATION_ERROR(-20000, ‘File handle was invalid’);
WHEN UTL_FILE.INVALID_PATH THEN
close_file;
RAISE_APPLICATION_ERROR(-20001, ‘Invalid path for file’);
WHEN OTHERS THEN
close_file;
RAISE_APPLICATION_ERROR(-20002,
‘OPEN_FILE Error in creating file. Message: ‘ ||
SQLERRM);
END open_file;

FUNCTION change_datatype(prm_value IN VARCHAR2,
prm_data_type IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF prm_value IS NULL THEN
RETURN(‘NULL’);
END IF;

IF prm_data_type = ‘C’ THEN
IF INSTR(prm_value, CHR(10)) > 0 THEN
RETURN(‘REPLACE(‘ || ”” || REPLACE(prm_value, CHR(10), CHR(977)) || ”” ||
‘, CHR(977), CHR(10))’);

END IF;
ELSIF prm_data_type = ‘D’ THEN
RETURN(‘TO_DATE(‘ || ”” || prm_value || ”” || ‘, ‘ || ”” ||
‘DD-MON-YYYY HH24:MI:SS’ || ”” || ‘)’);
ELSIF prm_data_type = ‘N’ THEN
RETURN(prm_value);
END IF;
RETURN(”” || prm_value || ””);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,
‘CHANGE_DATATYPE Error in Converting DataType. Message: ‘ ||
SQLERRM);
END change_datatype;

PROCEDURE generate_stmt(prm_table_name IN VARCHAR2,
prm_where_clause IN VARCHAR2,

prm_output_folder IN VARCHAR2,
prm_output_file IN VARCHAR2) IS
TYPE ref_cols IS REF CURSOR;
mmy_ref_cols ref_cols;

mmy_column_name VARCHAR2(100);
mmy_column_data_type VARCHAR2(1);
mmy_col_string VARCHAR2(32767);
mmy_query_col_string VARCHAR2(32767);
BEGIN
IF prm_table_name IS NULL OR prm_output_folder IS NULL OR
prm_output_file IS NULL THEN
RAISE_APPLICATION_ERROR(-20012, ‘Invalid Argument Passed’);
END IF;

OPEN mmy_ref_cols FOR
SELECT LOWER(column_name) column_name,
DECODE(data_type,
‘VARCHAR2’,
‘C’,
‘CHAR’,
‘C’,
‘LONG’,
‘C’,
‘NUMBER’,
‘N’,
‘DATE’,
‘D’) data_type
FROM user_tab_columns
WHERE table_name = UPPER(prm_table_name)
ORDER BY column_id;
LOOP
FETCH mmy_ref_cols
INTO mmy_column_name, mmy_column_data_type;
EXIT WHEN mmy_ref_cols%NOTFOUND;
mmy_col_string := mmy_col_string || mmy_column_name || ‘, ‘;
IF mmy_column_data_type = ‘D’ THEN
mmy_query_col_string := mmy_query_col_string || ‘change_datatype(‘ ||
‘TO_CHAR(‘ || mmy_column_name || ‘, ‘ || ”” ||
‘DD-MON-YYYY HH24:MI:SS’ || ”” || ‘)’ || ‘, ‘ || ”” ||
mmy_column_data_type || ”” || ‘) || ‘ || ”” || ‘, ‘ || ”” ||
‘ || ‘;
ELSIF mmy_column_data_type IN (‘N’, ‘C’) THEN
mmy_query_col_string := mmy_query_col_string || ‘change_datatype(‘ ||
mmy_column_name || ‘, ‘ || ”” ||
mmy_column_data_type || ”” || ‘) || ‘ || ”” || ‘, ‘ || ”” ||
‘ || ‘;
END IF;

END LOOP;
CLOSE mmy_ref_cols;

IF mmy_col_string IS NOT NULL AND mmy_query_col_string IS NOT NULL THEN

IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
open_file(prm_output_folder, prm_output_file);
END IF;

mmy_col_string := ‘INSERT INTO ‘ || LOWER(prm_table_name) || ‘ (‘ ||
CHR(10) || CHR(9) || CHR(9) || mmy_col_string;
mmy_col_string := RTRIM(mmy_col_string, ‘, ‘);
mmy_col_string := mmy_col_string || ‘)’ || CHR(10) ||
‘VALUES ( ‘ || CHR(9);
mmy_query_col_string := RTRIM(mmy_query_col_string,
‘ || ‘ || ”” || ‘,’ || ”” || ‘ || ‘) ||
‘ one_pare’;

OPEN mmy_ref_cols FOR ‘ SELECT ‘ || mmy_query_col_string || ‘ FROM ‘ || prm_table_name ||

‘ ‘ || prm_where_clause;
LOOP
FETCH mmy_ref_cols
INTO mmy_query_col_string;
EXIT WHEN mmy_ref_cols%NOTFOUND;
mmy_query_col_string := mmy_query_col_string || ‘);’;
UTL_FILE.put(cmn_file_handle, mmy_col_string);
UTL_FILE.put_line(cmn_file_handle, mmy_query_col_string);
END LOOP;
CLOSE mmy_ref_cols;

If UTL_FILE.IS_OPEN(cmn_file_handle) THEN
close_file;
END IF;
END IF;
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
IF mmy_ref_cols%ISOPEN THEN
CLOSE mmy_ref_cols;
END IF;

close_file;
RAISE_APPLICATION_ERROR(-20009, ‘File handle was invalid’);
WHEN UTL_FILE.INVALID_PATH THEN
IF mmy_ref_cols%ISOPEN THEN
CLOSE mmy_ref_cols;
END IF;
close_file;
RAISE_APPLICATION_ERROR(-20010, ‘Invalid path for file’);
WHEN OTHERS THEN
IF mmy_ref_cols%ISOPEN THEN
CLOSE mmy_ref_cols;
END IF;
close_file;
RAISE_APPLICATION_ERROR(-20011,
‘GENERATE_STMT Error in populating file. Message: ‘ ||
SQLERRM);
END generate_stmt;

PROCEDURE generate_all(prm_output_folder IN VARCHAR2)
IS
BEGIN
/* Please amend the SQL to extract the list of tables which u require */
FOR tab_rec IN ( SELECT table_name FROM all_tables )
LOOP
generate_stmt(tab_rec.table_name,”,prm_output_folder,trim(tab_rec.table_name) || ‘.sql’);
END LOOP;
END generate_all;
END utility;

Já usei os comandos: create or replace directory geraimp as ‘/home/oracle/importacao’;
e o grant read, write on directory geraimpr to admsiig;

quando fui usar o comando da package:

sql> EXECUTE utility.generate_stmt(‘cpr_interessado’, null, ‘geraimp’, ‘cpr_interessado’);

ao executar este comando o seguinte erro é apresentado: ‘ORA-29280: invalid directory path’

ou quando vou executar o comando:

sql> EXECUTE utility.generate_all(‘geraimp’);
o seguinte erro é apresentado: ‘ORA-29280: invalid directory path’

Aguardo a ajuda de vocês,

Um abraço,

José Augusto.

[Rodrigo AlmeidaParticipante]

Amigo,

Qual o valor que você passou para o parâmetro prm_output_folder ?

Veja se o valor que você passou, o caminho existe no SERVIDOR que ESTÁ o banco de dados, se o parâmetro UTL_FILE_DIR está com ‘*’ ou com algum caminho específico que não seja o que você está passando.

Abraços,

[jaugustoParticipante]

Caro Rodrigo,

  Este parâmetro UTL_FILE_DIR está em branco, mas já tentei alterá-lo e não consegui através do comando ALTER SYSTEM, mas o oracle não permite. Como devo proceder?

Um abraço,

José Augusto.

[Rodrigo AlmeidaParticipante]

José,

Veja se o seu banco de dados está trabalhando com SPFILE, pode verificar desse modo usando o SQLPLUS:

SQL> show parameters spfile

O campo SPFILE deve estar preenchido com um caminho do arquivo.

Depois, emita o comando:

SQL> ALTER SYSTEM SET UTL_FILE_DIR = ‘*’ scope=spfile;

Como o parâmetro UTL_FILE_DIR não é dinâmico, será obrigado a parar e iniciar o banco de dados, pode ser feito desse modo:

SQL> shutdown immediate;

SQL> startup;

E agora, pode verificar o parâmetro UTL_FILE_DIR novamente:

SQL> show parameters utl_file_dir

E fazer os testes com a sua procedure novamente.

Abraços,

[jaugustoParticipante]

Rodrigo,

  Realmente segui todos os passos e o erro agora é este:

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 33
ORA-06512: at “SYS.UTL_FILE”, line 460
ORA-29283: invalid file operation
ORA-06512: at “ADMSIIG.UTILITY”, line 28
ORA-06512: at “ADMSIIG.UTILITY”, line 131
ORA-06512: at line 1

Será que o diretório tem que ser o mesmo do spfile?

Um abraço,

José Augusto.

[Rodrigo AlmeidaParticipante]

Verifique as permissões para o usuário “EVERYONE”, dê FULL CONTROL.

Abraços,

[David SiqueiraParticipante]

Se o seu diretório estiver pré-determinado no parametro UTL_FILE_DIR, sim, ele tem que ser identico e correspondente ao que esta no Sistema Operacional, se você estiver usando “*”, pode ser qualquer diretório.

Abraço

[jaugustoParticipante]

Perdoe-me Rodrigo,

  Não entendi usuário EVERYONE, estou usando o usuário ADMSIIG que é usuário  no qual tem diversos privilégios inclusive o de UNLIMITED TABLESPACE, inclusive para este usuário concedi privilégios de READ and WRITE para o diretório no qual criei que foi geraimp, no qual corresponde ao diretório '/home/oracle/importacao'.

[Rodrigo AlmeidaParticipante]

José,

Desculpe, o usuário EVERYONE é no nível de sistema operacional e somente para ambiente windows.

Como você está usando Linux/Unix, veja se o diretorio que você está tentando criar o arquivo possui a permissão, no sistema operacional. Dê um chmod 775 no diretorio e refaça – teste.

Abraços,

[jaugustoParticipante]

Rodrigo,

atribui direitos ao diretórioa;  /home/oracle/importacao, e ao realizar o teste

o seguinte erro foi exibido:

SQL> EXECUTE utility.generate_stmt(‘cpr_interessado’, null, ‘geraimp’, ‘cpr_interessado’);
BEGIN utility.generate_stmt(‘cpr_interessado’, null, ‘geraimp’, ‘cpr_interessado’); END;

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 33
ORA-06512: at “SYS.UTL_FILE”, line 460
ORA-29283: invalid file operation
ORA-06512: at “ADMSIIG.UTILITY”, line 28
ORA-06512: at “ADMSIIG.UTILITY”, line 131
ORA-06512: at line 1

[David SiqueiraParticipante]

Cara posta pra gente o seu parametro de UTL_FILE

show paramter utl_file_dir

Ao que tudo indica vocÊ esta usando DIRECTORY de Banco certo?
E qual é a sua release?

Abraço

[jaugustoParticipante]

SQL> show parameter utl_file_dir

NAME TYPE VALUE

---

utl_file_dir string *

SQL*Plus: Release 10.1.0.3.0 – Production on Tue Aug 4 14:07:46 2009

Copyright (c) 1982, 2004, Oracle. All rights reserved.

[David SiqueiraParticipante]

Cara desculpa a insistencia, mais você criou esse diretório IMPORTACAO no S.O como user Oracle correto?

Tente passar permissão chmod 777 nesse diretório e veja se funciona.

Abraço

[paleoParticipante]

Veja que voce esta começando do /home. O /home puro não permite escrita. Peça para alguem ai de so fazer um link simbolico a partir do / com ln -s /home/oracle/importacao importacao. Isso deve dar certo .

E substitua no utl_files o * por /importacao

[]s

[jaugustoParticipante]

Pessoal,

Criei este diretório importacao do /home/oracle/importacao como usuário root.

[Autor]

Posts