Re: PLS-00201 Error

Dave Wishart (dwishar1_at_ford.com) wrote:
: I have an oracle stored procedure which on execution from a perl DBI:Oracle
: script generates the following:-
 

: Inputs are:- drname=Oracle, dbname=test, dbuser=system, dbpw=manager,
: my_dblink=
: gatewayeuro_008, my_procedure=production_procedure_008, my_username=APPT008,
: my_
: password=WRIGHT8
: Oracle connect parameters:-test, system ,manager, Oracle
: After connect
: BEGIN production_procedure_008(:data, :itpname, :userid, :pwd); END;
: BEGIN production_procedure_008(:data, TC022, APPT008, WRIGHT8); END;
: DBD::Oracle::st execute failed: ORA-06550: line 1, column 7:
: PLS-00201: identifier 'PRODUCTION_PROCEDURE_008' must be declared
: ORA-06550: line 1, column 7:

My first question would be: what does the Oracle reference say about PLS-00201? It's not clear to me from the above that the DBI connection is running as the same user as the SQLplus connection.

Try to run the procedure using its full name

        e.g. BEGIN XXXX.PRODUCTION_PROCEDURE_008(); END;

where XXXX is the correct value, (username of package owner? I forget right now).

: I know that the procedure exists because if a run the query

I am not convinced that your source code extraction query proves the procedure is defined and runable by you.

Did you try to EXEC the procedure interactively?

: select text from user_source where name = 'PRODUCTION_PROCEDURE_008' order
: by line;
 

: I get the procedure listed correctly:-
 

: SQL> select text from user_source where name = 'PRODUCTION_PROCEDURE_008'
: order
: by line;
 

: TEXT
: ----------------------------------------------------------------------------
: ----
 

: PROCEDURE PRODUCTION_PROCEDURE_008
: (io_MESSAGE IN OUT VARCHAR2, ITPname IN VARCHAR2, IUser IN VARCHAR2, IPW IN
: VARCHAR2)
 
: IS
: indx BINARY_INTEGER;
: trannum BINARY_INTEGER;
: apirc BINARY_INTEGER;
: BUILDMSG varchar2(4000);
: PLSMsgIn varchar2(4000);
: PLSMsgOut varchar2(4000);
 

: TEXT
: ----------------------------------------------------------------------------
: ----
 

: testoutput varchar2(4000);
: passarea varchar2(4000);
: ISendBufL integer;
: IRecvBufL integer;
: /* ITPname VARCHAR2(8) := 'TC052 ';*/
: ILUname VARCHAR2(8) := 'IM1WAMVS';
: ISideName VARCHAR2 (8) := 'CAFEM1WA';
: BEGIN
: dbms_output.enable;
: passarea := io_MESSAGE;
: trannum := 0;
 

: TEXT
: ----------------------------------------------------------------------------
: ----
 

: dbms_output.put_line('Before Initialise gateway');
: APPC2IMS4.APPC2IMS4_G_init_at_gatewayEURO_008(
: trannum ,
: ITPname ,
: ILUname ,
: ISideName ,
: IUser ,
: IPW ,
: apirc );
: dbms_output.put_line('after initialise gateway');
: PLSMsgIn := passarea;
 

: TEXT
: ----------------------------------------------------------------------------
: ----
 

: PLSMsgOut := ' ';
: ISendBufL := 4000;
: IRecvBufL := 4000;
: dbms_output.put_line('Start SendRecv');
: APPC2IMS4.SENDRECV_at_gatewayEURO_008(
: trannum ,
: ISendBufL ,
: PLSMsgIn ,
: IRecvBufL ,
: PLSMsgOut ,
: apirc );
 

: TEXT
: ----------------------------------------------------------------------------
: ----
 

: dbms_output.put_line('After SendRecv');
: APPC2IMS4.DeAllocateTP_at_gatewayEURO_008(trannum ,0,apirc);
: dbms_output.put_line('After Deallocate');
: io_MESSAGE := substr(PLSMsgOut,3,800);
: dbms_output.put_line(' send and receive answer back was '||testoutput);
: END PRODUCTION_PROCEDURE_008;
 
: 48 rows selected.
 

: Having done a search on the net for the PLS error a consistent suggestion
: was to check the security so after doing a
 

: grant all privileges on PRODUCTION_PROCEDURE_008 to public;
 

: I tried again and got the same error. I have also 'dropped' and recreated
: the procedure but without success.

: Has anyone got any suggestions as to what else may be causing the error ?
 

: The script is being run using:-
 

: perl v5.6.0 built for sun4-solaris with DBI v1.14 and DBD-Oracle v1.06
: on Solaris 2.6 and Oracle7 Server Release 7.3.3.0.0 - Production Release

: Many thanks.
 

: Regards,
 

: David Wishart
: Server And Interface Team
: Dealer Applications
: FCE Bank plc
 

: Tel: +44-(0)-1277-692570 Fax: +44-(0)-1277-692124
: SMTP: dwishar1_at_ford.com
: Mail: GB-12/300, Jubilee House, The Drive, Brentwood, Essex, CM13 3AR

--