Oracle PL/SQL FAQ

$Date: 05-Jan-2000 $
$Revision: 1.50a $
$Author: Frank Naudé $

Topics

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

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

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;

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

i := sq_sequence.NEXTVAL;

select sq_sequence.NEXTVAL into :i from dual;

Thanks to Ronald van Woensel

In Oracle8 you can call external 3GL code in a dynamically linked
library (DLL or shared object). So you just write a library in C doing
what you want, ie in your case a host function taking the command line
as input argument.  And that function will be callable from PL/SQL.

So what you have to do is more or less:

1. Write C code in host.c: int host(char *command) { ... }.
2. Compile C code to DLL or shared object, eg
c:\winnt\system32\host.dll.
3. "create or replace library host as 'c:\winnt\system32\host.dll';"
4. "create or replace function host(command in varchar2) return
pls_integer
    is external
    library host
    name "host"
    language c
    calling standard pascal
    parameters (host string, return long);"

Let's say I had a PL/SQL block and I wanted to do a "ls -l" to get a
directory listing.  Is there any way to do that?

In C Language, I can do
  {
    x = system("ls -l");
  }

The way most people do that is to use the pipe facility of the DBMS
kernel: set up a pipe for use by a sender (the PL/SQL program that
needs to invoke a command) and a receiver, written in Pro*C, responsible for
executing the command piped by the sender. Maybe there are some tools
around so one doesn't need to code this, as it involves a listener and
multiple server processes on your machine.

Alternatively I have a more complex solution, which uses a table to
pass arguments and the command to execute - just as the DBMS_PIPE package
does internally. You would insert a row into the table and the
listener would execute a command, passing back succession status and
indicating "in progress" on long-running commands. This tools allows for
non-blocking execution of commands.

  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;

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

-- Database A: receives a PL/SQL table from database B
CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
BEGIN
   null;
END;
/

-- Database B: send a PL/SQL table to database A
CREATE OR REPLACE PROCEDURE pcalling IS
   TabX DBMS_SQL.VARCHAR2S@DBLINK2;
BEGIN
   pcalled@DBLINK2(TabX);
END;
/

You can run the following select statement to query the size of an existing package or procedure: SQL> select * from dba_object_size where name = 'procedure_name'

• Oracle's PL/SQL Home Page

• PLSolutions.com