Syntax a Package with Procedures in Oracle. Any Ideas?

From: James Thompson <james.thompson_at_noridian.com>
Date: Wed, 19 May 1999 15:20:10 -0500
Message-ID: <f2F03.764$S6.99452@news.uswest.net>

Two Questions.

1.)What is the correct syntax for creating a package that contains procedures inside of it?
2.)How do you call them from VB?

#1) Here is what I have so far.
But it is giving me an error on LINE 8 with the message "Error when encountering the work CREATE"

CREATE OR REPLACE package EE_PACKAGE AS

   PROCEDURE EE_SUB_LASTNAME(pDCN IN CHAR,

pLastName OUT CHAR,

pFirstName OUT CHAR,

pMemCode OUT CHAR,

pOtherDCN OUT CHAR);
END EE_PACKAGE;
CREATE PACKAGE BODY EE_PACKAGE AS
   PROCEDURE EE_SUB_LASTNAME (pDCN IN CHAR,

pLastName OUT CHAR,

pFirstName OUT CHAR,

pMemCode OUT CHAR,

pOtherDCN OUT CHAR)
IS
BEGIN
  declare
  cursor csrresponse is
    SELECT app.last_name, app.first_name, mem.mem_code, other.dcn     FROM app_keys_tb app, app_member_tb mem, app_other_coverage_tb other     WHERE (app.dcn(+) = mem.dcn) AND app.dcn = other.dcn AND app.dcn = pDCN;   begin
    open csrresponse;
    loop

      fetch csrresponse into pLastName, pFirstName, pMemCode, pOtherDCN;
      exit when csrresponse%notfound;

    end loop;
    close csrresponse;
  end;
end;
END EE_SUB_LASTNAME; #2) What is wrong with this?

    Set cn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset

    With cn

.CursorLocation = adUseClient
.Open DataSource

    End With

    pDCN = "1000000000"
    strSQL = "{call ee_package.ee_sub_lastname({pDCN, LastName, FirstName, MemCode, OtherDCN})}"

    With cmd

.CommandText = strSQL
.CommandType = adCmdText
.ActiveConnection = cn
.Parameters.Append cmd.CreateParameter("pDCN", adVarChar,
adParamInput, 11, "1000000000")

.Parameters.Append cmd.CreateParameter("pLastName", adVarChar,
adParamOutput, 25)

.Parameters.Append cmd.CreateParameter("pFirstName", adVarChar,
adParamOutput, 25)

.Parameters.Append cmd.CreateParameter("pMemCode", adVarChar,
adParamOutput, 5)

.Parameters.Append cmd.CreateParameter("pOtherDCN", adVarChar,
adParamOutput, 11)

    End With

    With rs

.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic

        Set .Source = cmd


        .Open

    End With

Any suggestions would be appreciated. Received on Wed May 19 1999 - 15:20:10 CDT