-- 
-- Copyright (c) 1997 by Oracle Corporation
--
-- NAME
--   EXTERN.SQL
--
-- DESCRIPTION
--   Creates a PL/SQL library and registers the sample external procedure
-- 
-- NOTES
--   Before running this script, add your internal password to the
--   'connect internal' line (if necessary) [or enter it at runtime
--   at the Password: prompt].
--

connect internal
create user sample identified by sample;
grant connect, resource, dba to sample;
connect sample/sample;


--First associate a library with an actual DLL in the operating system...
drop library externProcedures;
create library externProcedures as 'd:\oracle\ora81\RDBMS\EXTPROC\extern.dll';

--In a given library, you may have multiple functions.
--You must register each function that you want to use from PL/SQL

--Next register an external function in that DLL with PL/SQL...
--This function returns the maximum of the two values passed to it.
CREATE OR REPLACE FUNCTION PLS_MAX(
      x BINARY_INTEGER, 
      y BINARY_INTEGER) 
RETURN BINARY_INTEGER AS 
   EXTERNAL LIBRARY externProcedures
   NAME "find_max"  -- Name of function call. Quotes preserve lower case.
   LANGUAGE C
   PARAMETERS (
      x long,            -- stores value of x
      x INDICATOR short,  -- this is used to determine if x is a NULL value
      y long,            -- stores value of y
      y INDICATOR short,  -- this is used to determine if y is a NULL value
      RETURN INDICATOR short ); -- need to pass pointer to return value's   
				-- indicator variable to determine if NULL.
   --This means that my function will be defined as:
   --long max(long x, short x_indicator, 
   --         long y, short y_indicator, short * ret_indicator)
/

--here is how it would be used:

CREATE OR REPLACE PROCEDURE UseIt AS
	a integer;
	b integer;
	c integer;
BEGIN
	a := 1;
	b := 2;
	c := PLS_MAX(a,b);
	dbms_output.put_line('The maximum of '||a||' and '||b||' is '||c);
END;
/

set serveroutput on;
execute UseIt;
/

