Oracle Advanced Benefits Overview

Benefits Overview:

General Definitions:

·        Eligibility/Rate Factors

  • Benefits Group   BEN_BENFTS_GRP
  • Derived Factors    BEN_COMP_LVL_FCTR
  • Postal/Zip   BEN_PSTL_ZIP_RNG_F
  • Service Areas BEN_SVC_AREA_F

Continue reading →

Advertisements

Table Lock Query in Oracle

To find locked objects in Oracle –>


Use this Query to identify the locked objects  –>

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;
To Remove lock use below query –>
alter system kill session ‘SID,SERIALl#’;
Example :
alter system kill session ‘607,1402’;

PL SQL Interview Important Questions

PL/SQL–

1) Difference between union and union all in oracle ?

Answer –> Union all does not remove duplicates
— Union removes duplicate records

Both queries should have same columns.

Number of columns in each UNION query must match

Data types must match.

For large data set queries UNION might have performance issues. So use it very carefully.

Link –>  http://sqlandplsql.com/2012/06/22/difference-between-union-and-union-all-clause-oracle/

***********************************************************************************************************

2) What is Subquery in Oracle?

Answer –> http://www.techonthenet.com/oracle/subqueries.php

Subquery can be used in 3 location –>
— Where Clause —

SELECT *
FROM all_tables tabs
WHERE tabs.table_name IN (SELECT cols.table_name
FROM all_tab_columns cols
WHERE cols.column_name = ‘SUPPLIER_ID’);

** Limitation: Oracle allows up to 255 levels of subqueries in the WHERE clause.

— From –it is known as Inline views

SELECT suppliers.name, subquery1.total_amt
FROM suppliers,
(SELECT supplier_id, SUM(orders.amount) AS total_amt
FROM orders
GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;

** Oracle allows an unlimited number of subqueries in the FROM clause.

—SELECT CLAUSE–

SELECT tbls.owner, tbls.table_name,
(SELECT COUNT(column_name) AS total_columns
FROM all_tab_columns cols
WHERE cols.owner = tbls.owner
AND cols.table_name = tbls.table_name) subquery2
FROM all_tables tbls;

The trick to placing a subquery in the select clause is that the subquery must return a single value.
This is why an aggregate function such as SUM function, COUNT function, MIN function, or MAX function is commonly used in the subquery.

**********************************************************************************************************************************************

3) Usage of INSTR and SUBSTR in oracle?

select  INSTR(‘1234-abc’,’-‘) from dual;   –will find postion, for eg. in this case it will give 5

select SUBSTR(‘1234-abc’,1,5-1) from dual ;   — over here 5 -1 is the position of separator -1 ;

**********************************************************************************************************************************************
— Not answered Completely

4) USage of regexp_substr to split single row into multiple columns?

select regexp_substr(‘111*222*333’, ‘[^*]+’, 1, level) str
from dual
connect by level <= length(‘111*222*333’)-length(replace(‘111*222*333′,’*’))+1

http://www.oracle-developer.net/display.php?id=508

**********************************************************************************************************************************************

5) Use of Execute Immediate Statement –>

The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement .

DECLARE
sql_stmt    VARCHAR2(200);

BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE bonus (id NUMBER, amt NUMBER)’;
sql_stmt := ‘INSERT INTO dept VALUES (:1, :2, :3)’;

—–

In a PL/SQL program, you can invoke static SQL without any special notation. What is static SQL ? Static SQL is :

Data Manipulation Language (DML) Statements (except EXPLAIN PLAN) : INSERT, UPDATE, DELETE
Transaction Control Language (TCL) Statements : COMMIT, ROLLBACK…
SQL Functions
SQL Pseudocolumns : ROWID, ROWNUM…
SQL Operators

As you can see, CREATE statements are not static SQL. To invoke them, you have to use dynamic SQL, via EXECUTE IMMEDIATE.

———————————
**********************************************************************************************************************************************
6) what are the compound symbols? 
–Answer:

:= assignment operator
.. range operator
|| concatenation operator
— single-line comment indicator

**********************************************************************************************************************************************
7) To DEbug and Test PL / SQL package –>

