procedure & function inside packages

From: chris <lazyboy_2k_at_yahoo.com>
Date: Fri, 18 Apr 2008 11:08:32 -0700 (PDT)
Message-ID: <bd84c3cf-114b-4d3c-b7d4-d9ffc7dd21c4@a5g2000prg.googlegroups.com>


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 Received on Fri Apr 18 2008 - 13:08:32 CDT

Original text of this message