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

I have the following subroutine that is supposed to create a SQL stmt on the fly, but it keeps failing with this error: ORA-00972: identifier is too long (DBD ERROR: OCIStmtPrepare) at promo.pl line 933. when it gets to the prepare section, and it prints out the else part of the query when it should not be. I'm stumped. 
sub get_BCE_qty
{
    my $num_days_of_supp  = shift;
    my $loc                             = shift;
    my $dmdunit                     = shift;
    
    LogMsg("Days of Supply = $num_days_of_supp");
    
    my $qry;
  my @new_array;
  
  my @months_to_sum = ( 12 - ($num_days_of_supp/30) .. 11 );
  
  if ( $num_days_of_supp % 30 == 0 )
  {
    $qry = '"SELECT GREATEST(ROUND((SUM('
    . join( ' + ', map { sprintf "mth_%02i", $_ } ( 1 .. 12 ) )
    . ") / 365 ) * $num_days_of_supp ), ROUND (SUM ("
    . join(' + ', map { sprintf "mth_%02i", $_ } @months_to_sum)
    . ')))  QTY, b. p_dlrnet, b.p_dlrsplrcode '
    . <<'EOF';
  
  FROM pac.historyreportmonthlybysku a
              , stsc.sku b
          WHERE a.loc                  = ?
          AND a.dmdunit                = ?
          AND a.loc                    = b.loc
          AND a.dmdunit                = b.ITEM
          GROUP BY a.loc
                  , a.dmdunit
                  , b.p_dlrnet
                  , b.p_dlrsplrcode"
EOF
  
  }
  else
  {
      @new_array = @months_to_sum;
    shift @new_array;   # remove the 1st element since this is the one
+ we divide by 2
  
    $qry = '"SELECT GREATEST(ROUND((SUM('
    . join( ' + ', map { sprintf "mth_%02i", $_ } ( 1 .. 12 ) )
    . ") / 365 ) * $num_days_of_supp ), ROUND (SUM ("
    . sprintf ("mth_%02i", $months_to_sum[0]) . " / 2 + "
    . join(' + ', map { sprintf "mth_%02i", $_ } @new_array)
    . ')))  QTY, b. p_dlrnet, b.p_dlrsplrcode '
    . <<'EOF';
  
  FROM pac.historyreportmonthlybysku a
              , stsc.sku b
          WHERE a.loc                  = ?
          AND a.dmdunit                = ?
          AND a.loc                    = b.loc
          AND a.dmdunit                = b.ITEM
          GROUP BY a.loc
                  , a.dmdunit
                  , b.p_dlrnet
                  , b.p_dlrsplrcode"
EOF
  
  };

    LogMsg($qry);
    
     # prepare and execute the query
     my $sth_C = $dbh->prepare( $qry ) or die $dbh->errstr;
                                                                      
+     
    $sth_C->execute( $loc, $dmdunit );        

    my ($col1, $col2, $col3);
      
  $sth_C->bind_col(1, \$col1);
  $sth_C->bind_col(2, \$col2);
  $sth_C->bind_col(3, \$col3);
  
    $sth_C->fetch();
  my $BCE_qty         = $col1;
  my $dlrnet          = $col2;
  my $dlrsupcode     = $col3;
  
  return $BCE_qty, $dlrnet, $dlrsupcode;
}

[download]

Replies are listed 'Best First'.
Re: Help with Dynamic SQL subroutine
by SheridanCat (Pilgrim) on Nov 22, 2006 at 03:08 UTC
    I would suggest doing a google search on the Oracle error. Apparently an identifier can only be 30 characters long. Are you violating that length? These are any schema objects: tables, views, synonyms, etc.
[reply]
      I'm using Oracle 9.2.0.2. I don't think it's a problem with the query because I can cut and paste it into TOAD and it runs with no problem. This is what's getting prepared: 
      "SELECT GREATEST(ROUND((SUM(mth_01 + mth_02 + mth_03 + mth_04 + mth_05
+ + mth_06 + mth_07 + mth_08 + mth_09 + mth_10 + mth_11 + mth_12) / 36
+5 ) * 120 ), ROUND (SUM (mth_08 + mth_09 + mth_10 + mth_11)))  QTY, b
+. p_dlrnet, b.p_dlrsplrcode   
  FROM pac.historyreportmonthlybysku a
              , stsc.sku b
          WHERE a.loc                  = ?
          AND a.dmdunit                = ?
          AND a.loc                    = b.loc
          AND a.dmdunit                = b.ITEM
          GROUP BY a.loc
                  , a.dmdunit
                  , b.p_dlrnet
                  , b.p_dlrsplrcode"

      [download]
[reply]
[d/l]
        Maybe it's the difference between the libraries that DBD::Oracle was compiled against vs. the libraries that TOAD was compiled against (I assume you did not compile TOAD anyway)? Someone said there was a 30 character limit, but even that long table name is not 30 characters long. I would start by pasting that SQL as is into a DBI prepare statement and see if it works. Then I might edit the SQL to see if I can narrow down the problem (e.g. does a 'select * from that_long_table_name' work?).

        Update:(I also might try calling Oracle tech support, though I don't know if they support perl...they didn't when I last did Oracle...you might have to come up with an OCI example to submit if it comes to that).

        Good luck.
[reply]

        You mean you have a table with columns mth_01, mth_02, ... mth_12? Looks like a perfect example of an incorrectly designed table to me. Let's suppose the pac.historyreportmonthlybysku had columns id,loc,dmdunit (primary key is the id) and there was a table pac.historyreportmonthlybyskuval with columns id,mth,qty (where mth is the number of month and qty is the value for that month, primary key is (id,mth)): 

        SELECT GREATEST(
     ROUND(SELECT SUM(qty) FROM pac.historyreportmonthlybyskuval as hv
+ WHERE hv.id = h.id),
     ROUND(SELECT SUM(qty) FROM pac.historyreportmonthlybyskuval as hv
+ WHERE hv.id = h.id and mth >= ? and mth <= 11)
    ) QTY,
    b.p_dlrnet, b.p_dlrsplrcode   
  FROM pac.historyreportmonthlybysku h -- at least use meaningfull let
+ters!
  JOIN stsc.sku ON sku.loc = h.loc and sku.ITEM = h.dmdunit
 WHERE a.loc     = ?
   AND a.dmdunit = ?
 GROUP BY a.loc, a.dmdunit, b.p_dlrnet, b.p_dlrsplrcode

        [download]
        No need for dynamic SQL. So you are more likely to be able to reuse the query plan instead of forcing Oracle to recompile and reoptimize your query every time.

        {REM}I think I'll go crazy when I finally move to (name snipped to protect the quilty) with their 8500 tables in the database. A separate table for the usernames&passwords of each client ... oh my! And the butt that deserves to be kicked is long gone.{/REM}

        Jenda
        Support Denmark!
        Defend the free world!

[reply]
[d/l]
        Can anyone please help me with this?
[reply]
Re: Help with Dynamic SQL subroutine
by jesuashok (Curate) on Nov 22, 2006 at 01:53 UTC
    Hi sasrs99

    You have not mentioned what version of Oracle you are using. The solution which I can suggest is Instead of prepare try with "do" or upgrade Oracle 10.0 or to Oracle 9.2.0.2

    "Keep pouring your ideas"
[reply]

Back to Seekers of Perl Wisdom