asked on

ORA-03001 when running execute immeadiate bulk collect through a plsql page

Hi,

Im trying to run an execute immediate statement in a web based oracle system.

declare
type varcol is table of VARCHAR(50) index by binary_integer;
lv_reason_code varcol;

begin
.......
EXECUTE IMMEDIATE 'select reason_code bulk collect INTO lv_reason_codeFROM test';

this gives me:

The following error occured :
ORA-03001: unimplemented feature

any ideas of whats happening here and how to resolve?

ASKER CERTIFIED SOLUTION

morphman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joehodge

ASKER

hi,

the original code:

TYPE rec_column IS RECORD (lv_col VARCHAR2(32767));
TYPE tab_column IS TABLE OF rec_column INDEX BY BINARY_INTEGER;

TYPE cv_type IS REF CURSOR;
cv cv_type;

out_tab tab_column;

begin
................

                        OPEN cv FOR lv_sql;
                        LOOP
                        FETCH cv INTO out_tab(1).lv_col,
                                             out_tab(2).lv_col,
                                             out_tab(3).lv_col,
                                           out_tab(4).lv_col,
                                           out_tab(5).lv_col,
                                           out_tab(6).lv_col,
                                           out_tab(7).lv_col,
                                           out_tab(8).lv_col,
                                           out_tab(9).lv_col,
                                           out_tab(10).lv_col,
                                           out_tab(11).lv_col,
                                             out_tab(12).lv_col,
                                             out_tab(13).lv_col,
                                           out_tab(14).lv_col,
                                           out_tab(15).lv_col,
                                           out_tab(16).lv_col,
                                           out_tab(17).lv_col,
                                           out_tab(18).lv_col,
                                           out_tab(19).lv_col,
                                           out_tab(20).lv_col,
                                           out_tab(21).lv_col,
                                             out_tab(22).lv_col,
                                             out_tab(23).lv_col,
                                           out_tab(24).lv_col,
                                           out_tab(25).lv_col,
                                           out_tab(26).lv_col,
                                           out_tab(27).lv_col,
                                           out_tab(28).lv_col,
                                           out_tab(29).lv_col,
                                           out_tab(30).lv_col,
                                           out_tab(31).lv_col;
                                    EXIT WHEN cv%NOTFOUND;


                                           lv_no_rows := lv_no_rows +1;
                                          htp.tabledata(htf.tablerowopen);
                                           FOR x IN 1..lv_no_cols LOOP
                                                  htp.tabledata(htf.bold(NVL(out_tab(x).lv_col,CHR(0160)), cattributes => 'style="font-size:10" align-center'));
                                           END LOOP;
                                           htp.tabledata(htf.tablerowclose);

                              END LOOP;

on a medium-large table took a couple of minutes to execute so I thought that doing an execute immediate would speed things up.

the select statement was just a test and it could be a select statement of 30 columns.

joehodge

ASKER

Hi,

EXECUTE IMMEDIATE (lv_sql) bulk collect INTO lv_tab1, lv_tab2, lv_tab3, lv_tab4, lv_tab5, lv_tab6, lv_tab7, lv_tab8, lv_tab9, lv_tab10, lv_tab11, lv_tab12, lv_tab13, lv_tab14,lv_tab15,lv_tab16,lv_tab17,lv_tab18,lv_tab19,lv_tab20,lv_tab21,lv_tab22,lv_tab23,lv_tab24,lv_tab25,lv_tab26,lv_tab27,lv_tab28,lv_tab29,lv_tab30 ;

works but then I cant do a forall?
forall i in lv_tab1.first..lv_tab1.last
      htp.tabledata(htf.bold(NVL(lv_tab1(i),CHR(0160)), cattributes => 'style="font-size:10" align-center'));
I get

PLS-00103: Encountered the symbol "HTP" when expecting one of the following:

. ( * @ % & - + / at mod rem select update with
<an exponent (**)> delete insert || execute save merge
The symbol ".

the following works but doews not improve performance

FOR x IN lv_tab1.first..lv_tab1.last LOOP
             htp.p('<TR class="tablerow0B">');
             htp.tabledata(htf.bold(NVL(lv_tab1(x),CHR(0160)), cattributes => 'style="font-size:10" align-center'));
             htp.tabledata(htf.bold(NVL(lv_tab2(x),CHR(0160)), cattributes => 'style="font-size:10" align-center'));
            ...........

as the table has 20000 records this just causes the page to hang

could I do a forall into a blob and then output                         this to the screen or am I barking up the wrong tree?

morphman

hmm, the FORALL statement is usually used to send bulk operations to the sql engine. This is not what you are doing so wouldnt speed anything up.