RE: calling a stored procedure from a sitescope monitor

Chris,

You could try "exec" but I'm still not sure what your procedure is going to show. If it has no parameters then it can't "return" a value as such. Maybe it shows a value on the screen using dbms_output? If so, I would doubt this will work over JDBC.

What should your procedure do / show?

Bruce Reardon

-----Original Message-----
Sent: Tuesday, 21 May 2002 16:43

Sitescope is monitoring software produced by Freshwater software. It has many different types of templates for monitors for OS, web servers, databases, etc.

You're right that I could probably do what I want here with a function and not a procedure. This is really a prototype for more complicated things I want to do, so I made it a procedure. But this one is really just a wrapper for a function that does all the work.

If I say desc foo in sqlplus, it returns

PROCEDURE foo

and that is it.

The Freshwater support guy I talked to suggested that I try

call foo

and that did not work, but I just noticed that it doesn't work in sqlplus either -- but call foo() does. Tried to redo the monitor to use that but it didn't work there either. Gives an ora-900 invalid sql error.

Let's say bar is the one function in procedure foo. I actually can just say

select bar from dual

in the monitor, and it runs, but I want to keep at it until I can call procedures too.

BTW, desc bar returns

FUNCTION bar RETURNS NUMBER

-Chris

>From: "Reardon, Bruce (CALBBAY)" <Bruce.Reardon_at_comalco.riotinto.com.au>
>To: <ORACLE-L_at_fatcity.com>
>CC: <aminotorami_at_hotmail.com>
>Subject: RE: calling a stored procedure from a sitescope monitor
>Date: Tue, 21 May 2002 14:42:45 +1000
>
>Chris,
>
>I don't know what Sitescope is and I haven't used JDBC but can perhaps
>offer a suggestion.
>
>You say foo doesn't have any arguments and that it returns a value.
>I take it that foo is actually a function then?
>
>From sqlplusw, what does "desc foo" show?
>
>Consider the following:
>SQL> create or replace function foo return varchar is
> 2 begin
> 3 return 'a';
> 4 end;
> 5 /
>
>Function created.
>
>SQL> desc foo
>FUNCTION foo RETURNS VARCHAR2
>
>SQL> select foo from dual;
>
>FOO
>-------------------------------------------------------
>a
>
>SQL>
>
>
>So, can you just do "select foo from dual" via JDBC?
>
>If I've misunderstood, what is sitescope and how would you execute your foo
>from SQLPlus?
>
>Regards,
>Bruce Reardon
>
>-----Original Message-----
>From: Chris McGrail [mailto:aminotorami_at_hotmail.com]
>Sent: Tuesday, 21 May 2002 14:33
>
>Has anyone done this? Let's say I have a procedure named foo and want to
>call it in a Sitescope monitor. There are no arguments to foo. I just want
>to call it and get the one value it returns. Freshwater doesn't have any
>doc
>for this and they've been sitting on my request for information for a week.
>
>They do have a document with an example for SQL Server but nothing for
>Oracle. If Oracle were like SQL Server, you'd just put the name of the
>procedure in the query line on a typical database monitor, but I tried that
>and it doesn't work.
>
>We're using the jdbc thin driver to a version 8.1.7.2 instance if it makes
>any difference.
>
>Thanks.
>
>-Chris

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).