i have a script that contains the code to recreate the packege. that script will be called from another script and will be run from SQL*Plus.
for example recompile_pkg.sql:
select substr(user,1,15) user_name,
substr(sys_context('USERENV','SESSION_SCHEMA'),1,20) schema_name,
substr(sys_context('userenv','db_name'),1,15) database_name,
to_char(sysdate,'dd-mon-rrrr hh:mi:ss am') run_start_date
from dual;
@CUSTOM_PKG_UTILITIES.sql;
/
show errors;
select to_char(sysdate,'dd-mon-rrrr hh:mi:ss am') run_end_date
from dual;
spool off;
script CUSTOM_PKG_UTILITIES.sql has code that is to recreate the package.
because i need to execute the "recompile_pkg.sql" script in a multiple database i need to include some lines that will verify if the package exist. if exists then execute it.
for example
column obj_name new_value vDBname noprint;
select owner||'.'||object_name obj_name from dba_objects
where owner = 'APPS'
and object_name = 'CUSTOM_PKG';
select substr(user,1,15) user_name,
substr(sys_context('USERENV','SESSION_SCHEMA'),1,20) schema_name,
substr(sys_context('userenv','db_name'),1,15) database_name,
to_char(sysdate,'dd-mon-rrrr hh:mi:ss am') run_start_date
from dual;
if obj_name is not null then
@CUSTOM_PKG_UTILITIES.sql;
end if;
/
show errors;
select to_char(sysdate,'dd-mon-rrrr hh:mi:ss am') run_end_date
from dual;
spool off;
thank you.
[Updated on: Thu, 15 November 2018 09:16]
Report message to a moderator
] 
Blog
Search
Help
Members
Register
Login
Home
