SQLPLUS HTML COLOR

Recently i was asked to generate a report that shows all the databases that were backed up via RMAN and they wanted a color code when a backup failed or wasn’t completed successfully.

That posed a challenge and took some research, but finally generated a report.   Below is a sample report and how to color code on afailed backup, but can be used on any sql report:


SET MARKUP HTML ON SPOOL ON
SET TERMOUT OFF
SET PAGESIZE 1000
SET LINESIZE 300
SET TRIMOUT ON
SET TERMOUT ON
set pages 999
set feedback off
break on db_name page
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
spool backup_report
SET MARKUP HTML ENTMAP OFF
PROMPT <H2><center><b> RMAN Backup  details  </b></center></H2>
SET MARKUP HTML ENTMAP ON
SET VERIFY    off
SET lines 132 pages 9999 feedback off
COLUMN start_time      format date              heading ‘Start Date’
COLUMN end_time      format date              heading ‘End Date’
COLUMN input_bytes format 999,999,999,999 heading ‘Input Bytes’
COLUMN output_bytes        format 999,999,999,999 heading ‘Output Bytes’
COLUMN cstatus format a40  heading ‘Status’
COLUMN object_type              format a10  heading ‘Backup Type’
COLUMN time_taken_display      format a40  heading ‘Time Taken’
COLUMN cstatus ENTMAP OFF
select db_name, object_type,start_time,end_time,input_bytes, output_bytes,
(case
when status like ‘%WARN%’ then ‘<font color=”green”>COMPLETED</font>’   <==== Note color default for non-failed jobs
when status like ‘COMPLETED’ then ‘<font color=”green”>COMPLETED</font>’
ELSE ‘ <font color=”red”>’||status||'</font>’                                                             <=== Note – falls through code and any noncompleted status uses this color
END) cstatus
from <RMAN CATALOG>.rc_rman_status
where object_type = ‘DB FULL’
and operation not in (‘RESTORE VALIDATE’)
order by db_name,start_time Asc;
SET MARKUP HTML ENTMAP OFF
spool off

Hope this helps with your reports and can be used for any type of report that requires color coding.

–Mike

Advertisements

GLOC16 is here !!

This year there are more Oracle ACES presenting than ever before at the Great Lakes Oracle Users Conference. Check out the Schedule at a Glance and see which sessions you want to attend. Oracle Aces include Susan Behn, Karen Brownfield, Michael Gangler, Gustavo Gonzalez, Tim Gorman, Janis Griffin, Kyle Hailey, Francis Mignault, Daniel Morgan, Rich Niemiec, Kerry Osborne, Elke Phelps, Tanel Poder, Kellyn Pot’Vin-Gorman, Carlos Sierra, Scott Spendolini, Deanna Sunde, Mark Bobak, Ric Van Dyke, and Tim Vlamis.

I will be speaking on May 19th from 4 – 5:00pm on “Using RPM’s to distribute OEM Agents”
https://www.neooug.org/gloc/agenda.aspx

TO Register:

https://www.neooug.org/gloc/registration.aspx

 

US Pro Rugby – premiers this weekend – AOL.com

Saturdays game was good to see, closest team for us in Midwest is Ohio…  

After an exciting opening week, PRO Rugby continues its debut season when Denver looks to win its second straight while taking on San Francisco. The much-anticipated showdown will be streamed live on AOL.com Sunday at 6 p.m. ET.

Fans can also enjoy a replay of Saturday’s game between San Diego and Sacramento.

PRO Rugby — the first team-sport professional league to launch in the United States since Major League Soccer in 1993 and Major League Lacrosse in 2001 — officially kicked off last Sunday, as Denver got by Ohio, 19-13, in overtime and Sacramento edged San Francisco 37-25 in a rivalry match.

For full rosters of each team, CLICK HERE.

 

Granting Oracle Schema Permissions (Objects not created yet) – Part 2

After presenting at Collaborate 16 about granting schema permissions, a colleague – Frank Pound send me the following that can also be used to perform the trigger only on “Tables”:

https://community.oracle.com/thread/402826?start=0&tstart=0

create or replace trigger trigger_grant_dml

after create on schema
declare
v_job number;
v_todo varchar2(200);
begin
     if ora_sysevent = 'CREATE' and ora_dict_obj_type = 'TABLE' then
     v_todo:='execute immediate ''grant select, insert, update, delete on '||ora_dict_obj_name||' to my_new_role'';';
     dbms_job.submit(job=>v_job, what=>v_todo);
  end if;
exception
  when others then
  null;
end;
/

2016_1198_gangler_ppt

Please contact me with any questions - mjgangler@gmail.com

Collaborate 16 !

I will be presenting 3 presentations at this years Collaborate 16 in Las Vegas.

Monday – April 11 – 12 – 12:30 – Palm B – Using Puppet and RPM’s to deploy EM12C Agents

Wednesday – April 13 – – 12:00 – 12:30 – Jasmine B – Granting Oracle Schema Permissions When Objects not Created Yet !

Thursday – April 14 – 8:30 am – 9:30 am – Palm  D – Optimize and Simplify Oracle 12C RAC using dNFS, ZFS and OISP

2016_1197_gangler_pdf

 

2016_1198_gangler_ppt

 

2016_1201_gangler_ppt

MOUS15 is here !!!

If you are in the Indiana, Michigan, Illinois, Ohio or Canada region and want to see and experience great Oracle user and technical presentations, networking and hands-on workshops, please register to the Michigan Oracle User Summit (#MOUS15), on Wednesday, November 18th at Schoolcraft college in Livonia, Michigan (www.mous.us).    Its a full day of presentations (45) that include Big Data presentations, Hyperion presentations, JDEdwards, Oracle Applications, Middleware (SOA), Database (Oracle12c, RAC, others) and 2 hands-on workshops – #cloneattack and #racattack.   We also have 13 Oracle Ace and Ace directors, Rich Niemiec of Rolta will be our keynote speaker.   Please visit our website (www.mous.us) or guidebook for more details on the conference content.

So not only do you get some great user and technical presentations, but you get 2 great hands-on workshops – Clone Attack and Racattack (www.racattack.org).

Clone attack (www.delphix.com ) – did you ever want to create development databases from production with a small footprint and dynamic way ?   Well come to MOUS15 and create many development databases on your own PC.

RACATTACK – (www.racattack.org) – Racattack allows you to create a 12c RAC database on your own PC, yes, you too can have your own RAC database on your pc.

Wait there is more !! – WE ARE ON GUIDEBOOK – http://www.guidebook.com (Look for MOUS) its free !!

Lastly, this conference is a great way to network with other Oracle users, a full day of presentations, hands-on workshops, great food , education and its priced reasonably ($50 pre-register, $75 at door).

Hope to see you at the conference and please contact me with any questions,

--Mike Gangler
mjgangler@yahoo.com