Friday, July 3, 2015

A simple way of SELECTing values from a comma-separated string

Want a quick and easy way to select from a comma-separated list? Just use xmltable. 

This can be used for a FOR loop too! 


Note: You probably shouldn't use this in an environment that requires high-performance though.  As Mathias Rogel and Carsten Czarski have pointed out here, it's not as efficient as a PL/SQL-pipelined function or a pure SQL-approach - but it does have the merit of being simple.

Wednesday, March 11, 2015

Recently, I decided to automate the regular backup of APEX applications on my servers.  To achieve this I scheduled a regular PL/SQL job using crontab.

Basically I just loop through all of the applications, then export them to a clob and write this clob to a directory.   I use the application ID, its name and the date as the output file name.

I have an optional part at the end where I push the directory to Amazon's S3 as off-site storage.  

 #!/bin/bash   
 export ORACLE_BASE=/u01/oracle  
 export ORACLE_HOME=${ORACLE_BASE}/product/11.2.0/dbhome_1  
 export PATH=${PATH}:${ORACLE_HOME}/bin  
 export ORACLE_SID=MYSID  -- replace this with your SID
 export ORAENV_ASK=NO  
 export OUTPUT_DIR=/u01/exports  -- same as oracle dir DATA_PUMP_DIR
 . ${ORACLE_HOME}/bin/oraenv  
   
 date  
 echo "Oracle Home is " ${ORACLE_HOME}  
   
 ${ORACLE_HOME}/bin/sqlplus system/mysystempassword << EOC  
 SET ECHO ON  
 SET FEEDBACK ON  
 SET TIMING ON  
 SET TIME ON  
 SET SERVEROUTPUT ON  
   
 DECLARE  
  v_clob CLOB;  
 BEGIN  
  FOR rec IN (SELECT application_id, application_name   
                FROM apex_applications   
               ORDER BY application_id)  
  LOOP  
    v_clob := wwv_flow_utilities.export_application_to_clob(trim(to_char(rec.application_id)));  
    dbms_xslprocessor.clob2file  
     (cl => v_clob,   
      flocation => 'DATA_PUMP_DIR',   -- oracle dir points to the same as $OUTPUT_DIR
      fname => trim(to_char(rec.application_id,'99900000'))||'_'  
               ||replace(rec.application_name,' ','_')  
               ||'.'||to_char(sysdate,'YYYYMMDD')||'.sql');  
    dbms_output.put_line(rec.application_name||':'||length(v_clob));  
  END LOOP;  
 END;  
 /  
   
 EOC  
   
 date  
   
 #optional copy to Amazon S3 - you don't necessarily need this   
 TODAY=`date "+%Y%m%d"`  
 echo $TODAY  
 s3cmd put $OUTPUT_DIR/*$TODAY* s3://DBExports/APEXapplications/$TODAY/  
   
 date  

Monday, August 17, 2009

Getting yesterday's date in Linux

How to get yesterday's date in Linux

Ever need to know yesterday's day in a shell script?

After much searching here is how I finally did it:

$DAY=`date +"%A"`
$YESTERDAY=`(date --date='1 days ago' '+%A')`
echo $DAY
echo $YESTERDAY

Other variations on this theme:

date
date --date=now
date --date=today # same thing
date --date='3 seconds'
date --date='3 seconds ago'
date --date='4 hours'
date --date='tomorrow'
date --date='1 day'
date --date='1 days'
date --date='yesterday'
date --date='1 day ago'
date --date='1 days ago'
date --date='1 week'
date --date='1 fortnight'
date --date='1 month'
date --date='1 year'


I hope this is useful for you.
Enjoy!

Thursday, June 18, 2009

UTL_URL - escaping and unescaping URLs in Oracle PL/SQL

I've just discovered UTL_URL, a great little package that will escape and unescape URLs.

For example:

utl_url.escape('http://myserver.com/path/trythis?p1=inniú&p2=café croissant')
returns
http://myserver.com/path/trythis?p1=inni%FA&p2=caf%E9%20croissant

and

utl_url.unescape('http://myserver.com/path/trythis?p1=inni%FA&p2=caf%E9%20croissant')
returns
http://myserver.com/path/trythis?p1=inniú&p2=café croissant

It's that simple!

Friday, May 1, 2009

Forcing the language of an Oracle 10g installation on Windows

Well, it looks like my first blog entry will be short and simple. I was faced with a small problem yesterday installing Oracle 10gR2 on Windows Vista Ultimate Edition.

My Windows installation is entirely in English and my keyboard is Swiss French. Somehow, the Oracle Universal Installer (OUI) decided that everything should be in French. So I uninstalled, deleted the "c:\oracle" directory, deleted the "c:\Program Files\Oracle" which housed the inventory and used regedit to remove all registry keys that pertained to Oracle. Started re-installing again, and same thing - OUI was in French!

After three hours and multiple rounds of registry cleaning, deleting, re-installing. I finally came across some 11g documentation which explained that OUI gets its locale from Java (it didn't explain where Java gets it from). But, more importantly, it also said that this can be overridden, and the magic method for forcing an installation in English is:

1. cd to the installation directory that contains "setup.exe"

2. start OUI manually using the following command
setup.exe SELECTED_LANGUAGES={"en"}

So that's my modest first blog entry. I hope it helps someone avoid spending the time I spent solving this little problem.