Home » SQL & PL/SQL » SQL & PL/SQL » Can't figure out the problem, can anyone help (Oracle XE, Windows XP)
Can't figure out the problem, can anyone help [message #356334] Thu, 30 October 2008 07:03 Go to next message
susanreid
Messages: 9
Registered: September 2008
Junior Member
Hi
I've got a procedure as part of a package but it won;t compile. Can anyone point out where I'm going wrong?

There are PLS-00302 errors being generated when the package is compiled saying that the component 'parcelid' must be declared. It is generating the same error for the components demurageid and loaddischargeid.

Aren;t the components being declared in the TYPE statements?


 /*==============================================================
  Declare Record Types
  ===============================================================*/
  TYPE recParcel is RECORD(
    parcelid number);
  
  TYPE recDemurrage is RECORD(
    demurrageid number);
    
  TYPE recLoadDischarge is RECORD(
    loaddischargeid number);
    
  /*=============================================================
  Declare Table Types
  ===============================================================*/
  
  TYPE tParcel IS TABLE OF recParcel
    INDEX BY BINARY_INTEGER;    
    
  TYPE tDemurrage IS TABLE OF recDemurrage
    INDEX BY BINARY_INTEGER;
    
  TYPE tLoadDischarge IS TABLE OF recLoadDischarge
    INDEX BY BINARY_INTEGER;


 PROCEDURE DeleteLifting(pLIFTINGID IN NUMBER, pUSERID IN NUMBER)
  AS

  --LOCAL VARIABLES
  nPARCELID NUMBER;
  nLOADDISCHARGEID NUMBER;
  nDEMURAGEID NUMBER;
  nPARCELCOUNT NUMBER;
  nDEMURAGECOUNT NUMBER;
  nLOADCOUNT NUMBER;
  
  tblParcel tParcel;
  tblDemurrage tDemurrage;
  tblLoadDischarge tLoadDischarge;
  
  nIndex number:=1;
  iIndex number:=1;
  
  --Declare cursors
  CURSOR cParcels IS
      SELECT distinct parcelid
      FROM ST_PARCEL
      WHERE liftingid = pliftingid;
     
  CURSOR cDemurrage IS
      SELECT DEMURAGEID
      FROM ST_DEMURAGE
      WHERE LOADDISCHARGEID IN (SELECT LOADDISCHARGEID
                              FROM ST_LOADDISCHARGE
                              WHERE LIFTINGID = pLiftingID);
                              
  CURSOR cLoadDischarge IS
      SELECT LOADDISCHARGEID
      FROM ST_LOADDISCHARGE
      WHERE LIFTINGID = pLIFTINGID;                           
   
  BEGIN

  	 -- CHECK HOW MANY RECORDS EXIST  
  	SELECT COUNT(*)
	INTO nPARCELCOUNT
	FROM ST_PARCEL
	WHERE LIFTINGID = pLIFTINGID;
	
	IF nPARCELCOUNT <> 0 THEN
	   FOR cvParcel in cParcels LOOP
      tblParcel(nIndex).parcelid:=cvParcel.parcelid;
      nIndex := nIndex +1;
    END LOOP;
    nIndex := 1;
	END IF;
	
	SELECT COUNT(*)
	INTO nDEMURAGECOUNT
	FROM ST_DEMURAGE
	WHERE LOADDISCHARGEID IN (SELECT LOADDISCHARGEID
                              FROM ST_LOADDISCHARGE
                              WHERE LIFTINGID = pLiftingID);
							  
	IF nDEMURAGECOUNT <> 0 THEN
	   FOR cvDemurrage in cDemurrage LOOP
      tbldemurrage(nIndex).demurrageid := cvDemurrage.demurageid;
      nIndex := nIndex +1;
    END LOOP;
    nIndex := 1;
	END IF;
							  
	SELECT COUNT(*)
	INTO nLOADCOUNT
	FROM ST_LOADDISCHARGE
	WHERE LIFTINGID = pLIFTINGID;
	
   IF nLOADCOUNT <> 0 THEN
   	FOR cvLoadDischarge in cLoadDischarge LOOP
      tblLoadDischarge(nIndex).LoadDischargeid := cvLoadDischarge.LoadDischargeid;
      nIndex := nIndex +1;
    END LOOP;
    nIndex :=1;
   END IF;
   
    --Delete all Parcel Records for the lifting
    DELETE FROM ST_PARCEL
    WHERE LIFTINGID = pLiftingID;

    --Delete all Demurrage Records
    DELETE FROM ST_DEMURAGE
    WHERE LOADDISCHARGEID IN (SELECT LOADDISCHARGEID
                              FROM ST_LOADDISCHARGE
                              WHERE LIFTINGID = pLiftingID);

    --Delete all the load discharge Records
    DELETE FROM ST_LOADDISCHARGE
    WHERE LIFTINGID = pLiftingID;


    --Delete all the MatchedDeal Records
    DELETE FROM ST_MATCHDEAL
    WHERE LiftingID = pLiftingID;


    --Delete the lifting record, and delete the lifting record if it matches the
    --the jointLIftingID
    DELETE FROM ST_LIFTING
    WHERE LIFTINGID = pLIFTINGID;
	
	/* ===================================================================================================
 Purpose: to update the st_audit table when a record is deleted
 ====================================================================================================*/ 
  UPDATE ST_AUDIT
	SET DELETEUSERID = pUserID,
		DELETEDATE = SYSDATE
		WHERE RECORDID = pLIFTINGID
		AND DELETEDATE IS NULL
		AND TABLENAME = 'ST_LIFTING';	
	
		
	IF nPARCELCOUNT <> 0 THEN
		FOR iIndex in tblParcel.FIRST..tblParcel.LAST LOOP
      nParcelID := tblParcel.parcelid;
   
    UPDATE ST_AUDIT
		SET DELETEUSERID = pUserID,
			DELETEDATE = SYSDATE
			WHERE RECORDID = nPARCELID
			AND DELETEDATE IS NULL
			AND TABLENAME = 'ST_PARCEL';	
    END LOOP;
	END IF;
	
	-- UPDATE THE DEMURAGE RECORD IN ST_AUDIT
	IF nDEMURAGECOUNT <> 0 THEN
  for iIndex in tblDemurrage.FIRST..tblDemurrage.LAST LOOP
    nDemurageID := tblDemurrage.demurageID;
    
		UPDATE ST_AUDIT
		SET DELETEUSERID = pUserID,
			DELETEDATE = SYSDATE
			WHERE RECORDID = nDEMURAGEID
			AND TABLENAME = 'ST_DEMURAGE';
    END LOOP;
	END IF; 
	
	--UPDATE THE LOADDISCHARGE RECORD IN ST_AUDIT
	IF nLOADCOUNT <> 0 THEN
    for iindex in tblLoaddischarge.FIRST.. tblLoadDischarge.LASt LOOP
      nLoaddischargeID := tblLoaddischarge.loaddischargeid;
      
    UPDATE ST_AUDIT
		SET DELETEUSERID = pUserID,
			DELETEDATE = SYSDATE
			WHERE RECORDID = nLOADDISCHARGEID
			AND TABLENAME = 'ST_LOADDISCHARGE';
      
    END LOOP;
	END IF;

    COMMIT;

  END DeleteLifting;


