How to run a package in SQL Developer

I actually have 3 packages and I want them run in a certain sequence. Ive never done this before so Im looking for a little guidance. Lets say I have pkg1, pkg2 and pkg3. I have 2 procedures in pkg1 that I need run in a specific order(proc1 and then proc2) and when that package is complete I want to run the next 2 packages that only have 1 procedure each. This is all being done in SQL Developer..Any help would be appreciated...

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

johnsoneSenior Oracle DBACommented:
You cannot run a package.  You have to run the procedures in the package.  I don't use SQL Developer, but I would guess it takes the same syntax as SQL*Plus:

exec pkg1.proc1;
exec pkg1.proc2;
exec pkg1.proc3;
exec pkg2.proc1;
exec pkg3.proc1;

If it doesn't take EXEC, then:

begin pkg1.proc1; end;
/
begin pkg1.proc2; end;
/
begin pkg1.proc3; end;
/
begin pkg2.proc1; end;
/
begin pkg3.proc1; end;
/

Or

begin
  pkg1.proc1;
  pkg1.proc2;
  pkg1.proc3;
  pkg2.proc1;
  pkg3.proc1;
end;
/
0
jknj72Author Commented:
ok makes sense but where would I be making these calls from. We have to put this on a schedule but that's not my concern yet. I just need to know where I would run the code you suggested? Also, I have an out parameter for each procedure. Would I be able to check each out parameter before running the next proc?
0
sdstuberCommented:
something like this?
change the variable name and type to whatever you need and of course change the test conditions to whatever is appropriate

put it in the sql window and press F5

DECLARE
    v_result NUMBER;
BEGIN
    pkg1.proc1(v_result);

    IF v_result > 0
    THEN
        pkg1.proc2(v_result);

        IF v_result = 234423
        THEN
            pkg1.proc3(v_result);

            IF v_result < 10
            THEN
                pkg2.proc1(v_result);

                IF v_result = 0
                THEN
                    pkg3.proc1(v_result);
                END IF;
            END IF;
        END IF;
    END IF;
END;
/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jknj72Author Commented:
Ok that makes sense but would this have to be manually run everyday or could I take this code and place it in a job or something that can be scheduled to run everyday?
0
johnsoneSenior Oracle DBACommented:
You can schedule it to run with DBMS_SCHEDULER.  Doc is here -> http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN034
0
sdstuberCommented:
to run the above block every day at 8am might look something like this...


BEGIN
    DBMS_SCHEDULER.create_job(
        job_name         => 'JKNJ72_DAILY',
        job_type         => 'PLSQL_BLOCK',
        job_action       => q'[
DECLARE
    v_result NUMBER;
BEGIN
    pkg1.proc1(v_result);

    IF v_result > 0
    THEN
        pkg1.proc2(v_result);

        IF v_result = 234423
        THEN
            pkg1.proc3(v_result);

            IF v_result < 10
            THEN
                pkg2.proc1(v_result);

                IF v_result = 0
                THEN
                    pkg3.proc1(v_result);
                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;

Open in new window

0

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
jknj72Author Commented:
Thank you both...
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.