Creating a package

[WP]

I'm new to PL/SQL and I am trying to create a package that contains one simple procedure :

Create Package OnDemand_CC As
PROCEDURE P_OD_LOAD;
End OnDemand_CC;

CREATE PACKAGE BODY OnDemand_CC AS
PROCEDURE P_OD_LOAD is
cursor c1 is
select b.olympic_mandate_id || ',' || a.COST_CENTRE as MandCC
from ENT_INT_PARTY a, ent_int_mandate_access b
where a.PARTY_ID=b.INT_PARTY_ID and a.GPIN_ID>0;

my_MandCC VARCHAR2(13);

begin
DELETE FROM ONDEMAND.BADMSP@ESDEV01.DB2_W;
open c1;
<<read_c1>>
LOOP
FETCH c1 INTO my_MandCC;
EXIT WHEN c1%NOTFOUND;


INSERT INTO ONDEMAND.BADMSP@ESDEV01.DB2_W (BADMSP)
VALUES(my_MandCC);
END LOOP read_c1;

COMMIT;
End P_OD_LOAD;

End OnDemand_CC;

It doesn't compile and complians about the CREATE BODY statement.

Anyone know what is wrong here.

Thanks in advance. WP Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out

http://www.deepturtle.ch

 

[mesuj]

try this

Create OR REPLACE Package OnDemand_CC As
End OnDemand_CC;
/

CREATE OR REPLACE PACKAGE BODY OnDemand_CC AS
End OnDemand_CC;

That / is a must if you are creating the package & body together.

 

[rcurva]

Change your AS to IS, like;

Create Package OnDemand_CC IS
PROCEDURE P_OD_LOAD;
End OnDemand_CC;

CREATE PACKAGE BODY OnDemand_CC IS
PROCEDURE P_OD_LOAD is
cursor c1 is
select b.olympic_mandate_id || ',' || a.COST_CENTRE as MandCC
from ENT_INT_PARTY a, ent_int_mandate_access b
where a.PARTY_ID=b.INT_PARTY_ID and a.GPIN_ID>0;

my_MandCC VARCHAR2(13);

begin
DELETE FROM ONDEMAND.BADMSP@ESDEV01.DB2_W;
open c1;
<<read_c1>>
LOOP
FETCH c1 INTO my_MandCC;
EXIT WHEN c1%NOTFOUND;


INSERT INTO ONDEMAND.BADMSP@ESDEV01.DB2_W (BADMSP)
VALUES(my_MandCC);
END LOOP read_c1;

COMMIT;
End P_OD_LOAD;

End OnDemand_CC;

Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War