[Dbix-class] running stored procedures

[Peter Rabbitson]

On Thu, Oct 20, 2011 at 11:01:25AM +0100, Roger Day wrote:
> Hi
> I'm trying to run stored procedures through DBIx::Class and I get this
> problem:
> 
> DBI Exception: DBD::Oracle::db prepare_cached failed: ORA-00907: missing
> right parenthesis (DBD ERROR: error possibly near <*> indicator at char 49
> in 'SELECT * FROM (
>    BEGIN
>    CreateTemporaryTables<*>();
>    END;
>   ) me ') [for Statement "SELECT * FROM (
>    BEGIN
>    CreateTemporaryTables();
>    END;
>   ) me "
> 
> Code is as follows:
> 
> package DSTI::CPP::Schema::Result::CreateTemporaryTables;
> use base qw/DBIx::Class::Core/;
> __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
> 
> __PACKAGE__->table('USER_TABLES');
> 
> __PACKAGE__->result_source_instance->is_virtual(1);
> 
> __PACKAGE__->result_source_instance->view_definition(q[
>    BEGIN
>    CreateTemporaryTables();
>    END;
>   ]);
> 1;
> __EOF__
> 
> and I call it thus:
> 
> $handle->resultset('CreateTemporaryTables')->all;
> 
> The procedure has no arguments to pass ... just what am I doing wrong?
> 

It looks like your stored proce call is malformed. Run the same sql via 
$schema->storage->dbh_do(sub { $_[1]->selectall_hashref($sql) }) to get DBIC
out of the picture. I suspect the error will be identical.