> # Using Stored Procedures with Oracle

> SequeLink supports stored procedures against Oracle, including stored procedures in packages.

> NOTE: Stored procedures in packages must be qualified with the package name, for example, EmployeePackage.EmployeeProc.

> Also, SQLProcedures and SQLProcedureColumns can return information on procedures within PL/SQL packages, allowing ODBC applications to execute these procedures. This section contains an example that shows you how to fetch rows using Oracle PL/SQL procedures.

> Example - Part 1

>     Create or replace package EmployeeInfo as
>        Type EmployeeRec is record
>        (
>        Employee_Id     integer,
>        Employee_Name   varchar2(25),
>        Employee_Job    varchar2(25),
>        Department_Name varchar2(30),
>        Employee_Salary integer
>        );
>        Type EmployeeCursor is ref cursor return
>        EmployeeRec;
>        End EmployeeInfo;

>        Create or replace procedure EmployeeInfoProc
>        (empname IN varchar2, empcursor IN OUT
>        EmployeeInfo.EmployeeCursor)
>        As
>        Begin
>        Open empcursor For
>        select empno, ename, job, dname, sal
>        from emp, dept
>        where emp.deptno=dept.deptno and
>        ename like empname;
>        End;

> NOTE: In this Oracle PL/SQL package, a record type and a cursor (result set) type is defined. The procedure contains an input parameter that can have a value, such as `Smi%`, to request information about employees whose last name starts with the letters 'Smi' (for example, Smith or Smithwick). The procedure also has one input/output parameter of the cursor type defined in the package.

> Example - Part 2

> This example shows an ODBC function call sequence executing the stored procedure.

>     SQLPrepare(...,'{call EmployeeInfoProc(?)}',...)
>                              <- ODBC SQL syntax to executed stored procedures
>     SQLBindParameter(...,'Smi%',...)
>                              <- Define the input variable for the input marker ?
>                                 in the SQL stmt and assign the value 'Smi% to it
>     SQLExecute()             <- Execute the stored procedure
>     SQLBindCol()             <- Assign storage for result column 1 in the
>                                 result set (Employee_Id)
>     SQLBindCol()             <- Assign storage for result column 2 in the
>                                 result set (Employee_Name)
>     SQLBindCol()             <- Assign storage for result column 3 in the
>                                 result set (Employee_Job)
>     SQLBindCol()             <- Assign storage for result column 4 in the
>                                 result set (Department_Name)
>     SQLBindCol()             <- Assign storage for result column 5 in the
>                                 result set (Employee_Salary)
>     SQLFetch()               <- Fetch the first record from the result set
>                                 generated by the stored procedure.

> IMPORTANT: From the following procedure definition, you might think that, by having two parameters, the procedure must call SQLBindParameter twice:

>     Create or replace procedure EmployeeInfoProc
>     (empname IN varchar2, empcursor IN OUT
>     EmployeeInfo.EmployeeCursor)

> Actually, it does not. The only way to create a result set from an Oracle stored procedure is to declare this result set, empcursor, as an input/output parameter. This can be seen in the result of `SQLProcedureColumns(...,'EmployeeInfoProc',...)` which an application can use to query the server about a stored procedure.

> The following is an excerpt of a session using the tool ODBCTest:

>     SQLAllocStmt:
>        In: hdbc=0x004609F0, phstmt=VALID
>        Return:    SQL_SUCCESS=0
>        SQLPrepare:
>        In: hstmt=#3 0x00305850, szSqlStr={call EmployeeInfoProc(?)}, cbSqlStr=26
>        Return:    SQL_SUCCESS=0
>        SQLBindParameter:
>        In: hstmt=#3 0x00305850, ipar=1, fParamType=SQL_PARAM_INPUT=1,
>        fCType=SQL_C_CHAR=1,
>        fSqlType=SQL_CHAR=1, cbColDef=10, ibScale=0, rgbValue=VALID,
>        cbValueMax=300, pcbValue=VALID, SQL_LEN_DATA_AT_EXEC=FALSE
>        Return:    SQL_SUCCESS=0
>        SQLExecute:
>        In: hstmt=#3 0x00305850
>        Return:    SQL_SUCCESS=0
>        Get Data All:
>        "EMPNO", "ENAME", "JOB", "DNAME", "SAL"
>        7934, "MILLER", "CLERK", "ACCOUNTING", 1300.00
>        7654, "MARTIN", "SALESMAN", "SALES", 1250.00
>        2 rows fetched from 5 columns.
>        SQLProcedureColumns:
>        In: hstmt=#4 0x00305BD8, ...Qualifier=NULL, ...Qualifier=0,
>        Owner=SCOTT, ...Owner=5, ...Name=EMPLOYEEINFOPROC,
>        ...Name=16, ...Name=NULL, ...Name=0
>        Return:    SQL_SUCCESS=0
>        Get Data All:
>        "PROCEDURE_CAT", "PROCEDURE_SCHEM", "PROCEDURE_NAME", "COLUMN_NAME",
>        "COLUMN_TYPE", "DATA_TYPE", ..."TYPE_NAME", "COLUMN_SIZE",
>        "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE",
>        "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB",
>        "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
>        "", "SCOTT", "EMPLOYEEINFOPROC", "EMPNAME", 1, 12, "VARCHAR2", 2000,
>        2000, <Null>, <Null>, 1, <Null>, <Null>, ...12, <Null>, 2000, 1, "YES"