Susan
Re: Can't figure out the problem, can anyone help [message #356336 is a reply to message #356334] Thu, 30 October 2008 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session, use "show errors" to display the errors.

Regards
Michel
Re: Can't figure out the problem, can anyone help [message #356337 is a reply to message #356334] Thu, 30 October 2008 07:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Thanks for formatting your post.

Incorrect one :
Quote:

IF nPARCELCOUNT <> 0 THEN
FOR iIndex in tblParcel.FIRST..tblParcel.LAST LOOP
nParcelID := tblParcel.parcelid;



Correct One :
Quote:

IF nPARCELCOUNT <> 0 THEN
FOR cvParcel in cParcels LOOP
tblParcel(nIndex).parcelid:=cvParcel.parcelid;



Hope this helps.

Regards

Raj
Re: Can't figure out the problem, can anyone help [message #356338 is a reply to message #356334] Thu, 30 October 2008 07:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Next time please show the complete error, including linenumbers

This line tries to address a parcelid directly from the table (no index used):
nParcelID := tblParcel.parcelid;
Re: Can't figure out the problem, can anyone help [message #356344 is a reply to message #356338] Thu, 30 October 2008 07:29 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I find it very strange that before opening of every cursor you are doing count(*) to check if records exists and then process the cursor

SELECT COUNT(*)
	INTO nPARCELCOUNT
	FROM ST_PARCEL
	WHERE LIFTINGID = pLIFTINGID;

	
	IF nPARCELCOUNT <> 0 THEN
	   FOR cvParcel in cParcels LOOP
      tblParcel(nIndex).parcelid:=cvParcel.parcelid;
      nIndex := nIndex +1;
    END LOOP;



It is bad coding habit and has unwanted perfomance impact. You should just open the cursor and process it.

I know that I did not help you in identifying reason of error but felt that when you fix the error you need to relook this aspect as well.
Re: Can't figure out the problem, can anyone help [message #356354 is a reply to message #356334] Thu, 30 October 2008 08:38 Go to previous message
susanreid
Messages: 9
Registered: September 2008
Junior Member
Thanks for all your help. Its working now
Previous Topic: merge statment
Next Topic: find 2 to 3rd character but within a range (merged)
Goto Forum:
  


Current Time: Sun Dec 11 08:02:13 CST 2016

Total time taken to generate the page: 0.13410 seconds