Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Unanswered: Query analyzer and stored procedures

    Hi,

    After much reading, I seem to still have issues using stored procedures in a query analyzer.

    Goal--

    Using standard query analyzer (odbc/jdbc--e.g. dbvisualizer), I would like to run stored procedures --pass parameters and receive result sets directly in a to the sql query window--

    I'm able to compile queries with reference cursors, etc. I'm able to follow psoug's examples using sql plus. However if I seem to be unable to do this..

    Attached is the simplest query I could come up with

    CREATE OR REPLACE PROCEDURE MATT.TEST (TEST OUT varchar2)
    IS
    BEGIN
    TEST :='figuring it out';
    END TEST;


    Query analyzer->call test()

    returns (ora 06553: pls 306: wrong number or types of arguments in call to 'TEST'.

    I know this is because the stored procedure is returning a variable...but I'm not sure how to deal with this...

    in sql + , I can do
    > var x varchar2(50)
    >exec test(:x)
    >print x

    but how do I do this with purely db vis? Although sql server is not my favorite, I don't remember this issue with sql server.

    thanks for any assistance

    matt

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Query analyzer->call test()
    obviously cannot work as you are not passing a variable

    The standard way in Oracle would be:

    Code:
    declare
      result varchar2(100);
    begin
      test(result);
      dbms_output.put_line(result);
    end;
    /
    For this to work your query tool must support enabling the DBMS_OUTPUT package.

    If you want to call the procedure directly without the anonymous PL/SQL block you need to look into the manual of your query tool.
    Maybe you need to define some kind of placeholder for the out parameter or some kind of special command.

  3. #3
    Join Date
    Jun 2009
    Posts
    4

    Query Analyzer

    Thanks for the input.

    mh

  4. #4
    Join Date
    Jun 2009
    Posts
    4

    query analyzer

    can I post a result set through dbms....?

    MH

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by mattclay
    can I post a result set through dbms....?
    Your question does not make any sense.
    What do you mean with "post a result set"?
    Please give an example.

  6. #6
    Join Date
    Jun 2009
    Posts
    4

    query analyzer

    CREATE OR REPLACE PROCEDURE
    (name IN varchar2,result out types.cursor)
    AS
    CURSOR C1 IS
    SELECT * FROM TEMP WHERE TYPE=name;

    BEGIN
    open C1;
    fetch c1 into result;
    dbms_output.put(result)
    close C1;
    END

    Ideal situation is to return result set from complex query in analyzer.

    thanks

    Matt

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by mattclay
    Ideal situation is to return result set from complex query in analyzer.
    What "analyzer"?
    Query analyzer is a SQL Server product.


    What should be returned in your example?

    Can you turn on dbms_output in whatever you are using (you still haven told us, definitely not Query Analyzer as that is for SQL Server only)

    The example procedure is not valid syntax. Even after adding a name for the procedure it does not compile.

    What exactly are you trying to achieve?

    Are you trying to display the result of a REF CURSOR?

    What does the manual of whatever tool you are using say about that problem?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •