Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Question Unanswered: cant insert records using cursor

    please help

    i have the code below but it is not inserting the records whats wrong?

    BEGIN
    DECLARE
    CURSOR C IS
    SELECT (SUM(FLAG||AMOUNT)/12) SUM1 ,SHORT_NAME TRANS_DESCRIPTION,LEDGER_ACC
    from gen_ledger,LEDGER_MAINT
    where ledger_acc between 5000 and 8999
    AND CODE = LEDGER_ACC
    and amount is not null
    GROUP BY
    SHORT_NAME,LEDGER_ACC ;

    BEGIN



    SELECT seq_journal.NEXTVAL INTO SEQ FROM DUAL;
    REFERENCEN_NO1 := SEQ;



    for I IN C
    LOOP
    IF SUBSTR(sum1,1,1) = '-' THEN
    AMT2 := SUBSTR(sum1,2);
    FLA1 := '-';

    ELSE
    AMT2 := sum1;
    FLA1 := '+';
    END IF;

    INSERT INTO GEN_LEDGER(AMOUNT,TRANS_DESCRIPTION,trans_code,gen _ledger.created_by,
    gen_ledger.created_date,flag,effective_date,LEDGER _ACC ,referencen_no)



    VALUES (AMT2,I.TRANS_DESCRIPTION,'JV',USER,SYSDATE,FLA1,: EFFECTIVEDATE,I.LEDGER_ACC,REFERENCEN_NO1);

    END LOOP;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What does it actually mean (that you can't insert a record)? Is there any error? If so, which one?

    Another possibility is that cursor's SELECT doesn't return any records, so - there's nothing to insert.

  3. #3
    Join Date
    Mar 2011
    Posts
    4
    if i run the select statement it selects everything so the select statement selects so there is something to insert. there is no error being thrown its just that after I run this code data is not inserted into the table as expected from the insert into statement. that is what is confusing me the data is selected but is not being inserted as expected and there is no error thrown.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps you should post exact code you use. This one has (probably) one BEGIN too much, one END is missing. It would be great if you posted SQL*Plus session that displays your code (i.e. the whole PL/SQL block) and its execution. The last line should be "PL/SQL procedure successfully completed."

    When doing so, please, format it so that it easy to read. In order to preserve formatting, use [code] tags (see BB code help if you are unsure how to do that).

Posting Permissions

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