Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Kansas City
    Posts
    3

    Question Unanswered: declare, procedure.. what?!?

    I don't know what I am doing wrong.. I am still learning SQL/Oracle.. Any advise, help welcome! Thanks in advanced...
    Code:
    Here is error code:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/11     PLS-00323: subprogram or cursor 'SP_POPULATE_FINE_TABLE' is
             declared in a package specification and must be defined in the
             package body
    
    
    Here is my code:
    CREATE OR REPLACE PACKAGE BODY P_P8_Batch
    AS
    PROCEDURE sp_populate_fine_table
    	(
    	return_message		OUT	CHAR,
    	return_sqlcode		OUT	NUMBER
    	)
    AS
    BEGIN	
    		INSERT INTO Fine_Table
    			SELECT
    				CIRC.scan_code,
    				DECODE(CIRC.ssn,NULL,'Unknown',CIRC.ssn),
    				DECODE(Title,NULL,'Unknown',Title),
    				TO_CHAR(date_last_out,'MM-DD-YYYY'),
    				DECODE(date_due,NULL,'',TO_CHAR(date_due,'MM-DD-YYYY')),
    				(todays_date - date_due),
    				TO_CHAR(DECODE(SIGN(todays_date - date_due),1,(LEAST((todays_date - date_due) * .15,(LEAST((todays_date - date_due) * .15, 3)))),0),'$990.99'),
    				0
    			FROM
    				Collection, DP260I.BIBLIO, DP260I.PATRON, CIRC, Current_Date
    			WHERE
    				Collection.Book_ID IN DP260I.BIBLIO.Book_ID
    				AND CIRC.SSN IN DP260I.PATRON.SSN
    				AND Collection.Scan_Code IN CIRC.Scan_Code
    				AND Book_Status = 'O';
    			return_message := 'Fine Table Populated';
    			return_sqlcode := 0;
    			COMMIT;
    EXCEPTION
    	WHEN NO_DATA_FOUND THEN
    		return_message := 'No Fine Found';
    		return_sqlcode := +100;
    	WHEN OTHERS THEN
    		return_message := 'Fine Table Population - Error';
    		return_sqlcode := SQLCODE;
    END sp_populate_fine_table;
    END P_P8_Batch;
    /
    Sean
    Last edited by wizpooter; 12-02-03 at 03:37.

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    A subprogram specification was placed in a package specification, but the corresponding subprogram body was not placed in the package body. The package body implements the package specification. So, the package body must contain the definition of every subprogram declared in the package specification.
    SATHISH .

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    Check the spelling of the subprogram name. If necessary, add the missing subprogram body to the package body.
    SATHISH .

  4. #4
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: declare, procedure.. what?!?

    Originally posted by wizpooter
    I don't know what I am doing wrong.. I am still learning SQL/Oracle.. Any advise, help welcome! Thanks in advanced...
    Code:
    Here is error code:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/11     PLS-00323: subprogram or cursor 'SP_POPULATE_FINE_TABLE' is
             declared in a package specification and must be defined in the
             package body
    
    
    Here is my code:
    CREATE OR REPLACE PACKAGE BODY P_P8_Batch
    AS
    PROCEDURE sp_populate_fine_table
    	(
    	return_message		OUT	CHAR,
    	return_sqlcode		OUT	NUMBER
    	)
    AS
    BEGIN	
    		INSERT INTO Fine_Table
    			SELECT
    				CIRC.scan_code,
    				DECODE(CIRC.ssn,NULL,'Unknown',CIRC.ssn),
    				DECODE(Title,NULL,'Unknown',Title),
    				TO_CHAR(date_last_out,'MM-DD-YYYY'),
    				DECODE(date_due,NULL,'',TO_CHAR(date_due,'MM-DD-YYYY')),
    				(todays_date - date_due),
    				TO_CHAR(DECODE(SIGN(todays_date - date_due),1,(LEAST((todays_date - date_due) * .15,(LEAST((todays_date - date_due) * .15, 3)))),0),'$990.99'),
    				0
    			FROM
    				Collection, DP260I.BIBLIO, DP260I.PATRON, CIRC, Current_Date
    			WHERE
    				Collection.Book_ID IN DP260I.BIBLIO.Book_ID
    				AND CIRC.SSN IN DP260I.PATRON.SSN
    				AND Collection.Scan_Code IN CIRC.Scan_Code
    				AND Book_Status = 'O';
    			return_message := 'Fine Table Populated';
    			return_sqlcode := 0;
    			COMMIT;
    EXCEPTION
    	WHEN NO_DATA_FOUND THEN
    		return_message := 'No Fine Found';
    		return_sqlcode := +100;
    	WHEN OTHERS THEN
    		return_message := 'Fine Table Population - Error';
    		return_sqlcode := SQLCODE;
    END sp_populate_fine_table;
    END P_P8_Batch;
    /
    Sean
    You need also have declaration in a package. (There is two objects with same name: Package and package body).
    You need to add following to the PACKAGE.

    PROCEDURE sp_populate_fine_table (return_message OUT CHAR,return_sqlcode OUT NUMBER);

  5. #5
    Join Date
    Nov 2003
    Location
    Kansas City
    Posts
    3
    I'm feeling sheepish, I forgot to modify the package to correspond with the package body.. That got my attention, excellent now with no error.. however now it won't "import" the data from any of those table according to my procedure. What am i doing wrong?

    Thanks..

    Originally posted by satish_ct
    HI,

    Check the spelling of the subprogram name. If necessary, add the missing subprogram body to the package body.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •