Oracle Package not executing 1

[NuniPR]

Hi Guys!

I have a package with 7 procedures. I have run these procedures separately and they work perfectly. Now I have assembled all into 1 package and I can't get the packate to run. I get this error:

ORA-06550: line 2, column 16:
PLS-00302: component 'PKG_E911' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

I am running this package in TOAD because I am working on my test environment before running it in production. I notice that when trying to run it, the Set Parameters window has the following script:

BEGIN 
  schema.PKG_INFO.SP_INFO_DEL_REC;
  COMMIT; 
END;

SP_INFO_DEL_REC is the second procedure within the package. Enough said, This is what my package looks like:

CREATE OR REPLACE PACKAGE PKG_INFO AS

PROCEDURE SP_INFO_EXTRACT;

PROCEDURE SP_INFO_DEL_REC;

PROCEDURE SP_SNAP_TRUNC;

PROCEDURE SP_INFO_SNAPSHOT;

PROCEDURE SP_INFO_LOAD_DATA;

PROCEDURE SP_INFO_UPDATE_NAME;

PROCEDURE SP_INFO_UPDATE_ADDRESS;

END;
/

What I am missing?

Thanks so much for your help, guys! You are the best!



Nunina
Cognos BI Administrator
San Juan, PR

 

[NuniPR]

Sorry there's a typo. This is the error:

ORA-06550: line 2, column 16:
PLS-00302: component 'PKG_INFO' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Thanks again, guys!

Nunina
Cognos BI Administrator
San Juan, PR

 

[SantaMufasa]

Enough said...

Not quite...where is your PACKAGE BODY definition? The "total PACKAGE" definition needs to look like this:

CREATE OR REPLACE PACKAGE PKG_INFO AS
    PROCEDURE SP_INFO_EXTRACT;
    PROCEDURE SP_INFO_DEL_REC;
    PROCEDURE SP_SNAP_TRUNC;
    PROCEDURE SP_INFO_SNAPSHOT;
    PROCEDURE SP_INFO_LOAD_DATA;
    PROCEDURE SP_INFO_UPDATE_NAME;
    PROCEDURE SP_INFO_UPDATE_ADDRESS;
END;
/

CREATE OR REPLACE PACKAGE [b]BODY[/b] PKG_INFO AS
    PROCEDURE SP_INFO_EXTRACT IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_INFO_DEL_REC IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_SNAP_TRUNC IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_INFO_SNAPSHOT IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_INFO_LOAD_DATA IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_INFO_UPDATE_NAME IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_INFO_UPDATE_ADDRESS IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
END;
/

Let us know is this structure is what you really have.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[NuniPR]

I have done many procedure but in reality, this is my first package. I am using existing packages (I inherited the DB) as examples to create this one... that's why your answer just surprised me!

Well, let me try it, and I'll let you know!

Thanks!!



Nunina
Cognos BI Administrator
San Juan, PR

 

[NuniPR]

Mufasa...

These procedures have been running for years. A person (me in this case), loads up SQL Plus and executes them one by one. I decided that it was too complicated to do it like that, so I decided to package them up and just run it once with a command script.

Sooo, if I am undestanding your code, I'd have to re-create the procedure within the package? The package would look something like this, right?

CREATE OR REPLACE PACKAGE PKG_INFO AS
    PROCEDURE SP_INFO_EXTRACT;
    PROCEDURE SP_INFO_DEL_REC;
    PROCEDURE SP_SNAP_TRUNC;
    PROCEDURE SP_INFO_SNAPSHOT;
    PROCEDURE SP_INFO_LOAD_DATA;
    PROCEDURE SP_INFO_UPDATE_NAME;
    PROCEDURE SP_INFO_UPDATE_ADDRESS;
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_INFO AS
    PROCEDURE SP_INFO_EXTRACT IS
        V_TNAME VARCHAR2(100);	--New table name --
	V_CREATAB VARCHAR2(200);--Create table statement --
	
	BEGIN
 
 	V_TNAME:='EXTRACT_INFO_BK_' || to_char(trunc(sysdate),'YYYYMMDD')|| '';
	V_CREATAB:='CREATE TABLE INFO_EXTRACT.' || V_TNAME || ' AS SELECT 
	* FROM EXTRACT_INFO_NEW';
 	
	EXECUTE IMMEDIATE V_CREATAB;
	
	EXCEPTION
	WHEN OTHERS THEN
	 dbms_output.put_line('Error Exception : '||sqlerrm||' '||sqlcode);
	
	COMMIT;	
END;

    PROCEDURE SP_INFO_DEL_REC IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_SNAP_TRUNC IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_INFO_SNAPSHOT IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_INFO_LOAD_DATA IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_INFO_UPDATE_NAME IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
    PROCEDURE SP_INFO_UPDATE_ADDRESS IS
            <variable definitions, if applicable>
        BEGIN
            <procedure logic>
        END;
END;
/

Nunina
Cognos BI Administrator
San Juan, PR

 

[SantaMufasa]

Sí, eso es correcto.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[NuniPR]

Ok, gracias!

