Oracle SQL PLSQL Interview Questions
interview questions for oracle apps technical
practice sql for interview
basic interview questions in sql
query questions in sql
sql practice for interview
practice questions for sql
practical sql interview questions
Write a query to eliminate duplicate rows in a table?
Delete from emp x where rowid <= (select max(rowid) from emp y where x.kye_values = y.key_values)
Write a query to find max 5 salaries of employees ?
Select * from (select * from emp order by sal desc) where rownum < 6
Given a Procedure in a Package and a Procedure standalone what is the diff between the two and which is better to use ?
Modularity -Group logically related PL/SQL types, items, and subprograms.
Easier Application Design bcoz you can code and compile a specification without its body. You need not define the package body fully until you are ready to complete the application.
Information Hiding -You can decide which constructs are public (visible and accessible) or private (hidden and inaccessible). The package hides the definition of the private constructs (i.e they are not included in package specification) so that only the package is affected (not your application) if the definition changes. Also, by hiding implementation details from users, you protect the integrity of the package.
Note:. When coding the package body, the definition of the private function has to be above the definition of the public procedure.
Persistence - Packaged public variables and cursors persist for the duration of a session. Thus they can be shared by all subprograms that execute in the environment
Overloading -Packages allow you to overload procedures and functions, which means you can create multiple subprograms with the same name in the same package, each taking parameters of different number, order or datatype. Necessity of overloading is - Sometimes the processing in two subprograms is the same. In that case it is logical to give them the same name. PL/SQL determines which subprogram is being called by checking its formal parameters. Only local or packaged subprograms can be overloaded.
You cannot overload:
•Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and their total number is same).
•Two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family (number and decimal belong to the same family)
•Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)
•Two functions that differ only in return type, even if the types are in different families.
A packaged procedure is better to use because:
In case of stand alone procedure, whenever it is called it has to be loaded into memory thus disk I/O takes place everytime while if the procedure is in a package then first time when we call the procedure from the package it gets loaded into memory and any later calls doesn’t require disk I/O.
Package procedure can be defined as public which can be called from other subprograms of the package and from outside the package. Similarly we can define public function and public variables.
What is FORWARD DECLARATION in Packages?
PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification in the package body terminated by a semicolon. You can use forward declarations to do the following:
•Define subprograms in logical or alphabetical order.
•Define mutually recursive subprograms.(both calling each other).
•Group subprograms in a package
Example of forward Declaration:
CREATE OR REPLACE PACKAGE BODY forward_pack
PROCEDURE calc_rating(. . .); -- forward declaration
PROCEDURE award_bonus(. . .)
IS -- subprograms defined
BEGIN -- in alphabetical order
calc_rating(. . .);
. . .
How can we drop a table and its dependent integrity constraints?
DROP TABLE table [CASCADE CONSTRAINTS];
Which table stores subprogram errors ?
How to change Oracle password ?
Alter user <username> identified by <pwd>
WITH GRANT OPTION with Grant command gives a user authority to pass along the privileges
GRANT select ON s_emp TO scott WITH GRANT OPTION;
How to see which user has logged in ?
What is the use of ON DELETE CASCADE clause in a table?
Allows deletion in the parent table and deletion of the dependent rows in the child table.
How to add comments on a table or column?
You can add comments to a table or column by using the COMMENT command.
SQL> COMMENT ON TABLE s_emp IS 'Employee Information';
How can one disable SQL*Plus prompting with &?
If you run a script that contains "&" symbols SQL*Plus thinks that you want to prompt the user for a value. To turn this off:
SET ESCAPE ON
SET ESCAPE "\"
SELECT 'You \& me' FROM DUAL;
SET DEFINE ?
SELECT 'You & me' FROM DUAL;
Note: You can disable substitution variable prompting altogether by issuing the SET SCAN OFF command
How does one trace SQL statement execution?
Run the PLUSTRCE.SQL script from the SYS database user. This script is located the in $ORACLE_HOME/sqlplus/admin.
Create a PLAN_TABLE using the UTLXPLAN.SQL script. This script is in $ORACLE_HOME/rdbms/admin.
Use the "SET AUTOTRACE ON" command to trace SQL execution. This will print the result of your query, an explain plan and high level trace information. Look at this example:
SQL> set autotrace on
SQL> select * from dual;
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
0 recursive calls
2 db block gets
1 consistent gets
0 physical reads
0 redo size
181 bytes sent via SQL*Net to client
256 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
How can users be prevented from executing devious commands?
Yes, command authorization is verified against the SYSTEM.PRODUCT_USER_PROFILE table. This table is created by the PUPBLD.SQL script. Note that this table is not used when someone signs on as user SYSTEM.
Eg. to disable all users whose names starts with OPS$ from executing the CONNECT command:
SQL> INSERT INTO SYSTEM.PRODUCT_USER_PROFILE VALUES ('SQL*Plus', 'OPS$%', 'CONNECT', NULL, NULL, 'DISABLED', NULL, NULL);
How can one disable SQL*Plus formatting?
Issue the following SET commands to disable all SQL*Plus formatting:
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
These settings can also be entered on one line, eg.:
SET ECHO OFF NEWPAGE 0 SPACE 0 PAGESIZE 0 FEED OFF HEAD OFF TRIMSPOOL ON
Can one send operating system parameters to SQL*Plus?
One can pass operating system variables to sqlplus using this syntax:
sqlplus username/password @cmdfile.sql var1 var2 var3
Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc. Look at this example:
sqlplus scott/tiger @x.sql '"test parameter"' dual
Where x.sql consists of:
select '&1' from &2;
What is WITH CHECK OPTION CONSTRAINT and WITH READ ONLY in a view ?
SQL> CREATE OR REPLACE VIEW empvu41
2 AS SELECT *
3 FROM s_emp
4 WHERE dept_id = 41
5 WITH CHECK OPTION CONSTRAINT empvu41_ck;
If you attempt to change the department number for any rows in the view, the statement will fail because it violates the CHECK OPTION constraint.
Ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition in case of simple views.
What is normalization and what are the 3 normal forms ?
Benefits of Normalization:
•Minimizes data redundancy
•Reduces integrity problems. (entity, column, referential integrity are maintained.)
•Identifies missing entities, relationships, and tables
First normal form All attributes must be single-valued. (Intersection of a row and column must be a single value).
Second normal form An attribute must depend upon its entity's entire UID (Unique identifier).
(Each table should have a PK column and Other columns of a table must depend on PK column of the table)
Third normal form No non-UID attribute can be dependent upon another non-UID attribute.
Advantage & Disadvantage of using Truncate command over Delete command?
Truncate table <table_name>
–Removes all rows from a table.
–Releases the storage space used by that table.
Disadvantage is that it is a DDL command so cant be rolled back.
How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
How can one search PL/SQL code for a key?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.
SELECT TYPE, NAME, LINE
WHERE UPPER(TEXT) LIKE '%&KEYWORD%';
* By using DBA_DEPENDENCIES table you can find out.
How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.
This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your
proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.plb
Can one read/write files from PL/SQL?
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started:
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
NOTE: The DDL statement in quotes should not be terminated with a semicolon.
Can one use dynamic SQL statements from PL/SQL?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements. Eg:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
when others then
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
Grant direct access on the tables to your user. Do not use roles!
GRANT select ON scott.emp TO my_user;
Define your procedures with invoker rights (Oracle 8i and higher);
Move all the tables to one user/schema.
Can one pass an object/table as an argument to a remote procedure?
The only way the same object type can be referenced between two databases is via a database link. Note that it is not enough to just use the same type definitions. Look at this example:
-- Database A: receives a PL/SQL table from database B
CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
-- do something with TabX from database B
-- Database B: sends a PL/SQL table to database A
CREATE OR REPLACE PROCEDURE pcalling IS
How does one count different data values in a column?
SELECT dept, sum( decode(sex,'M',1,0)) MALE,
sum( decode(sex,'F',1,0)) FEMALE,
GROUP BY dept;
How does one count/sum RANGES of data values in a column?
A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:
sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
GROUP BY f2;
Can one retrieve only the Nth row from a table?
WHERE rowid = (SELECT rowid
WHERE rownum <= 10
WHERE rownum < 10);
AND rowid NOT IN (SELECT rowid
WHERE rownum < 10);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
Can one retrieve only rows X to Y from a table?
To display rows 5 to 7, construct a query like this:
WHERE rowid in (
WHERE rownum <= 7
WHERE rownum < 5);
How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
WHERE (ROWID, 0) IN (
SELECT ROWID, MOD(ROWNUM,4)
Method 2: Use dynamic views (available from Oracle7.2):
SELECT rownum rn, empno, ename
WHERE MOD(temp.ROWNUM, 4) = 0;
How does one select the TOP N rows from a table?
Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:
ORDER BY col_name_1 DESC)
WHERE ROWNUM < 10;
How does one code a matrix report in SQL?
Look at this example query with sample output:
sum(decode(deptno,10, sal)) DEPT10,
sum(decode(deptno, 20, sal)) DEPT20,
sum(decode(deptno, 30,sal)) DEPT30,
GROUP BY job)
ORDER BY 1;
JOB DEPT10 DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------
CLERK 1300 1900 950
MANAGER 2450 2975 2850
- Oracle Apps Procure To Pay Cycle (P2P Cycle)
- Oracle Apps Order Management Tables
- Oracle SQL PLSQL Interview Questions
- Oracle EBS - Order Management Interview Questions
- Oracle Forms and Reports - Practice Questions
- Oracle Forms and Reports - Practice Questions -2
- Oracle Forms and Reports - Practice Test
- Oracle Forms and Reports - Practice Questions
- Oracle Reports Frequently Asked Questions (FAQ)/ Oracle Reports Interview Questions