Oracle SQL PLSQL Interview Questions

Delete from emp x where rowid <= (select max(rowid) from emp y where x.kye_values = y.key_values)

Select * from (select * from emp order by sal desc) where rownum < 6

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.

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
IS
PROCEDURE calc_rating(. . .);        — forward declaration  
PROCEDURE award_bonus(. . .)
IS                                                            — subprograms defined
BEGIN                                                 — in alphabetical order
  calc_rating(. . .);      
 . . .
END;

DROP TABLE table [CASCADE CONSTRAINTS];

User_errors

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;

Sql>show user

Allows deletion in the parent table and deletion of the dependent rows in the child table.

You can add comments to a table or column by using the COMMENT command.
SQL> COMMENT ON TABLE s_emp   IS ‘Employee Information’;

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;

or

SET DEFINE ?
SELECT ‘You & me’ FROM DUAL;
Note: You can disable substitution variable prompting altogether by issuing the SET SCAN OFF command

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;
           D
             –
           X
Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF ‘DUAL’
Statistics
———————————————————-
          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

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);

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

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;
                exit 5;

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.

Benefits of Normalization:
•Minimizes data redundancy
•Reduces integrity problems. (entity, column, referential integrity are maintained.)
•Identifies missing entities, relationships, and tables
Rule                                                       Description
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.

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.

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:
        SELECT OBJECT_NAME,
                       TO_CHAR(CREATED,       ‘DD-Mon-RR HH24:MI’) CREATE_TIME,
                       TO_CHAR(LAST_DDL_TIME, ‘DD-Mon-RR HH24:MI’) MOD_TIME,
                       STATUS
            FROM USER_OBJECTS
         WHERE LAST_DDL_TIME > ‘&CHECK_FROM_DATE’;

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
            FROM USER_SOURCE
         WHERE UPPER(TEXT) LIKE ‘%&KEYWORD%’;
* By using DBA_DEPENDENCIES table you can find out.

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

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:
                DECLARE
                  fileHandler UTL_FILE.FILE_TYPE;
                BEGIN
                  fileHandler := UTL_FILE.FOPEN(‘/tmp’, ‘myfile’, ‘w’);
                  UTL_FILE.PUTF(fileHandler, ‘Look ma, I”m writing to a file!!!\n’);
                  UTL_FILE.FCLOSE(fileHandler);
                EXCEPTION
                  WHEN utl_file.invalid_path THEN
                     raise_application_error(-20000, ‘ERROR: Invalid path for file or path not in INIT.ORA.’);
                END;
                /

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).
                BEGIN
                   EXECUTE IMMEDIATE ‘CREATE TABLE X(A DATE)’;
                END;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.

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 integer;
                  rc  integer;
                BEGIN
                  cur := DBMS_SQL.OPEN_CURSOR;
                  DBMS_SQL.PARSE(cur, ‘CREATE TABLE X (Y DATE)’, DBMS_SQL.NATIVE);
                  rc := DBMS_SQL.EXECUTE(cur);
                  DBMS_SQL.CLOSE_CURSOR(cur);
                END;
                /
Another example:
                CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
                  v_cursor integer;
                  v_dname  char(20);
                  v_rows   integer;
                BEGIN
                  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);
                  loop
                    if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
                       exit;
                    end if;
                    DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
                    DBMS_OUTPUT.PUT_LINE(‘Deptartment name: ‘||v_dname);
                  end loop;
                  DBMS_SQL.CLOSE_CURSOR(v_cursor);
                EXCEPTION
                  when others then
                       DBMS_SQL.CLOSE_CURSOR(v_cursor);
                       raise_application_error(-20000, ‘Unknown Exception Raised: ‘||sqlcode||’ ‘||sqlerrm);
                END;
                /

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.

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
                BEGIN
                   — do something with TabX from database B
                   null;
                END;
                /
                — Database B: sends a PL/SQL table to database A
                CREATE OR REPLACE PROCEDURE pcalling IS
                   TabX DBMS_SQL.VARCHAR2S@DBLINK2;
                BEGIN
                   pcalled@DBLINK2(TabX);
                END;
                /

        SELECT dept, sum(  decode(sex,’M’,1,0)) MALE,
                       sum(  decode(sex,’F’,1,0)) FEMALE,
                       count(decode(sex,’M’,1,’F’,1)) TOTAL
           FROM  my_emp_table
        GROUP BY dept;

A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:
        SELECT f2,
                       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”
            FROM my_table
        GROUP BY f2;

        SELECT f1
           FROM t1
        WHERE  rowid = (SELECT rowid
                                         FROM t1
                                        WHERE rownum <= 10
   MINUS
                                               SELECT rowid
    FROM t1
                                                WHERE rownum < 10);
Alternatively…
        SELECT *
            FROM emp
         WHERE rownum=1
               AND rowid NOT IN (SELECT rowid
        FROM emp
     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.

To display rows 5 to 7, construct a query like this:
        SELECT *
            FROM tableX
         WHERE rowid in (
                                               SELECT rowid
   FROM tableX
                                               WHERE rownum <= 7
                                                  MINUS
                                               SELECT rowid
    FROM tableX
                                                WHERE rownum < 5);

One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
        SELECT *
           FROM emp
        WHERE (ROWID, 0) IN (
SELECT ROWID, MOD(ROWNUM,4)
                                                                 FROM emp);
Method 2: Use dynamic views (available from Oracle7.2):
        SELECT *
            FROM (
SELECT rownum rn, empno, ename
                                  FROM emp
               ) temp
         WHERE MOD(temp.ROWNUM, 4) = 0;

Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:
        SELECT *
            FROM (
SELECT *
    FROM my_table
ORDER BY col_name_1 DESC)
                                WHERE ROWNUM < 10;

Look at this example query with sample output:
        SELECT *
            FROM (
SELECT job,
                                            sum(decode(deptno,10, sal)) DEPT10,
                                              sum(decode(deptno, 20, sal)) DEPT20,
                                              sum(decode(deptno, 30,sal)) DEPT30,
                                              sum(decode(deptno,40,sal)) DEPT40
                                 FROM scott.emp
                              GROUP BY job)
        ORDER BY 1;
        JOB                              DEPT10                DEPT20                DEPT30                DEPT40
        ———                           ———-                   ———-                   ———-                   ———-
        ANALYST                                                   6000
        CLERK                         1300                      1900                                                      950
        MANAGER                  2450                      2975                                                      2850
        PRESIDENT               5000
        SALESMAN                                                                                                              5600