microsoft

Grid Control Job to Excel Worksheet

Often times I’m asked to run a report that requires querying multiple databases. I suppose I could just setup DB Links and use a UNION ALL but why, when I have Grid Control?

First thing I do is setup a SQL Script job in Grid Control that looks like this. In addition to the user information, this query will get the DB Name so I know the user-DB relationship.

WHENEVER SQLERROR EXIT FAILURE;
set head off
set lines 210
column output format a140
select (select name from v$database)||’,’||username||’,’||account_status||’,’||created||’,’||profile as output from dba_users;

Make sure you run this job as SYSDBA so you have the correct privileges to query dba_users

Now that you have the results you can query them from SYSMAN’s table…

select output from MGMT$JOB_STEP_HISTORY where job_name = ‘TEST111′;

Paste that output into Excel and you’ll end up with something like this:

Now use the Text to Columns function in Excel to migrate the data to individual columns.

The final output!

This is a great way to produce quick reports that span multiple databases.

Preventing MS Word from formatting your code…

This is probably a problem that was solved years ago but I figured it out recently. Hopefully it helps someone!

The Problem

When I’m in Word and I’m creating or modifying documentation that involves code, Word wants to automatically replace certain characters with characters it thinks looks better.

Often times, I don’t notice it did that until I go to use the code. Here you’ll see the “smart quotes” ( ` ) that Word automatically replaced my straight quote ( ‘ ) with.


SQL> select job_id, job_name, job_owner, job_status
from mgmt_job
where job_status != 0
and job_name like ‘%LEVEL%0%’
order by job_name

It doesn’t look like much of difference, but it shows up in SSH as a period ( . ) and then errors.


SQL> select job_id, job_name, job_owner, job_status
from mgmt_job
where job_status != 0
and job_name like .%LEVEL%0%.
order by job_name
/
and job_name like .%LEVEL%0%.
*
ERROR at line 4:
ORA-00936: missing expression

The Fix

When you set options on the AutoFormat As You Type tab (Tools menu, AutoCorrect Options command), Word can automatically format text as you’re typing your document. For example, if you type a number followed by a period or hyphen, followed by a space or tab, followed by text, Word makes the text a numbered list.

When Word applies automatic formatting, you can use the AutoCorrect Options button to undo the action or change AutoFormat settings. Depending on the situation, the entire button may appear or the button may first appear as a small, blue box that changes to a button icon when you point to it.

I unchecked the first option, straight quotes vs. smart quotes as well as the hyphens and the bold/italic options.

If the automatic borders, tables or bullet lists annoy you there are options there to fix them as well