5 Replies Latest reply: Sep 2, 2012 9:24 PM by rp0428 RSS

    User definied aggregate function for varray of number

    959451
      Hello!

      I am trying to write some aggregate function for the varray and I get this error code when I'm trying to use it:

      ORA-00600 internal error code, arguments: [kodpunp1], [], [], [], [], [], [], [], [], [], [], []
      [koxsihread1], [0], [3989], [45778], [], [], [], [], [], [], [], []

      Code of the function is really simple(in fact it does nothing ):
      create or replace
      TYPE "TEST_VECTOR" as varray(10) of varchar(20)
       ALTER TYPE "TEST_VECTOR" MODIFY LIMIT 4000 CASCADE
      
      create or replace
      type Test as object(
        lastVector TEST_VECTOR,
        STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number,
      MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in TEST_VECTOR) return number  ,
      MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number,
      MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT TEST_VECTOR, flags IN number) return number
      );
      
      create or replace
      type body Test is
        STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number is
        begin
          sctx := Test(TEST_VECTOR());
          return ODCIConst.Success;
        end;
      MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in TEST_VECTOR) return number  is
      begin
        self.lastVector := value;
        return ODCIConst.Success;
      end;
      MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number is 
      begin
       return ODCIConst.Success;
      end;
      MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT TEST_VECTOR, flags IN number) return number is
      begin
        returnValue := self.lastVector;
       return ODCIConst.Success;
      end;
      end;
      
      create or replace
      FUNCTION test_fn (input TEST_VECTOR) RETURN TEST_VECTOR 
      PARALLEL_ENABLE AGGREGATE USING Test;
      Version of DBMS I use is: 11.2.0.3.0
      What can be the reason of this error? What to do in order to solve it?
      Thanks in advance for any help.

      Edited by: 956448 on 2012-09-01 10:07

      Edited by: 956448 on Sep 2, 2012 4:06 PM - code correction

      Edited by: 956448 on Sep 2, 2012 5:02 PM
        • 1. Re: User definied aggregate function for varray of number
          sb92075
          956448 wrote:
          Hello!

          I am trying to write some aggregate function for the varray and I get this error code when I'm trying to use it:

          ORA-00600 internal error code, arguments: [kodpunp1], [], [], [], [], [], [], [], [], [], [], []
          [koxsihread1], [0], [3989], [45778], [], [], [], [], [], [], [], []
          ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support
          • 2. Re: User definied aggregate function for varray of number
            rp0428
            >
            Code of the function is really simple(in fact it does nothing ):
            >
            Really simple and really wrong. Thanks for posting the code but the code you posted has errors and won't compile. If you had fixed these you would not have had the problem.
            create or replace
            TYPE "test_vector" as varray(10) of Number
             ALTER TYPE "test_vector" MODIFY LIMIT 4000 CASCADE
            The double quotes makes the name case sensitive; remove the double quotes. And why are you altering the type? Just define it the way you want to begin with.
            >
            create or replace
            type Test as object(
            lastVector test_vector,
            STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number,
            MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in ) return number ,
            >
            test_vector specified without quotes will get converted to upper case and is thus referencing TEST_VECTOR which isn't defined because you used double quotes around the type when you defined it.

            The iterate function has a second parameter of 'value in' but doesn't specify the datatype.
            >
            create or replace
            FUNCTION test_vector (input test_vector) RETURN test_vector
            PARALLEL_ENABLE AGGREGATE USING Test;
            >
            Why are you using 'test_vector' for the function name when you already used "test_vector" for the type name? The function name will get converted to upper case and be treated as TEST_VECTOR which, of course is a different name than "test_vector". Although that is legal it basically caused you to shoot yourself in the foot because your method datatypes that you specify as test_vector are being converted to upper case and actually reference the function TEST_VECTOR.

            That 'case mangling' is what is causing your problem because you got them confused.

            This code works just fine for me.
            create or replace
            TYPE test_vector as varray(10) of Number
             ALTER TYPE test_vector MODIFY LIMIT 4000 CASCADE
             
            create or replace
            type Test as object(
              lastVector test_vector,
              STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number,
            MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in test_vector) return number  ,
            MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number,
            MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT test_vector, flags IN number) return number
            );
             
            create or replace
            type body Test is
              STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number is
              begin
                sctx := Test(test_vector());
                return ODCIConst.Success;
              end;
            MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in test_vector) return number  is
            begin
              self.lastVector := value;
              return ODCIConst.Success;
            end;
            MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number is 
            begin
             return ODCIConst.Success;
            end;
            MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT test_vector, flags IN number) return number is
            begin
              returnValue := self.lastVector;
             return ODCIConst.Success;
            end;
            end;
            
             
            create or replace
            FUNCTION fn_test_vector (input test_vector) RETURN test_vector 
            PARALLEL_ENABLE AGGREGATE USING Test;
            
            select fn_test_vector (test_vector(1, 3, 5, 7)) from dual
            
            FN_TEST_VECTOR(TEST_VECTOR(1,3,5,7))
            (1, 3, 5, 7, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , )
            See the Data Cartridge Developer's Guide for how to create user defined aggregate functions and an example function.
            http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/aggr_functions.htm#sthref466

            When learning and trying to use new functionality ALWAYS find an example that works and then make sure it works in your environment.

            And ALWAYS start with the simplest possible example. You are starting with a PARALLEL example before you even have a normal example working.
            • 3. Re: User definied aggregate function for varray of number
              959451
              @up
              You are right i provide some code that does not compile. In fact just before posting I try to modify code. I do it wrong and this is the reason why it does not compile(n DB i have compiling code)
              What is more the example You provide also work properly for me. The code i write on my own also work well with the dual table, but when i 'm trying to use it with real data it does not work and give me the error that i specified in the first post.

              What is also interesting:
              Please imagine sucha a situation:
              Select X from Y where condition
              return me value: MEASUREMENT_VECTOR_NAMES(S,PX,Y,X).
              I have aggregate function (functionA) which operate on a MEASUREMENT_VECTOR_NAMES.
              When i am trying to execute such a query:
              Select functionA(X) from Y where condition
              I get error which I mention on the first post.

              But when I execute next query:
              Select functionA(MEASUREMENT_VECTOR_NAMES('S','PX','Y','X')) from dual
              everything is OK.

              It is quite strange for me that it work well with the object created just before executing command and do not work with the value selected from DB.
              Sorry for the mistakes i made in the earlier post nad thanks for the help

              To give code example:
              I execute queries given in the first post(queries creating aggregate function and type, I modifiy them now).
              Next i create some test table:
              create table t1_test_table(
                t1_id number not null,
                t1_value TEST_VECTOR not null,
                Constraint PRIMARY_KEY_1 PRIMARY KEY (t1_id)
              )
              Next step is to put some data to the table
              insert into t1_test_table (t1_id,t1_value) values (1,TEST_VECTOR('x','y','z'))
              Now everything is prepared to perform queries:
              Select test_fn(TEST_VECTOR('y','x')) from dual
              Query above work well
              Select test_fn(t1_value) from t1_test_table where t1_id = 1
              This query execution result in error

              Sorry that i do not provide code example

              One more thing it the thread title i mention about varray of number but it does not matter whether it is varray of number or varray of varchar

              Edited by: 956448 on Sep 2, 2012 4:18 PM

              Edited by: 956448 on Sep 2, 2012 5:03 PM

              Edited by: 956448 on Sep 2, 2012 5:10 PM

              Edited by: 956448 on Sep 2, 2012 5:15 PM
              • 4. Re: User definied aggregate function for varray of number
                sb92075
                How do I ask a question on the forums?
                SQL and PL/SQL FAQ
                • 5. Re: User definied aggregate function for varray of number
                  rp0428
                  >
                  return me value: MEASUREMENT_VECTOR_NAMES(S,PX,Y,X).
                  >
                  When you use different code that what you are posting it wastes everyone's time. There is no MEASUREMENT_VECTOR_NAMES function in the code you posted. And you are still using double quotes around the type names making them case sensitive.
                  >
                  One more thing it the thread title i mention about varray of number but it does not matter whether it is varray of number or varray of varchar
                  >
                  Yes it does matter if it is number or varchar. You posted code that uses number so stick to that code and don't switch datatypes, function names or anything else. No one can help you if you are using code that is different than what you give us to use.

                  The cause of the problem appears to be a bug. The version I am using
                  >
                  BANNER
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                  PL/SQL Release 11.2.0.1.0 - Production
                  CORE     11.2.0.1.0     Production
                  TNS for Linux: Version 11.2.0.1.0 - Production
                  NLSRTL Version 11.2.0.1.0 - Production
                  >
                  The bug may have been fixed in later releases. You should file an SR with Oracle and post the below code which demonstrates the problem.
                  DO NOT MAKE CHANGES TO MY CODE!

                  If the 'test_vector' type is defined as 168 or less the code works fine. If it is defined as 169 or greater the code fails
                  >
                  ORA-00600: internal error code, arguments: [kodpunp1], [], [], [], [], [], [], [], [], [], [], []
                  ORA-00600: internal error code, arguments: [koxsihread1], [0], [1989], [21719], [], [], [], [], [], [], [], []
                  >
                  This is the code that will work/fail on my system
                  drop table t1_test_table cascade constraints
                  
                  drop type test
                  
                  drop type test_vector
                  
                  -- 168 works but 169 or greater gives internal error
                  create or replace
                  TYPE test_vector as varray(168) of Number
                   
                  create or replace
                  type Test as object(
                    lastVector test_vector,
                    STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number,
                  MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in test_vector) return number  ,
                  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number,
                  MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT test_vector, flags IN number) return number
                  );
                   
                  create or replace
                  type body Test is
                    STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number is
                    begin
                      sctx := Test(test_vector());
                      return ODCIConst.Success;
                    end;
                  MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in test_vector) return number  is
                  begin
                    self.lastVector := value;
                    return ODCIConst.Success;
                  end;
                  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number is 
                  begin
                   return ODCIConst.Success;
                  end;
                  MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT test_vector, flags IN number) return number is
                  begin
                    returnValue := self.lastVector;
                   return ODCIConst.Success;
                  end;
                  end;
                   
                  create or replace
                  FUNCTION fn_test_vector (input test_vector) RETURN test_vector 
                  PARALLEL_ENABLE AGGREGATE USING Test;
                  
                  create table t1_test_table(
                    t1_id number not null,
                    t1_value TEST_VECTOR not null,
                    Constraint PRIMARY_KEY_1 PRIMARY KEY (t1_id)
                  )
                  
                  insert into t1_test_table (t1_id,t1_value) values (1,TEST_VECTOR(1,3, 5))
                  
                  select fn_test_vector (t1_value) from t1_test_table