Re: procedure & function inside packages

On Apr 18, 3:14 pm, Urs Metzger <u...@xxxxxxxxxxxxx> wrote:
chris schrieb:



Hi All,

I'm a new learner in pl/sql & trying to write a small basic pkg which
includes both functions & procedures.  could you take a look & tell me
where I'm doing wrong & how to fix it? Appreciate your helps/
suggestions.

CREATE OR REPLACE PACKAGE job_pkg IS
      g_jobid    VARCHAR2(50) := upper('dba');
      g_jobname  VARCHAR2(50)  := upper('app analyst ');
      PROCEDURE  add_job (g_jobid VARCHAR2, g_jobname VARCHAR2);
END job_pkg;
/

CREATE OR REPLACE PACKAGE BODY job_pkg IS
      FUNCTION get_job (jobid IN jobs.job_id%TYPE) RETURN boolean IS
      BEGIN
            SELECT job_id INTO jobid FROM jobs WHERE job_id = jobid;
            RETURN jobid;
      END get_job;

      PROCEDURE add_job
            (g_jobid   IN jobs.job_id%TYPE,
             g_jobname IN jobs.job_title%TYPE)
      IS
      BEGIN
            IF get_job(g_jobid) THEN
           DBMS_OUTPUT.PUT_LINE ('Job Id: ' || g_jobid || 'already exist in
DB.');
       ELSIF get_job(g_jobid) IS NULL THEN
           DBMS_OUTPUT.PUT_LINE (' Please re-run program with provided info.');
       ELSE
                   INSERT INTO jobs (job_id, job_title)
                   VALUES (g_jobid, g_jobname);
       END IF;
      END add_job;
END;

TIA,
-Chris

a couple of errors:

- parameters for add_job are declarated differently in spec and body.
- IN-parameter jobid cannot be assigend a value by selecting into it.
- jobid is not boolean, so it cannopt be a return value for get_job.

hth
Urs Metzger- Hide quoted text -

- Show quoted text -

Some adjustments to your original code and this works:

SQL>
SQL> create table jobs (
2 job_id varchar2(50),
3 job_title varchar2(50)
4 );

Table created.

SQL>
SQL> CREATE OR REPLACE PACKAGE job_pkg IS
2 g_jobid VARCHAR2(50) := upper('dba');
3 g_jobname VARCHAR2(50) := upper('app analyst ');
4 PROCEDURE add_job (g_jobid in jobs.job_id%type, g_jobname in
jobs.job_title%type);
5 END job_pkg;
6 /

Package created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY job_pkg IS
2 FUNCTION get_job (jobid IN jobs.job_id%TYPE) RETURN varchar2
IS
3 v_jobid jobs.job_id%type:=null;
4 BEGIN
5 SELECT job_id INTO v_jobid FROM jobs WHERE job_id = jobid;
6 RETURN jobid;
7 END get_job;
8
9
10 PROCEDURE add_job
11 (g_jobid IN jobs.job_id%TYPE,
12 g_jobname IN jobs.job_title%TYPE)
13 IS
14 BEGIN
15 IF get_job(g_jobid) IS NOT NULL THEN
16 DBMS_OUTPUT.PUT_LINE ('Job Id: ' || g_jobid || ' already
exist in DB.');
17 END IF;
18 EXCEPTION
19 when no_data_found then
20 INSERT INTO jobs (job_id, job_title)
21 VALUES (g_jobid, g_jobname);
22 when others then
23 dbms_output.put(dbms_utility.format_error_stack);
24
dbms_output.put_line(dbms_utility.format_error_backtrace);
25 END add_job;
26 END;
27 /

Package body created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> insert all
2 into jobs values ('cta', 'Chicken tickler')
3 into jobs values ('tta', 'Turkey tickler')
4 into jobs values ('tba', 'Turkey baster')
5 into jobs values ('bsa', 'Beef slicer')
6 into jobs values ('ira', 'Iguana rangler')
7 into jobs values ('pta', 'Pickle tickler')
8 into jobs values ('tpa', 'Tickled pickler')
9 into jobs values ('bba', 'Buffalo bouncer')
10 select * From dual;

8 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> exec job_pkg.add_job('bst', 'Borscht stirrer')

PL/SQL procedure successfully completed.

SQL>
SQL> select * from jobs;

JOB_ID JOB_TITLE
--------------------------------------------------
--------------------------------------------------
cta Chicken tickler
tta Turkey tickler
tba Turkey baster
bsa Beef slicer
ira Iguana rangler
pta Pickle tickler
tpa Tickled pickler
bba Buffalo bouncer
bst Borscht stirrer

9 rows selected.

SQL>
SQL> exec job_pkg.add_job('bst', 'Borscht stirrer')
Job Id: bst already exist in DB.

PL/SQL procedure successfully completed.

SQL>
SQL> exec job_pkg.add_job(job_pkg.g_jobid, job_pkg.g_jobname)

PL/SQL procedure successfully completed.

SQL>
SQL> select * from jobs;

JOB_ID JOB_TITLE
--------------------------------------------------
--------------------------------------------------
cta Chicken tickler
tta Turkey tickler
tba Turkey baster
bsa Beef slicer
ira Iguana rangler
pta Pickle tickler
tpa Tickled pickler
bba Buffalo bouncer
bst Borscht stirrer
DBA APP ANALYST

10 rows selected.

SQL>

Let's do some more modifications and make this a bit better:

SQL> create table jobs (
2 job_id varchar2(50),
3 job_title varchar2(50),
4 constraint jobs_pk
5 primary key(job_id)
6 );

Table created.

SQL>
SQL> CREATE OR REPLACE PACKAGE job_pkg IS
2 g_jobid VARCHAR2(50) := upper('dba');
3 g_jobname VARCHAR2(50) := upper('app analyst ');
4 PROCEDURE add_job (g_jobid in jobs.job_id%type, g_jobname in
jobs.job_title%type);
5 END job_pkg;
6 /

Package created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY job_pkg IS
2 FUNCTION get_job (jobid IN jobs.job_id%TYPE) RETURN varchar2
IS
3 v_jobid jobs.job_id%type:=null;
4 BEGIN
5 SELECT job_id INTO v_jobid FROM jobs WHERE job_id = jobid;
6 RETURN jobid;
7 END get_job;
8
9
10 PROCEDURE add_job
11 (g_jobid IN jobs.job_id%TYPE,
12 g_jobname IN jobs.job_title%TYPE)
13 IS
14 v_result jobs.job_id%type:=null;
15 job_found exception;
16 pragma exception_init(job_found, -20099);
17 BEGIN
18 v_result := get_job(g_jobid);
19 if v_result = g_jobid then
20 raise job_found;
21 end if;
22 EXCEPTION
23 when no_data_found then
24 INSERT INTO jobs (job_id, job_title)
25 VALUES (g_jobid, g_jobname);
26 when job_found then
27 DBMS_OUTPUT.PUT_LINE ('Job Id: ' || g_jobid || ' already
exist in DB.');
28 when others then
29 dbms_output.put(dbms_utility.format_error_stack);
30
dbms_output.put_line(dbms_utility.format_error_backtrace);
31 END add_job;
32 END;
33 /

Package body created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> insert all
2 into jobs values ('cta', 'Chicken tickler')
3 into jobs values ('tta', 'Turkey tickler')
4 into jobs values ('tba', 'Turkey baster')
5 into jobs values ('bsa', 'Beef slicer')
6 into jobs values ('ira', 'Iguana rangler')
7 into jobs values ('pta', 'Pickle tickler')
8 into jobs values ('tpa', 'Tickled pickler')
9 into jobs values ('bba', 'Buffalo bouncer')
10 select * From dual;

8 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> exec job_pkg.add_job('bst', 'Borscht stirrer')

PL/SQL procedure successfully completed.

SQL>
SQL> select * from jobs;

JOB_ID JOB_TITLE
--------------------------------------------------
--------------------------------------------------
cta Chicken tickler
tta Turkey tickler
tba Turkey baster
bsa Beef slicer
ira Iguana rangler
pta Pickle tickler
tpa Tickled pickler
bba Buffalo bouncer
bst Borscht stirrer

9 rows selected.

SQL>
SQL> exec job_pkg.add_job('bst', 'Borscht stirrer')
Job Id: bst already exist in DB.

PL/SQL procedure successfully completed.

SQL>
SQL> exec job_pkg.add_job(job_pkg.g_jobid, job_pkg.g_jobname)

PL/SQL procedure successfully completed.

SQL>
SQL> select * from jobs;

JOB_ID JOB_TITLE
--------------------------------------------------
--------------------------------------------------
cta Chicken tickler
tta Turkey tickler
tba Turkey baster
bsa Beef slicer
ira Iguana rangler
pta Pickle tickler
tpa Tickled pickler
bba Buffalo bouncer
bst Borscht stirrer
DBA APP ANALYST

10 rows selected.

SQL>

To improve it even further:

SQL> create table jobs (
2 job_id varchar2(50),
3 job_title varchar2(50),
4 constraint jobs_pk
5 primary key(job_id)
6 );

Table created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY job_pkg IS
2
3 PROCEDURE add_job
4 (g_jobid IN jobs.job_id%TYPE,
5 g_jobname IN jobs.job_title%TYPE)
6 IS
7 BEGIN
8 INSERT INTO jobs (job_id, job_title)
9 VALUES (g_jobid, g_jobname);
10 EXCEPTION
11 when dup_val_on_index then
12 DBMS_OUTPUT.PUT_LINE ('Job Id: ' || g_jobid || ' already
exist in DB.');
13 when others then
14 dbms_output.put(dbms_utility.format_error_stack);
15
dbms_output.put_line(dbms_utility.format_error_backtrace);
16 END add_job;
17 END;
18 /

Package body created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> insert all
2 into jobs values ('cta', 'Chicken tickler')
3 into jobs values ('tta', 'Turkey tickler')
4 into jobs values ('tba', 'Turkey baster')
5 into jobs values ('bsa', 'Beef slicer')
6 into jobs values ('ira', 'Iguana rangler')
7 into jobs values ('pta', 'Pickle tickler')
8 into jobs values ('tpa', 'Tickled pickler')
9 into jobs values ('bba', 'Buffalo bouncer')
10 select * From dual;

8 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> exec job_pkg.add_job('bst', 'Borscht stirrer')

PL/SQL procedure successfully completed.

SQL>
SQL> select * from jobs;

JOB_ID JOB_TITLE
--------------------------------------------------
--------------------------------------------------
cta Chicken tickler
tta Turkey tickler
tba Turkey baster
bsa Beef slicer
ira Iguana rangler
pta Pickle tickler
tpa Tickled pickler
bba Buffalo bouncer
bst Borscht stirrer

9 rows selected.

SQL>
SQL> exec job_pkg.add_job('bst', 'Borscht stirrer')
Job Id: bst already exist in DB.

PL/SQL procedure successfully completed.

SQL>
SQL> exec job_pkg.add_job(job_pkg.g_jobid, job_pkg.g_jobname)

PL/SQL procedure successfully completed.

SQL>
SQL> select * from jobs;

JOB_ID JOB_TITLE
--------------------------------------------------
--------------------------------------------------
cta Chicken tickler
tta Turkey tickler
tba Turkey baster
bsa Beef slicer
ira Iguana rangler
pta Pickle tickler
tpa Tickled pickler
bba Buffalo bouncer
bst Borscht stirrer
DBA APP ANALYST

10 rows selected.

SQL>



David Fitzjarrell
.

Relevant Pages