Title: External Procedures
This is a sample program to demostrate the creation and usage of external procedures feature of PL/SQL. This External Procedures demo program consists of two files extproc.sql & extproc.c. extproc.c contains the C functions that are referenced in extproc.sql.Source/Text/Comments
Rem
Rem extproc.sql
Rem
Rem
Rem Copyright (c) Oracle Corporation 1997, 1998. All Rights Reserved.
Rem
Rem NAME
Rem
Rem extproc.sql - Demo program for external procedures.
Rem
Rem DESCRIPTION
Rem
Rem This is a sample program to demostrate the creation and usage of
Rem external procedures feature of PL/SQL.
Rem
Rem REQUIREMENTS
Rem
Rem o ORACLE database user SCOTT exists with demo tables
Rem EMP and DEPT.
Rem
Rem o User SCOTT has CREATE LIBRARY privileges.
Rem
Rem o Package DBMS_OUTPUT is installed for the database.
Rem
Rem
Rem NOTES ON EXECUTING THIS DEMO PROGRAM
Rem
Rem o This External Procedures demo program consists of two files
Rem
Rem extproc.sql & extproc.c
Rem
Rem extproc.c contains the C functions that are referenced in
Rem extproc.sql (this file)
Rem
Rem o Compile extproc.c to generate a shared library extproc.so
Rem (or extproc.dll on NT).
Rem Assume you create /tmp/extproc.so
Rem (or ORACLE_HOME\plsql81\extproc.dll on NT).
Rem
Rem o Since this demo program requires the EMP and DEPT tables
Rem which are installed under ORACLE database user SCOTT,
Rem request the ORACLE System Administrator to grant
Rem 'CREATE LIBRARY' privilege to ORACLE database user SCOTT.
Rem
Rem o Create a PL/SQL library named 'demolib' for user SCOTT.
Rem
Rem CONNECT SCOTT/TIGER
Rem CREATE OR REPLACE LIBRARY demolib IS '/tmp/extproc.so';
Rem /
Rem
Rem or on an NT system
Rem
Rem CONNECT SCOTT/TIGER
Rem CREATE OR REPLACE LIBRARY demolib IS
Rem 'ORACLE_HOME\plsql81\extproc.dll';
Rem /
Rem
Rem Note that the full path name of the shared library is
Rem used.
Rem
Rem o Connect as SCOTT/TIGER and run extproc.sql
Rem
Rem CONNECT SCOTT/TIGER
Rem @extproc.sql
Rem
Rem
CREATE OR REPLACE PACKAGE demopack IS
--
-- Update the salary using the rowid.
--
PROCEDURE UpdateSalary(row_id IN CHAR);
--
-- Calculate % commission with respect to the salary.
--
FUNCTION PercentComm(sal IN FLOAT, comm IN FLOAT) RETURN FLOAT;
PRAGMA RESTRICT_REFERENCES(PercentComm, WNDS);
--
-- Calculate %commission with respect to the salary.
-- This function is similar to function PercentComm but
-- uses the BY REFERENCE qualifier to pass C parameters
--
FUNCTION PercentComm_byref(sal IN DOUBLE PRECISION, comm IN DOUBLE PRECISION)
RETURN double precision;
PRAGMA RESTRICT_REFERENCES(PercentComm_byref, WNDS);
--
-- Get the experience of the employee.
--
PROCEDURE EmployeeExperience(hiredate IN DATE,
num_experience OUT BINARY_INTEGER);
--
-- Copy the new_name to old_name only if the new_name can fit.
-- To ensure this, we pass the MAXLEN of old_name and check if
-- LENGTH of new_name is <= MAXLEN of old_name. If the check
-- fails, the C calout will raise an exception.
-- This function cannot be called from SQL since by default
-- this function is considered impure.
--
FUNCTION CheckEmpName(old_ename IN OUT VARCHAR2,
new_ename IN VARCHAR2) RETURN BINARY_INTEGER;
--
-- demo_procedure calls the external functions
--
PROCEDURE Demo_Procedure;
end demopack;
/
show errors
CREATE OR REPLACE PACKAGE BODY demopack IS
PROCEDURE UpdateSalary(row_id IN CHAR)
IS EXTERNAL
NAME "UpdateSalary"
LIBRARY demolib
WITH CONTEXT
PARAMETERS (CONTEXT,
row_id STRING,
row_id LENGTH SB4);
FUNCTION PercentComm(sal IN FLOAT, comm IN FLOAT) RETURN FLOAT
IS EXTERNAL
NAME "PercentComm"
LIBRARY demolib
PARAMETERS (sal FLOAT,
sal INDICATOR SHORT,
comm FLOAT,
comm INDICATOR SHORT,
RETURN INDICATOR SHORT,
RETURN FLOAT);
FUNCTION PercentComm_ByRef(sal IN DOUBLE PRECISION,
comm IN DOUBLE PRECISION) RETURN double precision
IS EXTERNAL
NAME "PercentComm_ByRef"
LIBRARY demolib
WITH CONTEXT
PARAMETERS (CONTEXT,
sal BY REFERENCE DOUBLE,
sal INDICATOR SHORT,
comm BY REFERENCE DOUBLE,
comm INDICATOR BY REFERENCE SHORT,
RETURN INDICATOR SHORT,
RETURN BY REFERENCE DOUBLE);
PROCEDURE EmpExp(hiredate IN VARCHAR2, num_experience OUT BINARY_INTEGER)
IS EXTERNAL
NAME "EmpExp"
LIBRARY demolib
PARAMETERS (hiredate STRING,
hiredate LENGTH INT,
hiredate INDICATOR SHORT,
num_experience LONG,
num_experience INDICATOR INT);
PROCEDURE EmployeeExperience(hiredate IN DATE,
num_experience OUT BINARY_INTEGER) IS
BEGIN
--
-- Call EmpExp by wrapping a to_char with a format mask on the hiredate
--
EmpExp(to_char(hiredate, 'DD-MM-YY'), num_experience);
END EmployeeExperience;
FUNCTION CheckEmpName(old_ename IN OUT VARCHAR2,
new_ename IN VARCHAR2) RETURN BINARY_INTEGER
IS EXTERNAL
NAME "CheckEmpName" LIBRARY demolib
WITH CONTEXT
PARAMETERS (old_ename STRING,
old_ename MAXLEN SB4,
new_ename STRING,
new_ename LENGTH SB4,
CONTEXT,
RETURN INT);
PROCEDURE Demo_Procedure is
rowid_t CHAR(18);
ename_t VARCHAR2(20);
job_t VARCHAR2(20);
sal_t NUMBER;
comm_t FLOAT;
PreComm_t FLOAT;
PreComm_tmp DOUBLE PRECISION;
eno_t NUMBER;
hire_date DATE;
num_exp_c BINARY_INTEGER;
num_exp_plsql BINARY_INTEGER;
old_ename emp.ename%TYPE;
retval number;
begin
-- Get rowid for SMITH.
SELECT rowid
INTO rowid_t
FROM emp
WHERE ename = 'SMITH' FOR UPDATE;
-- Update the salary by passing the rowid.
demopack.UpdateSalary(rowid_t);
-- Calculate the Percent Commission for ALLEN.
SELECT ename, job, sal, comm, demopack.PercentComm(sal, comm)
INTO ename_t, job_t, sal_t, comm_t, PreComm_t
FROM emp
WHERE ename = 'ALLEN';
dbms_output.new_line;
dbms_output.put_line (' ENAME : ' || ename_t);
dbms_output.put_line (' JOB : ' || job_t);
dbms_output.put_line (' SALARY : ' || sal_t);
dbms_output.put_line (' COMMISSION : ' || comm_t);
dbms_output.put_line (' Percent Commission : ' || PreComm_t);
-- Calculate the Percent Commission for MARTIN.
SELECT ename, job, sal, comm, demopack.PercentComm_ByRef(sal, comm)
INTO ename_t, job_t, sal_t, comm_t, PreComm_tmp
FROM emp
WHERE ename = 'MARTIN';
dbms_output.new_line;
dbms_output.put_line (' ENAME : ' || ename_t);
dbms_output.put_line (' JOB : ' || job_t);
dbms_output.put_line (' SALARY : ' || sal_t);
dbms_output.put_line (' COMMISSION : ' || comm_t);
dbms_output.put_line (' Percent Commission : ' || PreComm_tmp);
-- Check if the employee name fits in the ENAME column.
SELECT ename
INTO old_ename
FROM emp
WHERE ename = 'SMITH';
retval := CheckEmpName(old_ename, 'ANIL');
dbms_output.new_line;
dbms_output.put_line (' Return value from CheckEmpName : ' || retval);
dbms_output.put_line (' old_ename value on return : ' || old_ename);
-- Find the number of years of experience of an employee.
SELECT empno, hiredate
INTO eno_t, hire_date
FROM emp
WHERE ename ='SMITH';
EmployeeExperience(hire_date, num_exp_c);
SELECT (sysdate-hire_date)
INTO num_exp_plsql
FROM emp
WHERE ename = 'SMITH';
dbms_output.new_line;
dbms_output.put_line (' ENAME : ' || eno_t);
dbms_output.put_line (' HIREDATE : ' || hire_date);
if num_exp_c != num_exp_plsql then
dbms_output.put_line (' Employee Experience Test Passed.');
else
dbms_output.put_line (' Employee Experience Test Failed.');
end if;
dbms_output.new_line;
dbms_output.put_line('***************************************');
dbms_output.new_line;
rollback; -- Rolling back the changes made to the demo tables.
END Demo_Procedure;
END demopack;
/
show errors
set serveroutput on
execute demopack.demo_procedure;
/
Rem --------------------------------------------------
Rem
Rem Example using Lobs in External Procedures.
Rem
Rem Create table to hold lob data.
DROP TABLE lobtab;
CREATE TABLE lobtab (
id number,
clobcol clob,
blobcol blob,
nclobcol nclob
);
Rem Insert some rows into lobtab table.
INSERT INTO lobtab VALUES (
1,
'Just trying to insert some data into a CLOB column',
'AAAABBBBCCCCDDDDEEEEFFFF111122223333444455556666',
N'Just trying to insert some data into a NCLOB column'
);
INSERT INTO lobtab VALUES (
2,
empty_clob(),
empty_blob(),
empty_clob()
);
INSERT INTO lobtab VALUES (
3,
NULL,
NULL,
NULL
);
COMMIT;
Rem Create external procedure for CLOB demo.
CREATE OR REPLACE FUNCTION clob_demo
( lob_in IN CLOB,
lob_inout IN OUT CLOB,
lob_out OUT CLOB
) RETURN CLOB
IS EXTERNAL
NAME "LobDemo"
LIBRARY demolib
WITH CONTEXT
PARAMETERS
(
context,
lob_in,
lob_inout,
lob_out,
lob_in INDICATOR SB4,
lob_inout INDICATOR SB4,
lob_out INDICATOR SB4,
RETURN INDICATOR SB4
);
/
show errors;
Rem Create external procedure for BLOB demo.
CREATE OR REPLACE FUNCTION blob_demo
( lob_in IN BLOB,
lob_inout IN OUT BLOB,
lob_out OUT BLOB
) RETURN BLOB
IS EXTERNAL
NAME "LobDemo"
LIBRARY demolib
WITH CONTEXT
PARAMETERS
(
context,
lob_in,
lob_inout,
lob_out,
lob_in INDICATOR SB4,
lob_inout INDICATOR SB4,
lob_out INDICATOR SB4,
RETURN INDICATOR SB4
);
/
show errors;
Rem Create external procedure for NCLOB demo.
CREATE OR REPLACE FUNCTION nclob_demo
( lob_in IN NCLOB,
lob_inout IN OUT NCLOB,
lob_out OUT NCLOB
) RETURN NCLOB
IS EXTERNAL
NAME "LobDemo"
LIBRARY demolib
WITH CONTEXT
PARAMETERS
(
context,
lob_in,
lob_inout,
lob_out,
lob_in INDICATOR SB4,
lob_inout INDICATOR SB4,
lob_out INDICATOR SB4,
RETURN INDICATOR SB4
);
/
show errors;
CREATE OR REPLACE PROCEDURE run_lob_demo IS
clob_in CLOB; clob_inout CLOB; clob_out CLOB; clob_ret CLOB;
blob_in BLOB; blob_inout BLOB; blob_out BLOB; blob_ret BLOB;
nclob_in NCLOB; nclob_inout NCLOB; nclob_out NCLOB; nclob_ret NCLOB;
begin
-- Execute CLOB Demo.
begin
-- Fetch CLOB data from the database.
select clobcol into clob_in from lobtab where id = 1 for update;
select clobcol into clob_inout from lobtab where id = 2 for update;
select clobcol into clob_out from lobtab where id = 3 for update;
dbms_output.put_line('Before executing clob_demo.');
dbms_output.put_line('clob_in : ' || dbms_lob.getlength(clob_in));
dbms_output.put_line('clob_inout : ' || dbms_lob.getlength(clob_inout));
dbms_output.put_line('clob_out : ' || dbms_lob.getlength(clob_out));
dbms_output.put_line('clob_ret : ' || dbms_lob.getlength(clob_ret));
-- Call external procedure clob_demo.
clob_ret := clob_demo(clob_in, clob_inout, clob_out);
dbms_output.new_line;
dbms_output.put_line('---------------------------------------');
dbms_output.new_line;
dbms_output.put_line('After executing clob_demo.');
dbms_output.put_line('clob_in : ' || dbms_lob.getlength(clob_in));
dbms_output.put_line('clob_inout : ' || dbms_lob.getlength(clob_inout));
dbms_output.put_line('clob_out : ' || dbms_lob.getlength(clob_out));
dbms_output.put_line('clob_ret : ' || dbms_lob.getlength(clob_ret));
dbms_output.put_line('---------------------------------------');
dbms_output.new_line;
exception when others
then
dbms_output.put_line('SQL Error: ' || sqlerrm);
end;
-- Execute BLOB Demo.
begin
-- Fetch BLOB data from the database.
select blobcol into blob_in from lobtab where id = 1;
select blobcol into blob_inout from lobtab where id = 2 for update;
select blobcol into blob_out from lobtab where id = 3 for update;
dbms_output.put_line('Before executing blob_demo.');
dbms_output.put_line('blob_in : ' || dbms_lob.getlength(blob_in));
dbms_output.put_line('blob_inout : ' || dbms_lob.getlength(blob_inout));
dbms_output.put_line('blob_out : ' || dbms_lob.getlength(blob_out));
dbms_output.put_line('blob_ret : ' || dbms_lob.getlength(blob_ret));
-- Call external procedure blob_demo.
blob_ret := blob_demo(blob_in, blob_inout, blob_out);
dbms_output.put_line('After executing blob_demo.');
dbms_output.put_line('blob_in : ' || dbms_lob.getlength(blob_in));
dbms_output.put_line('blob_inout : ' || dbms_lob.getlength(blob_inout));
dbms_output.put_line('blob_out : ' || dbms_lob.getlength(blob_out));
dbms_output.put_line('blob_ret : ' || dbms_lob.getlength(blob_ret));
dbms_output.put_line('---------------------------------------');
dbms_output.new_line;
exception when others
then
dbms_output.put_line('SQL Error: ' || sqlerrm);
end;
-- Execute NCLOB Demo.
begin
-- Fetch NCLOB data from the database.
select nclobcol into nclob_in from lobtab where id = 1;
select nclobcol into nclob_inout from lobtab where id = 2 for update;
select nclobcol into nclob_out from lobtab where id = 3 for update;
dbms_output.put_line('Before executing nclob_demo.');
dbms_output.put_line('nclob_in : ' || dbms_lob.getlength(nclob_in));
dbms_output.put_line('nclob_inout : ' || dbms_lob.getlength(nclob_inout));
dbms_output.put_line('nclob_out : ' || dbms_lob.getlength(nclob_out));
dbms_output.put_line('nclob_ret : ' || dbms_lob.getlength(nclob_ret));
-- Call external procedure nclob_demo.
nclob_ret := nclob_demo(nclob_in, nclob_inout, nclob_out);
dbms_output.put_line('After executing nclob_demo.');
dbms_output.put_line('nclob_in : ' || dbms_lob.getlength(nclob_in));
dbms_output.put_line('nclob_inout : ' || dbms_lob.getlength(nclob_inout));
dbms_output.put_line('nclob_out : ' || dbms_lob.getlength(nclob_out));
dbms_output.put_line('nclob_ret : ' || dbms_lob.getlength(nclob_ret));
dbms_output.put_line('***************************************');
dbms_output.new_line;
exception when others
then
dbms_output.put_line('SQL Error: ' || sqlerrm);
end;
exception when others
then dbms_output.put_line('SQL Error: ' || sqlerrm);
end;
/
show errors
execute run_lob_demo;
/