Re: Oracle Package Problems

Hello Jon!

There are two errors in your skript.
See anwers embedded.

Hope this helps,
Stefan.

<jay99999_at_my-deja.com> schrieb in im Newsbeitrag: 8nrsom$hr$1_at_nnrp1.deja.com...
> Hi, I'm trying to run the following package in Oracle using SQL
> Navigator. I keep getting errors starting with "SQL Command not
> properly ended" (at the line "create package WMPD.ADD_PERSONNEL_PKG is
> "), followed by a bunch of "Invalid SQL statement"'s and one or
> two "Missing keyword" 's. I adapted this script from a script that
> worked fine and I can't figure out why it won't run. If anyone can
> spot an error in the following code, I'd appreciate feedback. I have
> to teach myself this stuff and I don't have any help from anyone at
> work. Thanks in advance,
>
> Jon
>
>
> --Script for P_ADD_PERSONNEL
> --Generated July 2000
>
> --drop package
> drop package WMPD.ADD_PERSONNEL_PKG

----->> end the above line with the missing ";"

>
>
> --create package
> create package WMPD.ADD_PERSONNEL_PKG is
>
> --Add
> procedure ADD_PERSONNEL_PKG_INSERT_PRC
> (
> v_LAST_NAME in VARCHAR2 default NULL,
> v_FIRST_NAME in VARCHAR2 default NULL,
> v_TITLE in VARCHAR2 default NULL,
> v_PHONE_NUMBER in VARCHAR2 default NULL,
> v_EXTENSION in VARCHAR2 default NULL,
> v_GIC in VARCHAR2 default NULL,
> v_FACSIMILE_NUMBER in VARCHAR2 default NULL,
> v_ROOM in VARCHAR2 default NULL,
> v_EMAIL_ADDRESS in VARCHAR2 default NULL,
> v_SECTION in VARCHAR2 default NULL,
> v_LOCATION in VARCHAR2 default NULL,
> v_COMMENTS in VARCHAR2 default NULL,
> v_FUNDING_SOURCE in VARCHAR2 default NULL,
> v_POSITION_NUMBER in VARCHAR2 default NULL,
> v_CLASS_CODE in VARCHAR2 default NULL,
> v_PAYGRADE in VARCHAR2 default NULL,
> v_MONTHLY_RATE in VARCHAR2 default NULL,
> v_ANNUAL_RATE in VARCHAR2 default NULL,
> v_MAIL_STOP in VARCHAR2 default NULL,
> v_PASSWD in VARCHAR2 default NULL,
> v_PK_PERSONNEL out VARCHAR2,
> results out VARCHAR2
> );
>
>
> --Change
> procedure ADD_PERSONNEL_PKG_UPDATE_PRC
> (
> v_PK_PERSONNEL in VARCHAR2 default NULL,
> v_LAST_NAME in VARCHAR2 default NULL,
> v_FIRST_NAME in VARCHAR2 default NULL,
> v_TITLE in VARCHAR2 default NULL,
> v_PHONE_NUMBER in VARCHAR2 default NULL,
> v_EXTENSION in VARCHAR2 default NULL,
> v_GIC in VARCHAR2 default NULL,
> v_FACSIMILE_NUMBER in VARCHAR2 default NULL,
> v_ROOM in VARCHAR2 default NULL,
> v_EMAIL_ADDRESS in VARCHAR2 default NULL,
> v_SECTION in VARCHAR2 default NULL,
> v_LOCATION in VARCHAR2 default NULL,
> v_COMMENTS in VARCHAR2 default NULL,
> v_FUNDING_SOURCE in VARCHAR2 default NULL,
> v_POSITION_NUMBER in VARCHAR2 default NULL,
> v_CLASS_CODE in VARCHAR2 default NULL,
> v_PAYGRADE in VARCHAR2 default NULL,
> v_MONTHLY_RATE in VARCHAR2 default NULL,
> v_ANNUAL_RATE in VARCHAR2 default NULL,
> v_MAIL_STOP in VARCHAR2 default NULL,
> v_PASSWD in VARCHAR2 default NULL,
> results out VARCHAR2
> );
>
> --Delete
> procedure ADD_PERSONNEL_PKG_DELETE_PRC
> (
> v_PK_PERSONNEL in VARCHAR2 default NULL,
> results out VARCHAR2
> );
> end WMPD.ADD_PERSONNEL_PKG;
> /
>
> create package body WMPD.ADD_PERSONNEL_PKG is
> procedure ADD_PERSONNEL_PKG_INSERT_PRC
> (
> v_LAST_NAME in VARCHAR2 default NULL,
> v_FIRST_NAME in VARCHAR2 default NULL,
> v_TITLE in VARCHAR2 default NULL,
> v_PHONE_NUMBER in VARCHAR2 default NULL,
> v_EXTENSION in VARCHAR2 default NULL,
> v_GIC in VARCHAR2 default NULL,
> v_FACSIMILE_NUMBER in VARCHAR2 default NULL,
> v_ROOM in VARCHAR2 default NULL,
> v_EMAIL_ADDRESS in VARCHAR2 default NULL,
> v_SECTION in VARCHAR2 default NULL,
> v_LOCATION in VARCHAR2 default NULL,
> v_COMMENTS in VARCHAR2 default NULL,
> v_FUNDING_SOURCE in VARCHAR2 default NULL,
> v_POSITION_NUMBER in VARCHAR2 default NULL,
> v_CLASS_CODE in VARCHAR2 default NULL,
> v_PAYGRADE in VARCHAR2 default NULL,
> v_MONTHLY_RATE in VARCHAR2 default NULL,
> v_ANNUAL_RATE in VARCHAR2 default NULL,
> v_MAIL_STOP in VARCHAR2 default NULL,
> v_PASSWD in VARCHAR2 default NULL,
> v_PK_PERSONNEL out VARCHAR2,
> results out VARCHAR2
> )
> is
> begin
> insert into WMPD.T_PERSONNEL
> (
> PK_PERSONNEL,
> LAST_NAME,
> FIRST_NAME,
> TITLE,
> PHONE_NUMBER,
> EXTENSION,
> GIC,
> FACSIMILE_NUMBER,
> ROOM,
> EMAIL_ADDRESS,
> SECTION,
> LOCATION,
> COMMENTS,
> FUNDING_SOURCE,
> POSITION_NUMBER,
> CLASS_CODE,
> PAYGRADE,
> MONTHLY_RATE,
> ANNUAL_RATE,
> MAIL_STOP,
> PASSWD
> )
> values
> (
> PERSONNEL_SEQ.NEXTVAL,
> v_LAST_NAME,
> v_FIRST_NAME,
> v_TITLE,
> v_PHONE_NUMBER,
> v_EXTENSION,
> v_GIC,
> v_FACSIMILE_NUMBER,
> v_ROOM,
> v_EMAIL_ADDRESS,
> v_SECTION,
> v_LOCATION,
> v_COMMENTS,
> v_FUNDING_SOURCE,
> v_POSITION_NUMBER,
> v_CLASS_CODE,
> v_PAYGRADE,
> v_MONTHLY_RATE,
> v_ANNUAL_RATE,
> v_MAIL_STOP,
> v_PASSWD
> );
> select PERSONNEL_SEQ.CURRVAL into v_PK_PERSONNEL from dual;
> COMMIT;
> results:='SUCCESS';
> exception
> when others then
> results:=sqlerrm;
> end;
> procedure ADD_PERSONNEL_PKG_UPDATE_PRC
> (
> v_PK_PERSONNEL in VARCHAR2 default NULL,
> v_LAST_NAME in VARCHAR2 default NULL,
> v_FIRST_NAME in VARCHAR2 default NULL,
> v_TITLE in VARCHAR2 default NULL,
> v_PHONE_NUMBER in VARCHAR2 default NULL,
> v_EXTENSION in VARCHAR2 default NULL,
> v_GIC in VARCHAR2 default NULL,
> v_FACSIMILE_NUMBER in VARCHAR2 default NULL,
> v_ROOM in VARCHAR2 default NULL,
> v_EMAIL_ADDRESS in VARCHAR2 default NULL,
> v_SECTION in VARCHAR2 default NULL,
> v_LOCATION in VARCHAR2 default NULL,
> v_COMMENTS in VARCHAR2 default NULL,
> v_FUNDING_SOURCE in VARCHAR2 default NULL,
> v_POSITION_NUMBER in VARCHAR2 default NULL,
> v_CLASS_CODE in VARCHAR2 default NULL,
> v_PAYGRADE in VARCHAR2 default NULL,
> v_MONTHLY_RATE in VARCHAR2 default NULL,
> v_ANNUAL_RATE in VARCHAR2 default NULL,
> v_MAIL_STOP in VARCHAR2 default NULL,
> v_PASSWD in VARCHAR2 default NULL,
> results out VARCHAR2
> )

----->> add here a new line with the missing keyword "is"

> begin
> update WMPD.T_PERSONNEL
> set LAST_NAME=v_LAST_NAME,
> set FIRST_NAME=v_FIRST_NAME,
> set TITLE=v_TITLE,
> set PHONE_NUMBER=v_PHONE_NUMBER,
> set EXTENSION=v_EXTENSION,
> set GIC = v_GIC,
> set FACSIMILE_NUMBER = v_FACSIMILE_NUMBER,
> set ROOM = v_ROOM,
> set EMAIL_ADDRESS = v_EMAIL_ADDRESS,
> set SECTION = v_SECTION,
> set LOCATION = v_LOCATION,
> set COMMENTS=v_COMMENTS,
> set FUNDING_SOURCE=v_FUNDING_SOURCE,
> set POSITION_NUMBER=v_POSITION_NUMBER,
> set CLASS_CODE=v_CLASS_CODE,
> set PAYGRADE=v_PAYGRADE,
> set MONTHLY_RATE=v_MONTHLY_RATE,
> set ANNUAL_RATE=v_ANNUAL_RATE,
> set MAIL_STOP=v_MAIL_STOP,
> set PASSWD=v_PASSWD
> where PK_PERSONNEL=v_PK_PERSONNEL;
> COMMIT;
> results:='SUCCESS';
> exception
> when others then
> results:=sqlerrm;
> end;
>
> procedure ADD_PERSONNEL_PKG_DELETE_PRC
> (
> v_PK_PERSONNEL in VARCHAR2 default NULL,
> results out VARCHAR2
> )
> is
> begin
> delete
> from WMPD.T_PERSONNEL
> where PK_PERSONNEL=v_PK_PERSONNEL;
> COMMIT;
> results:='SUCCESS';
> exception
> when others then
> results:=sqlerrm;
> end;
> end WMPD.ADD_PERSONNEL_PKG;
> /
>
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Aug 23 2000 - 13:47:10 CDT