to_char(function, 'DD-MM'YYYY') giving strange errors

[Ramnarayan]

Hi,

I have written the below code snippet which selects the value of date from a table. Now it is working fine. But when I give to_char function, it is giving uninitialized value. Can someone help me here why PERL is not processing when Oracle works fine!

HEre is the code:

my $sth = do_select($dbh,
"select to_char(PUB_DATE, 'DD-MM-YYYY') " .
"from iz_article where article_id=? and batch_no=?", $art_id, '008Ma');
while (defined (my $r = $sth->fetchrow_hashref()))
{
my $pub_date = $r->{$c_pub_date};
print "~~~$pub_date~~~\n";
}


The error given out is "Use of uninitialized value in concatenation(.) or string at a.pl line no 8.
~~~~~~

However if the above code is run without the to_char function, it works fine as below:


my $sth = do_select($dbh,
"select PUB_DATE " .
"from iz_article where article_id=? and batch_no=?", $art_id, '008Ma');
while (defined (my $r = $sth->fetchrow_hashref()))
{
my $pub_date = $r->{$c_pub_date};
print "~~~$pub_date~~~\n";
}

OUTPUT: ~~~01-MAY-07~~~

 

[xmassey]

I'm not totally sure if you are just trying to print the date in the format xx-xx-xxxx. If you are then I just use sprintf

$adatetime = sprintf ('%02d/%02d/%4d <b>(at</b> %02d:%02d:%02d<b>)</b>', $mday, $mon+1, $year+1900, $hour, $min, $sec);

which gives i.e...

01/01/07 (at 01:40)

Sorry, if this isn't what you meant

 

[KevinADC]

to_char() is not a perl function.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[MikeLacey]

Ramnarayan - have you tested your SQL statement, using the to_char() function, using SQL/Plus?

Mike

When working on any project the value of other people is exactly that - they are other people, with views that don't necessarily match yours. This mismatch, between their views and the view you've been contentedly assuming is right, is where that value lies.

 

[Ramnarayan]

Yes, I checked the sql statement in Oracle sql plus and it works fine. ONly problem is through perl!.

 

[KevinADC]

most likely $sth is not getting defined when you do this:

my $sth = do_select($dbh,
        "select to_char(PUB_DATE, 'DD-MM-YYYY') " .
        "from iz_article where article_id=? and batch_no=?", $art_id, '008Ma');

But I don't now why. You can read the DBI module documentation and try and add in some error checking to your code.



------------------------------------------
- Kevin, perl coder unexceptional!