Errore Oracle

[Denis Carraro]

Ciao a Tutti,

ho costruito una procedura che mi fa il Dump di una tabella in un file CSV e
funziona al 90%, mentre con alcune tabelle ho questo errore:

ERRORE alla riga 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 103
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 287
ORA-06512: at "CAPCARTASI1_O.DUMP_TABLE_TO_CSV_TKT", line 64
ORA-06512: at line 1

e non riesco a capire il xkè?

vi giro anche il codice:
PROCEDURE dump_table_to_csv_tkt( p_tname IN varchar2,p_dir IN varchar2,
p_filename IN varchar2 )

IS

/* *******************************************************************

** ------------------------------------------------------------------

** Copyright The Knowledge Team S.P.A 2003 - Denis Carraro

** ------------------------------------------------------------------

** NAME: dump_table_to_csv_tkt

**

** Scopo:

** Questa Procedura server per esportare una tabella passata come parametro

** tramite "p_tname" nel directory "p_dir" sul file "p_filename "

**

**

** Called by:

** Schedulata una volta al mese.

**

** Application Errors:

**

** Results SET:

** <none>

**

**
****************************************************************************
**

** Change History

**
****************************************************************************
**

** Rev DATE NAME Description

** 01 25/06/2003 Denis Carraro Original Implementation

**
****************************************************************************
**

*/

l_output utl_file.file_type;

l_theCursor INTEGER DEFAULT dbms_sql.open_cursor;

l_columnValue varchar2(4000);

l_status INTEGER;

l_query varchar2(1000)

DEFAULT 'select * from ' || p_tname;

l_colCnt NUMBER := 0;

l_separator varchar2(1);

l_descTbl dbms_sql.desc_tab;

BEGIN

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy
hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );

dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

FOR i IN 1 .. l_colCnt loop

utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name ||
'"' );

dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );

l_separator := ',';

-- dbms_output.put_line(l_descTbl(i).col_name);

END loop;

utl_file.new_line( l_output );

l_status := dbms_sql.EXECUTE(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop

l_separator := '';

FOR i IN 1 .. l_colCnt loop

dbms_sql.column_value( l_theCursor, i, l_columnValue );

utl_file.put( l_output, l_separator || l_columnValue );

l_separator := ',';

END loop;

utl_file.new_line( l_output );

END loop;

dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );

-- EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';

-- EXCEPTION

-- WHEN others THEN

-- EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy''';

-- raise;

END;

Aiutatemi :''''''''''''''''''''(

[Ubi]

Post by Denis Carraro
utl_file.fclose( l_output );
-- EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';
-- EXCEPTION
-- WHEN others THEN
-- EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy''';
-- raise;

Ma l'errore lo dà anche con queste righe commentate?
E poi è utile sapere se le tabelle che danno errore, lo danno
sistematicamente. Cioè se capita o meno che la stessa tabella alcune volte
dà arrore ed altre nò.

Io non sono riuscito a riprodurre l'errore.

[Ubi]

Secondo me i possibili errori sono:
1) Superi la lunghezza massima della riga.
2) C'è un campo strano. Tipo un blob.

Per individure l'errore farei così:
1) Aggiungo nella parte exception la scrittura su file dei codici SQLERR e
SQLMSG. Per capire quale eccezione viene lanciata.
2) Eseguo la procedure su una vista definita sulla tabella che da errore.
Nella vista elenchi la prima metà delle colonne. SE funziona ripeti
l'operazione aggiungendo colonne, se da errore invece levi delle colonne.
Procedi fino a quando non individui la/le colonne incriminate.

Secondo me il problema è che superi i 4000 caratteri.

[Denis Carraro]

Post by Ubi
Secondo me il problema è che superi i 4000 caratteri.

ho provato a mettere il max (vedi parte di esempio sotto),cioè "32767 ", ma
nulla... ho sempre l'errore:

l_output utl_file.file_type;

l_theCursor INTEGER DEFAULT dbms_sql.open_cursor;

