GET HIERARCHICAL VALUES PRESENT IN SAME COLUMN OF A TABLE

Lets discuss about the parent and child relationship present in the same column of a table. That means both exists in the same column and there is always a identifier which says whether a record belongs to parent value or it belongs to child value. See the below data .As it is clearly seen from the data, parent and child record can be easily identified from column c1. Here , we need to apply self join to get parent and child values in the same row. See the below Query.…

Check SQL History and Monitor Sessions in PL/SQL Developer Tool

Today, i will discuss about how to monitor Sessions and check what all queries executed in pl/sql Sessions. First, In order to Monitor Sessions which are active /inactive in particular Schema in pl/sql developer tool, Go to Tools->Monitor Sessions. See the below Screen Shot for the same. Second, In order to see what all queries executed in particular Session , go to View -> SQL History. See the below image for the same.

GET COLUMN NAMES ON WHICH INDEX IS CREATED IN ORACLE

Today, i will share the query which will give the column names along with table name on which index is created.Below is the Query for the same. select a.index_name,a.table_name,b.column_name from all_indexes a, all_ind_columns b where a.table_name=b.table_name and a.index_name=b.index_name and a.uniqueness=’UNIQUE’ and a.table_name in <LLIST OF TABLE NAMES> Query to give column names which are not null in the table. select table_name,column_name from all_tab_columns where table_name like ‘%ABC%’ and NULLABLE=’N’ and owner=<SCHEMA_NAME> order by table_name

CHECK WHETHER VALUES ARE UPPERCASE OR NOT IN DATABASE

Today, i will discuss about how to check which values in the table for a particular column are uppercase or not. I have designed a Query using dual to achieve the same. Below is the Query. select * from (select ‘A’ as col1, ‘B’ as col2 from dual union select ‘a’ col1,’D’ as col2 from dual) A, (select ‘A’ as col1 ,’B’ as col2 from dual union select ‘a’ col1 ,’D’ as col2 from dual)B where upper(B.col1)=A.col1 and B.col2=A.col2; I created table A and table B using dual command .…