PL/SQL error returning in OEM12c

From: John Jones <john.jones_at_duke.edu>
Date: Tue, 17 Mar 2015 18:54:14 +0000
Message-ID: <DM2PR05MB542B212BFDF2C7BD6292DD6EF030_at_DM2PR05MB542.namprd05.prod.outlook.com>

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-l

Received on Tue Mar 17 2015 - 19:54:14 CET