Re: procedure & function inside packages
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 injobs.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 injobs.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