Class::DBI Oracle View Access

set_uk has asked for the wisdom of the Perl Monks concerning the following question:

After creating many Class::DBI modules which access Oracle tables I have come to create a module which attempts to access a view and it fails. Module:-

package SDU::DBI_MaxSeqTotPorts;
use strict;
use base 'SDU::DBI';
# SDU_DBI is based on Class::DBI::Oracle
__PACKAGE__->set_up_table('MAX_SEQ_TOT_PORTS');
__PACKAGE__->columns(Primary => 'ID');
__PACKAGE__->columns(Others => qw/CUSTOMER SWITCH_NAME SEQUENCE PORTS/
+);
1;
[download]

View:-

CREATE OR REPLACE VIEW sdu.max_seq_tot_ports (
   id,
   customer,
   switch_name,
   sequence,
   ports )
AS
SELECT a.ID, b.CUSTOMER, b.SWITCH_NAME, b.SEQUENCE, SUM(b.COUNT_X)
FROM  SDU_SWITCHES a , SDP_QUERY_RESULTS b
WHERE a.SWITCH_NAME = b.SWITCH_NAME
AND a.CUSTOMER = b.CUSTOMER
AND (b.SWITCH_NAME, b.SEQUENCE) IN
(
  SELECT SWITCH_NAME, MAX(SEQUENCE)
  FROM  SDP_QUERY_RESULTS
  GROUP BY SWITCH_NAME
)
GROUP BY a.ID, b.CUSTOMER, b.SWITCH_NAME, b.SEQUENCE
/
[download]

Error:-

The "MAX_SEQ_TOT_PORTS" table has no primary key at /opt/ar/scripts/sd
+p_upload/SDU/DB.pm line 15
Compilation failed in require at /opt/ar/scripts/sdp_upload/SDU/DB.pm 
+line 15.
BEGIN failed--compilation aborted at /opt/ar/scripts/sdp_upload/SDU/DB
+.pm line 15.
Compilation failed in require at ./tobj.pl line 3.
BEGIN failed--compilation aborted at ./tobj.pl line 3.
[download]

Using Class::DBI - 0.94, Class::DBI::Oracle 0.51 Any one had this issue?

Comment on Class::DBI Oracle View Access Select or Download Code

Replies are listed 'Best First'.
Re: Class::DBI Oracle View Access by ant9000 (Monk) on Oct 21, 2003 at 12:22 UTC
I fear my contribute will be very modest, I have no Oracle here to try with (and not much experience on DBD::Oracle, to be honest). Anyway, I'd say that the line `__PACKAGE__->columns(Primary => 'ID');` [download] is likely to be responsible for the error 'The "MAX_SEQ_TOT_PORTS" table has no primary key'. Maybe you don't need the line at all? Your view clearly has no primary key, and Oracle should automatically take care of indexes on the underlying tables. Take a look at the Oracle docs for further reference. Ant9000	[reply] [d/l]

Replies are listed 'Best First'.

Re: Class::DBI Oracle View Access
by ant9000 (Monk) on Oct 21, 2003 at 12:22 UTC

__PACKAGE__->columns(Primary => 'ID');
[download]

The "MAX_SEQ_TOT_PORTS" table has no primary key

Oracle docs

[reply]
[d/l]