Home » SQL & PL/SQL » Client Tools » If Then Else on a Script to call another Script (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0)
If Then Else on a Script to call another Script [message #673337] Thu, 15 November 2018 09:15 Go to next message
wtolentino
Messages: 284
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 #673338 is a reply to message #673337] Thu, 15 November 2018 09:22 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't do that with pure sqlplus scripting you would need to use shell scripting.
But why are you worried about running in a package that already exists?
Re: If Then Else on a Script to call another Script [message #673339 is a reply to message #673338] Thu, 15 November 2018 09:31 Go to previous messageGo to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
because not all database has that package and we need only to recreate the package where is it currently at.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
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

icon14.gif  Re: If Then Else on a Script to call another Script [message #673341 is a reply to message #673340] Thu, 15 November 2018 10:32 Go to previous message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
thanks so much Thumbs Up
Previous Topic: Enterprise Data Quality
Next Topic: Invalid User Name (ORA-01017)
Goto Forum:
  


Current Time: Sat Dec 14 07:25:15 CST 2019