Re: procedure & function inside packages

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 Received on Fri Apr 18 2008 - 15:14:10 CDT