|
SQL>
SQL> CREATE OR REPLACE PACKAGE globals IS
2 FUNCTION what_version RETURN VARCHAR2;
3 PRAGMA RESTRICT_REFERENCES(what_version, WNDS, WNPS, RNDS);
4 END globals;
5 /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY globals IS
2 pv_version_txt VARCHAR2(30) := '19980519.1';
3 FUNCTION what_version RETURN VARCHAR2 IS
4 BEGIN
5 RETURN pv_version_txt;
6 END; -- what_version
7 END globals;
8 /
Package body created.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DECLARE
2 lv_dml_statement_txt VARCHAR2(100);
3 lv_package_version_txt VARCHAR2(100);
4 lv_record_count_num PLS_INTEGER;
5 lv_version_cursor_num PLS_INTEGER;
6 CURSOR cur_source IS
7 SELECT DISTINCT name
8 FROM user_source
9 WHERE type = 'PACKAGE BODY';
10 BEGIN
11 FOR cur_source_rec IN cur_source LOOP
12 lv_version_cursor_num := DBMS_SQL.OPEN_CURSOR;
13 lv_dml_statement_txt := 'SELECT ' || cur_source_rec.name || '.what_version FROM DUAL';
14 BEGIN
15 DBMS_SQL.PARSE(lv_version_cursor_num, lv_dml_statement_txt, DBMS_SQL.NATIVE);
16 DBMS_SQL.DEFINE_COLUMN(lv_version_cursor_num, 1, lv_package_version_txt, 100);
17 lv_record_count_num := DBMS_SQL.EXECUTE(lv_version_cursor_num);
18 IF DBMS_SQL.FETCH_ROWS(lv_version_cursor_num) > 0 THEN
19 DBMS_SQL.COLUMN_VALUE(lv_version_cursor_num, 1, lv_package_version_txt);
20 ELSE
21 lv_package_version_txt := 'Version Reporting Failed';
22 END IF;
23 EXCEPTION
24 WHEN OTHERS THEN
25 lv_package_version_txt := 'Version Reporting Not ' || 'Supported';
26 END;
27 DBMS_OUTPUT.PUT_LINE(LOWER(cur_source_rec.name) || ': ' || lv_package_version_txt);
28 DBMS_SQL.CLOSE_CURSOR(lv_version_cursor_num);
29 END LOOP;
30 END;
31 /
gender_ts: Version Reporting Not Supported
demo_pkg: Version Reporting Not Supported
emp_coll_pkg: Version Reporting Not Supported
book_info: Version Reporting Not Supported
hr_app: Version Reporting Not Supported
onecur: Version Reporting Not Supported
empinfo: Version Reporting Not Supported
employees_pkg: Version Reporting Not Supported
dates: Version Reporting Not Supported
classpackage: Version Reporting Not Supported
plw5000: Version Reporting Not Supported
grp: Version Reporting Not Supported
desccols: Version Reporting Not Supported
fixer: Version Reporting Not Supported
process_vacations: Version Reporting Not Supported
xbuff: Version Reporting Not Supported
timer: Version Reporting Not Supported
srpkg1: Version Reporting Not Supported
bt: Version Reporting Not Supported
oracle_error_info: Version Reporting Not Supported
test_pack: Version Reporting Not Supported
order_entry: Version Reporting Not Supported
procesorders: Version Reporting Not Supported
pipe_output: Version Reporting Not Supported
overload: Version Reporting Not Supported
flog: Version Reporting Not Supported
audit_trail_pkg: Version Reporting Not Supported
dyn_demo: Version Reporting Not Supported
p1: Version Reporting Not Supported
workplace_type_ts: Version Reporting Not Supported
scopes: Version Reporting Not Supported
rfpkg2: Version Reporting Not Supported
srpkg2: Version Reporting Not Supported
valstd: Version Reporting Not Supported
nocopy_test: Version Reporting Not Supported
favorites_pkg: Version Reporting Not Supported
pack1: Version Reporting Not Supported
dowpack: Version Reporting Not Supported
pack2: Version Reporting Not Supported
globals: 19980519.1
mypackage1: Version Reporting Not Supported
guestbook: Version Reporting Not Supported
worker_ts: Version Reporting Not Supported
worker_type_ts: Version Reporting Not Supported
valerr: Version Reporting Not Supported
mydate: Version Reporting Not Supported
orgmaster: Version Reporting Not Supported
nocopypkg: Version Reporting Not Supported
calc_pkg: Version Reporting Not Supported
thisuser: Version Reporting Not Supported
my_package: Version Reporting Not Supported
employee_pkg: Version Reporting Not Supported
diana_size: Version Reporting Not Supported
process_emps: Version Reporting Not Supported
demo2: Version Reporting Not Supported
employee_types: Version Reporting Not Supported
salespkg: Version Reporting Not Supported
orgmaster2: Version Reporting Not Supported
authors_pkg: Version Reporting Not Supported
anynums_pkg: Version Reporting Not Supported
bidir: Version Reporting Not Supported
fullname_pkg: Version Reporting Not Supported
clean_schema: Version Reporting Not Supported
sessval: Version Reporting Not Supported
datecalc: Version Reporting Not Supported
my_date: Version Reporting Not Supported
genders: Version Reporting Not Supported
dynsql: Version Reporting Not Supported
multdim: Version Reporting Not Supported
inline_pkg: Version Reporting Not Supported
parameters: Version Reporting Not Supported
rfpkg: Version Reporting Not Supported
dynvar: Version Reporting Not Supported
name_pkg: Version Reporting Not Supported
pkg_vars: Version Reporting Not Supported
my_pkg: Version Reporting Not Supported
not_mypackage2: Version Reporting Not Supported
outputpackage: Version Reporting Not Supported
pizza_pkg: Version Reporting Not Supported
PL/SQL procedure successfully completed.
SQL>
|