TOAD, SQL Editor, and EXEC'ing a Stored Procedure?

actually, i take that back..  your syntax should be correct if you just execute as script from within toad.

good luck,
daniels

as long as your ref cursor is properly pre-defined it looks ok.

thx,
dan

but somehow you need to initialize the ref cursor; that is why it's asking for param values.

good luck,
daniels

well,
I think you are executing a procedure that is defined in a package. And "currefcursor" is a type defined in the package. You cannot invoke the procedure from the sql editor the way you are trying here.

the call should be like

declare
p1 VARCHAR2 := 'some text';
p2 <package_name>.currefcursor;
begin
 <package_name>.get_stuff(p1, p2);
end;
/

For dan, running your suggestion as a script gives me this:

    Bind variable "p1" not declared.
    Bind variable "p2" not declared.
    ORA-01008: not all variables bound

For MohanKNair, running your suggestion as a script gives me this:

    Bind variable "p2" not declared.
    PL/SQL procedure successfully completed.
    ORA-01008: not all variables bound

For sujith80, running your suggestion as a script gives me this:

    ORA-06550: line 2, column 4:
    PLS-00215: String length constraints must be in range (1 .. 32767)
    ORA-06550: line 3, column 4:
    PLS-00201: identifier 'PKG_SOME_PKG.CURREFCURSOR' must be declared
    ORA-06550: line 3, column 4:
    PL/SQL: Item ignored
    ORA-06550: line 5, column 38:
    PLS-00320: the declaration of the type of this expression is incomplete or malformed
    ORA-06550: line 5, column 3:
    PL/SQL: Statement ignored

You're right that I'm using a package.  So my code looked like this when I ran your suggestion:

    declare
    p1 VARCHAR2 := 'some text';
    p2 PKG_SOME_PKG.currefcursor;
    begin
     PKG_SOME_PKG.get_stuff(p1, p2);
    end;

I tried adding a length to VARCHAR2 like VARCHAR2(200), which fixed the length constraints part of the error, so it became this:

    ORA-06550: line 3, column 4:
    PLS-00201: identifier 'PKG_SOME_PKG.CURREFCURSOR' must be declared
    ORA-06550: line 3, column 4:
    PL/SQL: Item ignored
    ORA-06550: line 5, column 38:
    PLS-00320: the declaration of the type of this expression is incomplete or malformed
    ORA-06550: line 5, column 3:
    PL/SQL: Statement ignored


For all:  I may or may not try more over the weekend, so please be patient if I don't get back with you for a couple of days.  Please do continue to assist, though - I appreciate your help!

post up the pkg code as well as the get_stuff code; i'll play around with it.

thx,
daniels

Here:
    declare
    p1 VARCHAR2 := 'some text';
    p2 PKG_SOME_PKG.currefcursor;
    begin
     PKG_SOME_PKG.get_stuff(p1, p2);
    end;

You should know where "currefcursor" is defined. It is not a standard declaration. It must be defined in one of your packages.
Otherwise if you just want to test your code you can change the above declaraion as :

    declare
    p1 VARCHAR2(200) := 'some text';
    p2 sys_refcursor;
    begin
     PKG_SOME_PKG.get_stuff(p1, p2);
    end;

Here's a summary of the procedure code within the package:

   PROCEDURE get_stuff (
      p1_parm   IN       VARCHAR2,
      p2_parm   OUT      currefcursor) IS
   BEGIN
      OPEN p2_parm FOR
         SELECT   ...
         .
         .  (uses p1_parm for a value in the WHERE clause)
         .
   END get_stuff;

I didn't write the procedure - it's been around awhile, being used by some applications.  It seems to work fine for the applications.  I want to see the data that should be returned by the procedure, so I can understand its relationship to the applications that use it.  There is a view in the FROM that I don't have access to, but I would expect a different error message if it was failing because of that.  From the messages I'm getting, Toad (or Oracle, actually) seems just to dislike my syntax.  Aargh!

'currefcursor' is used in many of the procedures included in this package.  I don't see it defined anywhere, though.

And, sujith80, I tried both of your suggestions earlier in this thread - see my response from 10/14, 4:46pm, above, for the results...

Thanks!

Oh, and another note:  even if I just try (either with 'Execute statement' or 'Execute as script') -

EXEC PKG_SOME_PKG.get_stuff('some text', NULL);


I get:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'PKG_SOME_PKG.get_stuff' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

???

From the two 'desc' statements, got this:

    Error: object currefcursor does not exist

    Error: object PKG_SOME_PKG does not exist

Is this a security issue?  Even though the application can run the sp, perhaps I can't manually run it via TOAD?

I didn't think so - I'm taking over support of this package since the person who wrote it has left the company.  I'm using a team id to try to access it.  However, the procedure I'm trying to run references a view that I don't have access to.  I thought if I ran the procedure from the package via TOAD, it would have access and be able to return data.  I guess not.

I'm thinking the applications that use it probably utilize a special user id that gives them rights to run the procedures in the package.  I'm new to stored procedures, obviously.  From my programming background, I know this kind of thing is done on servers for applications, but I didn't know it could be done for databases (and, I'm assuming this is the case).  Does that make sense?

Thanks for working on this with me, you all!

I've closed the question and will work with our DBA to ensure my accesses are correct.  You've shown some good examples of how to run a package and its procedures, so this might be helpful to someone else as well.

Mr: lisfolks

I dont understand how my answer becomes an "assisted answer",
Thats what exactly I asked you to check by the desc command, whether the package you are using is existing in your scope.