What is PL/SQL

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

PL/SQL is Oracles Procedural Language extension to SQL. PL/SQLs language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, and information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.

Should one use PL/SQL or Java to code procedures and triggers?

Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?”
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
• Data centric and tightly integrated into the database.
• Proprietary to Oracle and difficult to port to other database systems.
• Data manipulation is slightly faster in PL/SQL than in Java.
• Easier to use than Java (depending on your background).
Java:
• Open standard, not proprietary to Oracle.
• Incurs some data conversion overhead between the Database and Java type systems.
• Java is more difficult to use (depending on your background).

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:
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 can one 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 can one 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. Look at this 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

Can one 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 havent 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.

Note that DBMS_OUTPUT doesnt print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP; Look at this example with this option first disabled and then enabled:

SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line(The next line is blank);
3 dbms_output.put_line();
4 dbms_output.put_line(The above line should be blank);
5 end;
6 /

The next line is blank
The above line should be blank

SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line(The next line is blank);
3 dbms_output.put_line();
4 dbms_output.put_line(The above line should be blank);
5 end;
6 /

The next line is blank
The above line should be blank

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.

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;

One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:

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

More complex DBMS_SQL example using bind variables:

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

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

 -===============

Rewrite complex subqueries with temporary tables Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements. Use minus instead of EXISTS subqueries Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan. Use SQL analytic functions The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL. Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join. select book_key from book where book_key NOT IN (select book_key from sales); Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan. select b.book_key from book b, sales s where b.book_key = s.book_key(+) and s.book_key IS NULL; Index your NULL values If you have SQL that frequently tests for NULL, consider creating an index on NULL values. To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns. Leave column names alone Never do a calculation on an indexed column unless you have a matching function-based index (a.k.a. FBI). Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF: where salary*5 > :myvalue where substr(ssn,7,4) = "1234" where to_char(mydate,mon) = "january" Avoid the use of NOT IN or HAVING Instead, a NOT EXISTS subquery may run faster (when appropriate). Avoid the LIKE predicate Always replace a "like" with equality, when appropriate. Never mix data types If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates: where cust_nbr = "123" where substr(ssn,7,4) = 1234 Dont fear full-table scans Not all OLTP queries are optimal when they uses indexes. If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan. This depends on many factors, including your configuration (values for db_file_multiblock_read_count, db_block_size), query parallelism and the number of table/index blocks in the buffer cache. Use those aliases Always use table aliases when referencing columns.