When including script names in OEM you must carefully include the fully-qualified path for the script: (i.e. /u01/app/oracle/scripts/myscript.sql). Placing custom scripts within OEM is a great way to improve reporting. Here is a quick overview of script execution from OEM:

Create a "new event" from the OEM console

Choose "enable unsolicited event" and then "test unsolicited event"

Enter the script name with the full path.

You can also use the oemevent command to invoke a SQL script from OEM:

After you succeed connecting Oracle with SQL Server probably you want to create database link between it. Oracle heterogeneous services allow you to define a database link between Oracle and SQL Server, as well as links to DB2 and other inferior databases.

Here are complete notes in creating a database link between Oracle and SQL Server.

1. Install Oracle ODBC drivers on the server (your local Oracle database) that will access the remote SQL Server database using the database link.2. Setup the ODBC connection on the local Oracle database using the Windows ODBC Data Source Administrator3. Test the ODBC drivers to ensure that connectivity is made to the SQL Server database.4. Ensure that your global_names parameter is set to False.5. Configure the Oracle Heterogeneous services by creating an initodbc.ora file within the Oracle database.

6. Modify the Listener.ora file.

SID_NAME is the DSN for the remote database. ORACLE_HOME is the actual Oracle home file path. PROGRAM tells Oracle to use heterogeneous services.

8. Reload the listener on local Oracle database9. Create a database link on the local Oracle installation that accesses the heterogeneous connection, which, in turn, connect to SQL Server.10. Run a SQL Server Select statement from the Oracle installation using the database link.

One of the confounding problems with Oracle DATE datatypes is the computation of elapsed time.

Oracle supports date arithmetic and you can make expressions like "date1 - date2" to get the difference between the two dates. Once you have the date difference, you can use simple techniques to express the difference in days, hours, minutes or seconds.

To get the values for data differences, you must choose you unit of measurement, and this is done with the data format mask:

It might be tempting to use sophisticated conversion functions to convert a data, but we will see that this is not the most elegant solution:

round(to_number(end-date-start_date))– elapsed days

round(to_number(end-date-start_date)*24)– elapsed hours

round(to_number(end-date-start_date)*1440)– elapsed minutes

How are elapsed time data displayed by default? To find out, we issue a simple SQL*plus query:

SQL> select sysdate-(sysdate-3) from dual;

SYSDATE-(SYSDATE-3)

-------------------

3

Here we see that elapsed times are expressed in days. Hence, we can use easy conversion functions to convert this to hours or minutes:

However, when the minutes are not a whole number, we have the problem of trailing decimal places:

select

(sysdate-(sysdate-3.111))*1440

from

dual;

(SYSDATE-(SYSDATE-3.111))*1440

------------------------------

4479.83333

Of course, we can overcome this with the ROUND function, remembering that we must first convert the DATE datatype to a NUMBER:

select

round(to_number(sysdate-(sysdate-3.111))*1440)

from

dual;

ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)

----------------------------------------------

4480

Hence, we can use these functions to convert an elapsed time into rounded elapsed minutes, and place the value inside an Oracle table. In this example, we have a logoff system-level trigger that computes the elapsed session time and places it inside a Oracle STATSPACK user_log extension table:

Oracle has a fascinating non-ANSI feature called an in-line view. The in-line view allows for a query to be placed into the FROM clause of an SQL statement, where you would normally place a table name.

By performing a SELECT in the FROM clause, we can summarize two columns, and display a third value based on two other columns.

In the example below, we take the employee name (ename) and the department number (deptno) and display the salary of the employee by their department.

prompt Display of Salary by Department

SELECT

*

FROM (SELECT

ename,

sum(decode(deptno,10,sal)) DEPT10,

sum(decode(deptno,20,sal)) DEPT20,

sum(decode(deptno,30,sal)) DEPT30,

sum(decode(deptno,40,sal)) DEPT40

FROM

emp

GROUP BY

ename)

ORDER BY 1;

Display of Salary by Department

ENAME DEPT10 DEPT20 DEPT30 DEPT40

---------- ---------- ---------- ---------- ----------

ADAMS 1100

ALLEN 1600

BLAKE 2850

CLARK 2450

FORD 3000

JAMES 950

JONES 2975

KING 5000

MARTIN 1250

MILLER 1300

SCOTT 3000

SMITH 800

TURNER 1500

WARD 1250

This type of query is very useful for Oracle data warehouse system where you must display information from a FACT table according to the values of two other columns.

The initialization part of a package is run just once, the first time you reference the package. So, in the last example, only one row is inserted into the database table emp_audit. Likewise, the variable number_hired is initialized only once.

Every time the procedure hire_employee is called, the variable number_hired is updated. However, the count kept by number_hired is session specific. That is, the count reflects the number of new employees processed by one user, not the number processed by all users.