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.

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:ORA–4084 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 SAL, COMM, and so on), then you can include statements in the trigger body. For example:

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

How to convert reference cursor to oracle pipelined table data results

Problem: Converting from reference cursor to oracle pipelined results

Solution: Package body declaration is shown here, it is assumed that the specification is already taken care to declare the pipelined record datastructure, if not please look into my other posts on how to declare the specification part of it at

https://javatoj2ee.wordpress.com/2012/08/30/oracle-pipelined-table-functions/

function get_customer_data

(firstName IN VARCHAR2 DEFAULT NULL, lastName in VARCHAR2 DEFAULT NULL)

return customerpipelineddata PIPELINED AS

output_record                 customerpipelineddata;

v_customerdetails           ref_cursor_type;

–select statement goes here

open v_customerdetails for (select statement query)

–Below is the part needed to have reference cursor data into pipelined data

loop
fetch v_customerdetails_cursor bulk collect into output_record limit batchsize;
for i in 1 .. output_record.count() loop
pipe row (output_record(i));
end loop;
exit when output_record.count < batchsize;
end loop;

end get_customer_data;

here customerpipelineddata  is the actual pipelined data record, a set of records of a particular data structure like

TYPE customerpipelinedrecord  IS RECORD (id varchar2(100),firstname CUSTOMER.FIRST_NAME%type);

TYPE customerpipelineddata  IS TABLE OF customerpipelinedrecord;

Hope this helps….Please do not forget to like or post your comments 🙂

oracle pipelined table functions

Problem: How to create pipelined table functions in oracle

Solution: 

Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations. Regular table functions require named row and table types to be created as database objects.

Typically pipelined functions are useful if there is a need for a data source other than a table in a select statement as shown below

SELECT * FROM TABLE(PACKAGE_NAME.PIPELINED_FUNC_NAME)

Lets see how to create a pipelined function 

1. If you are defining these in a package, below definitions goes into the spec of the package, first thing is to specify the columns that are returned along with the type of the columns

create or replace PACKAGE package_name 

AS 

TYPE pipelinedDataRecord IS RECORD (id varchar2(100),firstname CUSTOMER.FIRST_NAME%type);

END package_name

The above definition declares the format of the rows that are returned

2. Add the below collection type for the above defined type

TYPE pipelinedDataRecordCol IS TABLE OF pipelinedDataRecord;

So the entire package declaration looks like 

 

create or replace PACKAGE package_name 

AS 

TYPE pipelinedDataRecord IS RECORD (id varchar2(100),firstname CUSTOMER.FIRST_NAME%type);

TYPE pipelinedDataRecordCol IS TABLE OF pipelinedDataRecord;

END package_name

 

3. Now define the body of the package

 

create or replace PACKAGE BODY package_name

AS

FUNCTION func_name return pipelinedDataRecordCol 

PIPELINED IS

out_record pipelinedDataRecord ;

BEGIN

FOR in_record IN(

********select statements goes here********

) LOOP

out_record := in_record;

PIPE_ROW (out_record);

END LOOP;

END func_name;

 

END package_name;

4. How to call the above pipelined function? 

SELECT * FROM TABLE(PACKAGE_NAME.FUNC_NAME);

ORA-00942: table or view does not exist

Problem: You tried to execute an SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn’t reference the table by the schema name.

Solution:

Lets suppose there are two schemas, one for all the tables and one for all the procedures and functions.

1. In order to make it clear, lets name them to be TABLE_SCHEMA and PROC_SCHEMA.

2. TABLE_SCHEMA has your business domain tables, say customer table, address table etc

3. PROC_SCHEMA has the procedures PROC_NAME that access these tables

 

If you try to connect to proc_schema to execute the procedures, you will get a message ORA-00942: table or view does not exist, even though the tables exist. In this case, proper grant permissions are not provided and synonyms are not created. Follow the below indicative steps to potentially resolve this issue

1. If you are using SQLDeveloper, connect to the proper schema to grant access as described below.

2. First connect to the table_schema where all the tables exists and run the below command

3. grant select,insert,update,delete, references on CUSTOMER to PROC_SCHEMA

4. Now DISCONNECT from TABLE_SCHEMA and connect to PROC_SCHEMA and run the below command to create synonym for the table in PROC_SCHEMA

5. create or replace SYNONYM PROC_SCHEMA.CUSTOMER FOR TABLE_SCHEMA.CUSTOMER

 

Lets extend the above concept and see what steps to take if we decide to execute the stored procedure PROC_NAME available in PROC_SCHEMA from another schema, say ANOTHER_SCHEMA

1. Connect to PROC_SCHEMA and run the below command

2. grant execute on PROC_SCHEMA.PROC_NAME to ANOTHER_SCHEMA

3. Disconnect from PROC_SCHEMA and connect to ANOTHER_SCHEMA and run the below command

4. create or replace SYNONYM ANOTHER_SCHEMA.PROC_NAME FOR PROC_SCHEMA.PROC_NAME

 

Also, take a look at the below tutorial, for further explanation

http://www.adp-gmbh.ch/ora/err/ora_00942.html

package org.springframework.orm.hibernate3 does not exist

Problem: Recently i am working on SpringMVC REST +Hibernate application, where in i encountered the following 
issue during the integration package org.springframework.orm.hibernate3 does  not exist 

 Solution:  Add the following dependency to the pom.xml

 <dependency>  
<groupId>org.springframework</groupId>
 <artifactId>spring-orm</artifactId>   
<version>${org.springframework.version}</version> </dependency> 

Replace the above version with the actual version or specify the version as a property in pom.xml.  
Note that, when i was working on Spring 2.5 + Hibernate, the above dependency was not needed, 
 but with Spring 3 and Hibernate, to resolve the issue that package org.springframework.orm.hibernate3
 does not exist, i added the above dependency 

ORA-01436 CONNECT BY loop in user data

Problem:  When traversing a database table to find the hierachy of parent child (like manager, employee), this error happens

ORA-01436 CONNECT BY loop in user data

Solution:  If SQL Query looks like

select * from customer

start with manager_id = ‘334’

connect by prior emp_id = manager_id

above problem can happen, if the next record that is to be selected is a descendent of itself, which causes loop problem. By having another condition like below

 

select * from customer

start with manager_id = ‘334’

connect by prior emp_id = manager_id

AND relationship_cd = ‘IS MANAGER OF’

retrieves all the employees whose manager is either 334 and also those employees whose manager is children of manager 334