Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Nov 2004
    Posts
    57

    Question Unanswered: Problem with using cursor

    I'm attempting to write a cursor that will read data from a table, reformat the data and insert it into another table. I have an existing SQL script that contains a cursor. When I copy the same cursor into my script it doesn't work. I get an error message of ORA-06512: at line 188. Line 188 contains the following "FETCH varial_fib into" I don't know where my problem is since both of the cursors contained are identical.

    Here's a copy of portions of my script: (the entire script is attached to this thread)

    SET NEWPAGE 0
    SET SPACE 0
    SET LINESIZE 80
    SET PAGESIZE 0
    SET ECHO OFF
    SET FEEDBACK OFF
    SET HEADING OFF


    DECLARE
    applik_kz CHAR(2) := 'XX';
    firmennummer CHAR(3) := '001';
    buchungsperiode CHAR(6) := '000000';
    erfassungsdatum CHAR(6) := '000000';
    erfassungsnummer CHAR(6) := '000001';
    laufende_nr CHAR(5) := '00000';
    transakt_nr CHAR(5) := '00000';

    (cont...)
    cursor varial_fib IS
    select
    LPAD(NVL(TO_CHAR(buch_jahr,'FM99999'),'0'),4,'0') || -- buch_periode
    LPAD(NVL(TO_CHAR(buch_monat,'FM999'),'0'),2,'0'),

    (cont...)
    from "MOSCA"."PFIB"
    where pfib.BELEGART = 'LAG'
    ;

    BEGIN
    OPEN varial_fib;
    LOOP
    FETCH varial_fib into
    buchungsperiode,
    erfassungsdatum,
    konto_sa,

    (cont...)
    EXIT when varial_fib%NOTFOUND;

    schreib_string :=

    applik_kz ||
    firmennummer ||
    buchungsperiode ||
    ;

    (cont...)
    insert into w100.varial_fibu values (schreib_string,'N',belegnummer,
    substr(konto_nr,2,8),substr(gegenkonto_nr,2,8),zah lungs_kond_nr,
    kst_ktr1_nr,bs_bezeichnung);
    commit;
    END LOOP;

    update w100.pfib set gebucht = 'J'
    where beleg_nr = nr_merker
    and buchungs_pos_nr = pos_merker;
    commit;
    END LOOP;
    CLOSE varial_fib;

    END;
    /
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Generally speaking, this kind of a fetch is not recommended. Causes problems such as you have. Why don't you try something like this:
    Code:
    declare
      cursor varial_fib is
        select 
          LPAD(NVL(TO_CHAR(buch_jahr,'FM99999'),'0'),4,'0') buchungsperiode,
          something_else erfassungsdatum,
        ...
        from ...;
        /* note that I don't declare variables you used to fetch data into them */
    
      schreib_string some_type;
    
    begin
      for cur_r in varial_fib loop
        schreib_string :=
          cur_r.applik_kz || cur_r.firmennummer || cur_rbuchungsperiode;
    
        insert into ...
        commit;
      end loop;
    end;
    /
    I *guess* you received an error message because there was incorrect number of fetched fields and corresponding "into" variables, or respective datatypes might be wrong, or ... something else.

    I'd suggest you to simplify the way data is manipulated and try to execute your procedure again. Let us know what happened.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Using FETCH into 37 variables really isn't a good idea. Probably you have the wrong number of variables (eg. should be 36 or 38), or some of them are in the wrong order so that it tries to fetch 10 chars into a CHAR(3) variable or whatever. (Sorry, I haven't the stamina to actually count and compare them for you!)

    Instead you should use a row variable anchored to the cursor:

    Code:
    DECLARE
      cursor varial_fib IS...;
      ...
      r_fib varial_fib%ROWTYPE;
    BEGIN
      ...
      FETCH varial_fib into r_fib;
      ...
      schreib_string := 
        applik_kz ||
        firmennummer ||
        r_fib.buchungsperiode ||
    ...

  4. #4
    Join Date
    Nov 2004
    Posts
    57

    Wink

    Maybe I'll try this another way with VB. I'm looking to write an interface between 2 systems. All I need to do really is read a tablespace and format it into a file for input into the other application. Perhaps this might be more easier to do this in VB rather than with SQL scripts.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    All you really need to do is a single INSERT statement in SQL Plus:

    insert into w100.varial_fibu (a, b, c, ...)
    select ...
    from "MOSCA"."PFIB"
    where pfib.BELEGART = 'LAG';

    Perform all the trimming and concatenation in the SELECT statement.

    Using VB is overkill, really.

  6. #6
    Join Date
    Nov 2004
    Posts
    57
    What I'm looking to do is a little bit more than just copying the data from one table an to another. I'm looking to read the data from one table and then insert it into another table field in a special format. The newly created table field is then read once more by another SQL script that writes it to a file.

    Attached are snippets of the tables that I'm using in tables.zip and the output is in output.txt

    You can see the information in the destination table varial_fibu the column "schrieb_string" that contains all the information from the pfib table. The "schrieb_string" rows are read and written into a file (output.txt)

    The values in the schrieb_string and likewise the output.txt have to adhere to a format. The file is read as a string. Example the first 2 letters of a entry is seen as "XX" stating the application this must be two letters followed directly by the "firmennummer (company number) this has to be 3 numbers and so on for 93 values. This formatting can be seen in the SQL script.

    A value might be 1 or 2 characters in the database but might need to be written into the output file as 8 characters so that the application that reads this file knows where to insert the values in to which fields into the application.

    In VB I can read the pfib table and write the output file directly and hopefully less complicated.
    Attached Files Attached Files

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I would do this without the intermediate table as a SQL script something like this:
    Code:
    set pagesize 0 linesize 200 feedback off
    
    select 'XX'||ltrim(to_char(empno,'0000000000'))||rpad(ename,20)||to_char(sal,'000000.00')
    from emp
    
    spool emp.txt
    /
    spool off
    The output file looks like this:
    Code:
    XX0000007369SMITH                000800.00                                                                                                                                                              
    XX0000007499ALLEN                001600.00                                                                                                                                                              
    XX0000007521WARD                 001250.00                                                                                                                                                              
    XX0000007566JONES                002975.00                                                                                                                                                              
    XX0000007654MARTIN               001250.00                                                                                                                                                              
    XX0000007698BLAKE                002850.00                                                                                                                                                              
    XX0000007782CLARK                002450.00                                                                                                                                                              
    XX0000007788SCOTT                003000.00                                                                                                                                                              
    XX0000007839KING                 005000.00                                                                                                                                                              
    XX0000007844TURNER               001500.00                                                                                                                                                              
    XX0000007876ADAMS                001100.00                                                                                                                                                              
    XX0000007900JAMES                000950.00                                                                                                                                                              
    XX0000007902FORD                 003000.00                                                                                                                                                              
    XX0000007934MILLER               001300.00

  8. #8
    Join Date
    Nov 2004
    Posts
    57

    Question Problem with INTO list and SELECT statement

    I've managed to widdle down my problem to being a mismatch between the SELECT statement of the cursor and the INTO list of the FETCH statment of the cursor.

    I've been looking through the two lists and cannot pinpoint the record ( or lack of) that is causing the problem. All I know is that the 2 lists are not the same and the script will not run.

    Below the cursor part of my script that is causing the problem:


    select
    LPAD(NVL(TO_CHAR(buch_jahr,'FM99999'),'0'),4,'0') || -- buch_periode
    LPAD(NVL(TO_CHAR(buch_monat,'FM999'),'0'),2,'0'),
    LPAD(NVL(TO_CHAR(erstell_jahrzehnt,'FM999'),'0'),2 ,'0') || -- erfassungsdatum
    LPAD(NVL(TO_CHAR(erstell_monat,'FM999'),'0'),2,'0' ) ||
    LPAD(NVL(TO_CHAR(erstell_tag,'FM999'),'0'),2,'0'),
    NVL(kontenart,' '), -- konto_sa
    LPAD(TO_CHAR(TO_NUMBER(konten_nr)),8,'0'), -- konto_nr
    LPAD(TO_CHAR(TO_NUMBER(konten_nr)),8,'0'), -- erl_kto
    NVL(gegenkonto_kontenart,' '), -- gegenkonto_sa
    LPAD(NVL(TO_CHAR(TO_NUMBER(gegenkonto),'FM99999999 9'),'0'),8,'0'), -- gegenkonto_nr
    LPAD(NVL(TO_CHAR(beleg_nr,'FM99999999999'),'0'),11 ,'0'), -- belegnummer
    LPAD(NVL(TO_CHAR(rechnungs_jahrzehnt,'FM999'),'0') ,2,'0') || -- beleg_datum
    LPAD(NVL(TO_CHAR(rechnungs_monat,'FM999'),'0'),2,' 0') ||
    LPAD(NVL(TO_CHAR(rechnungs_tag,'FM999'),'0'),2,'0' ),
    LPAD(NVL(TO_CHAR(valuta_jahrzehnt,'FM999'),'0'),2, '0') || -- valut_datum
    LPAD(NVL(TO_CHAR(valuta_monat,'FM999'),'0'),2,'0') ||
    LPAD(NVL(TO_CHAR(valuta_tag,'FM999'),'0'),2,'0'),
    RPAD(NVL(belegart,' '),3,' '), -- bs_bezeichnung
    NVL(soll_oder_haben,' '), -- soll_haben_sz
    buch_betrag, -- ew_vz_ermittler
    RPAD(replace(substr(to_char(abs(buch_betrag),'FM09 9999999999.99'),2,14),'.',','),14,'0'),
    RPAD(replace(nvl(substr(to_char(abs(steuerbetrag), 'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    RPAD(replace(nvl(substr(to_char(abs(skonto_betrag) ,'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    LPAD(NVL(TO_CHAR(TO_NUMBER(steuerart),'FM9999'),'0 '),3,'0'), -- steuersatznummer
    RPAD(replace(nvl(substr(to_char(abs(steuersatz),'F M0999.99'),2,6),'0'),'.',','),6,'0'),
    RPAD(NVL(laender_kenner,' '),2,' ') || -- ust_id_nummer_kd
    RPAD(NVL(ust_id_nr_rest,' '),12,' '),
    RPAD(replace(nvl(substr(to_char(abs(fremdw_betrag) ,'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    RPAD(replace(nvl(substr(to_char(abs(steuerbetrag), 'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    RPAD(replace(nvl(substr(to_char(abs(skonto_betrag) ,'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    RPAD(NVL(waehrung,' '),3,' '), -- waehrungsbez
    RPAD(NVL(TO_CHAR(beleg_nr,'FM999999999'),'0'),25,' '), -- text_fremdbelg_nr
    RPAD(NVL(buchungstext,' '),50,' '), -- buchungstext
    LPAD(NVL(FLOOR(ABS(entnahme_menge)),'0'),11,'0'), -- menge
    RPAD(NVL(mengeneinheit,' '),3,' '), -- mengen_kz
    LPAD(NVL(TO_CHAR(TO_NUMBER(zahlungsziel_kenner),'F M9999'),'0'),3,'0'), -- zahlungs_kond_nr
    LPAD(NVL(TO_CHAR(skonto_tage_1,'FM9999'),'0'),3,'0 '), -- skonto1_tage
    LPAD(NVL(TO_CHAR(skonto_tage_2,'FM9999'),'0'),3,'0 '), -- skonto2_tage
    LPAD(NVL(TO_CHAR(TO_NUMBER(kostenstelle),'FM999999 999'),'0'),8,'0'), -- kostenstelle
    LPAD(NVL(TO_CHAR(TO_NUMBER(kt_frei),'FM999999999') ,'0'),8,'0'), -- kt_frei als kostentraeger
    LPAD(NVL(TO_CHAR(netto_tage,'FM99999'),'0'),4,'0') , -- netto_faellig_tage
    buchungs_pos_nr,
    beleg_nr,
    RPAD(NVL(lieferanten_kunden_name,' '),30,' '), -- ku_li_name
    fremdw_betrag -- fw_vz_ermittler
    from "MOSCA"."PFIB"
    where gebucht <> 'J';



    BEGIN
    OPEN varial_fib;
    LOOP
    FETCH varial_fib into
    buchungsperiode,
    erfassungsdatum,
    konto_sa,
    konto_nr,
    erl_kto,
    gegenkonto_sa,
    gegenkonto_nr,
    belegnummer,
    beleg_datum,
    valuta_datum,
    bs_bezeichnung,
    soll_haben_sz,
    ew_vz_ermittler,
    betrag_ew,
    steuer_betrag_ew,
    skonto_betrag_ew,
    steuersatznummer,
    steuer_prozentsatz,
    ust_id_nummer_kd,
    betrag_fw,
    steuer_betrag_fw,
    skonto_betrag_fw,
    waehrungsbez,
    text_fremdbelg_nr,
    buchungstext,
    menge,
    mengen_kz,
    zahlungs_kond_nr,
    skonto1_tage,
    -- skonto1_prozent,
    skonto2_tage,
    -- skonto2_prozent,
    kst_ktr1_nr,
    -- k_traeger,
    netto_faellig_tage,
    pos_merker,
    nr_merker,
    ku_li_name,
    fw_vz_ermittler;

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What help are you wanting? Since it's just a matter of running one finger down the SELECT statement and another down the FETCH INTO until you find a mismatch?

    Or you could follow the advice I gave you back in my first post (and Littlefoot's) and dispense with the 37 variables by using a cursor record:
    Code:
      r_fib varial_fib%ROWTYPE;
    BEGIN
      ...
      FETCH varial_fib into r_fib;
    If you do that, the potential for the mismatch doesn't even arise!

  10. #10
    Join Date
    Nov 2004
    Posts
    57

    Question

    I tried your suggestion this time Tony but I get a new error now saying:

    " r_fib varial_fib%ROWTYPE;
    *
    ERROR at line 172:
    ORA-06550: line 172, column 4:
    PLS-00103: Encountered the symbol "R_FIB" when expecting one of the following:
    . ( * @ % & - + ; / at for mod rem <an exponent (**)> and or
    group having intersect minus order start union where connect
    ||
    The symbol ";" was substituted for "R_FIB" to continue.
    ORA-06550: line 222, column 2:
    PLS-00103: Encountered the symbol "EXIT" when expecting one of the following:
    . ( , % ; limit "


    Can you be a little bit more specific as to how I use a cursor record better?

    Here's a snippet of where I added your suggestion in my script:

    RPAD(NVL(lieferanten_kunden_name,' '),30,' '), -- ku_li_name
    fremdw_betrag -- fw_vz_ermittler
    from "MOSCA"."PFIB"
    where gebucht <> 'J'
    r_fib varial_fib%ROWTYPE;



    BEGIN
    OPEN varial_fib;
    LOOP
    FETCH varial_fib into
    r_fib
    -- buchungsperiode,
    -- erfassungsdatum,
    -- konto_sa,

  11. #11
    Join Date
    Nov 2004
    Posts
    57

    Red face

    Is what your suggesting only available in Oracle 9i? If so I'm using Oracle 8i

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    where gebucht <> 'J'
    r_fib varial_fib%ROWTYPE;
    You have missed the final semi-colon on the cursor:

    where gebucht <> 'J';
    r_fib varial_fib%ROWTYPE;

    Using a record based on a cursor makes your processing so much easier. However (and I should have mentioned this before, sorry) you do need to give every result column in the cursor a name, e.g.

    select
    LPAD(NVL(TO_CHAR(buch_jahr,'FM99999'),'0'),4,'0') || -- buch_periode
    LPAD(NVL(TO_CHAR(buch_monat,'FM999'),'0'),2,'0') as buch_periode,

    ... so that you can then reference it later on, e.g.

    r_fib.buch_periode

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Paul Izzo
    Is what your suggesting only available in Oracle 9i? If so I'm using Oracle 8i
    No, it goes back before 8i even!

  14. #14
    Join Date
    Nov 2004
    Posts
    57
    I tried your suggestions and now I get another set of error messages, but I'm not quite there yet. I get a new set of error messages now:

    cursor varial_fib IS
    *
    ERROR at line 124:
    ORA-06550: line 124, column 9:
    PLS-00402: alias required in SELECT list of cursor to avoid duplicate column
    names
    ORA-06550: line 173, column 10:
    PL/SQL: Item ignored
    ORA-06550: line 181, column 3:
    PLS-00320: the declaration of the type of this expression is incomplete or
    malformed
    ORA-06550: line 180, column 3:
    PL/SQL: SQL Statement ignored



    I'll give you guys a sample of the script so far:

    cursor varial_fib IS
    select
    LPAD(NVL(TO_CHAR(buch_jahr,'FM99999'),'0'),4,'0')a s buch_periode, --buch_periode
    LPAD(NVL(TO_CHAR(buch_monat,'FM999'),'0'),2,'0'),
    LPAD(NVL(TO_CHAR(erstell_jahrzehnt,'FM999'),'0'),2 ,'0')as erfassungsdatum, --erfassungsdatum
    LPAD(NVL(TO_CHAR(erstell_monat,'FM999'),'0'),2,'0' ) ||
    LPAD(NVL(TO_CHAR(erstell_tag,'FM999'),'0'),2,'0'),
    NVL(kontenart,' ')as konto_sa,
    LPAD(TO_CHAR(TO_NUMBER(konten_nr)),8,'0')as konto_nr, -- konto_nr
    LPAD(TO_CHAR(TO_NUMBER(konten_nr)),8,'0')as erl_kto, -- erl_kto
    NVL(gegenkonto_kontenart,' ')as gegenkonto_sa, -- gegenkonto_sa
    LPAD(NVL(TO_CHAR(TO_NUMBER(gegenkonto),'FM99999999 9'),'0'),8,'0')as egenkonto_nr, -- gegenkonto_nr
    LPAD(NVL(TO_CHAR(beleg_nr,'FM99999999999'),'0'),11 ,'0')as belegnummer, -- belegnummer
    LPAD(NVL(TO_CHAR(rechnungs_jahrzehnt,'FM999'),'0') ,2,'0')as beleg_datum, -- beleg_datum
    LPAD(NVL(TO_CHAR(rechnungs_monat,'FM999'),'0'),2,' 0') ||
    LPAD(NVL(TO_CHAR(rechnungs_tag,'FM999'),'0'),2,'0' ),
    LPAD(NVL(TO_CHAR(valuta_jahrzehnt,'FM999'),'0'),2, '0')as valut_datum, -- valut_datum
    LPAD(NVL(TO_CHAR(valuta_monat,'FM999'),'0'),2,'0') ||
    LPAD(NVL(TO_CHAR(valuta_tag,'FM999'),'0'),2,'0'),
    RPAD(NVL(belegart,' '),3,' ')as bs_bezeichnung, -- bs_bezeichnung
    NVL(soll_oder_haben,' ')as soll_haben_sz, -- soll_haben_sz
    buch_betrag as ew_vz_ermittler, -- ew_vz_ermittler
    RPAD(replace(substr(to_char(abs(buch_betrag),'FM09 9999999999.99'),2,14),'.',','),14,'0'),
    RPAD(replace(nvl(substr(to_char(abs(steuerbetrag), 'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    RPAD(replace(nvl(substr(to_char(abs(skonto_betrag) ,'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    LPAD(NVL(TO_CHAR(TO_NUMBER(steuerart),'FM9999'),'0 '),3,'0')as steuersatznummer, -- steuersatznummer
    RPAD(replace(nvl(substr(to_char(abs(steuersatz),'F M0999.99'),2,6),'0'),'.',','),6,'0'),
    RPAD(NVL(laender_kenner,' '),2,' ')as ust_id_nummer_kd, -- ust_id_nummer_kd
    RPAD(NVL(ust_id_nr_rest,' '),12,' '),
    RPAD(replace(nvl(substr(to_char(abs(fremdw_betrag) ,'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    RPAD(replace(nvl(substr(to_char(abs(steuerbetrag), 'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    RPAD(replace(nvl(substr(to_char(abs(skonto_betrag) ,'FM099999999999.99'),2,14),'0'),'.',','),14,'0'),
    RPAD(NVL(waehrung,' '),3,' ')as waehrungsbez, -- waehrungsbez
    RPAD(NVL(TO_CHAR(beleg_nr,'FM999999999'),'0'),25,' ')as text_fremdbelg_nr, -- text_fremdbelg_nr
    RPAD(NVL(buchungstext,' '),50,' ')as buchungstext, -- buchungstext
    LPAD(NVL(FLOOR(ABS(entnahme_menge)),'0'),11,'0')as menge, -- menge
    RPAD(NVL(mengeneinheit,' '),3,' ')as mengen_kz, -- mengen_kz
    LPAD(NVL(TO_CHAR(TO_NUMBER(zahlungsziel_kenner),'F M9999'),'0'),3,'0')as zahlungs_kond_nr, -- zahlungs_kond_nr
    LPAD(NVL(TO_CHAR(skonto_tage_1,'FM9999'),'0'),3,'0 ')as skonto1_tage, -- skonto1_tage
    LPAD(NVL(TO_CHAR(skonto_tage_2,'FM9999'),'0'),3,'0 ')as skonto2_tage, -- skonto2_tage
    LPAD(NVL(TO_CHAR(TO_NUMBER(kostenstelle),'FM999999 999'),'0'),8,'0')as kostenstelle, -- kostenstelle
    LPAD(NVL(TO_CHAR(TO_NUMBER(kt_frei),'FM999999999') ,'0'),8,'0')as kt_frei, -- kt_frei als kostentraeger
    LPAD(NVL(TO_CHAR(netto_tage,'FM99999'),'0'),4,'0') as netto_faellig_tage, -- netto_faellig_tage
    buchungs_pos_nr,
    beleg_nr,
    RPAD(NVL(lieferanten_kunden_name,' '),30,' ')as ku_li_name, -- ku_li_name
    fremdw_betrag -- fw_vz_ermittler
    from "MOSCA"."PFIB"
    where gebucht <> 'J';
    r_fib varial_fib%ROWTYPE;



    BEGIN
    OPEN varial_fib;
    LOOP
    FETCH varial_fib into r_fib;
    -- buchungsperiode,
    -- erfassungsdatum,
    -- konto_sa,
    -- konto_nr,
    -- erl_kto,
    -- gegenkonto_sa,
    -- gegenkonto_nr,
    -- belegnummer,
    -- beleg_datum,
    -- valuta_datum,
    -- bs_bezeichnung,
    -- soll_haben_sz,
    -- ew_vz_ermittler,
    -- betrag_ew,
    -- steuer_betrag_ew,
    -- skonto_betrag_ew,
    -- steuersatznummer,
    -- steuer_prozentsatz,
    -- ust_id_nummer_kd,
    -- betrag_fw,
    -- steuer_betrag_fw,
    -- skonto_betrag_fw,
    -- waehrungsbez,
    -- text_fremdbelg_nr,
    -- buchungstext,
    -- menge,
    -- mengen_kz,
    -- zahlungs_kond_nr,
    -- skonto1_tage,
    -- -- skonto1_prozent,
    -- skonto2_tage,
    -- -- skonto2_prozent,
    -- kst_ktr1_nr,
    -- -- k_traeger,
    -- netto_faellig_tage,
    -- pos_merker,
    -- nr_merker,
    -- ku_li_name,
    -- fw_vz_ermittler;


    EXIT when varial_fib%NOTFOUND;


    schreib_string :=

    applik_kz ||
    firmennummer ||
    buchungsperiode ||
    erfassungsdatum ||
    erfassungsnummer ||
    laufende_nr ||
    transakt_nr ||
    buchung_kz ||
    buchungs_id ||
    konto_nr ||
    gegenkonto_nr ||
    an_firmennummer ||
    von_filialnummer ||
    an_filialnummer ||
    belegnummer ||
    unter_beleg_nummer ||
    beleg_datum ||
    valuta_datum ||
    bs_bezeichnung ||
    bs_nummer_kd ||
    bs_bezeichnung_kd ||
    herkunft ||
    soll_haben_sz ||
    storno_sz ||
    betrag_ew ||
    vz_betrag_ew ||
    steuer_betrag_ew ||
    vz_steuer_betrag_ew ||
    skonto_betrag_ew ||
    vz_skonto_betrag_ew ||
    steuersatznummer ||
    steuerart ||
    steuer_prozentsatz ||
    entgelt_betrag ||
    vz_entgelt_betrag ||
    sz_skontofähig ||
    ust_id_nummer ||
    ust_id_nummer_kd ||
    werkleistungs_kz ||
    dreiecksgeschäft_kz ||
    betrag_fw ||
    vz_betrag_fw ||
    steuer_betrag_fw ||
    vz_steuer_betrag_fw ||
    skonto_betrag_fw ||
    vz_skonto_betrag_fw ||
    waehrungsnummer ||
    waehrungskurs ||
    waehrungseinheit ||
    waehrungsbez ||
    sachkonto_f_v_sa ||
    sachkonto_f_v_nr ||
    ford_verbindl_kz ||
    text_fremdbelg_nr ||
    zuordnung ||
    buchungstext ||
    zusatztext_sz ||
    menge ||
    vz_menge ||
    mengen_kz ||
    kst_ktr1_sa ||
    kst_ktr1_nr ||
    kst_ktr2_sa ||
    kst_ktr2_nr ||
    kst_ktr3_sa ||
    kst_ktr3_nr ||
    koa_kst_sa ||
    koa_kst_nr ||
    koa_ktr_sa ||
    koa_ktr_nr ||
    koa_pro_sa ||
    koa_pro_nr ||
    zahlungs_kond_nr ||
    skonto1_tage ||
    -- skonto1_prozent ||
    skonto2_Tage ||
    -- skonto2_prozent ||
    netto_faellig_tage ||
    skonto1_faellig ||
    skonto2_faellig ||
    netto_faellig ||
    skontofaehiger_betr ||
    vz_sf_betrag ||
    skonto1_betrag ||
    vz_skonto1_betrag ||
    skonto2_betrag ||
    vz_skonto2_betrag ||
    stichtag_zahlanw ||
    verrechnungs_bel_nr ||
    unter_verr_bel_nr ||
    op_kz_status ||
    op_kz_status_extern ||
    op_sperr_kz ||
    op_sperr_dat_von ||
    op_sperr_dat_bis ||
    auftrag_nr ||
    auftrags_datum ||
    lieferschein_nr ||
    lieferdatum ||
    mahnstufe ||
    datum_letzte_mahn ||
    betrag_ew_euro;

    -- insert into t100.varial_fibu values (schreib_string,'N',belegnummer,
    -- substr(konto_nr,2,8),substr(gegenkonto_nr,2,8),zah lungs_kond_nr,
    -- kst_ktr1_nr,bs_bezeichnung);
    commit;
    -- END IF;

    -- update t100.pfib set gebucht = 'J'
    -- where beleg_nr = nr_merker
    -- and buchungs_pos_nr = pos_merker;
    commit;
    END LOOP;
    CLOSE varial_fib;

    END;

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names
    This is because you need to alias the SELECT columns just like I showed 2 posts back:

    select
    LPAD(NVL(TO_CHAR(buch_jahr,'FM99999'),'0'),4,'0') || -- buch_periode
    LPAD(NVL(TO_CHAR(buch_monat,'FM999'),'0'),2,'0') as buch_periode,
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •