Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Oracle Package Problems

Oracle Package Problems

From: <jay99999_at_my-deja.com>
Date: Mon, 21 Aug 2000 18:35:04 GMT
Message-ID: <8nrsom$hr$1@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




--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




)


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 Mon Aug 21 2000 - 13:35:04 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US