-- Copyright © 2007, Oracle Corporation.  All Rights Reserved. 
-- 
-- This script creates an interface to a set of external routines written
-- using PRO*C for accessing an Oracle RDBMS database. To use the script, either
-- define a logical for PROCEXTFUNC to point to the location of the
-- PRO_C_EXT_FUNC.EXE shared RTL or edit the script to specify that location
-- directly. Then attach the desired database with sql and do "@PRO_C_EXT_FUNC".
-- 
create module ORA_PRO_C_DEMO_FUNCS
    declare :rc integer = 0
    declare :name varchar(100)
    declare :job_title varchar(100)
    declare :mgr_id integer
    declare :hire_date date vms
    declare :salary float
    declare :commission float
    declare :dept_code integer

function roif_connect 
    (in :username varchar(100) by reference,
     in :password varchar(100) by reference,
     in :sid varchar(100) by reference)
returns integer;
external
    name ROIF_CONNECT
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'Connects to Oracle instance';

function roif_disconnect ()
    returns integer;
external
    name ROIF_DISCONNECT
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'Disconnects from Oracle instance';

function roif_commit ()
    returns integer;
external
    name ROIF_COMMIT
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'Commits external Oracle transaction';

function roif_rollback ()
    returns integer;
external
    name ROIF_ROLLBACK
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'Rolls back external Oracle transaction';

procedure roif_get_errmsg
    (out :errmsg varchar(100) by reference);
external
    name ROIF_GET_ERRMSG
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'Get the message for last SQLCODE';

procedure roif_get_employee (
    in :empno integer by value,
    out :ename varchar(100) by reference,
    out :job varchar(100) by reference,
    out :mgr integer by reference,
    out :hiredate date vms by reference,
    out :sal double precision by reference,
    out :comm double precision by reference,
    out :deptno integer by reference,
    out :status integer by reference
    );
external
    name ROIF_GET_EMPLOYEE
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'retrieve an employee record';

function roif_open_emp_cursor ()
    returns integer;
external
    name ROIF_OPEN_EMP_CURSOR
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'Opens a cursor on the emp table';

procedure roif_fetch_emp_cursor (
    out :empno integer by reference,         out :ind1 smallint by reference,
    out :ename varchar(100) by reference,    out :ind2 smallint by reference,
    out :job varchar(100) by reference,      out :ind3 smallint by reference,
    out :mgr integer by reference,           out :ind4 smallint by reference,
    out :hiredate date vms by reference,     out :ind5 smallint by reference,
    out :sal double precision by reference,  out :ind6 smallint by reference,
    out :comm double precision by reference, out :ind7 smallint by reference,
    out :deptno integer by reference,        out :ind8 smallint by reference,
    out :status integer by reference
    );
external
    name ROIF_FETCH_EMP_CURSOR
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'fetch from the emp table';

function roif_close_emp_cursor ()
    returns integer;
external
    name ROIF_CLOSE_EMP_CURSOR
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'Closes a cursor on the emp table';

procedure roif_update_employee (
    in :empno integer by reference,
    in :ename varchar(100) by reference,    in :ind2 smallint by value,
    in :job varchar(100) by reference,      in :ind3 smallint by value,
    in :mgr integer by reference,           in :ind4 smallint by value,
    in :hiredate date vms by reference,     in :ind5 smallint by value,
    in :sal double precision by reference,  in :ind6 smallint by value,
    in :comm double precision by reference, in :ind7 smallint by value,
    in :deptno integer by reference,        in :ind8 smallint by value,
    out :status integer by reference
    );
external
    name ROIF_UPDATE_EMPLOYEE
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'update emp table by empno';

procedure roif_insert_employee (
    in :empno integer by reference,
    in :ename varchar(100) by reference,    in :ind2 smallint by value,
    in :job varchar(100) by reference,      in :ind3 smallint by value,
    in :mgr integer by reference,           in :ind4 smallint by value,
    in :hiredate date vms by reference,     in :ind5 smallint by value,
    in :sal double precision by reference,  in :ind6 smallint by value,
    in :comm double precision by reference, in :ind7 smallint by value,
    in :deptno integer by reference,        in :ind8 smallint by value,
    out :status integer by reference
    );
external
    name ROIF_INSERT_EMPLOYEE
    location 'PROCEXTFUNC:PRO_C_EXT_FUNC.EXE'
    language C
    parameter style general
    comment is 'insert into emp table';

end module;