Writing to a text file from PL/SQL
Jasna Prester
can anyone please help me with declaration problems.
I'm trying to retrive data from an Oracle table into a text file by the sql
procedure wich is atached to this mail but I get following declaration
mistakes:
Input truncated to 1 characters
f TEXT_IO.FILE_TYPE;
*
ERROR at line 16:
ORA_0650:line 16,column 10:
PLS-00201: Identifier 'TEXT_TO.FILE_TYPE' must be declared
ORA-06550: line 16, column 10:
PL/SQL: Item ignored
ORA-06550: line 41, column 5:
PLS-00320: the declaration of type of this expresion is incomplete or
malformed
ORA-06550: line 41, column 5:
PL/SQL: Statement ignored
ORA-06550: line 44, column 7:
PLS-00201: identifier 'LP_ERR.ERR_POSTAVI_MODAL' must be declared
ORA-06550: line 44, column 7:
PL/SQL: Statement ignored
ORA-06550: line 45, column 13:
PLS-00201: identifier 'FORM_TRIGGER_FAILURE' must be declared
PL/SQL: Statement ignored
ORA-06550: line 79, column 24:
PLS-00320: the declaration of type of this expresion is incomplete or
malformed
ORA-06550: line 79, column 7:
PL/SQL: Statement ignored
ORA-06550: line 89, column 18:
PLS-00320: the declaration of type of this expresion is incomplete or
malformed
ORA-06550: line 89, column 3:
PL/SQL: Statement ignored
The procedure by wich I'm trying to do it from is:
declare
cursor c is select a.inv_broj, b.oj oj, c.kto_pr1 kto1, b.naziv,
b.marka, b.oznaka, b.dat_nab, b.ser_broj, b.god_proiz,
b.snaga, b.kapacitet, b.regist,
sum(nvl(a.nab_vr,0)) nab, sum(nvl(a.aku_am,0)) aku,
sum(nvl(a.amort_1,0)) amort
from OS310 a, os050 b, os030 c
where a.pod = 'V1' and a.gg = '2002' and
b.pod (+)= a.pod and b.inv_broj (+)= a.inv_broj and
c.gr_pod (+)= b.gr_pod and c.gr_amort (+)= b.gr_amort
group by a.inv_broj, b.oj , c.kto_pr1 , b.naziv,
b.marka, b.oznaka, b.dat_nab, b.ser_broj, b.god_proiz,
b.snaga, b.kapacitet, b.regist;
f TEXT_IO.FILE_TYPE;
f_name varchar2(30);
i integer;
v_sif varchar2(7);
v_naz varchar2(20);
v_dat varchar2(6);
v_kto varchar2(6);
v_ozn varchar2(20);
v_marka varchar2(20);
v_tvor varchar2(12);
v_god varchar2(2);
v_snaga varchar2(4);
v_kap varchar2(5);
v_reg varchar2(10);
v_oj varchar2(2);
v_nab varchar2(13);
v_aku varchar2(13);
v_sad varchar2(13);
v_am varchar2(13);
v_slog varchar2(170);
BEGIN
f_name := 'c:\temp\osmat.txt';
begin
f := TEXT_IO.fopen(f_name, 'W');
exception
when others then
LP_ERR.ERR_POSTAVI_MODAL(f_name,sqlcode,sqlerrm);
raise form_trigger_failure;
end;
i := 1;
--
for c_rec in c loop
-- if c_rec.amort = 0 then goto dalje; end if; /* sva sredstva */
v_sif := rpad(c_rec.inv_broj,7,' ');
v_KTO := rpad(c_rec.kto1,6,' ');
v_oj := rpad(nvl(c_rec.oj,' '),2,' ');
v_naz := rpad(c_rec.naziv,20,' ');
v_dat := to_char(c_rec.dat_nab,'YYMMDD');
if c_rec.god_proiz is null then
v_god := ' ';
else
v_god := substr(c_rec.god_proiz,3,2);
end if;
v_marka := rpad(nvl(c_rec.marka,' '),20,' ');
v_ozn := rpad(nvl(c_rec.oznaka,' '),20,' ');
v_tvor := rpad(nvl(c_rec.ser_broj,' '),12,' ');
v_snaga := rpad(nvl(c_rec.snaga,' '),4,' ');
v_kap := rpad(nvl(c_rec.kapacitet,' '),5,' ');
v_reg := rpad(nvl(c_rec.regist,' '),10,' ');
v_nab := to_char(c_rec.nab * 100);
v_nab := lpad(v_nab,13,'0');
v_aku := to_char(c_rec.aku * 100);
v_aku := lpad(v_aku,13,'0');
v_sad := to_char((c_rec.nab - c_rec.aku) * 100);
v_sad := lpad(v_sad,13,'0');
v_am := to_char(c_rec.amort * 100);
v_am := lpad(v_am,13,'0');
v_slog := v_sif || v_kto || v_oj || v_naz || v_marka || v_ozn || v_dat
||
v_tvor || v_god || ' ' || v_snaga || ' ' || v_kap || v_reg ||
v_nab || v_aku || v_sad || v_am;
begin
TEXT_IO.put_line(f,v_slog);
exception
when others then
app.ServerExceptionHandler('Zapis sloga u datoteku!');
raise;
end;
<<dalje>>
i := i + 1;
end loop;
--
TEXT_IO.fclose(f);
END;
/
Greateful in advance,
Jasna Prester jpre...@viadukt.hinet.hr
Andrew Hardy
> I'm trying to retrive data from an Oracle table into a text file by the
sql
> procedure wich is atached to this mail but I get following declaration
> mistakes:
>
> Input truncated to 1 characters
> f TEXT_IO.FILE_TYPE;
Text_IO is an Oracle Forms package. If you want to do file IO on the
server/sql*plus have a look at the UTL_FILE package.
Andy
Jasna Prester
Where do I find UTL_FILE package?
Thanks again,
Jasna
"Andrew Hardy" <nob...@spam.from.news.AdvanticaTech.com> wrote in message
news:ahgd6a$c11$1...@sun-cc204.lut.ac.uk...
Andrew Hardy
> Thank you Andy for your quick answer.
>
> Where do I find UTL_FILE package?
It's a built-in package.
Andy
Jeff Smith
"Jasna Prester" <jasna....@viadukt.tel.hr> wrote in message
news:ahgeh9$74j9$1...@as201.hinet.hr...
Daniel Morgan
Also
desc UTL_FILE
and
SELECT *
FROM all_arguments
WHERE package_name = 'UTL_FILE'
and look at the following files in $ORACLE_HOME\rdbms\admin
dbmslmd.sql
utlfile.sql
Daniel Morgan
Niall Litchfield
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Andrew Hardy" <nob...@spam.from.news.AdvanticaTech.com> wrote in message
news:ahgfe4$cg5$1...@sun-cc204.lut.ac.uk...
Álvaro Palma Aste
>Jasna,
>Andy
Or you can do it using dbms_output instructions. You only have to
put the lines:
set serveroutput on size 1000000 -- (linit in bytes for the
output file)
spool <output_file_name>
declare
....
begin
...
dbms_output.put_line('Hello, I am an example');
end;
--
Atte.
Álvaro Palma Aste
Grupo de Ing. Biomédica
Depto. de Ing. Eléctrica - U. de Chile
******************************************************
It's the only form of protest they're allowed
I've seen their silent faces scream so loud
If they were to speak these words they'd go missing too
Another woman on a torture table what else can they do
Sting - They Dance Alone...
Tim Cross
> Andrew Hardy escribió:
> >Jasna,
>
> >> I'm trying to retrive data from an Oracle table into a text file by the
> >sql
> >> procedure wich is atached to this mail but I get following declaration
> >> mistakes:
> >>
>
> Or you can do it using dbms_output instructions. You only have to
> put the lines:
>
>
True, but don't forget DBMS_OUTPUT has some major restrictions -
1. Lines cannot be longer than 255 charcters
2. The total amount of data cannot exceed 1Mb
3. Nothing is output until after the script/block has executed and if
there is an exception, you may not see any data at all, plus you
must use the size option to the set serveroutput or
DBMS_OUTPUT.ENAMBLE() procedure to set it to the maximum 1Mb size.
From memory, I think DBMS_OUTPUT may also trim leading/trailing white
space - so any formatting may get modified/lost - not sure about this
though, I just recall something I encountered where the output was
modified by DBMS_OUTPUT.
I think DBMS_OUTPUT is really only useful as a debugging tool and you
are much better off using UTL_FILE.
Tim
--
Tim Cross E-Mail: tcr...@pobox.une.edu.au
Analyst/Programmer Phone: 6773 3210
Applications Group Mobile: 0412 969193
University of New England
---
find / -iname microsoft -exec rm -rf {} \;
Christop...@oracle.comx
> Álvaro Palma Aste <queria_d...@el.perla.invalid> writes:
> From memory, I think DBMS_OUTPUT may also trim leading/trailing white
> space - so any formatting may get modified/lost - not sure about this
> though, I just recall something I encountered where the output was
> modified by DBMS_OUTPUT.
In SQL*Plus, SET SERVEROUTPUT syntax has an option FOR[MAT]
{WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]} to control formatting. See
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a90842/ch13.htm#1012919
Chris
--
Christopher Jones, Oracle Corporation, Australia.
Jasna Prester
<Christop...@oracle.comX> wrote in message
news:u8z43k...@oracle.comX...
Jasna Prester
I wrote this sql procedure:
set serveroutput on size 100000
spool c:\temp\via2.txt
declare
f varchar2(30);
f:='hello';
dbms_output.put_line(f);
spool off;
end;
The file gets created but it stays empty - what am I doing wrong?
Thanks very much for help in advence,
Jasna Prester
Álvaro Palma Aste
>Please help me out one more time,
>I wrote this sql procedure:
>set serveroutput on size 100000
>spool c:\temp\via2.txt
>declare
>f varchar2(30);
BEGIN
>f:='hello';
>dbms_output.put_line(f);
>spool off; ----------------->you don´t need the spool off;
>end;
If this doesn´t work, try adding
dbms_output.enable(); at the beginning
Tim Cross
> Please help me out one more time,
> I wrote this sql procedure:
>
> set serveroutput on size 100000
> spool c:\temp\via2.txt
> declare
> f varchar2(30);
> f:='hello';
> dbms_output.put_line(f);
> spool off;
> end;
>
Try -
set serveroutput on size 100000
spool c:\temp\via2.txt
declare
f varchar2(30);
begin
f:='hello';
dbms_output.put_line(f);
end;
/
spool off
Tim