l_columnValue varchar2(32767);

l_status INTEGER;

l_query varchar2(1000)

DEFAULT 'select * from ' || p_tname;

l_colCnt NUMBER := 0;

l_separator varchar2(1);

l_descTbl dbms_sql.desc_tab;

BEGIN

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy
hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );

dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

FOR i IN 1 .. l_colCnt loop

utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name ||
'"' );

dbms_sql.define_column( l_theCursor, i, l_columnValue, 32767 );

[felice_pago]

Post by Denis Carraro

Post by Ubi
Secondo me il problema è che superi i 4000 caratteri.

ho provato a mettere il max (vedi parte di esempio sotto),cioè "32767 ", ma
l_output utl_file.file_type;
l_theCursor INTEGER DEFAULT dbms_sql.open_cursor;
l_columnValue varchar2(32767);
l_status INTEGER;
l_query varchar2(1000)
DEFAULT 'select * from ' || p_tname;
l_colCnt NUMBER := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
BEGIN
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy
hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );

...

quanto è lunga l_query ?
quando si blocca ?


felice_pago
-

--------------------------------
Inviato via http://usenet.libero.it

[Denis Carraro]

Post by felice_pago
quanto è lunga l_query ?
quando si blocca ?

era un l_query varchar2(1000), ma ho provato amettere pure a 4000.. ma
pikke :(

AIUTOOOOOOOOOOOOO

[felice_pago]

Post by felice_pago
quanto è lunga l_query ?
quando si blocca ?

era un l_query varchar2(1000), ma ho provato amettere pure a 4000.. ma
pikke :(
AIUTOOOOOOOOOOOOO

non la variabile, che è varchar2, ma il suo valore di volta in volta,
io non prg + da anni, ma dovrebbe essere 256 ?


se voui fare uno spool delle tabelle
ti conviene fare in altro modo.



felice_pago
-

--------------------------------
Inviato via http://usenet.libero.it

[felice_pago]

.... potrei postarti un paio di query che lanciate
ti fanno sia il ctl che il data source per il sqlloader.

te ne sarei grato se ci riuscissi :)

se ci riuscissi io,
o se ci riuscissi tu ?
:(



felice_pago
-

--------------------------------
Inviato via http://usenet.libero.it

[felice_pago]

se potessi postarmi un paio di query .....

attento alle date ed alle cose potrebbero servirti !

questo ti crea il file table_name.ctl
------------------------------------------------------
prompt Enter Table Name:
accept tname

set heading off;
set verify off;
set feedback off;
set show off trim off pages 0 concat on;
set echo off;
set timing off
set time off
spool &&tname..ctl

SELECT 'LOAD DATA'||chr(10)
||'INFILE '''||lower(table_name)||'.dat'' '||chr(10)
||'INTO TABLE '||table_name||chr(10)
||'FIELDS TERMINATED BY '','' '||chr(10)
||'TRAILING NULLCOLS'||chr(10)
||'(' FROM user_tables
WHERE TABLE_NAME = UPPER('&&tname');
SELECT decode(rownum,1,' ',' , ')||rpad(column_name,33,' ')
|| decode(data_type, 'VARCHAR2','CHAR NULLIF('|| column_name ||'=BLANKS)',
'FLOAT','DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
'NUMBER',decode(data_precision,0, 'INTEGER EXTERNAL NULLIF ('||column_name
||'=BLANKS)',
decode(data_scale,0,'INTEGER EXTERNAL NULLIF ('||column_name ||'=BLANKS)',
'DECIMAL EXTERNAL NULLIF ('||column_name ||'=BLANKS)')),
'DATE','DATE "MM/DD/YY" NULLIF ('||column_name ||'=BLANKS)',NULL)
FROM user_tab_columns
WHERE TABLE_NAME = UPPER('&&tname')
ORDER BY COLUMN_ID;
SELECT ')' FROM sys.dual;

spool off;
set heading on;
set feedback on;
set show on trim on pages 45 concat off;
set verify on;
undefine tname;
set echo on;
---------------------------------------------------------

questo ti crea il file table_name.dat

set serveroutput on;
set timing off
set feed off
set heading on
set pagesize 1400
prompt Spool dei dati
accept in_owner prompt "Immettere il nome schema :"
accept in_table_name prompt "Immettere il nome tabella :"
spool spool_&&in_table_name.sql

declare
cursor c_col IS
select column_name,owner,table_name
from dba_tab_columns
where owner = upper('&&in_owner')
and table_name = upper('&&in_table_name')
order by column_id;
stringa2 varchar2(20000);
cnr number(10) := 0;
avgr number(17,7) := 0;
begin
dbms_output.enable(1000000);
dbms_output.put_line ('set linesize 1400');
dbms_output.put_line ('set heading off');
dbms_output.put_line ('set pagesize 0');
dbms_output.put_line ('spool &&in_table_name.dat');
dbms_output.put_line ('select ');
for r_col in c_col loop
begin
dbms_output.put_line('||'||''','''||'||'||r_col.column_name);
end;
end loop; -- COLUMN
dbms_output.put_line('from &&in_owner'||'.'||'&&in_table_name ;');
dbms_output.put_line('spool off');
dbms_output.put_line ('set linesize 120');
dbms_output.put_line ('set heading on');
end;
/
spool off

---------------------------------------------------------------------------

controlla il tab_name.dat





felice_pago
-




--------------------------------
Inviato via http://usenet.libero.it

[Denis Carraro]

Post by Ubi
1) Aggiungo nella parte exception la scrittura su file dei codici SQLERR e
SQLMSG. Per capire quale eccezione viene lanciata.
2) Eseguo la procedure su una vista definita sulla tabella che da errore.
Nella vista elenchi la prima metà delle colonne. SE funziona ripeti
l'operazione aggiungendo colonne, se da errore invece levi delle colonne.
Procedi fino a quando non individui la/le colonne incriminate.

