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
