Oracle SQL Developer- Basic Select query using Job Wizard PL/SQL

Question

I've been trying to get SQL Developer to run a query automatically on a regular basis (on the sample HR database). Based on my research, I've determined that the best alternative for me would be to use the Job Wizard & PL/SQL (where it calls DBMS_SCHEDULER?).

I created a 'Schedule' that repeats every 5 minutes called: every_5mins

Here is what I have in the 'Job Details' section thus far: Job Name: Select_Employees

Job Class: SYS.DEFAULT_JOB_CLASS

Type of Job: PL/SQL block

When to Execute Job: Schedule

Schedule: SYSTEM.EVERY_5MINS

PL/SQL

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno IN HR.FIRST_NAME,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT FIRST_NAME,
LAST_NAME
FROM
HR.EMPLOYEES
WHERE FIRST_NAME=p_recordset
END;
/

it returns an error: "Encountered the symbol "/" when expecting one of the following: (begin case declare)..." and the rest is cut off the screen

Am I on the right track? What is the proper way to write it so the query is run every 5 mins? I have never used Oracle SQL Developer before.

Thanks in advance.

Answer 1

I have a new error: line 1, column 750 PLS-00103: Encountered the symbol "CREATE"

The PL/SQL payload is the code we want our job to execute. So it seems you are trying to schedule the creation of stored procedure every five minutes. The scheduler is expecting an executable PL/SQL call, not DDL.

So what you need to to is create the stored procedure first, then fire up the Job wizard. The PL/SQL block should be something like this:

declare
     rc sys_refcursor;
begin
     get_emp_rs ('MAYZIE', rc);
end;

This points us to the underlying problem with your scenario: jobs run in the background. There's no way for a job to accept user input or display output. So while your job will run every five minutes you'll never see the result set. You need a procedure which writes results somewhere (a table, a file) which can be read and displayed by some other program.