Oracle SQL Developer Online Demonstrations

Follow below Link:
http://www.oracle.com/technetwork/developer-tools/sql-developer/viewlets-082331.html

Tables

To find all the Oracle Database Tables: SELECT * FROM TAB;
To find all the PS AppDesigner Tables: SELECT * FROM PSRECDEFN;

Restrictions on ROWNUM in SQL

We can't use ROWNUM in a query with greater than condition.

For Example, we have below queries.

SELECT * FROM EMPTABLE WHERE ROWNUM > 100

output: 0 rows returned.

SELECT * FROM EMPTABLE WHERE ROWNUM < 100

output: it returns all 100 rows.

It is because rownum is a dymanic number generated and assigned to the rows while fetching the data from database. which is something like counting from 1,2..... It can't read which is 100th row unless it knows previous rows starting from 1. Hence it doesn't work with greater than (>) condition.

Difference between Delete, Truncate and Drop

Delete table -- will delete the rows from the table for that session/schema only.

Need to use explicit 'COMMIT' to reflect the changes across the schemas/sessions.

Truncate table -- will delete the rows from the table and an implicit 'COMMIT' command will be executed. Changes will be reflected across the schemas/sessions.

Delete table-- u can use ROLLBACK command to retrieve the deleted records

truncate table -- ROLLBACK can't be used

Delete table -- DML(Data Manipulation Language) Command 

Truncate table-- DDL (Data Definition Language) Command

Delete -- is row by row operation

Truncate -- will delete all the data from table

When ever we have to delete all the data in a table Truncate is suggested over Delete.

Drop will delete table structure also, where as Delete and Truncate will not delete Table structure.

SQL Developer - SQL Editor File

If you are using SQL Developer and by mistake you have closed window without saving your SQL Editor File?

Don't worry you can get your SQL Statements in unsaved SQL Editor File. The following screen shots will help you to get the unsaved files.

1. Go to the similar path in your machine:

2. To get the recent file, sort the folders by recently changed date time (as shown in the screen shoot) and open the first folder.

FYI: Number of folders in ‘.history’ folder represents number of SQL Editor Files you have opened in your machine.

3. To get the latest file, sort the file by recently changed date time (as shown in the screen shoot) and open the first file. That is the SQL Editor File that you have recently changed and not saved.

Some Tricky SQLs - Leave Days Count

I have following Requirement:

I have to calculate number of 'No Pay Leaves' consumed by an employee in a given period(suppose in the month of Aug 2010). Can i get the desired count in one SQL?

Solution:

In HCM System there is a Dates Attributes Table with name: 'PS_TL_DATES_TBL' (Time and Labor Module). This record will simplify our requirement as shown in the following SQL.

SELECT COUNT(A.THE_DATE)

FROM PS_TL_DATES_TBL A,PS_GP_ABS_EVENT B

WHERE B.PIN_TAKE_NUM = (SELECT PIN_NUM FROM PS_GP_PIN

WHERE PIN_NM LIKE 'TP NPL LVE')

AND B.EMPLID = '000052'

AND B.EMPL_RCD = 0

AND A.THE_DATE BETWEEN B.BGN_DT AND B.END_DT

AND A.THE_DATE BETWEEN '26-AUG-2010' AND '28-AUG-2010' ;

Some Tricky SQLs FTE

I have a requirement where in I have to get starting date and ending date of an FTE Value. Sample Employee data is as follows. Can we create a View which will give my desired rages as shown in the following format.

Employee Data:

EMPLID EMPL_RCD EFFDT FTE

000051 0 01-jan-2008 1

000051 0 10-feb-2008 1

000051 0 10-mar-208 0.5

000051 0 10-apr-2008 0.5

000051 0 10-may-2008 1

000051 0 10-jun-2008 1

000051 0 10-jul-2008 0.5

Required output format:

EMPLID EMPL_RCD FTE FTE_BGN_DT FTE_END_DT

000051 0 1 01-jan-2008 09-mar-2008

000051 0 0.5 10-mar-2008 09-may-2008

000051 0 1 10-may-2008 09-Jul-2008

000051 0 0.5 10-jul-2008 null

If the above tables are not clear enough to understand, Below image will give clear picture of the table structure.

Some Tricky SQLs

Let’s suppose, In a Table, we have the following kind of data. I want to change the SEQUENCE_NUMBER field value to a running number, numbering starting with 1000. Can we write a single SQL statement to get desired result?

Table Name: TABLE1

EMPLID NAME SEQUENCE_NUMBER
000071 Same Name 1
000071 Same Name 1
000071 Same Name 1
000071 Same Name 1

After executing SQL the data should be like this:

EMPLID NAME SEQUENCE_NUMBER
000071 Same Name 1001
000071 Same Name 1002
000071 Same Name 1003
000071 Same Name 1004

The tricky part is that, we can not select single row with any Where condition. Can you suggest any solution?

Answer: Is simple

UPDATE TABLE1 SET SEQUENCE_NUMBER = ROWNUM + 1000;

Subquery

SELECT (SELECT DEPENDENT_BENEF FROM PS_DEP_BENEF_VW WHERE EMPLID = A.EMPLID AND DEPENDENT_BENEF = '01') DEPENDENT_BENEF FROM (SELECT EMPLID,EMPL_RCD FROM PS_JOB WHERE EMPLID = '000072') A;

This should work in HCM Instance. Query before From should select only one field and one row.
If we select more than one field, we will get the following error: ‘ORA-00913: too many values’
If subquery selects more than one row, we will get the following error: ‘ORA-01427: single-row subquery returns more than one row’