INSERT INTO traders (name, email, interest)
VALUES ('Vishu', 'email@example.com', 'symbol = ''ABC'' AND price > 25')
SELECT name, email
WHERE EVALUATE (interest,
) = 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
DBMS_EXPFIL.UNASSIGN_ATTRIBUTE_SET (expr_tab => 'traders'
expr_col => 'Interest');
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.