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 the 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
PACKAGE and PACKAGE BODY are 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 |
+------+----------+------------------------------------------------------------------------+