Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Execute Oracle Stored procedure using DBIx::ProcedureCall

by kalyanrajsista (Scribe)
on Dec 21, 2009 at 13:02 UTC ( #813695=perlquestion: print w/ replies, xml ) Need Help??
kalyanrajsista has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to execute an Oracle stored procedure from Perl using DBI using DBIx::ProcedureCall and I'm encountering this error.

More Info::

I've successfully run my procedure from SQL Developer. Is it OK to use this module and try with other module..

---------- Perl ---------- DBD::Oracle::st execute failed: ORA-06550: line 1, column 14: PLS-00222: no function with name 'ABC' exists in this scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicato +r at char 13 in 'begin :p1 := <*>abc; end;') [for Statement "begin ? +:= abc; end;" with ParamValues: :p1=undef] at C:/Perl/site/lib/DBIx/P +rocedureCall/Oracle.pm line 152, <CONFIG> line 78. Output completed (2 sec consumed) - Normal Termination

Can any body guide of how should I fix this problem or use any module to execute my stored procedures..

Comment on Execute Oracle Stored procedure using DBIx::ProcedureCall
Download Code
Re: Execute Oracle Stored procedure using DBIx::ProcedureCall
by almut (Canon) on Dec 21, 2009 at 13:40 UTC
    PLS-00222: no function with name 'ABC' exists in this scope

    It's a little hard to tell without seeing any of your code...   but maybe you need to prefix the package/application wherein the procedure is defined — such as myapp.ABC (which would translate to something like myapp::ABC(...) on the Perl side).  Just a guess, though.

Re: Execute Oracle Stored procedure using DBIx::ProcedureCall
by afoken (Parson) on Dec 21, 2009 at 20:49 UTC

    A generic hint whenever Oracle gets confused and throws ORA-xxxxx and PLS-xxxxx codes: Type the exact code including the ORA- or PLS-Prefix into Google and look at the first few results. They are often very helpful, even better than the huge load of Oracle's documentation.

    ORA-06550 means that Oracle has found a syntax error in some PL/SQL code, PLS-00222 means PL/SQL function not found. The common webpages recommend to check the spelling. A common mistake seems to be that there is a PL/SQL procedure with the given name, but no function (yes, they are different).

    It seems you ran into that trap: You talk about running your procedure from SQL Developer, but then instruct perl to invoke a function.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

      Here is the Oracle procedure I'm trying to execute...This procedure is printing the output onto the screen. Is there anyway that i can get the output inform of rows from any CPAN Module

      create or replace PROCEDURE abc AS BEGIN DECLARE Tname user_tables.table_name%TYPE; Tstatus user_tables.status%TYPE; CURSOR data_groups IS SELECT table_name, status FROM user_tables; BEGIN OPEN data_groups; FETCH data_groups INTO Tname, Tstatus; WHILE data_groups % FOUND LOOP DBMS_OUTPUT.PUT_LINE(Tname || ' ==> ' || Tstatus); FETCH data_groups INTO Tname, Tstatus; END LOOP; END; END abc;
        Here is the Oracle procedure I'm trying to execute [...] create or replace PROCEDURE abc AS [...]

        This is a PROCEDURE. In your first posting starting this thread, you tried to call it as a FUNCTION.

        A FUNCTION is something else than a PROCEDURE. Functions must return values, procedures can not return anything. Assigning (or comparing) "the result" of a procedure is impossible, as there is no result, and there can't be one.

        something:=someProcedure is invalid, Oracle told you that, and the Google results for "PLS-00222" would have told you that even more clearly. (And I told you that pretty clear, too.)

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
Re: Execute Oracle Stored procedure using DBIx::ProcedureCall
by herveus (Parson) on Dec 22, 2009 at 12:36 UTC
    Howdy!

    It appears that you are attempting to execute the PL/SQL block:

    BEGIN ? := abc; END;

    That is attempting to call the function abc, not the proceudre abc, hence your error message. Try changing the PL/SQL to:

    BEGIN abc; END;

    yours,
    Michael
Re: Execute Oracle Stored procedure using DBIx::ProcedureCall
by Thilosophy (Curate) on Mar 01, 2011 at 03:06 UTC
    DBIx::ProcedureCall needs to know if you are about to call a function or a procedure (because the SQL is different). You have to make sure you call the wrapper subroutines in the right context:

    You have to call procedures in void context.

    You have to call functions in non-void context.

    If you do not want to rely on this mechanism, you can declare the correct type using the attributes :procedure and :function:

    use DBIx::ProcedureCall qw[ sysdate:function dbms_random.initialize:procedure ];
    If you use these attributes, the calling context will be ignored and the call will be dispatched according to your declaration.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://813695]
Approved by AnomalousMonk
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (9)
As of 2014-09-15 10:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (146 votes), past polls