Re: Syntax a Package with Procedures in Oracle. Any Ideas?

You forgot the / after creating the package specs so that it compiles.

Kenny Gump

James Thompson wrote in message ...
>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 Fri May 21 1999 - 09:12:30 CDT