It's been a while since I've had to play with SQL*Plus (thank Baud) but I seem to recall that @ is used to execute SP scripts.

A short snippet of code would help clear up the matter. Are you modelling your work on the examples shown in DBD::Oracle? Does the errstr method provide any clues? Are you using a single-quoted or double-quoted string for the SQL? If the latter, you could be running into interpolation problems (it would treat @foo as the name of an array), so the SQL Oracle sees is not what you think it is. Running at higher trace levels will show you what Oracle receives.

Here is the snippet that i used to execute a query.

my $_connectOracle = sub {
    my $oracle_server = $_[0];
    my $sid = $_[1];
    my $user_id = $_[2];
    my $password = $_[3];

    my $db_type = "Oracle";
    my $dbh;

    eval
    {
        $dbh = DBI->connect ("dbi:$db_type:host=$oracle_server;sid=$si
+d",
                    $user_id,
                    $password,
                    {
                        PrintError => 0,
                        RaiseError => 1
                    }
                     );
    };

    if ($@)        # check if the connection has made
    {
        $error_no = $DBI::err;
        $error_msg = $DBI::errstr;
    }
    
    return $dbh;
    
};



sub executeQuery () {
    my $query_type = $_[1];
    my $query_desc = $_[2];
    my $oracle_server = $_[3];
    my $sid = $_[4];
    my $user_id = $_[5];
    my $password = $_[6];

    my $dbh;
    my $sth;                                  # Statement handle
    my $output_values;
    my @output_values;

    # Get the database handle with the given input parameter.
    
    $error_no = "";
    
    $dbh = &$_connectOracle($oracle_server, $sid, $user_id, $password)
+; 
    
    if ( ! $error_no ) {
        eval
        {
            if ($query_type eq "S") {
                $sth = $dbh->prepare($query_desc);

                $sth->execute();
            
                while ($output_values = $sth->fetchrow_hashref)
                {
                    push (@output_values, $output_values);
                }
            }
            else {
                $dbh->do("$query_desc");
            }
            
            
            $dbh->disconnect();
        };

        if ($@)
        {
                $error_no = $DBI::err;
                $error_msg = $DBI::errstr;
        }
        
        return @output_values;
    }
    else {
        print "\nCannot connect to database\n";
        print "because $error_no\n";
    }
}
[download]

what am doing here is when i call the execute sub-routine i build a string that will have the file contents (to create a proc. which is 1100 lines ). When i call this function am getting the oracle error 24344 which doesnot convey anything other than "the procedure created with compilation errors". But when i run the same procedure in the sql plus using @ am able to create the proc. without any error that ensures that the procedure is correct syntactially. Hope am clear.

Edit by castaway - added code tags

Wow, that is some pretty funky code, did you copy it out of a book?

A couple of things spring to mind immediately: in the sub pointed to by _connectOracle, you initiate a new database connection each time you call it, until the passed handle goes out of scope, at which point it will be forcibly destructed, instead of being gracefully torn down with the disconnect method. I would rearrange that as:

{
  my $dbh;
  END { defined $dbh and $dbh->disconnect }
  sub get_dbh {
    return $dbh if defined $dbh;
    # ... connect to Oracle once only
    $dbh = DBI->connect( ... );
    return $dbh;
  }
}
[download]

In the executeQuery routine you are fetching the passed parameters into variable, except that you are ignoring the first parameter! Array indices start from zero, so the first parameter passed to the routine is $_[0]. Were you aware of this?

Why do you have $query_desc in quotes? You don't need them...

Also, there are a large number of optimizations (both speed and readability) you can make to that code. I would suggest reading Programming Perl and the DBI docs to get some ideas. This looks like it's code written by someone more familiar with Java or C++ than Perl.

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose