How to handle ‘&’ while working in SQL

Inserting values into a table with ‘&’ could be tricky therefore given below approaches could be used for resolution.

SET DEFINE OFF;
create table test_table (name varchar2(50));
insert into  test_table values ('Tom & Jerry');

or

SET SCAN OFF;
create table  test_table (name varchar2(50));
insert into  test_table values ('Tom & Jerry');

or

create table  test_table (name varchar2(50));
insert into test_table values ('Tom '|| chr(38) ||' Jerry');

Advertisements

SQL query to extract XML data from Oracle table

Following query will help to extract the information of xml element from stored XML data in Oracle table.

select
	XMLTYPE(ad.ad_profile).EXTRACT('//bankId').getStringVal(), 
	XMLTYPE(ad.ad_profile).EXTRACT('//altDesc').getStringVal(), 
	DECODE(ad.adsta_id, 1, 'Success', 2, 'Failure')
from 
	alert_delivery ad
where 
	ad.altd_id in (1001,1002)
group by 
	XMLTYPE(ad.ad_profile).EXTRACT('//bankId').getStringVal(), 
	XMLTYPE(ad.ad_profile).EXTRACT('//altDesc').getStringVal(), 
	ad.adsta_id;

SQL script to grant the access to Schema owners

Sometimes, we need a SQL script to grant the access of different objects to multiple schema owners. Following script could be used as a reference :-

  
FOR R IN (SELECT owner, object_name from all_objects 
                where object_type in ('FUNCTION', 'PROCEDURE') 
                and owner in ('USR_APP', 'PRO_APP', 'TXN_APP')) LOOP
        execute immediate 'grant execute on ' || R.OWNER || '.' || R.Object_Name || ' to ' || 'SC_READ_ONLY_ACCESS';
END LOOP;

Happy Coding !!