4 Replies Latest reply: Dec 12, 2012 11:18 AM by 979555 RSS

    Error while using dbms_expfil.assign_attribute_set

    470436
      Dear Guru's

      I am new to this feature and in the process of learning. I was trying out the examples provided by the Documentation

      These are the codes from the documentation

      CREATE TABLE traders
      (name VARCHAR2(10),
      email VARCHAR2(20),
      interest VARCHAR2(30));

      CREATE OR REPLACE TYPE ticker AS OBJECT
      (symbol VARCHAR2(20),
      price NUMBER,
      change NUMBER);

      BEGIN
      DBMS_EXPFIL.CREATE_ATTRIBUTE_SET
      (attr_set => 'ticker',
      from_type => 'YES');
      END;


      BEGIN
      DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET
      (attr_set => 'ticker',
      expr_tab => 'traders',
      expr_col => 'interest');
      END;


      INSERT INTO traders (name, email, interest)
      VALUES ('Vishu', 'vishu@example.com', 'symbol = ''ABC'' AND price > 25')



      SELECT name, email
      FROM traders
      WHERE EVALUATE (interest,
      'symbol=>''ABC'',
      price=>31,
      change=>5.2'
      ) = 1

      Upto this fine it worked well . But i couldnt understand the usage of a parameter called Change in the Evaluate expression in the above select statment. Because in the above scripts , there is no data for the parameter . hence i Decided to do with that. I tried removing the CHANGE=>5.2 from the evaluate operator but ended with an Error.

      Hence i decided to drop the entire thing

      I used the following procedures

      BEGIN
      DBMS_EXPFIL.UNASSIGN_ATTRIBUTE_SET (expr_tab => 'traders'
      expr_col => 'Interest');
      END;

      and

      DBMS_EXPFIL.DROP_ATTRIBUTE_SET(attr_set => 'ticker');

      and
      Drop type ticker

      Then i created the following
      CREATE OR REPLACE TYPE Trinfo AS OBJECT
      (symbol VARCHAR2(20),
      price NUMBER
      );

      /
      BEGIN
      DBMS_EXPFIL.CREATE_ATTRIBUTE_SET
      (attr_set => 'trinfo',
      from_type => 'YES');
      END;

      so far no problem

      when i executed the following

      BEGIN
      DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET
      (attr_set => 'trinfo',
      expr_tab => 'traders',
      expr_col => 'interest');
      END;
      /

      BEGIN
      *
      ERROR at line 1:
      ORA-38464: expression set is not empty.
      ORA-06512: at "EXFSYS.DBMS_EXPFIL_DR", line 13
      ORA-06512: at "EXFSYS.DBMS_EXPFIL", line 450
      ORA-06512: at line 2

      I got the above error

      I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
      With the Partitioning, OLAP and Data Mining options

      Kindly guide me

      with warm regards
      ssr
        • 1. Re: Error while using dbms_expfil.assign_attribute_set
          442199
          Hello,

          It might help you to think of the Attribute set as the table and the expressions as the queries on the table. The table may have N number of columns. The corresponding queries may refer to some subset of these columns. In your case, you are defining an attribute set with 3 attributes. It so happened that none of the expressions you defined used the attribute "change". But the attribute set is created to allow expressions that include predicates on "change" attribute. Extending our attribute set - table analogy, a data item specified with an EVALUATE operator is like an INSERT statement into the table. Irrespective of the queries, the INSERT statement expects values for each of the columns in the table (the INSERT statement with column enumeration is another way of assigning NULLs for missing columns). So, the EVALUATE operator expects the values for all the attributes in the attribute set even when there are no expressions defined. In 11g Release 1, you could have default values for some of the attributes and the default value will be used if certain attribute is missing in the data item specification.

          Since the column you are assigning the attribute set to has some data, you should use FORCE to validate those expressions or empty the table prior to assigning the attribute set.

          38464, 00000, "expression set is not empty."
          // *Cause: An attempt was made to assign an attribute set to a non-empty
          // expression set.
          // *Action: Use FORCE = 'TRUE' to validate all the existing expressions.

          Hope this helps,
          -Aravind.
          • 2. Re: Error while using dbms_expfil.assign_attribute_set
            470436
            Dear Aravind,
            Thanks for the reply and your explanation.


            +"Since the column you are assigning the attribute set to has some data, you should use FORCE to validate those expressions or empty the table prior to assigning the attribute set. "+

            Empty the table ... u mean the table in my example --Traders u mean to delete all the records

            Kindly clarify me in this regard


            with warm regards
            ssr
            • 3. Re: Error while using dbms_expfil.assign_attribute_set
              442199
              Please see the Usage Notes for the DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET for details.

              http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_expfilref.htm#ARPLS159

              Thanks,
              -Aravind.
              • 4. Re: Error while using dbms_expfil.assign_attribute_set
                979555
                I know its old thread but it was tagged as unanswered so replying as i was also stuck on same couple of days back. put force=>true , that shud resolve.

                BEGIN
                DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET
                (attr_set => 'trinfo',
                expr_tab => 'traders',
                expr_col => 'interest'
                force=>'true');
                END;
                /