Archive for June, 2013

How to search a string in clob column in oracle


Problem: How to search for a string in a CLOB Column

Solution: Suppose you have a table with a CLOB column and you want to search for an occurence of the string in the clob column, you can do like

SELECT *FROM TABLE_NAME WHERE DBMS_LOB.INSTR(CLOB_COLUMN_NAME, ‘SEARCHSTRING’,1,1)>0

Replace with your table name, CLOB Column name in the table and with the actual search string.

Leave a comment

ORA-04091:table is mutating, trigger/function may not see it


Problem: ORA-04091:table is mutating, trigger/function may not see it

Solution: There are various solutions to solve this, however it requires deeper understanding on what mutating error is and how to resolve this. You will encounter this when working on a trigger for a particular table and the trigger code is accessing the same table on which the trigger is operating on.

Simple solution is to remove references to any usage of the table on which trigger is operating on, if at all possible. How ? Go on…

If you have a ORDER table that has ORDER_ID AND CUSTOMER_ID AND LAST_UPDATED

An ORDER_DETAILS table with ORDER_ID,ORDER_DETAILS.

Suppose the trigger is on the ORDERS table FOR EACH ROW and BEFORE  UPDATE. Trigger is to update  the ORDER_DETAILS table for a particular existing order along with updating the timestamp in ORDERS table. To do so, if you try to access the ORDER_DETAILS as below, you will end up with mutating error, as in

SELECT ORDER_DETAILS INTO DETAILS

FROM ORDERS O, ORDER_DETAILS OD

WHERE O.ID = OD.ID AND O.ID = :OLD.ID

I have added some reference down below to understand :NEW and :OLD for those who want to understand how to use them in triggers. In the above query, problem is trigger is on ORDERS table and trigger is also trying to access the ORDERS table. Here there is no need to access ID from ORDERS table to join with ORDER_DETAILS Details.   

Instead, simplify the query by removing the references to ORDERS table to avoid mutating error

SELECT ORDER_DETAILS INTO DETAILS

FROM  ORDER_DETAILS OD

WHERE OD.ID  = :OLD.ID

Identifying scenarios like this before dwelling into complex solutions will save time 🙂

Accessing Column Values in Row Triggers

Within a trigger body of a row trigger, the PL/SQL code and SQL statements have access to the old and new column values of the current row affected by the triggering statement. Two correlation names exist for every column of the table being modified: one for the old column value, and one for the new column value. Depending on the type of triggering statement, certain correlation names might not have any meaning.

  • A trigger fired by an INSERT statement has meaningful access to new column values only. Because the row is being created by the INSERT, the old values are null.
  • A trigger fired by an UPDATE statement has access to both old and new column values for both BEFORE and AFTER row triggers.
  • A trigger fired by a DELETE statement has meaningful access to :old column values only. Because the row no longer exists after the row is deleted, the :new values are NULL. However, you cannot modify :new values:ORA4084 is raised if you try to modify :new values.

The new column values are referenced using the new qualifier before the column name, while the old column values are referenced using the old qualifier before the column name. For example, if the triggering statement is associated with the Emp_tab table (with the columns SALCOMM, and so on), then you can include statements in the trigger body. For example:

IF :new.Sal > 10000 ...
IF :new.Sal < :old.Sal ...

1 Comment

java.sql.SQLException: Protocol violation


Problem: Getting SQL Exception java.sql.SQLException: Protocol violation

Solution: I had researched various available solutions for this issue, however none of them solved my issue. I will first summarize some available solutions before presenting what solved the problem in a different way

 

Solution 1:   DB  password was showing an error ORA-28002: the password will expire within XX day. Reseting the password resolves the issue

Solution 2: use 64 bit VM. To use 64 bit VM pass argument -d64 as command line parameter to Java.

Solution 3: In My case, its the JVM Settings. Increasing the -Xmx<size> set maximum Java heap size

Increasing from -Xmx512M to -Xmx1024M resolved the issue as well

Leave a comment