problem in creating a package in oracle 8.i

Question

Post reply

problem in creating a package in oracle 8.i

sukhoi971

SSCarpal Tunnel

Points: 4833
More actions
August 18, 2004 at 12:16 am

#109380

iam trying to create a package and it is having one stored procedure but it is showing error
i dont know what it is
in benthic software it is-->"A PL/SQL Parse error occurred.
Please check the all_errors table for errors.
(or better yet, use PLEdit!)"

in toad it points at the "create "

can any 1 pls help
CREATE OR REPLACE PACKAGE ipts_mtn_Extra as
procedure Admin_ins_smsEmail
(pDAYSBLOCK in EMAIL_SMS_MASTER.DAYSBLOCK%TYPE,
pGRP_ID in EMAIL_SMS_MASTER.GRP_ID%TYPE,
pGRP_COMPANY_ID in EMAIL_SMS_MASTER.GRP_COMPANY_ID%TYPE,
pUPD_ID in EMAIL_SMS_MASTER.UPD_ID%TYPE,
pUPD_NO in EMAIL_SMS_MASTER.UPD_NO%TYPE,
pSTARTFROMDATE in EMAIL_SMS_MASTER.STARTFROMDATE%TYPE,
pPLANNAME in EMAIL_SMS_MASTER.PLANNAME%TYPE);
end ipts_mtn_extra;
create or replace package body ipts_mtn_Extra as
procedure Admin_ins_smsEmail
(pDAYSBLOCK in EMAIL_SMS_MASTER.DAYSBLOCK%TYPE,
pGRP_ID in EMAIL_SMS_MASTER.GRP_ID%TYPE,
pGRP_COMPANY_ID in EMAIL_SMS_MASTER.GRP_COMPANY_ID%TYPE,
pUPD_ID in EMAIL_SMS_MASTER.UPD_ID%TYPE,
pUPD_NO in EMAIL_SMS_MASTER.UPD_NO%TYPE,
pSTARTFROMDATE in EMAIL_SMS_MASTER.STARTFROMDATE%TYPE,
pPLANNAME in EMAIL_SMS_MASTER.PLANNAME%TYPE)
as
begin
select sysdate into sysdt from dual;
select EMAIL_SMS_MASTERSeQ.nextval into nnx from dual;
insert into EMAIL_SMS_MASTER(UNID,PLANDATE,DAYSBLOCK,GRP_ID,GRP_COMPANY_ID,UPD_ID,UPD_DT,UPD_NO,STARTFROMDATE,PLANNAME) values(nnx,sysdt,pDAYSBLOCK,pGRP_ID,pGRP_COMPANY_ID,pUPD_ID,pUPD_DT,pUPD_NO,pSTARTFROMDATE,pPLANNAME);
commit;
end admin_ins_smsemail;
end ipts_mtn_extra;

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Pooven Phillips SSC Journeyman Points: 90 More actions · Answer 1

Use show errors package in sqlplus to check the error

Show errors also works for procedure etc

You would also need to reference the package in your insert i.e. package_name.object_name - the package is external - treat it as a container

win SSCommitted Points: 1569 More actions · Answer 2

1) I assume your sequence exists

2) I cannot find any declaration of

sysdt date;

nnx number;

in package, package body or procedure

for

select sysdate into sysdt from dual; -- SQL Server equivalnet is: select @sysdt=getdate()

select EMAIL_SMS_MASTERSeQ.nextval into nnx from dual; --similar to select @nnx=@@identity+1

3) I noticed you are inserting 10 values into 10 fields, but you only provide 7 parameters, sysdt and nnx. pUPD_DT/UPD_DT appears to be missing out.

Cheers,

Win