How to Create Oracle Package?  ..

SQL> connect scott/tiger

SQL> desc scott.dept

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 DEPTNO                                    NOT NULL NUMBER(2)

 DNAME                                              VARCHAR2(14)

 LOC                                                VARCHAR2(13)

SQL> create table depttest as select * from dept ;

Table created.

desc scott.depttest

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 DEPTNO                                             NUMBER(2)

 DNAME                                              VARCHAR2(14)

 LOC                                                VARCHAR2(13)

1.

CREATE OR REPLACE PACKAGE pkg_dept IS

  FUNCTION prnt_strng RETURN VARCHAR2;

  PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2);

END pkg_dept;

2.

--Package Body

CREATE OR REPLACE PACKAGE BODY pkg_dept IS

  --Function 

  FUNCTION prnt_strng RETURN VARCHAR2 IS

    BEGIN

      RETURN 'http://anuj-singh.blogspot.com/';

    END prnt_strng;

   --Procedure 

   PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2) IS

     BEGIN

      INSERT INTO depttest (DEPTNO, DNAME,LOC) VALUES(d_DEPTNO, d_dname,d_loc);

     END;

  

END pkg_dept;

====================

Output 

CREATE OR REPLACE PACKAGE pkg_dept IS

  FUNCTION prnt_strng RETURN VARCHAR2;

  PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2);

END pkg_dept;

   /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg_dept IS

  2    --Function

  FUNCTION prnt_strng RETURN VARCHAR2 IS

  3    4      BEGIN

      RETURN 'http://anuj-singh.blogspot.com/';

  5    6      END prnt_strng;

   --Procedure

  7    8     PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2) IS

  9       BEGIN

      INSERT INTO depttest (DEPTNO, DNAME,LOC) VALUES(d_DEPTNO, d_dname,d_loc);

 10   11       END;

END pkg_dept; 12   13

 14  /

Package body created.

===========================================

Oracle Package DBMS METADATA

set long 5000 pagesize 300

SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PKG_DEPT','SCOTT') FROM DUAL;

 CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_DEPT" IS

  --Function

  FUNCTION prnt_strng RETURN VARCHAR2 IS

    BEGIN

      RETURN 'http://anuj-singh.blogspot.com/';

    END prnt_strng;

   --Procedure

   PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2) IS

     BEGIN

      INSERT INTO depttest (DEPTNO, DNAME,LOC) VALUES(d_DEPTNO, d_dname,d_loc);

     END;

END pkg_dept;

or 

define v_owner='TEST'   --- owner

define v_pkg='PKG_TEST'  --- PACKAGE NAME

declare 

  myPackageSpec clob;

  myPackageBody clob;

BEGIN

    select dbms_metadata.get_ddl('PACKAGE_BODY', '&v_pkg', '&v_owner')  into myPackageBody    FROM DUAL;

    select dbms_metadata.get_ddl('PACKAGE_SPEC', '&v_pkg', '&v_owner')  into myPackageSpec    FROM DUAL;

  dbms_output.put_line(myPackageSpec);

  dbms_output.put_line(myPackageBody);

end;

 CREATE OR REPLACE EDITIONABLE PACKAGE "SCOTT"."PKG_DEPT" IS

  FUNCTION

prnt_strng RETURN VARCHAR2;

  PROCEDURE proc_dept(d_DEPTNO number, d_dname

VARCHAR2,d_loc VARCHAR2);

END pkg_dept;

  CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_DEPT" IS

--Function

  FUNCTION prnt_strng RETURN VARCHAR2 IS

    BEGIN

      RETURN

'http://anuj-singh.blogspot.com/';

    END prnt_strng;

   --Procedure

PROCEDURE proc_dept(d_DEPTNO number, d_dname VARCHAR2,d_loc VARCHAR2) IS

BEGIN

      INSERT INTO depttest (DEPTNO, DNAME,LOC) VALUES(d_DEPTNO,

d_dname,d_loc);

     END;

END pkg_dept;

PL/SQL procedure successfully completed.

=========================================================================

Testing !!!!!!!!!!

--Test Function 

set serveroutput on ;

BEGIN

  DBMS_OUTPUT.PUT_LINE (pkg_dept.prnt_strng);

END;

http://anuj-singh.blogspot.com/

PL/SQL procedure successfully completed.

or 

exec DBMS_OUTPUT.PUT_LINE (pkg_dept.prnt_strng);

==========================================================

--Test procedure

SQL>

exec pkg_dept.proc_dept(11,'FFF','UUUU');SQL>

PL/SQL procedure successfully completed.

SQL> select * from depttest;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        11 FFF            UUUU  <<<< New row