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?
jknj72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Check when it last ran:
select to_char(last_start_date,'MM/DD/YYYY HH24:MI'), to_char(next_run_date,'MM/DD/YYYY HH24:MI') from user_scheduler_jobs where job_name='PCOV_CR_EXTRACT';


If that is the actual code, I would expect it to error at compile time.  You cannot have an empty IF statement.

begin
	dbms_output.put_line('Hello');
	if 1=1 then

	end if;
	dbms_output.put_line('World');
end;
/

ERROR at line 5:
ORA-06550: line 5, column 2:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge




begin
	dbms_output.put_line('Hello');
	if 1=1 then
		null;
	end if;
	dbms_output.put_line('World');
end;
/

Hello
World

PL/SQL procedure successfully completed.

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
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
jknj72Author Commented:
The first query returned
08/12/2015 07:03        -  08/12/2015 19:00

The USER_SCHEDULER_JOB_RUN_DETAILS didnt return anything?
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

slightwv (䄆 Netminder) Commented:
>>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.
jknj72Author Commented:
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
jknj72Author Commented:
Notice the job and schedule have the schema OAR_Admin and the OAR_Service is where the package and procedures are...
slightwv (䄆 Netminder) Commented:
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.
jknj72Author Commented:
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?
slightwv (䄆 Netminder) Commented:
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

jknj72Author Commented:
I fixed that syntax and was able to get the job to run. Its running now. I think that was it..
jknj72Author Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.