Link to home
Start Free TrialLog in
Avatar of jknj72
jknj72

asked on

Run Job in SQL Developer

I have a connection where I wrote a package that has procedures and functions all in the package. I have an Admin connection to the same server that I use to schedule the job with a different schema.

Schema - Admin
Create Schedule to kick off job(PCOV_CR_EXTRACT) that references the Schema and package.Procedure(See below)

Schema - Service
Has the package with the procedures I want to run.

Script I am running from job
BEGIN
    DBMS_SCHEDULER.create_job(
        job_name         => 'PCOV_CR_EXTRACT',
        job_type         => 'PLSQL_BLOCK',
        job_action       => q'[
DECLARE
    p_Successful NUMBER;
    p_SuccessfulNames NUMBER;
    p_successfulCancRein1 NUMBER;
    p_successfulCancRein2 NUMBER;
   
BEGIN
SERVICE.WEBSEARCH_INTERFACE_PKG.BuildPCOV_POLICY_Table(p_Successful);

    IF p_Successful > 0
    THEN
        SERVICE.WEBSEARCH_INTERFACE_PKG.BuildPCOV_NAMES_table(p_SuccessfulNames);

        IF p_SuccessfulNames > 0
        THEN
            SERVICE.WEBSEARCH_INTERFACE_PKG.BuildCancReinExtract(p_successfulCancRein1);

            IF p_successfulCancRein1 > 0
            THEN
                SERVICE.WEBSEARCH_INTERFACE_PKG.BuildPCOV_CANCREIN_table(p_successfulCancRein2);

                IF p_successfulCancRein2 > 0
                THEN
                   
                END IF;
            END IF;
        END IF;
    END IF;
END;
]',
        start_date       => TRUNC(SYSDATE) + 8 / 24,
        repeat_interval  => 'FREQ=daily;BYHOUR=8;BYMINUTE=0;BYSECOND=0',
        enabled          => TRUE,
        comments         => 'Your daily 8am job.');
END;

This doesnt run nor can I see it in any report trying to see what the problem was? Any clues?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You might also check:
USER_SCHEDULER_JOB_RUN_DETAILS

There is an ERROR# column that should provide the error number if the job ran.

This links is for the ALL_ level view but it has the column names.
http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2045.htm#REFRN20386
Avatar of jknj72

ASKER

The first query returned
08/12/2015 07:03        -  08/12/2015 19:00

The USER_SCHEDULER_JOB_RUN_DETAILS didnt return anything?
>>The first query returned

Something doesn't seem right.  You told it 8AM and the next time is 7PM?

Look at some of the other columns in the jobs table:
run_count, failure_count, last_run_duration.

>>The USER_SCHEDULER_JOB_RUN_DETAILS didn't return anything?

Maybe it is still running?  I'm not sure exactly when that view is populated.
Avatar of jknj72

ASKER

I have been playing around with the schedule to try and get it to run? I checked the properties of the job and I dont think it was logging all the possible values...I have attached the first tab of my job. If you want to see more let me know
Job-setup.jpg
Avatar of jknj72

ASKER

Notice the job and schedule have the schema OAR_Admin and the OAR_Service is where the package and procedures are...
The picture doesn't help me.  I'm not a GUI person.

What about the time differences?
What about the other columns in the view?

Is the code you posted what you submitted?  You didn't comment on the syntax issue.
Avatar of jknj72

ASKER

I changed the Type of Job being run to Stored Procedure and select the schema and Procedure to start with but it doesnt work. I had the Type as PL/SQL and then put the script above but that didnt work either. I dont know which one Im supposed to use. I just need to know how to do this. I can start from the beginning if thats the easiest way.
I have a Admin connection where I want to schedule and run the package and its procedures from a Service schema. How can I do this?
Is the spfile parameter job_queue_processes set to a number > 0?  If not, set it to a number > 0.

Here is a very simple test case.  It won't show up in user_scehduler_jobs since it isn't set up to repeat.

Other than that, it should be pretty close to what you are trying:
drop table tab1 purge;
create table tab1(junk number);

create or replace procedure myproc(p_Successful out number) is
begin
	--to confirm it ran in addition to the views
	insert into tab1 values(1);
	commit;
	p_Successful := 1;
end;
/

show errors

--test it
var junk number;
exec myproc(:junk);
print junk;
select * from tab1;


exec dbms_scheduler.create_job (job_name => 'MY_TEST_JOB', -
	job_type => 'PLSQL_BLOCK', -
	job_action => ' declare junk number; begin myproc(junk);  end;', -
	number_of_arguments => 0, -
	enabled => TRUE, -
	comments => 'my test job ');

select run_count from user_scheduler_job_run_details where job_name='MY_TEST_JOB';
--should now have two rows
select * from tab1;

Open in new window

Avatar of jknj72

ASKER

I fixed that syntax and was able to get the job to run. Its running now. I think that was it..
Avatar of jknj72

ASKER

Thanks