PL/SQL

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

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

3. 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:

4. 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:

Method 2:

Method 3:

 

5. 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:

or use a sequences generator:

Finally, create a unique index on this column.

6. 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:

Output

7. How does one count different data values in a column ?

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

8. How to retrieve only the Nth row from a table ?

Solution to select the Nth row from a table:

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:

Alternatively,

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

Solution to the problem:

Another solution is to use the MINUS operation.

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

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

11. How to select the top N rows from a table ?

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

Use this workaround with prior releases:

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

13. 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:

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

15. 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 from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes.

Example:

16. 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:

17. 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:

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.

18. 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:

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

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

20. 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:

21. 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:

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

Example:

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

23. 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:

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

24. How does one loop through tables in PL/SQL ?

Look at the following nested loop code example.

25. 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!

    Define your procedures with invoker rights (Oracle 8i and higher);
Move all the tables to one user/schema.

26. 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:

27. 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:

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

29. What types of locking are available with Oracle ?

Row locking and table locking.

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

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

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

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

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

35. What is a foreign key ?

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

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

37. What is a primary key on a table used for ?

Guaranteeing the uniqueness of a column on a table.

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