Re: PL/SQL error returning in OEM12c
Date: Tue, 17 Mar 2015 18:33:23 -0400
Message-ID: <CAAaXtLAGS1WFGOryhHHubCN-nh+nf4VySxqZV9ekazqWpeosJg_at_mail.gmail.com>
Why not use UTL_SMTP to send you (or your pager mailing list, or your ticketing system, or ...) an e-mail for the databases where you find other-than-7 results?
Of course, you would then need to make sure that all 100 databases are properly configured to allow outgoing email. That might be a much bigger hassle, at least in the short-term.
By the way, it might be better to verify that each database has the *right* 7 OPS$ accounts, but that's for you to decide.
On Tue, Mar 17, 2015 at 2: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?????
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 17 2015 - 23:33:23 CET