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?
joehodgeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

morphmanCommented:
You dont need an execute immediate statement for this, but if you really have to use one.

execute immediate ('select reason_code from test') bulk collect into lv_reason_code;

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joehodgeAuthor Commented:
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.
0
joehodgeAuthor Commented:
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?
0
morphmanCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.