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
