You can drop a PL program package and can view the package information for management purposes.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Drop a program package
You can execute the DROP PACKAGE statement to drop unwanted program packages. The syntax is as follows:
DROP PACKAGE [BODY] [user.]package_name;
Here is an example:
obclient>DROP PACKAGE obdemo_pack1;
Query OK, 0 rows affected
Manage program packages
You can use the following package-related views to view the package information:
USER_OBJECTS: displays database objects and allows you to view the names, current status, and creation time of packages.Notice
PACKAGEandPACKAGE BODYare different types of objects.USER_SOURCE: displays information about packages and the package bodies.USER_ERRORS: displays the errors returned in the last compilation of a package or package body.
Example 1: View the status and creation time of all packages under the current user
obclient> SELECT OBJECT_NAME,OBJECT_TYPE,CREATED,STATUS
FROM USER_OBJECTS WHERE OBJECT_TYPE LIKE 'PACKAGE%';
+-------------+-------------+-----------+---------+
| OBJECT_NAME | OBJECT_TYPE | CREATED | STATUS |
+-------------+-------------+-----------+---------+
| DEMO_PACK | PACKAGE | 17-DEC-20 | INVALID |
+-------------+-------------+-----------+---------+
1 row in set
Example 2: View the content of the package body of the OBDEMO_PACK package
obclient> SELECT TEXT FROM USER_SOURCE
WHERE TYPE='PACKAGE BODY'
AND NAME='OBDEMO_PACK'\G
*************************** 1. row ***************************
TEXT: PACKAGE BODY obdemo_pack
IS
FUNCTION add_obdept(dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)
RETURN NUMBER
IS
deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(deptno_remaining, -1);
BEGIN
INSERT INTO obdept VALUES(dept_no, dept_name, location);
IF SQL%FOUND THEN
RETURN 1;
END IF;
EXCEPTION
WHEN deptno_remaining THEN
RETURN 0;
WHEN OTHERS THEN
RETURN -1;
END add_obdept;
FUNCTION remove_obdept(dept_no NUMBER)
RETURN NUMBER
IS
BEGIN
DELETE FROM obdept WHERE deptno=dept_no;
IF SQL%FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END remove_obdept;
PROCEDURE query_obdept (dept_no IN NUMBER)
IS
BEGIN
SELECT * INTO obDeptRec FROM obdept WHERE deptno=dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No department numbered '||dept_no||' in the database.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Program runtime error!')
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
END query_obdept;
BEGIN
Null;
END obdemo_pack
1 row in set
Example 3: View the information and location of the error returned in the last compilation of the PKG package
obclient> CREATE OR REPLACE PACKAGE pkg IS v vvv;
END;
/
Query OK, 0 rows affected, 1 warning
obclient> SELECT LINE,POSITION,TEXT FROM USER_ERRORS
WHERE NAME='PKG' AND TYPE='PACKAGE';
+------+----------+------------------------------------------------------------------------+
| LINE | POSITION | TEXT |
+------+----------+------------------------------------------------------------------------+
| 0 | 34 | ORA-00600: internal error code, arguments: -5544, Undeclared type: VVV |
+------+----------+------------------------------------------------------------------------+
