[Dbix-class] running stored procedures

Roger Day c.roger.day at gmail.com
Thu Oct 20 15:59:24 GMT 2011


So it looks like the DBIx::Class code cannot run Oracle stored procedures in
the way proscribed in the documentation? I'm not too sure that the other
method described would work either ...

On Thu, Oct 20, 2011 at 4:42 PM, Roger Day <c.roger.day at gmail.com> wrote:

> Thank you for the tip.
>
> Colons or returns are invalid?
>
> DBI Exception: DBD::Oracle::db prepare_cached failed: ORA-00911: invalid
> character (DBD ERROR: error possibly near <*> indicator at char 40 in
> 'SELECT * FROM (
> BEGIN Clear_Event_Tables<*>;
>
> END;
>   ) me ') [for Statement "SELECT * FROM (
> BEGIN Clear_Event_Tables;
> END;
>   ) me "] at /usr/opt/perl5/lib64/site_perl/5.8.8/DBIx/Class/Schema.pm li=
ne
> 1078
>
> Running the above command in sqlplus ...
>
> <code>
> select * from ( BEGIN Clear_Event_Tables; End; ) me
>                                                             *
> </code>
>
> ERROR at line 1:
> ORA-00911: invalid character
>
> where the * comes under the ; if the formatting doesn't work out.
>
> I followed the instructions for running stored procedures/random SQL in t=
he
> DBIX::Class manual. Have I missed something?
>
> Regards
> Roger
>
>
> On Thu, Oct 20, 2011 at 2:12 PM, Jorge Gonzalez <jorge.gonzalez at daikon.es=
>wrote:
>
>>  I think the "<*>" is not introduced by DBIC, but rather is the way Orac=
le
>> uses to inform about the error location. At least it's that way in other
>> Oracle errors unrelated to this thread...
>>
>> When I have errors in Oracle queries, I always look for the <*> indicator
>> to see the error...
>>
>> El 20/10/11 14:28, Roger Day escribi=F3:
>>
>> It's DBIC (possibly, but not I thnik likely) that I think is creating the
>> superfluous <*>, as it is creating the wrapper around the original piece=
 of
>> SQL - which I give below as well.
>>
>> I'm trying to invoke a stored procedure with no arguments. I  just need =
to
>> poke it to try and stop the friendly software from being over helpful.
>>
>> On Thu, Oct 20, 2011 at 1:02 PM, Peter Rabbitson <rabbit+dbic at rabbit.us>=
wrote:
>>
>>>  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 ch=
ar
>>> 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.
>>>
>>>
>>> _______________________________________________
>>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>>> IRC: irc.perl.org#dbix-class
>>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>>> Searchable Archive:
>>> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>>>
>>
>>
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys=
.co.uk
>>
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive:
>> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20111020/e99=
19507/attachment.htm


More information about the DBIx-Class mailing list