Writing to a text file from PL/SQL

From: Jasna Prester <jasna.prester_at_viadukt.tel.hr>
Date: Mon, 22 Jul 2002 09:03:29 +0200
Message-ID: <ahgb19$62b9$2@as201.hinet.hr>

Hi there,
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 jprester_at_viadukt.hinet.hr Received on Mon Jul 22 2002 - 02:03:29 CDT