OraMatt: YABAOracle

Here’s a quick demo to show off DBMS_METADATA and DBMS_XMLGEN APIs in the Oracle Database…enjoy

#!/bin/bash 
#
#
# Quick Demo to get MetaData & Data in XML
# Author: Matt D
# Created: May 29, 2012
#
#
#
#
clear screen
echo "################################################"
echo "#                                              #"
echo "# Quick demo of the Oracle Database            #"
echo "# DBMS_METADATA & DBMS_XMLGEN APIs             #"
echo "#                                              #"
echo "################################################"
#
#
#
# Test for ORACLE_HOME
if [ ! -d $ORACLE_HOME ] ; then
   echo "Please set your ORACLE_HOME enviroment variable"
   echo "and then re-run this script."
   exit 1
 fi
#


# Get the connect information
#
echo "################################################"
echo "Please enter the username to connect as > "
read DBUSER
echo "################################################"
echo "Please enter the password for the user > "
read DBPASS
echo "################################################"
echo "Please enter the tablename for the Metadata  "
echo "& Data you wish to generate in XML          "
echo "Tablename >"
read DBTABLE
echo "################################################"
echo "Please enter the TNSNAME entry to connect to >"
read DBCONN
echo "################################################"
echo "Here are your entries: "
echo "DBUSER ="$DBUSER
echo "DBPASS="$DBPASS
echo "DBTABLE="$DBTABLE
echo "DBCONN="$DBCONN
echo "################################################"
read -p "Press [Enter] key to continue..."


# Convert from lowercase to UPPERCASE
#
# 
export DBUpUSER=`echo $DBUSER  | tr [:lower:] [:upper:]`
export DBUpTABLE=`echo $DBTABLE | tr  [:lower:] [:upper:]`
#
#


$ORACLE_HOME/bin/sqlplus -s ${DBUSER}/${DBPASS}@${DBCONN} <<EOF
set long 200000000
set sqlprompt ' '
set echo off
set feedback off
set timing off
set heading off
set pagesize 0
set linesize 9999
set trimspool on
set tab off
set recsep off
spool /tmp/${DBUSER}_${DBTABLE}_metadata.xml
select dbms_metadata.get_xml('TABLE','${DBUpTABLE}','${DBUpUSER}')
from dual;
spool off;

spool /tmp/${DBUSER}_${DBTABLE}_data.xml
select dbms_xmlgen.getxml('SELECT * FROM ${DBUSER}.${DBTABLE}') xml
from dual;
spool off;
exit;
EOF


more /tmp/${DBUSER}*.xml