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;
Advertisements

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 !!

ORA-28040: No matching authentication protocol error

We have got following error in Application logs and we were made to cross-check our JVM settings.

 
ERROR [2016-06-06 03:32:55,521]|[server.startup : 1]|[null]|getConnection
java.sql.SQLException: ORA-28040: No matching authentication protocol
DSRA0010E: SQL State = 99999, Error Code = 28,040
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:441)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOSESSKEY(T4CTTIoauthenticate.java:404)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:385)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:207)
at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPhysicalConnection(OracleConnectionPoolDataSource.java:139)

It was determined that removing JVM parameter -Doracle.jdbc.thinLogonCapability=o3 will fix the ORA-28040: No matching authentication protocol error. I remembered that we added this JVM parameter for certain applications during the WebSphere 7 upgrade. The problem was that certain applications running on WebSphere 7 with JDK 1.6 and ojdbc6.jar was not able to login to the Database. This was because of an Oracle driver “issue” when dealing with external 3rd party JCE libraries such as BouncyCastle.

SQLException: Failed preTestConnection

Today while working on a project requirement, we got following SQL Exception:-

java.sql.SQLException: Failed preTestConnection.
Pool requests blocked for jdbc/XXYYSZDB until the test connection thread is successful.

Since, our application uses pooled JDBC database connections from the app server hence when the connection to our database fails, pooled connections that are no longer valid might exist in the free pool.

Therefore, connection pretesting is a way to test connections from the free pool before giving them to the client. We can enable the PreTest Connections feature in the WebSphere administrative console to prevent application from obtaining connections that are no longer valid.

The feature is particularly useful for routine database outages. Because these outages are usually scheduled for periods of low use, connections to the database are likely to be in the free pool rather than in active use. Active connections are not pretested; pretesting impedes performance during normal operation.

For doing configuration in WebSphere 6.0.x, following are the steps:-
(1) In the administrative console, click Resources > JDBC providers.
(2) Select a provider and click Data Sources under Additional properties.
(3) Select a data source and click WebSphere Application Server data source properties under Additional properties.
(4) Select the PreTest Connections check box.
(5) Type a value for the PreTest Connection Retry Interval, which is measured in seconds. This property determines the frequency with which a new connection request is made after a pretest operation fails.
(6) Type a valid SQL statement for the PreTest SQL String. Use a reliable SQL command, with minimal performance impact; this statement is processed each time a connection is obtained from the free pool. For example, “select 1 from dual” in oracle or “SQL select 1” in SQL Server.

How to handle special character (such as ‘&’) in Oracle?

Following are the few options that could be tried to handle special characters in Oracle:-

Case Study 1

SELECT a.employee_id,
a.employee_code,
a.employee_display_name,
replace(a.employee_display_name,'&'||'reg;',NULL) updated_emp_display_name
FROM employee a
WHERE a.employee_id IN (26, 28, 48, 51, 61, 104, 39, 107);

Case Study 2

-- select ASCII('&') from dual
-- select CHR(38) from dual;

SELECT a.employee_id,
a.employee_code,
a.employee_display_name,
replace(a.employee_display_name,chr(38)||'reg;',NULL) updated_emp_display_name
FROM employee a
WHERE a.employee_id IN (26, 28, 48, 51, 61, 104, 39, 107);

Case Study 3

Set scan off
-- Setting the scan off tells sqlplus that dont scan for variables in the statements.

SELECT a.employee_id,
a.employee_code,
a.employee_display_name,
replace(a.employee_display_name,'®',NULL) updated_emp_display_name
FROM employee a
WHERE a.employee_id IN (26, 28, 48, 51, 61, 104, 39, 107);

Case Study 4

set define off

SELECT a.employee_id,
a.employee_code,
a.employee_display_name,
replace(a.employee_display_name,'®',NULL) updated_emp_display_name
FROM employee a
WHERE a.employee_id IN (26, 28, 48, 51, 61, 104, 39, 107);

set define on

How to Lock Table in Oracle

While working on a requirement, we have to raise the ReadTimeOut condition from one environment to another environment. We have followed the Table-Level lock operation in Oracle and hence achieved the functionality accordingly. Following is the brief information on the same:-

PL/SQL Developer Session-1:-

-- Creating Data-Set
create table emp (emp_id number, emp_name varchar2(20));
insert into emp values (1,'vipin’);
insert into emp values (2,'vinay’);
select * from emp;
commit;
-- Obtaining Table-Level Lock
lock table emp in exclusive mode nowait;
rollback; -- A locked table remains locked until we either commit our transaction or roll it back, either entirely or to a savepoint before you locked the table.

PL/SQL Developer Session-2:-

---- Verifying and Creating the Data-Set
select * from emp;
insert into emp values (3,'ananya'); -- insert statement will not get completed until or unless the lock is not released from another session.
commit;

Reference:-
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9015.htm

Achieve Network Encryption in WebSphere for Oracle

To enable the Network Encryption in WebSphere, we need to do configuration in custom properties of Data-Sources.

WebSphere Pooled Oracle Data-Source
In WebSphere console under JDBC providers > Oracle JDBC Driver > Data sources >
(1) Click on Custom Properties
(2) Add a following new property
Name: connectionProperties
Value: oracle.net.encryption_client=REQUESTED;oracle.net.encryption_types_client=RC4_128

Code Snippet:-

...
Properties prop = new Properties();
prop.put("oracle.net.encryption_client", "REQUIRED");
prop.put("oracle.net.encryption_types_client", "( RC4_128 )");
//prop.put("oracle.net.crypto_checksum_client", "REQUESTED");
//prop.put("oracle.net.crypto_checksum_types_client", "( MD5 )");

OracleDataSource ods = new OracleDataSource();
ods.setProperties(prop);
ods.setURL("jdbc:oracle:thin:@localhost:1521:main");
Connection conn = ods.getConnection();
...

Reference:-
https://docs.oracle.com/cd/B19306_01/java.102/b14355/clntsec.htm#EHAFHEIG