Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Stored Procedure Compilation Error (SQL Developer(2.1.1.64)/Oracle 10gR2/Windows XP-SP3)
| PL/SQL Stored Procedure Compilation Error [message #484046] |
Fri, 26 November 2010 11:41  |
oruorainfo
Messages: 46
Registered: May 2010
|
Member |
|
|
From the code below, i receive the following compile error:
Error(41,10): PLS-00323: subprogram or cursor 'GET_USERINFO' is declared in a package specification and must be defined in the package body. I am using SQL Developer(2.1.1.64) and
Oracle 10gR2. Would anyone know why please. Thank you.
--User Defined Object Specification
create or replace
TYPE USERS_TYPE_RS
AS OBJECT (
USERID NUMBER,
CODELOOKUPROLE NUMBER,
ORGID NUMBER,
FIRSTNAME VARCHAR2(50)
);
--Package Specification
create or replace
PACKAGE OTMP_TCIS_RS AS
PROCEDURE Get_UserInfo(
o_OutCode OUT INT,
i_language IN VARCHAR2);
FUNCTION Get_UserInfo(
i_language IN VARCHAR2)
RETURN NUMBER;
PROCEDURE Apply_Users_Update(
o_OutCode OUT INT,
i_users IN USERS_TYPE_RS );
END OTMP_TCIS_RS;
--Package Body
create or replace
PACKAGE BODY OTMP_TCIS_RS
AS
--1 PROCEDURE Get_UserInfo
PROCEDURE Get_UserInfo(
o_OutCode OUT INT,
i_language IN VARCHAR2)
AS
v_ProcedureName VARCHAR2(255) :='OTMP_TCIS_RS'||'.Get_UserInfo';
v_ErrorCode NUMBER;
v_ErrorMessage VARCHAR2(255);
v_Parameters VARCHAR2(4000) := SUBSTR('<parameters>' ||
'</parameters>', 1, 4000);
v_SQLStatement VARCHAR(5000) ;
exception_test EXCEPTION ;
exception_test_msg VARCHAR2(1000):= 'Exception test';
exception_validation EXCEPTION;
exception_validation_msg VARCHAR2(1000):= 'Validation failed';
v_userid NUMBER;
BEGIN
o_OutCode := 0;
SELECT systemuser_seq.nextval
INTO v_userid
FROM dual;
DECLARE
myusers USERS_TYPE_RS;
BEGIN
myusers := USERS_TYPE_RS(v_userid,214,214,'TESTINGUSER');
OTMP_TCIS_RS.Apply_Users_Update(o_OutCode, myusers);
EXCEPTION
WHEN exception_test THEN
o_OutCode := -20099;
OTMP_UTIL.InsertErrorMessage(v_ProcedureName, o_OutCode,
exception_test_msg, v_SQLStatement);
WHEN exception_validation THEN
o_OutCode := -20100;
OTMP_UTIL.InsertErrorMessage(v_ProcedureName, o_OutCode,
exception_validation_msg, v_Parameters);
WHEN OTHERS THEN
o_OutCode := SQLCODE;
v_ErrorCode := SQLCODE;
v_ErrorMessage := SUBSTR(SQLERRM, 1, 255);
OTMP_UTIL.InsertErrorMessage(v_ProcedureName, v_ErrorCode,
v_ErrorMessage, v_Parameters);
END;
END Get_UserInfo;
FUNCTION Get_UserInfo(
i_LanguageCode VARCHAR2
) RETURN NUMBER IS
v_result NUMBER;
v_OutCode NUMBER;
BEGIN
OTMP_TCIS_RS.Get_UserInfo(v_OutCode, i_LanguageCode);
IF (v_OutCode IS NULL OR v_OutCode != 0) THEN
v_result := -100;
DBMS_OUTPUT.PUT_LINE('Earth Station....we have a problem');
END IF;
RETURN v_result;
END Get_UserInfo;
PROCEDURE Apply_Users_Update(
o_OutCode OUT INT,
i_users IN USERS_TYPE_RS
)
IS
v_ProcedureName VARCHAR2(255) :='OTMP_TCIS_RS'||'.Apply_Users_Update';
v_ErrorCode NUMBER;
v_ErrorMessage VARCHAR2(255);
v_Parameters VARCHAR2(4000) := SUBSTR('<parameters>' ||
'</parameters>', 1, 4000);
v_SQLStatement VARCHAR(5000) ;
exception_test EXCEPTION ;
exception_test_msg VARCHAR2(1000):= 'Exception test';
exception_validation EXCEPTION;
exception_validation_msg VARCHAR2(1000):= 'Validation failed';
v_LanguageID NUMBER;
BEGIN
o_OutCode := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE(i_users.userid||' '
||i_users.CODELOOKUPROLE||' '
||i_users.CODELOOKUPROLE||' '||i_users.ORGID||' '
||i_users.FIRSTNAME);
EXCEPTION
WHEN exception_test THEN
o_OutCode := -20099;
OTMP_UTIL.InsertErrorMessage(v_ProcedureName, o_OutCode,
exception_test_msg, v_SQLStatement);
WHEN exception_validation THEN
o_OutCode := -20100;
OTMP_UTIL.InsertErrorMessage(v_ProcedureName, o_OutCode,
exception_validation_msg, v_Parameters);
WHEN OTHERS THEN
o_OutCode := SQLCODE;
v_ErrorCode := SQLCODE;
v_ErrorMessage := SUBSTR(SQLERRM, 1, 255);
OTMP_UTIL.InsertErrorMessage(v_ProcedureName, v_ErrorCode,
v_ErrorMessage, v_Parameters);
END;
END Apply_Users_Update;
END OTMP_TCIS_RS;
|
|
|
|
|
|
|
|
| Re: PL/SQL Stored Procedure Compilation Error [message #484051 is a reply to message #484050] |
Fri, 26 November 2010 11:57  |
oruorainfo
Messages: 46
Registered: May 2010
|
Member |
|
|
Thanks for the fast reply Mike, appreciate it. I do not have SQL Plus installed, but i can advise from the SQL Developer Window compile window that line 41 is:
OTMP_TCIS_RS.Apply_Users_Update(o_OutCode, myusers);
I am aware that Raise is missing, it was commented out, so i removed that statement. It looks like:--RAISE exception_test; It is only used to test exception handling.
Thanks.
|
|
|
|
| Re: PL/SQL Stored Procedure Compilation Error [message #484054 is a reply to message #484051] |
Fri, 26 November 2010 12:53 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
You wrote: Quote:It is only used to test exception handling.
WHEN OTHERS WHEN NOT FOLLOWED BY RAISE ITS ALWAYS ALMOST A BUG IN YOUR CODE AND IT HIDES ACTUAL ERROR.
Should not it be VARCHAR2 instead of VARCHAR?
v_SQLStatement VARCHAR(5000) ;
and,
DBMS_OUTPUT.PUT_LINE(i_users.userid||' '
||i_users.CODELOOKUPROLE||' '
||i_users.CODELOOKUPROLE||' '||i_users.ORGID||' '
||i_users.FIRSTNAME);
May be there are more...
Debug procedure OTMP_TCIS_RS.Apply_Users_Update.
It would always be better to /add one module at a time in your package then compile and verify.It should make things easier for you.
Regards
Ved
[Updated on: Fri, 26 November 2010 13:11] Report message to a moderator |
|
|
|
| Re: PL/SQL Stored Procedure Compilation Error [message #484055 is a reply to message #484054] |
Fri, 26 November 2010 13:22 |
oruorainfo
Messages: 46
Registered: May 2010
|
Member |
|
|
Thanks Ved! The code compiles and works with out the Function in the Body and Specification.
I added the wrapper function to allow the Stored Procedure to be call by a Java App (using Beehive).
It is only when i add the Function to the Specification section that i get the compile error!
The Raise does not hide any errors. When RAISE is un-commented, the "WHEN exception_test THEN" section is executed.
Have modified the Varchar to Varchar2..Thanks Ved for your response.
|
|
|
|
|
|
|
|
| Re: PL/SQL Stored Procedure Compilation Error [message #484063 is a reply to message #484062] |
Fri, 26 November 2010 17:21 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
oruorainfo wrote on Fri, 26 November 2010 14:48
...i have tried that, but still the same compilation error...
You tried what? According to what you posted, you have two different parameter names for the Get_UserInfo function in your package and your package body. You need to either make them both i_Lanauge or both i_LanguageCode. You cannot have one in the package and the other in the package body. That is what is causing the error in the code that you posted and fixing it resolves that error. If not, then you are not running what you have posted. As Michel previously stated, you need to post a copy and paste of a run of the compilation of the code and resulting error messages, complete with line numbers, run from SQL*Plus. You should also provide any related items, such as create statements for objects referenced in the code.
|
|
|
|
| Re: PL/SQL Stored Procedure Compilation Error [message #484064 is a reply to message #484063] |
Fri, 26 November 2010 17:38 |
oruorainfo
Messages: 46
Registered: May 2010
|
Member |
|
|
Barbara, you are absolutely correct! In my anxiousness to make the change i made a typo and that fooled me. As soon as i read your second post here i went back to checked again and yes indeed your suggestion has solved my problem. My apologies, and I thank you very much for your patience and kindness and wish you a wonderful weekend! Thanks again Barbara! 
|
|
|
|
Goto Forum:
Current Time: Thu Jul 24 20:14:40 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
