execute immediate


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: execute immediate

  1. #1
    Ervin Rodriguez Guest

    execute immediate


    Hello there, i have an anonymous block that i am trying to execute. This
    block is using EXECUTE IMMEDIATE command to execute queries that are build
    dynamically. When i run the block, i get the following error.

    ERROR at line 1:
    ORA-20101: ORA-00600: internal error code, arguments: [12261], [], [], [],
    [],
    [], [], []
    ORA-06512: at line 38


    When ran the select statement by itself,i get the dynamic queries. When
    i execute those queries, they return one single row. I don't know why EXECUTE
    IMMEDIATE is not working. Can somebody help? thanks

    my program is listed below:

    declare
    cursor get_data IS
    select 'select * from subp.' || m.table_nm
    || ' where protcl_id = ' || d.protcl_id ||
    ' and protcl_stage = $STAGE and bsln_nmbr = ' || d.bsln_nmbr || '
    and merck_actl_visit_nmbr = ' || '''' ||
    d.merck_actl_visit_nmbr || '''' || ' and rpt_seq = ' || d.rpt_seq
    ||
    ' and qstn_group_id = ' || d.qstn_group_id || ' and nvl(parent_rpt_seq_nmbr,
    -1) = ' ||
    nvl(d.PRNT_QSTN_GROUP_RPT_SEQ, -1) ||
    ' and ' || q.oracle_clmn_nm || '_unit = '|| q.oracle_clmn_nm || '_unit_term'
    l_query,
    trunc(d.eff_dt) eff_dt
    from mvm_tables m , extracts e , questions q , data_points_tbl d
    where d.protcl_id = 10869065
    and q.qstn_id = d.qstn_id
    and q.uom_reqr_flag = 'Y'
    and m.protcL_id = d.protcl_id
    and e.protcl_id = d.protcl_id
    and e.qstn_group_id = d.qstn_group_id
    and e.qstn_id = d.qstn_id
    and m.table_id = e.table_id
    and d.exprtn_dt is null ;

    BEGIN
    FOR rec1 IN get_data LOOP


    if (rec1.eff_dt >= to_date('06-03-2002', 'MM-DD-YYYY') AND rec1.eff_dt
    < to_date('06-30-2002', 'MM-DD-YYYY') ) then


    BEGIN
    EXECUTE IMMEDIATE rec1.l_query;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    null;
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20101, SQLERRM);

    END;
    end if;
    END LOOP;
    END;
    /

  2. #2
    Boris Milrud Guest

    Re: execute immediate


    Ervin,

    First of all, your EXECUTE IMMEDIATE statement does not have INTO clause,
    which makes no sense in case of query execution: you want to get something
    back, right?!

    Second, I don't have your tables with the data. So, I would suggest to comment
    EXECUTE IMMEDIATE out, and add the following line for debugging:

    dbms_output.put_line(rec1.l_query);

    Then, you would see exactly the SQL you are trying to execute and why it
    is failing.

    Boris.

    "Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >
    >Hello there, i have an anonymous block that i am trying to execute. This
    >block is using EXECUTE IMMEDIATE command to execute queries that are build
    >dynamically. When i run the block, i get the following error.
    >
    >ERROR at line 1:
    >ORA-20101: ORA-00600: internal error code, arguments: [12261], [], [], [],
    >[],
    >[], [], []
    >ORA-06512: at line 38
    >
    >
    >When ran the select statement by itself,i get the dynamic queries. When
    >i execute those queries, they return one single row. I don't know why EXECUTE
    >IMMEDIATE is not working. Can somebody help? thanks
    >
    >my program is listed below:
    >
    >declare
    >cursor get_data IS
    >select 'select * from subp.' || m.table_nm
    > || ' where protcl_id = ' || d.protcl_id ||
    > ' and protcl_stage = $STAGE and bsln_nmbr = ' || d.bsln_nmbr ||

    '
    >and merck_actl_visit_nmbr = ' || '''' ||
    > d.merck_actl_visit_nmbr || '''' || ' and rpt_seq = ' || d.rpt_seq
    >||
    > ' and qstn_group_id = ' || d.qstn_group_id || ' and nvl(parent_rpt_seq_nmbr,
    >-1) = ' ||
    > nvl(d.PRNT_QSTN_GROUP_RPT_SEQ, -1) ||
    > ' and ' || q.oracle_clmn_nm || '_unit = '|| q.oracle_clmn_nm || '_unit_term'
    >l_query,
    > trunc(d.eff_dt) eff_dt
    > from mvm_tables m , extracts e , questions q , data_points_tbl d
    > where d.protcl_id = 10869065
    > and q.qstn_id = d.qstn_id
    > and q.uom_reqr_flag = 'Y'
    > and m.protcL_id = d.protcl_id
    > and e.protcl_id = d.protcl_id
    > and e.qstn_group_id = d.qstn_group_id
    > and e.qstn_id = d.qstn_id
    > and m.table_id = e.table_id
    > and d.exprtn_dt is null ;
    >
    >BEGIN
    > FOR rec1 IN get_data LOOP
    >
    >
    > if (rec1.eff_dt >= to_date('06-03-2002', 'MM-DD-YYYY') AND rec1.eff_dt
    >< to_date('06-30-2002', 'MM-DD-YYYY') ) then
    >
    >
    > BEGIN
    > EXECUTE IMMEDIATE rec1.l_query;
    >
    > EXCEPTION
    > WHEN NO_DATA_FOUND THEN
    > null;
    > WHEN OTHERS THEN
    > RAISE_APPLICATION_ERROR(-20101, SQLERRM);
    >
    > END;
    > end if;
    > END LOOP;
    >END;
    >/



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center