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

Advertisements

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

ORA-00979: not a GROUP BY expression

While working on a Critical Issue, it comes a scenario wherein following exception is being obtained “ORA-00979: not a GROUP BY expression” in Oracle

After doing an analysis it has been observed that a redacted column cannot be used in a SQL statement where such a column has a function/expression applied and is used in a group by clause. If an action is performed then error “ORA-00979: not a GROUP BY expression” will be returned.

Sample example that works

SELECT COLUMN_NAME_1
FROM TABLE_NAME
WHERE COLUMN_NAME_ID BETWEEN 500 AND 600
GROUP BY COLUMN_NAME_1

Sample example that doesn’t works

SELECT SUBSTR(COLUMN_NAME_1, 1, 5)
FROM TABLE_NAME
WHERE COLUMN_NAME_ID BETWEEN 500 AND 600
GROUP BY SUBSTR(COLUMN_NAME_1, 1, 5)

Learning:
Do not include any redacted columns in a SQL expression that is used in a GROUP BY clause in a SQL statement. Oracle does not support this behavior, and raises an ORA-00979: not a GROUP BY expression error. This happens because internally the expression in the SELECT list must be modified by Data Redaction, but this causes it to no longer be found when it comes time to process the GROUP BY clause (which is currently not updated by Data Redaction) leading to this unintended error message.

Reference:
https://docs.oracle.com/cd/E11882_01/network.112/e40393/redaction_guidelines.htm#ASOAG10532
(Loop up the contents under: 7.1 General Usage Guidelines)