If Then Else on a Script to call another Script [message #673337] |
Thu, 15 November 2018 09:15 |
wtolentino
Messages: 404 Registered: March 2005
|
Senior Member |
|
|
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
|
|
|
|
|
Re: If Then Else on a Script to call another Script [message #673340 is a reply to message #673337] |
Thu, 15 November 2018 10:00 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You can do something like that:
Set termout off
Col script new_value script
Select decode(count(*),0,'nothing.sql','CUSTOM_PKG_UTILITIES.sql') script
from (
select owner||'.'||object_name obj_name from dba_objects
where owner = 'APPS'
and object_name = 'CUSTOM_PKG'
)
/
Set termout on
@&script
with "nothing.sql" containing just a blank line.
[Updated on: Thu, 15 November 2018 10:00] Report message to a moderator
|
|
|
|