| 10G Issue [message #234518] |
Tue, 01 May 2007 23:55  |
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
|
Member |
|
|
Hi,
I am facing one issue with 10G
i have one package in 9i which is not getting compiled on 10g
In that package i have one Common Variable having same name but different types. one is of varchar and other one is of number type
Error message:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1335/5 PL/SQL: SQL Statement ignored
1368/6 PLS-00371: at most one declaration for 'DISBURSEMENT_POSTING.CMN_ACC_YYMM' is permitted
1368/6 PL/SQL: ORA- 00904: "DISBURSEMENT_POSTING"."CMN_ACC_YYMM": invalid
identifier
This was working in 9i and not in 10g is 10g not supporting this existing feature
Thnx
Atul
|
|
|
|
|
|
| Re: 10G Issue [message #234527 is a reply to message #234518] |
Wed, 02 May 2007 00:54  |
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
|
Member |
|
|
Common Variable for the package.
cmn_refund_type policy_dues.pdue_refund_type%TYPE;
cmn_batch_no cash_batch_statement.cbs_batch_no%TYPE;
cmn_batch_revision_no cash_batch_statement.cbs_batch_revision_no%TYPE := '00';
cmn_check_slno policy_dues.pdue_cash_check_slno%TYPE := 0;
cmn_cash_batch_no cash_batch_statement.cbs_batch_no%TYPE;
cmn_cash_batch_rev_no cash_batch_statement.cbs_batch_revision_no%TYPE := '00';
cmn_payee_name cash_check.cc_payee_name%TYPE;
cmn_cash_check_slno policy_dues.pdue_cash_check_slno%TYPE := 0;
cmn_line cash_batch_statement.cbs_line%TYPE;
cmn_system_activity_no cash_batch_statement.cbs_system_activity_no%TYPE;
cmn_cash_activity_no cash_batch_statement.cbs_cash_activity_no%TYPE;
cmn_batch_total cash_batch_statement.cbs_batch_total%TYPE := 0;
cmn_no_of_checks cash_batch_statement.cbs_check_nos%TYPE := 0;
cmn_acc_yymm cash_batch_statement.cbs_acc_yymm%TYPE; -- Number
cmn_check_amount cash_check.cc_check_total%TYPE := 0;
cmn_broker cash_check.cc_broker%TYPE;
cmn_policy_no policy_register.preg_policy_no%TYPE;
cmn_account_code policy_register.preg_account_code%TYPE;
cmn_session_no policy_register.preg_session_no%TYPE;
cmn_policy_renew_no policy_register.preg_policy_renew_no%TYPE;
cmn_bank_code cash_check.cc_bank_code%TYPE;
cmn_payee_ind cash_check.cc_payee_ind%TYPE;
cmn_payee cash_check.cc_payee%TYPE;
cmn_ref_no cash_check.cc_broker_ref_no%TYPE;
cmn_tran_amount policy_dues.pdue_cash_entry_amount%TYPE;
cmn_system_activity_code policy_dues.pdue_system_activity_code%TYPE := '0924200';
cmn_cash_activity_code cash_batch_statement.cbs_cash_activity_code%TYPE := '0002110';
cmn_cash_type policy_dues.pdue_cash_type%TYPE;
-- cmn_subline disbursement_detail.dbd_subline%TYPE;
cmn_subline VARCHAR2(20);
cmn_acc_yymm VARCHAR2(6);
cmn_message activity_code_master.acm_system_message%TYPE;
cmn_cbs_message activity_code_master.acm_system_message%TYPE;
checkout the variable "cmn_acc_yymm"
|
|
|
|
| Re: 10G Issue [message #234532 is a reply to message #234527] |
Wed, 02 May 2007 01:12 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
This is simply a bug in your code.
I find it very strange that it was not picked up by 9i.
So, no option for you but fix your (faulty!) code.
|
|
|
|
| Re: 10G Issue [message #234535 is a reply to message #234532] |
Wed, 02 May 2007 01:14 |
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
|
Member |
|
|
Did that changes and tested -- working fine.
but its strange that this package got compiled in 9i without error....
|
|
|
|
| Re: 10G Issue [message #234545 is a reply to message #234535] |
Wed, 02 May 2007 01:45 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You are right:BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> create or replace package mytest
2 as
3 var1 varchar2(10);
4 var1 number;
5 end;
6 /
Package created. [Updated on: Wed, 02 May 2007 01:46] Report message to a moderator |
|
|
|
|
|
|
|
| Re: 10G Issue [message #234831 is a reply to message #234824] |
Thu, 03 May 2007 04:46 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You are right, Maarten, and this also goes for 9.2.0.8:
SQL> create or replace package mytest
2 as
3 var1 varchar2(10);
4 var1 number;
5 procedure test;
6 end;
7 /
Package created.
SQL> create or replace package body mytest
2 as
3 procedure test
4 is
5 begin
6 var1 := null;
7 end;
8 end;
9 /
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY MYTEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5 PLS-00371: at most one declaration for 'VAR1' is permitted in the
declaration section
6/5 PL/SQL: Statement ignored
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production |
|
|
|
|
|
| Re: 10G Issue [message #234858 is a reply to message #234518] |
Thu, 03 May 2007 06:05 |
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
|
Member |
|
|
Hi Frank,
Pls check the code which has the common variable with same name but different datatypes.
In your last reply u have shown that in 9i u have got the error message when compiling the package body but in my code the package has compiled successfully in 9i. And in 10g i am getting error.
LINE/COL ERROR
-------- -----------------------------------------------------------------
1335/5 PL/SQL: SQL Statement ignored
1368/6 PLS-00371: at most one declaration for 'DISBURSEMENT_POSTING.CMN_ACC_YYMM' is permitted
1368/6 PL/SQL: ORA- 00904: "DISBURSEMENT_POSTING"."CMN_ACC_YYMM": invalid
identifier
i have pasted the package specs & attached the body - pls check out the variable "cmn_acc_yymm"
SPECS --
CREATE OR REPLACE PACKAGE disbursement_posting
IS
strRevision Varchar2(100) := '$Revision:: 4 $';
cmn_refund_type policy_dues.pdue_refund_type%TYPE;
cmn_batch_no cash_batch_statement.cbs_batch_no%TYPE;
cmn_batch_revision_no cash_batch_statement.cbs_batch_revision_no%TYPE := '00';
cmn_check_slno policy_dues.pdue_cash_check_slno%TYPE := 0;
cmn_cash_batch_no cash_batch_statement.cbs_batch_no%TYPE;
cmn_cash_batch_rev_no cash_batch_statement.cbs_batch_revision_no%TYPE := '00';
cmn_payee_name cash_check.cc_payee_name%TYPE;
cmn_cash_check_slno policy_dues.pdue_cash_check_slno%TYPE := 0;
cmn_line cash_batch_statement.cbs_line%TYPE;
cmn_system_activity_no cash_batch_statement.cbs_system_activity_no%TYPE;
cmn_cash_activity_no cash_batch_statement.cbs_cash_activity_no%TYPE;
cmn_batch_total cash_batch_statement.cbs_batch_total%TYPE := 0;
cmn_no_of_checks cash_batch_statement.cbs_check_nos%TYPE := 0;
cmn_acc_yymm cash_batch_statement.cbs_acc_yymm%TYPE;
cmn_check_amount cash_check.cc_check_total%TYPE := 0;
cmn_broker cash_check.cc_broker%TYPE;
cmn_policy_no policy_register.preg_policy_no%TYPE;
cmn_account_code policy_register.preg_account_code%TYPE;
cmn_session_no policy_register.preg_session_no%TYPE;
cmn_policy_renew_no policy_register.preg_policy_renew_no%TYPE;
cmn_bank_code cash_check.cc_bank_code%TYPE;
cmn_payee_ind cash_check.cc_payee_ind%TYPE;
cmn_payee cash_check.cc_payee%TYPE;
cmn_ref_no cash_check.cc_broker_ref_no%TYPE;
cmn_tran_amount policy_dues.pdue_cash_entry_amount%TYPE;
cmn_system_activity_code policy_dues.pdue_system_activity_code%TYPE := '0924200';
cmn_cash_activity_code cash_batch_statement.cbs_cash_activity_code%TYPE := '0002110';
cmn_cash_type policy_dues.pdue_cash_type%TYPE;
-- cmn_subline disbursement_detail.dbd_subline%TYPE;
cmn_subline VARCHAR2(20);
cmn_acc_yymm VARCHAR2(6);
cmn_message activity_code_master.acm_system_message%TYPE;
cmn_cbs_message activity_code_master.acm_system_message%TYPE;
/*
FUNCTION check_cancellation_days( prm_policy_no IN VARCHAR2,
prm_policy_renew_no IN VARCHAR2)
RETURN number;
*/
PROCEDURE get_state_code ( prm_group_code IN VARCHAR2,
prm_code IN VARCHAR2,
prm_state_code OUT VARCHAR2,
prm_success_failure OUT VARCHAR2);
FUNCTION get_state_limit( prm_state_code IN VARCHAR2, prm_success_failure OUT VARCHAR2)
return number;
PROCEDURE populate_flds_cash_cbs ( prm_success_failure in out varchar2,
prm_err_msg in out varchar2);
PROCEDURE populate_flds_cash_chk ( prm_success_failure in out varchar2,
prm_err_msg in out varchar2);
PROCEDURE populate_flds_pol_due ( prm_success_failure in out varchar2,
prm_err_msg in out varchar2);
PROCEDURE populate_suspense_neg_chk ( prm_success_failure in out varchar2,
prm_err_msg in out varchar2);
END disbursement_posting;
/
|
|
|
|
| Re: 10G Issue [message #234869 is a reply to message #234858] |
Thu, 03 May 2007 06:41 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Hi,
I simplified your script like this:
CREATE TABLE mhe_foo(col1 VARCHAR2(50))
/
CREATE OR REPLACE PACKAGE mhe_pkg
IS
cmn_acc_yymm countries.country_id%TYPE;
cmn_acc_yymm VARCHAR2 (6);
END mhe_pkg;
/
sho err
CREATE OR REPLACE PACKAGE BODY mhe_pkg
IS
PROCEDURE TEST
IS
BEGIN
INSERT INTO mhe_foo values(mhe_pkg.cmn_acc_yymm);
END TEST;
END mhe_pkg;
/
sho err
DROP TABLE mhe_foo PURGE
/
DROP PACKAGE mhe_pkg
/
When I run it, I get this:
SQL> @orafaq
Table created.
Package created.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY MHE_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5 PL/SQL: SQL Statement ignored
6/32 PL/SQL: ORA-00904: "MHE_PKG"."CMN_ACC_YYMM": invalid identifier
6/32 PLS-00371: at most one declaration for 'MHE_PKG.CMN_ACC_YYMM' is
permitted
Table dropped.
Package dropped.
SQL>
This is consistent with my findings: you can declare the same variable twice without any error (the specification did compile successfully) but you cannot reference it. The package body did and compiled with errors.
MHE
[Updated on: Fri, 04 May 2007 01:15] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
