Package problem

[Glowworm27]

I have a package that takes a few parameters, and when I execute it, I get an error telling me that it cannot insert a null into my table. OK fine, so I modified the code so that if a null value is returned, to substitute a Zero, but I am still getting the error. Please help

HEre is the procedure....

 PROCEDURE INSERTGDPROGRAM(
 OCOMPANYCODE IN NUMBER
 , OUNIVERSALID IN NUMBER
 , OBEGINDATE IN DATE
 , OENDDATE IN DATE
 , OMINHOURS IN NUMBER
 , OGROSSCOMMBONUS IN CHAR
 , oGDAMOUNT IN NUMBER
 , oDrawToDate In Number
 , oComments In Varchar2
 )
 IS
  newSequenceNbr Number;	-- this procedure will query the master records for this employee and get the largest sequence nbr, and assign the next
  -- number in the sequence when createing the new record.

BEGIN
	-- First turn all the other programs off (Status = 'I')
	Update Guaranteed_Draw_Master Set Active_Ind = 'I'
	Where Guaranteed_Draw_Master.COMPANY_CODE = oCompanyCode
	And Guaranteed_Draw_Master.UNIVERSAL_ID = oUniversalID;

	--- try to get the Current (highest sequence number so we can create the new program
	Select (nvl(Max(Guaranteed_Draw_Master.SEQUENCE_NBR), -1) + 1) Into newSequenceNbr From Guaranteed_Draw_Master
	Where Guaranteed_Draw_Master.COMPANY_CODE = oCompanyCode
	And Guaranteed_Draw_Master.UNIVERSAL_ID = OUNIVERSALID;


	--- Now Insert the new GD Program will Create a new record in the Guaranteed_Draw_Master table for the employee i
	Insert Into Guaranteed_Draw_Master Values(Guaranteed_Draw_Master_Id_Seq.NExtval, oCompanyCode, oUniversalID, newSequenceNbr,
	'A', obeginDate, oEndDate, oMinHours, oGDAMOUNT , OGROSSCOMMBONUS, oDrawToDate, oComments )
	;

	--- just in case no sequence number was found we will create the first one (0) it is Zero Based
	Exception
		When No_Data_Found
		Then
	Insert Into Guaranteed_Draw_Master Values(Guaranteed_Draw_Master_Id_Seq.Nextval, oCompanyCode, oUniversalID, 0,
	'A', obeginDate, oEndDate, oMinHours, oGDAMOUNT , OGROSSCOMMBONUS, oDrawToDate, oComments )
	;

END;

Now I execute the following procedure from SQLPlus with the following code

BEGIN
GUARANTEEDDRAW.INSERTGDPROGRAM (10, 89024, '1 Feb 2006', '28 Feb 2007', 39, 'G', 230.77, 0, '');
END;

And here is the error I get.

ERROR
ORA-01400: cannot insert NULL into ("MTAP"."GUARANTEED_DRAW_MASTER"."SEQUENCE_NBR")
ORA-06512: at "MTAP.GUARANTEEDDRAW", line 94
ORA-06512: at line 2


The package(stored procedure should have put a zero into the variable newSequenceNbr because this code..

Select (nvl(Max(Guaranteed_Draw_Master.SEQUENCE_NBR), -1) + 1) Into newSequenceNbr From Guaranteed_Draw_Master
	Where Guaranteed_Draw_Master.COMPANY_CODE = oCompanyCode
	And Guaranteed_Draw_Master.UNIVERSAL_ID = OUNIVERSALID;

returns a zero when I run it in SQLPlus
So the part of the package that does the insert should be inserting the Zero that should be set in the variable???

Thanks
G

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[SimonSellick]

Are you sure that the column sequence in Guaranteed_Draw_Master matches the sequence in your insert statements?

I find it better to specify the column names even though it is an extra line of code, because it helps to show up problems like that, and makes it easier to spot required changes should a table structure alter.