FAQ
Hello everyone,

I'm trying to use flashback query to recover some data, and the
procedure I've used in the past is failing. Does anyone else see the
error in this code? The column match the table and I've used this in
the past.

declare cursor flash_recover is
select * from matl_doc_month;
matl_doc_month_rec matl_doc_month%rowtype;
begin
dbms_flashback.enable_at_time(sysdate - 60/1440);
open flash_recover;
dbms_flashback.disable;
loop
fetch flash_recover into matl_doc_month_rec;
exit when flash_recover%notfound;
insert into matl_doc_month values

(matl_doc_month.ACCT_DOC_FYP,
matl_doc_month.MATL_NUM,
matl_doc_month.PLANT,
matl_doc_month.MOVMNT_TYPE,
matl_doc_month.STOR_LOC,
matl_doc_month.RCVING_STOR_LOC,
matl_doc_month.DEBIT_CREDIT_IND,
matl_doc_month.PROFIT_CTR,
matl_doc_month.GL_ACCT_NUM,
matl_doc_month.BASE_UOM,
matl_doc_month.BASE_QTY,
matl_doc_month.TRANS_CNT,
matl_doc_month.ACCT_DOC_FY,
matl_doc_month.ACCT_DOC_FQ,
matl_doc_month.ACCT_DOC_FP,
matl_doc_month.ACCT_DOC_CY,
matl_doc_month.ACCT_DOC_CQ,
matl_doc_month.ACCT_DOC_CP,
matl_doc_month.ACCT_DOC_CYP,
matl_doc_month.SL_INV_GRP,
matl_doc_month.COST_CTR,
matl_doc_month.LOCAL_CURRENCY_AMT,
matl_doc_month.REASON_FOR_MOVMNT_IND,
matl_doc_month.LOAD_DATE);

end loop;

close flash_recover;
commit;
end;
/

