Re: procedure & function inside packages

On Apr 18, 1:58 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> 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
> > > FUNCTIONget_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;
>
> > > PROCEDUREadd_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 FUNCTIONget_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 PROCEDUREadd_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/SQLproceduresuccessfully 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/SQLproceduresuccessfully completed.
>
> SQL>
> SQL> exec job_pkg.add_job(job_pkg.g_jobid, job_pkg.g_jobname)
>
> PL/SQLproceduresuccessfully 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 FUNCTIONget_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 PROCEDUREadd_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/SQLproceduresuccessfully 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/SQLproceduresuccessfully completed.
>
> SQL>
> SQL> exec job_pkg.add_job(job_pkg.g_jobid, job_pkg.g_jobname)
>
> PL/SQLproceduresuccessfully 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
> ...
>
> read more »

Thank you very much everyone. You guys are owesome!!!!! This really encourages me to improve more about my coding skills & this is what I'm after. Practice makes perfect & thanks a lot for pointing out. Cheers. Received on Fri Apr 18 2008 - 16:14:26 CDT