Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    21

    Unanswered: Help using bulk collect and rownum.

    Basically I have a table with various datatypes, varchar2(255) specifically the column I am looking to bulk collect here.

    please note that this code is a work in progress, and I planned to integrate typeSQL into the dynamic sql and add input values for the rownum values.

    if i run the sql alone with the rownum, it works fine. If I do the bulk collect without the rownum, it works fine as well.

    when I combine the two i get this error and im a little stumped


    ORA-00932: inconsistent datatypes: expected - got -
    ORA-06512: at "SYSUSR.TESTPACKAGE", line 51
    ORA-06512: at line 1


    PHP Code:
    create or replace
    package testPackage is
      Type StringArray is table of varchar2
    (4000index by binary_integer;
      
    Procedure proc_test(

          
    TICKETNUM OUT StringArray 
       
    );
    end testPackage;


    create or replace
    package body testPackage is 
      Procedure proc_test
    (

          
    TICKETNUM OUT StringArray)
          
      as
      
    oSQL CLOB;
      
    iTYPE varchar2(255);
      
    typeSQL varchar2(1000); 
         
      
    Begin
        iTYPE 
    := 'ALL';

        if(
    iTYPE'ALL'then
            typeSQL 
    := '1 = 1';
        else
            
    typeSQL := 'a.TICKETID IN (select TICKETID from SPECIAL_TICKETS where TICKETID in (select column_value from table(FUNC_SPLIT(iTYPE))))';
        
    end if;
        
        
        
    oSQL := '
        select * from (select b.*, rownum rnum from(
          select
                TICKETNUM,                
            from TICKETS order by TICKETID) b where rownum <= 6) where rnum > 2'
    ;
        


        
    execute immediate oSQL bulk collect into 
          TICKETNUM
    ;
        
    end;
    end testPackage
    Last edited by fff398; 04-15-11 at 11:26.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, code you posted won't compile. oSQL variable's contents is invalid ("select ticketnum, from ...").

    As of the error:
    - TICKETNUM is of a StringArray type
    - StringArray is table of VARCHAR2(4000)s
    - You are selecting (at least) TICKETNUM, ROWNUM into it (which won't work).

    Here's an example (based on Scott's schema). Compare it with your code and, hopefully, make it work.
    Code:
    SQL> CREATE OR REPLACE PACKAGE testPackage
      2  IS
      3     TYPE StringArray IS TABLE OF VARCHAR2 (4000)
      4                            INDEX BY BINARY_INTEGER;
      5
      6     PROCEDURE proc_test (TICKETNUM OUT StringArray);
      7  END testPackage;
      8  /
    
    Package created.
    
    SQL>
    SQL> CREATE OR REPLACE PACKAGE BODY testPackage
      2  IS
      3     PROCEDURE proc_test (TICKETNUM OUT StringArray)
      4     AS
      5        oSQL   VARCHAR2 (4000);
      6     BEGIN
      7        oSQL := ' select ename
      8                  from (select b.*, rownum rnum
      9                        from (select ename
     10                              from emp
     11                              order by empno
     12                             ) b
     13                        where rownum <= 6
     14                       )
     15                  where rnum > 2';
     16
     17        EXECUTE IMMEDIATE oSQL BULK COLLECT INTO TICKETNUM;
     18     END;
     19  END testPackage;
     20  /
    
    Package body created.
    
    SQL>
    SQL> DECLARE
      2     l_out   TESTPACKAGE.STRINGARRAY;
      3  BEGIN
      4     testpackage.proc_test (l_out);
      5  END;
      6  /
    
    PL/SQL procedure successfully completed.
    
    SQL>

  3. #3
    Join Date
    Sep 2009
    Posts
    21
    thanks for the response, i deleted a bunch of elements in my code since it ended up being nest selects statements and messy to post.

    just as you suggested, rownum was the problem. Thought I could get away with just ignoring it if i didn't need that data.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You have to explicitly ignore it if you don't need it (at least, in this case).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •