PL/SQL

What is SQL and where does it come from ?

Structured Query Language (SQL) is a language that provides an interface to relational database systems.

SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced as SEQUEL.

What are the difference between DDL, DML and DCL commands ?

DDL is called Data Definition Language. For examples,

CREATE – to create objects in the database
ALTER – alters the structure of the database
DROP – delete objects from the database
TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
COMMENT – add comments to the data dictionary
GRANT – gives user’s access privileges to database
REVOKE – withdraw access privileges given with the GRANT command

DML is called Data Manipulation Language. For examples,

SELECT – retrieve data from the a database
INSERT – insert data into a table
UPDATE – updates existing data within a table
DELETE – deletes all records from a table, the space for the records remain
CALL – call a PL/SQL or Java subprogram
EXPLAIN PLAN – explain access path to data
LOCK TABLE – control concurrency

DCL is called Data Control Language. For examples,

COMMIT – save work done
SAVEPOINT – identify a point in a transaction to which you can later roll back
ROLLBACK – restore database to original since the last COMMIT
SET TRANSACTION – Change transaction options like what rollback segment to use

How does one escape special characters when building SQL queries ?

The LIKE keyword allows for string searches. The ‘_’ wild card character is used to match exactly one character, ‘%’ is used to match zero or more occurrences of any characters. These characters can be escaped in SQL.

Example:

SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';

How does one eliminate duplicates rows from a table ?

Any one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:

Method 1:

DELETE FROM table_name A
        WHERE ROWID > (SELECT min(rowid) FROM table_name B
            WHERE A.key_values = B.key_values);

Method 2:

create table table_name2 as select distinct * from table_name1;
    drop table_name1;
    rename table_name2 to table_name1;

Method 3:

delete from my_table t1
        where  exists (select 'x' from my_table t2
            where t2.key_value1 = t1.key_value1
                and t2.key_value2 = t1.key_value2
                    and t2.rowid      > t1.rowid);

How does one generate primary key values for a table ?

Create your table with a NOT NULL column (say SEQNO). This column can now be populated with unique values:

 UPDATE table_name SET seqno = ROWNUM;

or use a sequences generator:

CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
UPDATE table_name SET seqno = sequence_name.NEXTVAL;

Finally, create a unique index on this column.

How does one add a day/hour/minute/second to a date value ?

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:

select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

Output:

SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
    -------------------- -------------------- -------------------- --------------------
    03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13

How does one count different data values in a column ?

Use this simple query to count the number of data values in a column:

select my_table_column, count(*)
    from   my_table
    group  by my_table_column;

How to retrieve only the Nth row from a table ?

Solution to select the Nth row from a table:

SELECT * FROM (
        SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < N+1 )  
     WHERE  RN = N;

Note: In this first it select only one more than the required row, then it selects the required one. Its far better than using MINUS operation.

Another alternative:

SELECT f1 FROM t1
        WHERE  rowid = (
           SELECT rowid FROM t1
           WHERE  rownum <= N
          MINUS
           SELECT rowid FROM t1
           WHERE  rownum < N);

Alternatively,

SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
           (SELECT rowid FROM emp WHERE rownum < N);

How to retrieve only rows X to Y from a table ?

Solution to the problem:

SELECT * FROM (
        SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < Y+1  
     ) WHERE  RN between X+1 and Y ;

Another solution is to use the MINUS operation.

SELECT *
        FROM   tableX
        WHERE  rowid in (
           SELECT rowid FROM tableX
           WHERE rownum <= Y
          MINUS
           SELECT rowid FROM tableX
           WHERE rownum < X);

How to 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

SELECT *
        FROM   emp
        WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                             FROM   emp);

Method 2: Use dynamic views (available since Oracle7.2):

SELECT *
        FROM   ( SELECT rownum rn, empno, ename
                 FROM emp
               ) temp
        WHERE  MOD(temp.ROWNUM,4) = 0;

How to select the top N rows from a table ?

Form Oracle8i one can have an inner-query with an ORDER BY clause.

SELECT *
        FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)
        WHERE  ROWNUM < N;

Use this workaround with prior releases:

SELECT *
          FROM my_table a
         WHERE N >= (SELECT COUNT(DISTINCT maxcol)
                        FROM my_table b
                       WHERE b.maxcol >= a.maxcol)
         ORDER BY maxcol DESC;

What is PL/SQL and what is it used for ?

PL/SQL is Oracle’s Procedural Language extension to SQL. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.

How to find 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:

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

How to search PL/SQL code for a string/key value ?

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%';

How to keep a history of PL/SQL code changes ?

One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available since Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes.

Example:

CREATE TABLE SOURCE_HIST                     -- Create history table
          AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
             FROM   USER_SOURCE WHERE 1=2;

        CREATE OR REPLACE TRIGGER change_hist        -- Store code in hist table
               AFTER CREATE ON SCOTT.SCHEMA          -- Change SCOTT to your schema name
        DECLARE
        BEGIN
          if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
                          'PACKAGE', 'PACKAGE BODY', 'TYPE') then
             -- Store old code in SOURCE_HIST table
             INSERT INTO SOURCE_HIST
                SELECT sysdate, user_source.* FROM USER_SOURCE
                WHERE  TYPE = DICTIONARY_OBJ_TYPE
                  AND  NAME = DICTIONARY_OBJ_NAME;
          end if;
        EXCEPTION
          WHEN OTHERS THEN
               raise_application_error(-20000, SQLERRM);
        END;
        /
        show errors

How to protect 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 we 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

How one can print to the screen from PL/SQL ?

One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:

set serveroutput on
    begin
       dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
    end;
    /

DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000

If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven’t cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.

How to 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.

Example:

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

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

begin
    EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;

NOTE: The DDL statement in quotes should not be terminated with a semicolon.

Can one use dynamic SQL statements from PL/SQL ?

Starting from Oracle8i one can use the “EXECUTE IMMEDIATE” statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:

EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

    -- Using bind variables...
    sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
    EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

        -- Returning a cursor...
    sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
    EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

What is the difference between %TYPE and %ROWTYPE ?

The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor.

Example:

DECLARE
       v_EmpRecord  emp%ROWTYPE;

%TYPE is used to declare a field with the same type as that of a specified table’s column.

Example:

DECLARE
       v_EmpNo  emp.empno%TYPE;

What is the result of comparing NULL with NULL ?

NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.

declare
      a number := NULL;
      b number := NULL;
    begin
      if a=b then
         dbms_output.put_line('True, NULL = NULL');
      elsif a<>b then
         dbms_output.put_line('False, NULL <> NULL');
      else
         dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
      end if;
    end;

How does one get the value of a sequence into a PL/SQL variable ?

As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this:

i := sq_sequence.NEXTVAL;

However, one can use embedded SQL statements to obtain sequence values:

select sq_sequence.NEXTVAL into :i from dual;

How does one loop through tables in PL/SQL ?

Look at the following nested loop code example.

DECLARE
       CURSOR dept_cur IS
       SELECT deptno
         FROM dept
        ORDER BY deptno;
       -- Employee cursor all employees for a dept number
       CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
       SELECT ename
         FROM emp
        WHERE deptno = v_dept_no;
    BEGIN
       FOR dept_rec IN dept_cur LOOP
          dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
          FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
             dbms_output.put_line('...Employee is '||emp_rec.ename);
          END LOOP;
      END LOOP;
    END;
    /

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.

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

Is there a limit on the size of a PL/SQL block ?

Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure:

select * from dba_object_size where name = 'procedure_name';

What is locking and what is it used for ?

Locking is a mechanism to restrict access to data. It prevents one user updating data whilst another user is looking at it.

What types of locking are available with Oracle ?

Row locking and table locking.

What happens if a tablespace clause is left off of a primary key constraint clause ?

This results in the index that is automatically generated being placed in the users default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.

What packages (if any) has Oracle provided for use by developers ?

Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.

When should more than one DB writer process be used? How many should be used ?

If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter.

Explain the use of the WHERE clause.

It directs SQL to extract data from rows where the value of the column is the same as the current value of the WHERE clause variable.

What technique is used to retrieve data from more than one table in a single SQL statement ?

The Join statement combines data from more that two tables.

What is a foreign key ?

It identifies a related row in another table and establishes a logical relationship between rows in two tables.

What are the reasons for adding an index to a table ?

To increase lookup performance. For a unique key to guarantee the uniqueness of the values for a column on a table.

What is a primary key on a table used for ?

Guaranteeing the uniqueness of a column on a table.

What is the purpose of a view ?

Views can be used to join and display data from multiple tables and to limit the number of columns and/or rows that are selected and displayed.

How can you get the alternate records from the table in the SQL?

If you want to fetch the odd numbers then the following query can be used:

SELECT EmpId from (SELECT rowno,EmpId from Emp) WHERE mod(rowno,2)=1;

If you want to fetch the even numbers, then the following query can be used:

SELECT EmpId from (SELECT rowno,EmpId from Emp) WHERE mod(rowno,2)=0;