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

Posted on 2004-10-25
Last Modified: 2011-09-20

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

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

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?
Question by:joehodge
    LVL 6

    Accepted Solution

    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;


    Author Comment


    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;


                            OPEN cv FOR lv_sql;
                            FETCH cv INTO out_tab(1).lv_col,
                                        EXIT WHEN cv%NOTFOUND;
                                               lv_no_rows := lv_no_rows +1;
                                                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;

                                  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.

    Author Comment


    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?
    LVL 6

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    SQL Query 6 53
    Oracle 12c 10 83
    Left Justify field in Oracle 6 53
    oracle function to get percentge of shelf life remaining 17 34
    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now