03 July 2015

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

See my later blog post "An updated and better method for selecting values from a string using the APEX_STRING packagefor an updated and better way of doing this 

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.

11 March 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