So, what happens to the existing Procedure? Would it be good practice to include a package within a package? If so, is it the same, where I'd have to re-create the package within the other package?

Gracias!! I'll post in a couple of minutes the final code.


Nunina
Cognos BI Administrator
San Juan, PR

 

[NuniPR]

Here is the code for a couple of the procedures. Please let me know if I got the idea right.

Muchas Gracias!

CREATE OR REPLACE PACKAGE PKG_INFO AS
    PROCEDURE SP_INFO_EXTRACT;
    PROCEDURE SP_INFO_DEL_REC;
    
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_INFO AS
    PROCEDURE SP_INFO_EXTRACT IS
           	V_TNAME VARCHAR2(100);	--New table name --
	V_CREATAB VARCHAR2(200);	--Create table statement --
	
	BEGIN
 
 	V_TNAME:='EXTRACT_INFO_BK_' || to_char(trunc(sysdate),'YYYYMMDD')|| '';
	V_CREATAB:='CREATE TABLE INFO_EXTRACT.' || V_TNAME || ' AS SELECT 
			* FROM EXTRACT_INFO_NEW';
 	
	EXECUTE IMMEDIATE V_CREATAB;
	
	EXCEPTION
		WHEN OTHERS THEN
	     	 	dbms_output.put_line('Error Exception : '||sqlerrm||' '||sqlcode);
	
	COMMIT;	
END;

    PROCEDURE SP_INFO_DEL_REC IS

	DISCONNECT_RECS NUMBER:=0;

	TOTAL_DISCONNECT_RECS NUMBER :=0;

	CURSOR DISCONNECT_SERV IS
		SELECT  E.ROWID TEMP_ROWID,
		E.CUST_ACCT_ID,
		T.TEL_NBR_STATUS STATUS,
		T.TO_EFF_DT,
		T.LAST_MODIFIED_DATE,
		T.TEL_NBR_NPA||T.TEL_NBR_NXX||T.TEL_NBR_LINE_RANGE 					FROM    INFO_EXTRACT.INFO_EXTRACT_INFO E, 
		TEL_NUM_INV T 
	WHERE   E.NPA||E.CALLING_NUMBER = T.TEL_NBR_NPA||T.TEL_NBR_NXX||T.TEL_NBR_LINE_RANGE
		AND 	E.TEL_NBR_SUF = T.TEL_NBR_SUF
		AND 	T.CUST_ACCT_ID = E.CUST_ACCT_ID   
		AND 	TRUNC(T.LAST_MODIFIED_DATE) >= (SELECT MAX(TRUNC(EX.REC_SENT_DATE))
							FROM INFO_EXTRACT.INFO_EXTRACT_INFO EX)			   						               		  									
		AND 	T.TO_EFF_DT IS NOT NULL
		AND 	T.TEL_NBR_STATUS = '8'  
		AND 	E.FUNCTION_CODE != 'D';

BEGIN

FOR REC IN DISCONNECT_SERV LOOP
	DISCONNECT_RECS := DISCONNECT_RECS + 1;
	TOTAL_DISCONNECT_RECS := TOTAL_DISCONNECT_RECS + 1;
	UPDATE INFO_EXTRACT.INFO_EXTRACT_INFO
		SET FUNCTION_CODE = 'D',
		SERV_STATUS = REC.STATUS, 
		REC_SENT_DATE = NULL,
		TO_EFF_DT = REC.TO_EFF_DT,
		LAST_MODIFIED_DATE = REC.LAST_MODIFIED_DATE,
		RECEIVED_BY_PSAP_DATE = NULL
	WHERE ROWID = REC.TEMP_ROWID;

	IF DISCONNECT_RECS > 100 THEN
		COMMIT;
	DISCONNECT_RECS := 0;
		ELSE
	DISCONNECT_RECS := DISCONNECT_RECS+1;
		END IF;


END LOOP;

	DBMS_OUTPUT.PUT_LINE('TOTAL RECORDS DELETED: '||TOTAL_DISCONNECT_RECS);

COMMIT;

EXCEPTION

WHEN UTL_FILE.INVALID_PATH THEN
	RAISE_APPLICATION_ERROR(-20001,'INVALID_PATH: FILE location OR filename was invalid.');
WHEN UTL_FILE.INVALID_MODE THEN
	RAISE_APPLICATION_ERROR(-20002,'INVALID_MODE: The open_mode parameter IN FOPEN was invalid.');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
	RAISE_APPLICATION_ERROR(-20002,'INVALID_FILEHANDLE: The FILE handle was invalid.');
WHEN UTL_FILE.INVALID_OPERATION THEN
	RAISE_APPLICATION_ERROR(-20003,'INVALID_OPERATION: The FILE could NOT be opened OR operated ON AS requested.');
WHEN UTL_FILE.READ_ERROR THEN
	RAISE_APPLICATION_ERROR(-20004,'READ_ERROR: An operating system error occurred during the read operation.');
WHEN UTL_FILE.WRITE_ERROR THEN
	RAISE_APPLICATION_ERROR(-20005,'WRITE_ERROR: An operating system error occurred during the WRITE operation.');
