Re: procedure & function inside packages

From: <fitzjarrell_at_cox.net>
Date: Fri, 18 Apr 2008 13:58:36 -0700 (PDT)
Message-ID: <e09a8247-ff55-40a0-abaf-cb8455073c38@8g2000hse.googlegroups.com>


On Apr 18, 3:14 pm, Urs Metzger <u..._at_ursmetzger.de> 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 Received on Fri Apr 18 2008 - 15:58:36 CDT

Original text of this message