Re: PL/SQL error returning in OEM12c

Are you trying to submit this as a job in the em12c job system? Or something else? If you are trying to submit a job, and you want to use dbms_output then you'll need to use a sql script job type and set serveroutput on before submitting the job. The actual output shows in the job output in the EM console (you probably have to click the view full log link).

However if you are trying to confirm compliance with some policy I think there are a few improvements you could make. First you could convert your script into a metric extension to allow continous monitoring, and if necessary alerting, on the number of OPS$ users (there's a couple of video guides at
https://apexapps.oracle.com/pls/apex/f?p=44785:24:116934217700806:::24:P24_CONTENT_ID%2CP24_PREV_PAGE:5741%2C24#prettyPhoto/0/ for this). Second and much more important I'm rather surprised at the check being a simple count. I'm fairly certain you don't want OPS$NIALL,OPS$OBAMA, OPS$NSA and so on in your list! Finally your check won't produce any output if the count is > 7 a situation I suspect you are also interested in.

On Tue, Mar 17, 2015 at 6:54 PM, John Jones <john.jones_at_duke.edu> wrote:

> I have been away from PL/SQl programming for quite some time and have
> been bumping my head about the following problem with PL/SQL and OEM 12c.
>
>
>
> I want to submit a PL/SQL program to perform the following in over 100
> databases and do not want to scan each output for errors.
>
>
>
> I am writing code that will select the number of users that have an OPS$
> account. There should be 7 in each database, so I wrote a SQL Script that
> just select to count(*) of records into a variable and then check the
> results of that. If count(*) = 7 then everything is ok and I don’t need to
> know anymore. If the count(*) < 7 then I want the program to error out and
> return “something” so that I know which databases to check.
>
>
>
> declare
>
> holder number;
>
> begin
>
> select count(*) into holder from all_users where username like 'OPS$%';
>
> if holder = 7 then
>
> dbms_output.put_line('the count is '||holder);
>
> elsif
>
> holder < 7 then
>
> raise_application_error(-20010,'Users do not exists');
>
> end if;
>
> end;
>
>
>
> If I run this from SQL*Plus, I get an error and it prints users do not
> exists, but if I run this in OEM 12c, it tell me the job completed. Also I
> am getting no ouput on any of the jobs.
>
>
>
> Ideas?????
>
>
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l