PL/SQL FAQ - Oracle's Procedural Language extension
to SQL:
Contents
·
2 What is the
difference between SQL and PL/SQL?
·
3
Should one use PL/SQL or Java to code procedures and triggers?
·
4 How can one see if
somebody modified any code?
·
5 How can
one search PL/SQL code for a string/ key value?
·
6 How does one
keep a history of PL/SQL code changes?
·
7 How can I protect my
PL/SQL source code?
·
8 Can one print to the
screen from PL/SQL?
·
9 Can one read/write files
from PL/SQL?
·
10 Can one call DDL
statements from PL/SQL?
What is PL/SQL and what is it used for?
SQL is a declarative language that allows
database programmers to write a SQL declaration and hand it to the database for
execution. As such, SQL cannot be used to execute procedural code with
conditional, iterative and sequential statements. To overcome this limitation,
PL/SQL was created.
PL/SQL is Oracle's Procedural Language
extension to SQL. PL/SQL's language syntax, structure and data types are
similar to that of Ada. Some of the statements provided by
PL/SQL:
Conditional
Control Statements:
·
IF ... THEN ... ELSIF ... ELSE
... END IF;
·
CASE ... WHEN ... THEN ... ELSE
... END CASE;
Iterative
Statements:
·
LOOP ... END LOOP;
·
WHILE ... LOOP ... END LOOP;
·
FOR ... IN [REVERSE] ... LOOP
... END LOOP;
Sequential
Control Statements:
·
GOTO ...;
·
NULL;
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
/* Remember to SET SERVEROUTPUT ON
to see the output */
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/
BEGIN
-- A PL/SQL cursor
FOR cursor1 IN (SELECT * FROM table1) -- This is an embedded SQL
statement
LOOP
DBMS_OUTPUT.PUT_LINE('Column 1 = ' || cursor1.column1 ||
', Column 2 = ' ||
cursor1.column2);
END LOOP;
END;
/
What is the difference between SQL and PL/SQL?
Both SQL and PL/SQL are languages used to
access data within Oracle databases.
SQL is a limited language that allows you
to directly interact with the database. You can write queries (SELECT),
manipulate objects (DDL) and data (DML) with SQL. However, SQL doesn't include
all the things that normal programming languages have, such as loops and
IF...THEN...ELSE statements.
PL/SQL is a normal programming language
that includes all the features of most other programming languages. But, it has
one thing that other programming languages don't have: the ability to easily
integrate with SQL.
Some of the differences:
·
SQL is executed one statement
at a time. PL/SQL is executed as a block of code.
·
SQL tells the database what to
do (declarative), not how to do it. In contrast, PL/SQL tell the database how
to do things (procedural).
·
SQL is used to code queries, DML
and DDL statements. PL/SQL is used to code program blocks, triggers, functions,
procedures and packages.
·
You can embed SQL in a PL/SQL
program, but you cannot embed PL/SQL within a SQL statement.
Should one use PL/SQL or Java to code procedures and triggers?
Both PL/SQL and Java can be used to
create Oracle stored procedures and triggers. This often 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 9i supports
native compilation of Pl/SQL code to binaries. Not to mention the numerous
PL/SQL enhancements made in Oracle 10g and 11g.
PL/SQL and Java appeal to different
people in different job roles. The following table briefly describes the
similarities and difference between these two language environments:
PL/SQL:
·
Can be used to create Oracle
packages, procedures and triggers
·
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
·
PL/SQL is a traditional
procedural programming language
Java:
·
Can be used to create Oracle
packages, procedures and triggers
·
Open standard, not proprietary
to Oracle
·
Incurs some data conversion
overhead between the Database and Java type
·
Java is an Object Orientated
language, and modules are structured into classes
·
Java can be used to produce
complete applications
PS: Starting with Oracle 10g, .NET
procedures can also be stored within the database (Windows only). Nevertheless, unlike PL/SQL and JAVA, .NET
code is not usable on non-Windows systems.
PS: In earlier releases of Oracle it was
better to put as much code as possible in procedures rather than triggers. At
that stage procedures executed faster than triggers as triggers had to be
re-compiled every time before executed (unless cached). In more recent releases
both triggers and procedures are compiled when created (stored p-code) and one
can add as much code as one likes in either procedures or triggers. However, it
is still considered a best practice to put as much of your program logic as
possible into packages, rather than triggers.
How can one see if somebody modified any code?
The source code for stored procedures,
functions and packages are stored in the Oracle Data Dictionary. One can detect
code changes by looking at the TIMESTAMP and 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';
Note: If you recompile an object, the
LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated. If
you modified the code, both the TIMESTAMP and LAST_DDL_TIME columns are
updated.
How can one search PL/SQL code for a string/ key value?
The following query is handy if you want
to know where certain tables, columns and expressions are referenced in your
PL/SQL source code.
SELECT type, name, line
FROM user_source
WHERE
UPPER(text) LIKE UPPER('%&KEYWORD%');
If you run the above query from SQL*Plus,
enter the string you are searching for when prompted for KEYWORD. If not,
replace &KEYWORD with the string you are searching for.
How does 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 will allow you to 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, ALL_SOURCE.*
FROM ALL_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 ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY',
'TYPE', 'TYPE BODY')
THEN
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, all_source.* FROM
ALL_SOURCE
WHERE TYPE = ORA_DICT_OBJ_TYPE -- DICTIONARY_OBJ_TYPE IN 8i
AND NAME = ORA_DICT_OBJ_NAME; --
DICTIONARY_OBJ_NAME IN 8i
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000,
SQLERRM);
END;
/
show
errors
A better approach is to create an external
CVS or SVN repository for the scripts that
install the PL/SQL code. The canonical version of what's in the database must
match the latest CVS/SVN version or else someone would be cheating.
How can I protect my PL/SQL source code?
Oracle provides a binary wrapper utility
that can be used to scramble PL/SQL source code. This utility was introduced in
Oracle7.2 (PL/SQL V2.2) and is located in the ORACLE_HOME/bin directory.
The utility use human-readable PL/SQL
source code as input, and writes out portable binary object code (somewhat
larger than the original). The binary code can be distributed without fear of
exposing your proprietary algorithms and methods. Oracle will still understand
and know how to execute the code. Just be careful, there is no
"decode" command available. So, don't lose your source!
The syntax is:
wrap
iname=myscript.pls oname=xxxx.plb
Please note: there is no way to unwrap a
*.plb binary file. You are supposed to backup and keep your *.pls source files
after wrapping them.
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
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.
To display an empty line, it is better to
use new_line procedure than put_line with an empty string.
Can one read/write files from PL/SQL?
The UTL_FILE database package can be used
to read and write operating system files.
A DBA user needs to grant you access to
read from/ write to a specific directory before using this package. Here is an
example:
CONNECT / AS SYSDBA
CREATE OR REPLACE DIRECTORY mydir AS
'/tmp';
GRANT
read, write ON DIRECTORY mydir TO scott;
Provide user access to the UTL_FILE
package (created by catproc.sql):
GRANT
EXECUTE ON UTL_FILE TO scott;
Copy and paste these examples to get you
started:
Write
File
DECLARE
fHandler UTL_FILE.FILE_TYPE;
BEGIN
fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'w');
UTL_FILE.PUTF(fHandler, 'Look ma, Im
writing to a file!!!\n');
UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set
UTL_FILE_DIR.');
END;
/
Read
File
DECLARE
fHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
BEGIN
fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'r');
UTL_FILE.GET_LINE(fHandler, buf);
dbms_output.put_line('DATA FROM FILE: '||buf);
UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set
UTL_FILE_DIR.');
END;
/
NOTE: UTL_FILE was introduced with Oracle
7.3. Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT
with the SQL*Plus SPOOL command.
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 IMMEDIATE"
statement (native SQL). Examples:
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
begin
execute Immediate 'TRUNCATE TABLE emp'; end;
DECLARE
var VARCHAR2(100);
BEGIN
var := 'CREATE TABLE temp1(col1 NUMBER(2))';
EXECUTE IMMEDIATE var;
END;
NOTE: The DDL statement in quotes should
not be terminated with a semicolon.
Users running Oracle versions below
Oracle 8i can look at the DBMS_SQL package (see FAQ about
Dynamic SQL).
Posts