Scusa l'ignoranza. ma sto prendendo mano al PL/SQL da poco.. mi potresti
postare l'esempio?

ti ringrazio 1k ...

Ciaooo

[Ender Motion]

Il parametro UTL_FILE_DIR è configurato correttamente?

[Denis Carraro]

Post by Ender Motion
Il parametro UTL_FILE_DIR è configurato correttamente?

è messo = *

[Ender Motion]

E questo va bene...

Aggiungi alla procedura, prima della linea 64, un dbms_output per verificare
le variabili in oggetto:

63: dbms_sql.column_value( l_theCursor, i, l_columnValue );
-> 64: dbms_output(l_separator || l_columnValue);
65: utl_file.put( l_output, l_separator || l_columnValue );
66: l_separator := ',';

e prima di eseguirla abilita il serveroutput:

set serveroutput on
begin
dump_table_to_csv_tkt(...);
end;
/

Penso che il problema sia dovuto ad un valore strano della variabile che vai
a scrivere.
Il messaggio di errore che ricevi è di solito legato al fatto che Oracle non
riesce o non ha il permesso di aprire il file; anche mettere una gestione di
eccezione non farebbe altro che riportarti lo stesso identico errore, visto
che UTL_FILE rilancia sempre la stessa eccezione.

Già che ci sei, prima del fclose metti anche un fflush:

utl_file.fflush( l_output );
utl_file.fclose( l_output );

Al posto di un singolo dbms_output, puoi anche provare a mettere un if per
escludere alcuni valori:

63: dbms_sql.column_value( l_theCursor, i, l_columnValue );
-> 64: if (l_columnValue is null) then
-> 65: dbms_output("valore di l_columnValue nullo, riga saltata.");
-> 66: else
-> 67: dbms_output(l_separator || l_columnValue);
68: utl_file.put( l_output, l_separator || l_columnValue );
-> 69: end if;
70: l_separator := ',';

Saluti,
Ender