set serveroutput on
Begin
dbms_output.put_line(‘Hello Tester’);
End;
/

**********************************************************************************************************************************************

8) HOW TO SAVE YOUR QUERY AND RESULT IN THE TEXT FILE(working in SQLplus and SQL Developer): 

SPOOL C:\TEST_FILE.TXT
—QUERY
SPOOL OFF

**********************************************************************************************************************************************
9) Global temporary tables –>

********************************************************************************************************************************************
10)-– COALESCE   Function
The COALESCE function returns first not null expression among the arguments. Minimum 2 arguments required.

If all expressions are null then it returns null.

Syntax:-

COALESCE (expr_1,expr_2,…expr_n)

Examples :-

1. select COALESCE(null,null,null,10) from dual;

would return 10.

2. select COALESCE(null,null,null) from dual;

would return null.

**********************************************************************************************************************************************
—Why procedure can’t be used in SQL statement, whereas Function can be used ?

Answer ==

1) This is because Function must return a value, while procedure may or may not.
2) Also function can be used as an assignment statement, while Procedure can never be used as an assignment.

Function can be used as assignmnet statement.

http://www.club-oracle.com/threads/why-procedure-can-not-be-used-in-select-query.3193/

Primary vs Unique Candidate vs Composite Keys

Primary Key Unique Key 
Can be only one in a table Can be more than one unique key in one table.
It never allows null values Unique key can have null values
Primary Key is unique key identifier and can not be null and must be unique. It can’t be candidate key
Unique key can be null and may not be unique.
 All databases allow the definition of one, and only one, primary key per table.
Candidate Key Composite key
A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data. Each table may have one or more candidate keys, but one candidate key is special, and it is called the primary key. This is usually the best among the candidate keys.     When a key is composed of more than one column,
it is known as a composite key.

Difference between TRUNCATE, DELETE and DROP commands

Delete — The DELETE command is used to remove some rows from a table.
using where clause.
Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE removes all rows from a table.
The operation cannot be rolled back ,
no triggers will be fired.
As such, TRUNCATE is faster and doesn’t use as much undo space as a DELETE.
Reason –>
TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Table space first.
then delete operation get performed.performed. That’s why when you type ROLLBACK after deleting a table ,
you can get back the data(The system get it for you from the Rollback Table space).
All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Table space.
That’s why TRUNCATE is faster.Once you Truncate you can’t get back the data.
)
Drop
The DROP command removes a table from the database
All the tables’ rows, indexes and privileges will also be removed.
No DML triggers will be fired.
The operation cannot be rolled back.

DROP and TRUNCATE are DDL commands
whereas DELETE is a DML command.
Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
DML can be rolled out whereas DDL can’t be rolled out.

PL/SQL and Oracle Apps HRMS interview questions

To begin with , I will initially post some common interview question topics for PL/SQL developer and Oracle Apps HRMS. These questions really helped me lot for cracking interviews at an experience level of 2-3 years.

PL/SQL Topics   —->>
Constraints
Truncate and Delete
Cursor -Explicit ,Implicit cursor
lead and Lag function
Exception
Pragma exception init
Raise application error
Collection
bulk collect and for all
DDL and DML
Triggers
Performance Tuning
Pragma autonomous transaction
Reference cursor
Cursor Attributes
Rank and Dense Rank
how view can be updated with multiple table (Instead of trigger )
Global temporary table
Pseudo columns
Types of Indexes
Types of pay elements
what is standard Link
Diff between nested table and v array
Annualization factor
Oracle packages used
%Rowtype
Can DDL statement be used in PL/Sql Block
Difference between Rownum and row_number

Oracle Apps HRMS Topics —>

Eit and Sit
KFF and DFF
System Profile
termination of pay elements
AME
Costing
Concurrent program
rehire and Reverse termination
Balances and dimension
Costing and types of costing
Joins
Qualifiers
Multi Organization structure
Payroll processing types
Valueset and Look up
Job and position
payment methods
payroll processing types
Date track mode
Security Profile
User Tables
Pay Query Explanation

Please atleast go through these topics ,you will definitely crack the interview.

Suggestion are always welcome .

Thank you 🙂