This topic contains examples of SQL jobs that run SQL statements against Oracle database tables.
Example: Add a Row to an Oracle Database Table
Suppose that you want a job to add a row of data to the emp table.
AGENT DB_AGENT USER scott DB_URL jdbc:oracle:thin:@myhost:1521:orcl SQL 'INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, + COMM, DEPTNO) VALUES(2476, ''robert'', ''sales'', 435, + ''01-OCT-2005'', 65000, 10, 75)'
Example: Update a Row to an Oracle Database Table
Suppose that you want a job to update a record in the emp table and change the salary to 75,000 for the employee with name robert.
AGENT DB_AGENT USER scott DB_URL jdbc:oracle:thin:@myhost:1521:orcl SQL 'UPDATE EMP SET SAL=75000 where ENAME=''robert'''
Example: Delete a Row from an Oracle Database Table
Suppose that you want a job to delete a row from the emp table for the employee with name robert.
AGENT DB_AGENT USER scott DB_URL jdbc:oracle:thin:@myhost:1521:orcl SQL 'DELETE FROM EMP WHERE ENAME=''robert'''
Example: Return Data from an Oracle Database Table that Match a Condition
Suppose that you want a job to query the emp table for names that have salaries greater than 40,000. If the query returns a name that begins with the letter d, the job completes.
AGENT DB_AGENT USER scott DB_URL jdbc:oracle:thin:@myhost:1521:orcl SQL 'SELECT ENAME FROM EMP WHERE SAL > 40000' JOB_CRITERIA ENAME=d.* OUTPUT_FILE /emp/salary.txt
For example, the salary.txt file contains the following output:
Output for: SELECT ENAME FROM EMP WHERE SAL > 40000 ENAME ----------- donald
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|