WHEN UTL_FILE.INTERNAL_ERROR THEN
	RAISE_APPLICATION_ERROR(-20006,'INTERNAL_ERROR: An unspecified error IN PL/SQL.'||SQLCODE||' '||SQLERRM);

END;


END;
/

Nunina
Cognos BI Administrator
San Juan, PR

 

[SantaMufasa]

¡Sí, su intuición es excelente!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

...but, you cannot place a package within a package. You can, however, reference a packaged procedure from within another packaged procedure.

Also, you can have your procedures both packaged and independent since the references are different:

begin
    ...
    SP_INFO_EXTRACT;
    ...
end;

(versus)

begin
    ...
    PKG_INFO.SP_INFO_EXTRACT;
    ...
end;

Both references are legal and able to co-exist...the first as a user-defined procedure and the second as a packaged procedure.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[NuniPR]

Muchas gracias. Aqui tienes una estrella.

But, we still have a problem somewhere, because although it compiles, I still get the error:

ORA-06550: line 2, column 16:
PLS-00302: component 'PKG_INFO' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Nunina
Cognos BI Administrator
San Juan, PR

 

[SantaMufasa]

Nuni, please post the code that generated the error (i.e., "line 2...").

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[NuniPR]

Mufasa,

Line 2 is:

For the specs:

PROCEDURE SP_INFO_EXTRACT;

For the body:

PROCEDURE SP_INFO_EXTRACT IS

Nunina
Cognos BI Administrator
San Juan, PR

 

[SantaMufasa]

Sorry...Could you please do this for me:

1) In SQL*Plus, run the code that generates the error message.
2) At the SQL> prompt, enter the command, "list[Enter]"
3) Sweep your cursor from the start of the error listing downward to the end of the "list" results...[ctrl-C](to copy the area you swept).
4) Paste ([ctrl-V]) into a reply here the copied text.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[NuniPR]

Hola Mufasa,

Had a meeting all afternoon yesterday. Thanks for all your help.

The error is executing the package. Maybe I have the execute syntax wrong?

  SQL> exec pkg_info;
BEGIN pkg_info; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'PKG_INFO' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Nunina
Cognos BI Administrator
San Juan, PR

 

[NuniPR]

Thinking about it, could it be a GRANTS issue?

Gracias!

Nunina
Cognos BI Administrator
San Juan, PR

 

[SantaMufasa]

You are correct, Nunina, the syntax is incorrect, but you are very close...Although you cannot execute a package directly (i.e., cannot execute a package with an "EXEC <package>" command, you can execute a packaged procedure directly. For example, you can say:

exec PKG_INFO.SP_INFO_EXTRACT
exec PKG_INFO.SP_INFO_DEL_REC

Try that and tell us what happens.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Actually, now that I QC my statement, you can "EXEC <package>", but you must have an independent "BEGIN" section following all of the packaged procedure definitions. For example, if you wanted your package to execute all of your packaged procedures, you could do this (given your most recent two-procedure package:

CREATE OR REPLACE PACKAGE PKG_INFO AS
    PROCEDURE SP_INFO_EXTRACT;
    PROCEDURE SP_INFO_DEL_REC;
    
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_INFO AS
    PROCEDURE SP_INFO_EXTRACT IS
        <procedure definition>
    PROCEDURE SP_INFO_DEL_REC is
        <procedure definition>
BEGIN
    SP_INFO_EXTRACT;
    SP_INFO_DEL_REC;
END;
/

...Then, you could invoke the package using the syntax that you asserted earlier:

exec PKG_INFO

Let us know your reactions to this alternative.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[NuniPR]

Sorry, Mufasa. Had to make it a 2 SP package, since the SP def was too long for SQLPlus.

Well, then... Let me see if I understand, and you might be able to give me some pointers. The purpose of creating the package is to invoke it from a command script. It may just be easier to call each procedure within the cmd, and place a break between procedures to account for the time of execution. hmmm.... ideas?

I still get the same error with your second suggestion, which I loved. I am trying to get our DBA to confirm the GRANTS thing. I'll write back soon.

Muchas Gracias, amigo!

Nunina
Cognos BI Administrator
San Juan, PR

 

[SantaMufasa]

Had to make it a 2 SP package, since the SP def was too long for SQLPlus.

This puzzles me since I have package-definition scripts that are thousands of lines long...much longer that your package code. Are you running the code definition in SQL*Plus like this:

SQL> @<package-definition script file name>.sql

The purpose of creating the package is to invoke it from a command script.

Although you can invoke a package from a command script, you can also invoke non-packaged, user-defined procedures from a command script, as well.

There are other features and benefits of packages that are not available from non-packaged, user-defined procedures. But in your case, those other features and benefits do not seem to be key to your situation.

(I Googled for the search string, * Oracle "benefits of packages" * and it resulted in 118 hits, most of which are books, tutorials, on-line training, et cetera for a price, but there may be a link in there that deals directly (without purchase) on the topic.)

So, let us know if you want us to follow up with any of these issues.

...place a break between procedures to account for the time of execution.

No need for this...if you are invoking procedures from a single script, the executions are serial, not parallel, so you needn't worry about breaks to allow for asynchronous tasks.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]