Search Discussions

  • Robyn at Apr 12, 2007 at 5:06 pm
    Crisis averted ... got the data back from our replication site which
    just happened to be up for BCP testing ... the fates are smiling on us
    today :)

    However ... I'd still like to figure out what happened with the
    flashback procedure. I've used this successfully in the past so I'm
    puzzled. The error is:

    matl_doc_month.LOAD_DATE);

    *

    ERROR at line 62:
    ORA-06550: line 62, column 19:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 37, column 1:
    PL/SQL: SQL Statement ignored

    Sorry for the incomplete post earlier ... I was watching the clock run
    down on my retention window and stressing :)

    Robyn
    On 4/12/07, Robyn wrote:
    Hello everyone,

    I'm trying to use flashback query to recover some data, and the
    procedure I've used in the past is failing. Does anyone else see the
    error in this code? The column match the table and I've used this in
    the past.

    declare cursor flash_recover is
    select * from matl_doc_month;
    matl_doc_month_rec matl_doc_month%rowtype;
    begin
    dbms_flashback.enable_at_time(sysdate - 60/1440);
    open flash_recover;
    dbms_flashback.disable;
    loop
    fetch flash_recover into matl_doc_month_rec;
    exit when flash_recover%notfound;
    insert into matl_doc_month values
    (matl_doc_month.ACCT_DOC_FYP,
    matl_doc_month.MATL_NUM,
    matl_doc_month.PLANT,
    matl_doc_month.MOVMNT_TYPE,
    matl_doc_month.STOR_LOC,
    matl_doc_month.RCVING_STOR_LOC,
    matl_doc_month.DEBIT_CREDIT_IND,
    matl_doc_month.PROFIT_CTR,
    matl_doc_month.GL_ACCT_NUM,
    matl_doc_month.BASE_UOM,
    matl_doc_month.BASE_QTY,
    matl_doc_month.TRANS_CNT,
    matl_doc_month.ACCT_DOC_FY,
    matl_doc_month.ACCT_DOC_FQ,
    matl_doc_month.ACCT_DOC_FP,
    matl_doc_month.ACCT_DOC_CY,
    matl_doc_month.ACCT_DOC_CQ,
    matl_doc_month.ACCT_DOC_CP,
    matl_doc_month.ACCT_DOC_CYP,
    matl_doc_month.SL_INV_GRP,
    matl_doc_month.COST_CTR,
    matl_doc_month.LOCAL_CURRENCY_AMT,
    matl_doc_month.REASON_FOR_MOVMNT_IND,
    matl_doc_month.LOAD_DATE);
    end loop;
    close flash_recover;
    commit;
    end;
    /
    --
    http://www.freelists.org/webpage/oracle-l
  • Reidy, Ron at Apr 12, 2007 at 5:49 pm
    You are fetching into mal_doc_month_rec, but using matl_doc_month.

    --
    Ron Reidy
    Lead DBA
    Array BioPharma, Inc.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Robyn
    Sent: Thursday, April 12, 2007 11:07 AM
    To: oracle-l
    Subject: Re: Flashback query error

    Crisis averted ... got the data back from our replication site which
    just happened to be up for BCP testing ... the fates are smiling on us
    today :)

    However ... I'd still like to figure out what happened with the
    flashback procedure. I've used this successfully in the past so I'm
    puzzled. The error is:

    matl_doc_month.LOAD_DATE);
    *
    ERROR at line 62:
    ORA-06550: line 62, column 19:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 37, column 1:
    PL/SQL: SQL Statement ignored

    Sorry for the incomplete post earlier ... I was watching the clock run
    down on my retention window and stressing :)

    Robyn
    On 4/12/07, Robyn wrote:
    Hello everyone,

    I'm trying to use flashback query to recover some data, and the
    procedure I've used in the past is failing. Does anyone else see the
    error in this code? The column match the table and I've used this in
    the past.

    declare cursor flash_recover is
    select * from matl_doc_month;
    matl_doc_month_rec matl_doc_month%rowtype; begin
    dbms_flashback.enable_at_time(sysdate - 60/1440);
    open flash_recover;
    dbms_flashback.disable;
    loop
    fetch flash_recover into matl_doc_month_rec;
    exit when flash_recover%notfound;
    insert into matl_doc_month values
    (matl_doc_month.ACCT_DOC_FYP,
    matl_doc_month.MATL_NUM,
    matl_doc_month.PLANT,
    matl_doc_month.MOVMNT_TYPE,
    matl_doc_month.STOR_LOC,
    matl_doc_month.RCVING_STOR_LOC,
    matl_doc_month.DEBIT_CREDIT_IND,
    matl_doc_month.PROFIT_CTR,
    matl_doc_month.GL_ACCT_NUM,
    matl_doc_month.BASE_UOM,
    matl_doc_month.BASE_QTY,
    matl_doc_month.TRANS_CNT,
    matl_doc_month.ACCT_DOC_FY,
    matl_doc_month.ACCT_DOC_FQ,
    matl_doc_month.ACCT_DOC_FP,
    matl_doc_month.ACCT_DOC_CY,
    matl_doc_month.ACCT_DOC_CQ,
    matl_doc_month.ACCT_DOC_CP,
    matl_doc_month.ACCT_DOC_CYP,
    matl_doc_month.SL_INV_GRP,
    matl_doc_month.COST_CTR,
    matl_doc_month.LOCAL_CURRENCY_AMT,
    matl_doc_month.REASON_FOR_MOVMNT_IND,
    matl_doc_month.LOAD_DATE);
    end loop;
    close flash_recover;
    commit;
    end;
    /
    --
    http://www.freelists.org/webpage/oracle-l

    This electronic message transmission is a PRIVATE communication which contains
    information which may be confidential or privileged. The information is intended
    to be for the use of the individual or entity named above. If you are not the
    intended recipient, please be aware that any disclosure, copying, distribution
    or use of the contents of this information is prohibited. Please notify the
    sender of the delivery error by replying to this message, or notify us by
    telephone (877-633-2436, ext. 0), and then delete it from your system.

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Apr 12, 2007 at 6:02 pm
    Robyn,

    matl_doc_month is the table, right? And matl_doc_month_rec is the
    record type?

    So, in the insert statement, in the values clause, shouldn't each column
    name be prefixed with "matl_doc_month_rec", i.e., the record type, and
    not "matl_doc_month", which is the table?

    -Mark

    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest/CSA

    "There are 10 types of people in the world: Those who understand
    binary, and those who don't."

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Robyn
    Sent: Thursday, April 12, 2007 1:07 PM
    To: oracle-l
    Subject: Re: Flashback query error

    Crisis averted ... got the data back from our replication site which
    just happened to be up for BCP testing ... the fates are smiling on us
    today :)

    However ... I'd still like to figure out what happened with the
    flashback procedure. I've used this successfully in the past so I'm
    puzzled. The error is:

    matl_doc_month.LOAD_DATE);
    *
    ERROR at line 62:
    ORA-06550: line 62, column 19:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 37, column 1:
    PL/SQL: SQL Statement ignored

    Sorry for the incomplete post earlier ... I was watching the clock run
    down on my retention window and stressing :)

    Robyn
    On 4/12/07, Robyn wrote:
    Hello everyone,

    I'm trying to use flashback query to recover some data, and the
    procedure I've used in the past is failing. Does anyone else see the
    error in this code? The column match the table and I've used this in
    the past.

    declare cursor flash_recover is
    select * from matl_doc_month;
    matl_doc_month_rec matl_doc_month%rowtype; begin
    dbms_flashback.enable_at_time(sysdate - 60/1440);
    open flash_recover;
    dbms_flashback.disable;
    loop
    fetch flash_recover into matl_doc_month_rec;
    exit when flash_recover%notfound;
    insert into matl_doc_month values
    (matl_doc_month.ACCT_DOC_FYP,
    matl_doc_month.MATL_NUM,
    matl_doc_month.PLANT,
    matl_doc_month.MOVMNT_TYPE,
    matl_doc_month.STOR_LOC,
    matl_doc_month.RCVING_STOR_LOC,
    matl_doc_month.DEBIT_CREDIT_IND,
    matl_doc_month.PROFIT_CTR,
    matl_doc_month.GL_ACCT_NUM,
    matl_doc_month.BASE_UOM,
    matl_doc_month.BASE_QTY,
    matl_doc_month.TRANS_CNT,
    matl_doc_month.ACCT_DOC_FY,
    matl_doc_month.ACCT_DOC_FQ,
    matl_doc_month.ACCT_DOC_FP,
    matl_doc_month.ACCT_DOC_CY,
    matl_doc_month.ACCT_DOC_CQ,
    matl_doc_month.ACCT_DOC_CP,
    matl_doc_month.ACCT_DOC_CYP,
    matl_doc_month.SL_INV_GRP,
    matl_doc_month.COST_CTR,
    matl_doc_month.LOCAL_CURRENCY_AMT,
    matl_doc_month.REASON_FOR_MOVMNT_IND,
    matl_doc_month.LOAD_DATE);
    end loop;
    close flash_recover;
    commit;
    end;
    /
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Jason Heinrich at Apr 12, 2007 at 6:22 pm
    It looks like Ron found your problem, but I was wondering what version you
    are using? In 9.2 and higher you could do something like the following,
    which is a whole lot shorter and less error-prone:

    insert into matl_doc_month (select * from matl_doc_month as of timestamp
    (sysdate - 60/1440));
    On 4/12/07 12:06 PM, Robyn wrote:

    Crisis averted ... got the data back from our replication site which
    just happened to be up for BCP testing ... the fates are smiling on us
    today :)

    However ... I'd still like to figure out what happened with the
    flashback procedure. I've used this successfully in the past so I'm
    puzzled. The error is:

    matl_doc_month.LOAD_DATE);
    *
    ERROR at line 62:
    ORA-06550: line 62, column 19:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 37, column 1:
    PL/SQL: SQL Statement ignored

    Sorry for the incomplete post earlier ... I was watching the clock run
    down on my retention window and stressing :)

    Robyn
    Jason Heinrich
    Oracle Database Administrator
    Pensacola Christian College
  • Robyn at Apr 12, 2007 at 6:18 pm
    Thanks Alberto but the problem has been solved. The error was:

    ERROR at line 62:
    ORA-06550: line 62, column 19:
    PL/SQL: ORA-00984: column not allowed here
    ORA-06550: line 37, column 1:
    PL/SQL: SQL Statement ignored

    And the statement should have been:

    declare
    cursor flash_recover is
    select * from matl_doc_month;
    matl_doc_month_rec matl_doc_month%rowtype;
    begin
    dbms_flashback.enable_at_time(sysdate - 60/1440);
    open flash_recover;
    dbms_flashback.disable;
    loop
    fetch flash_recover into matl_doc_month_rec;
    exit when flash_recover%notfound;
    insert into matl_doc_month
    values

    (matl_doc_month_rec.ACCT_DOC_FYP,
    matl_doc_month_rec.MATL_NUM,
    matl_doc_month_rec.PLANT,
    matl_doc_month_rec.MOVMNT_TYPE,
    matl_doc_month_rec.STOR_LOC,
    matl_doc_month_rec.RCVING_STOR_LOC,
    matl_doc_month_rec.DEBIT_CREDIT_IND,
    matl_doc_month_rec.PROFIT_CTR,
    matl_doc_month_rec.GL_ACCT_NUM,
    matl_doc_month_rec.BASE_UOM,
    matl_doc_month_rec.BASE_QTY,
    matl_doc_month_rec.TRANS_CNT,
    matl_doc_month_rec.ACCT_DOC_FY,
    matl_doc_month_rec.ACCT_DOC_FQ,
    matl_doc_month_rec.ACCT_DOC_FP,
    matl_doc_month_rec.ACCT_DOC_CY,
    matl_doc_month_rec.ACCT_DOC_CQ,
    matl_doc_month_rec.ACCT_DOC_CP,
    matl_doc_month_rec.ACCT_DOC_CYP,
    matl_doc_month_rec.SL_INV_GRP,
    matl_doc_month_rec.COST_CTR,
    matl_doc_month_rec.LOCAL_CURRENCY_AMT,
    matl_doc_month_rec.REASON_FOR_MOVMNT_IND,
    matl_doc_month_rec.LOAD_DATE);

    end loop;
    close flash_recover;
    commit;
    end;
    /
    On 4/12/07, Alberto Dell'Era wrote:
    You forgot to post the error code ... is it perhaps ORA-1466 ?

    Alberto Dell'Era
    "dulce bellum inexpertis"
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 12, '07 at 4:12p
activeApr 12, '07 at 6:22p
posts6
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase