[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-10-25
4
Medium Priority
?
1,866 Views
Last Modified: 2011-09-20
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?
0
Comment
Question by:joehodge
  • 2
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
morphman earned 750 total points
ID: 12401667
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
 

Author Comment

by:joehodge
ID: 12408259
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
 

Author Comment

by:joehodge
ID: 12408858
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
 
LVL 6

Expert Comment

by:morphman
ID: 12408966
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question