PL/SQL: How to Remove trailing String in Select Statement

Suppose you have a column where some of the values have a particular training string. and you want to remove this particular pattern in selected values.

You can use the below code to remove this type of string.

declare
Input varchar2(20) := 'my name :Keyword';
myResult varchar2(20) := '';
begin

select case when Input like '%:Keyword'  then 
substr(trim(Input),0,length(trim(Input))-8)
else
Input
end into myResult from dual;

dbms_output.put_line(myResult);
end;

This is general solution and you can use this solution to format your selected values.

You can use this solution in following cases while using the the table column:

1. Leading values formatting

2. trailing values formatting

3. any pattern formatting

How to Convert Varchar Time Format to Seconds - PL/SQL

 

This will help pl/sql developer to convert a given varchar value to time format.

This useful while you need a time comparison or want to display data in AM/PM format or any other time format.

Here we are converting varchar AM/PM format to seconds for the comparison purpose.

select  to_number(to_char(to_date('11:00 AM','hh:mi AM'), 'sssss')) from dual

How to Apply Truncate Inside Oracle Procedure?

 

you can not directly apply the truncate statement inside the oracle procedure. DDL and Truncate command not allowed directly inside the PL/SQL block. So always keep in mind.

but there is another way to execute these statement even inside the PL/SQL blocks.

use execute immediate statement to execute such kind of statements. for example we have a table called my_sql_table and we want to use truncate state with this table. use the blow statement to use truncate

execute immediate 'TRUNCATE  table GT_RO_exhibrts_appliesto'; 

Unexpected Results While Using Oracle Global Temporary Tables | Sorting Issue

 

While working on oracle 10g global temporary tables, i noticed a very strange thing. I was inserting values in global temporary table and at the end of loop i wanted the same order as i inserted into the table.

if i create a global temporary table with option delete rows on commit the table select query returns the correct order.

while if i create a global temporary table with option preserve rows on commit. first time it works fine but next time it returns the wrong order.

This happens because i was using delete statement to delete all the rows from GT table and it was not deleting correctly.

use truncate statement to delete the rows from table and in this case it will work fine.

 

execute immediate 'TRUNCATE  table GT_MyTable';

 

Please let me know if anyone face any other problem while using GT tables.

How to Get Second Highest Salary from a Table in PL/SQL

 

To get the second highest salary in SQL server is easy task using top keyword while in PL/SQL you need to write a customize  query to get the second highest salary from table. The logic behind this query is to first sort the table in reverse order and get the second top salary using rownum.

 

Table contents:

SELECT * FROM TB_TEST

 

PL/SQL Code:

SELECT T3.*
  FROM (SELECT T2.*, ROWNUM NUM
          FROM (SELECT T.* FROM TB_TEST T ORDER BY T.SALARY DESC) T2) T3
WHERE NUM = 2 --SECOND HIEGHTS SALRY

How to Get List of All Table with Specific Column Name – PL/SQL

 

Sometimes while programming we need to have all tables in system with specific column name. Here is the PL/SQL query for the same.

You have to write the column name you are looking for in place if ‘ID’ and this query will return all the tables in system.

You need to take care of that you want column name with upper case only or all.

This query is helpful in purging table with particular column name. It can be also useful in very complicated systems to find out that what places system storing particular data.

 

select * from sys.user_tab_cols where upper(column_name)='ID';

For Loop in PL/SQL – Example Code

 

This post helps new bees to understand for loop working in PL/SQL. Here is a very simple PL/SQL code for for loop. This for loop start from 1 and end to 10. You can check it by running this script block in your sql window.

 Declare
begin   

for i in 1..10 loop
    dbms_output.put_line(i);
end loop;

end;

Output

1
2
3
4
5
6
7
8
9
10

 

Here is another program which runs the for loop in reverse order. just use the reverse keyword as given in below example.  you can also check the output by running this code in your SQL window.

 Declare
begin
    for i in reverse 1..10 loop
    dbms_output.put_line(i);
    end loop;
end;

 

Output:

10
9
8
7
6
5
4
3
2
1