Home » SQL & PL/SQL » SQL & PL/SQL » 10G Issue
10G Issue [message #234518] Tue, 01 May 2007 23:55 Go to next message
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 #234526 is a reply to message #234518] Wed, 02 May 2007 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please post the relevant part of your code
Re: 10G Issue [message #234527 is a reply to message #234518] Wed, 02 May 2007 00:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #234818 is a reply to message #234545] Thu, 03 May 2007 04:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
On 10gR2 it is the same:
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>
SQL> create or replace package mhe_foo
  2  as
  3    var1 varchar2(10);
  4    var1 number;
  5  end;
  6  /

Package created.

SQL>
SQL> DROP PACKAGE mhe_foo
  2  /

Package dropped.


MHE
Re: 10G Issue [message #234824 is a reply to message #234818] Thu, 03 May 2007 04:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I looked a little bit deeper and it seems that all goes well, until you reference the ambiguous variable in your code.

MHE
Re: 10G Issue [message #234831 is a reply to message #234824] Thu, 03 May 2007 04:46 Go to previous messageGo to next message
Frank
Messages: 7880
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 #234836 is a reply to message #234831] Thu, 03 May 2007 05:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In 10gR2, you can have the compiler to give compilation warnings. And that seems to help in this case:
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Session altered.

SQL>
SQL> create or replace package mhe_foo
  2  as
  3    var1 varchar2(10);
  4    var1 number;
  5  end;
  6  /

SP2-0808: Package created with compilation warnings

SQL> sho err
Errors for PACKAGE MHE_FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PLW-05001: previous use of 'VAR1' (at line 3) conflicts with this
         use

SQL>
SQL> drop package mhe_foo
  2  /

Package dropped.


MHE
Re: 10G Issue [message #234858 is a reply to message #234518] Thu, 03 May 2007 06:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Maaher
Messages: 7062
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

Re: 10G Issue [message #235089 is a reply to message #234869] Fri, 04 May 2007 01:15 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sorry, I had posted the input twice. Check out the run, I've corrected it. Thanks, Frank, for letting me know!

MHE

[Updated on: Fri, 04 May 2007 01:16]

Report message to a moderator

Previous Topic: small doubt in sql
Next Topic: Why ROWID starts with only 1?
Goto Forum:
  


Current Time: Sat Dec 03 22:35:51 CST 2016

Total time taken to generate the page: 0.10708 seconds