This discussion is archived
4 Replies Latest reply: Dec 12, 2012 9:18 AM by 979555 RSS

Error while using dbms_expfil.assign_attribute_set

470436 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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;
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points