Oracle PL/SQL FAQ

$Date: 17-Dec-2001 $
$Revision: 2.05 $
$Author: Frank Naudé $

Topics

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:

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

        SELECT TYPE, NAME, LINE
        FROM   USER_SOURCE
        WHERE  UPPER(TEXT) LIKE '%&KEYWORD%';

        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

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

	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.

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

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

	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;

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

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

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

	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;

	i := sq_sequence.NEXTVAL;

	select sq_sequence.NEXTVAL into :i from dual;

Thanks to Ronald van Woensel

In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure 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;
	/

To fix this problem one can easily rewrite code like this:

	FOR records IN my_cursor LOOP
	   ...do some stuff...
	   COMMIT;
	END LOOP;

	FOR records IN my_cursor LOOP
	   ...do some stuff...
	   i := i+1;
	   IF mod(i, 10000) THEN    -- Commit every 10000 records
	      COMMIT;
	   END IF;
	END LOOP;

NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.

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

Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

There are several restrictions in Oracle regarding triggers:

• A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
• A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
• Etc.

Can one pass an object/table as an argument to a remote procedure?

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

	SQL> select * from dba_object_size where name = 'procedure_name';

• Oracle FAQ: PL/SQL code examples

• Oracle FAQ: PL/SQL Books

• PLNet.org - An open source repository for PL/SQL developers

• RevealNet PL/SQL Pipeline - A free community for Oracle developers worldwide

• The PL/SQL Cellar - Free Oracle PL/SQL scripts including a bitwise operations package and message digest algorithms

• PLSolutions.com - PL/Solutions provides consulting and training services for the Oracle PL/SQL language and PL/Vision

• The DMOZ PL/SQL Directory