execute immediate

[Ervin Rodriguez]

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;
/

[Boris Milrud]

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;
>/