SQL*Plus con salida XLS

Este es un sencillo ejemplo de como podemos utilizar el SPOOL de Oracle SQL*Plus para poder obtener un archivo .XML pero que puede ser interpretado por el programa EXCEL.
Este ejemplo va más alla a la simple salida separada por comas (,) que se traduce a un archivo .CSV ya que nos permite tener varias pestañas (sheets) dentro de nuestro libro de excel.

Aquí vamos......

Los pasos a seguir son:

1.- Crea un archivo .sql con el siguiente nombre "xls.sql".
2.- Copia y pega el siguiente código SQL en el archivo creado en el paso 1.

---INICIO DEL CÓDIGO

----------------------------------------------------
--CONFIGURACION DE SQL PLUS
----------------------------------------------------
CLEAR BREAK COMPUTE;
REPFOOTER OFF;

SET HEADING OFF;
SET TIMING OFF VERI OFF SPACE 1 FLUSH ON PAUSE OFF TERMOUT ON NUMWIDTH 10;
SET TRIMSPOOL ON TRIMOUT ON DEFINE "&" CONCAT "." SERVEROUTPUT ON;
SET UNDERLINE OFF
SET LONG 1000000000 LONGC 60000


SET ECHO OFF;
SET FEEDBACK 1;
SET HEAD OFF;
SET LINES 9000;
SET PAGES 10000;
SET TERMOUT OFF;
SET TRIMSP ON;
SET FEED OFF;
SET LINE 400;


----------------------------------------------------
--CREACION DEL ARCHIVO DE SALIDA .XML
----------------------------------------------------
SPOOL xls.xml;

----------------------------------------------------
--CABECERA DEL ARCHIVO XML
----------------------------------------------------
PROMPT <?xml version="1.0"?>
PROMPT <?mso-application progid="Excel.Sheet"?>
PROMPT <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
PROMPT xmlns:o="urn:schemas-microsoft-com:office:office"
PROMPT xmlns:x="urn:schemas-microsoft-com:office:excel"
PROMPT xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
PROMPT xmlns:html="http://www.w3.org/TR/REC-html40">
PROMPT <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
PROMPT <Created>2006-00-12T12:46:56Z</Created>
PROMPT <LastSaved>2006-00-12T12:49:13Z</LastSaved>
PROMPT <Version>12.00</Version>
PROMPT </DocumentProperties>
PROMPT <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
PROMPT <RemovePersonalInformation/>
PROMPT </OfficeDocumentSettings>
PROMPT <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
PROMPT <WindowHeight>7815</WindowHeight>
PROMPT <WindowWidth>14880</WindowWidth>
PROMPT <WindowTopX>360</WindowTopX>
PROMPT <WindowTopY>300</WindowTopY>
PROMPT <ProtectStructure>False</ProtectStructure>
PROMPT <ProtectWindows>False</ProtectWindows>
PROMPT </ExcelWorkbook>
PROMPT <Styles>
PROMPT <Style ss:ID="Default" ss:Name="Normal">
PROMPT <Alignment ss:Vertical="Bottom"/>
PROMPT <Borders/>
PROMPT <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
PROMPT <Interior/>
PROMPT <NumberFormat/>
PROMPT <Protection/>
PROMPT </Style>
PROMPT <Style ss:ID="s81">
PROMPT <Borders>
PROMPT <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
PROMPT <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
PROMPT <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
PROMPT <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
PROMPT </Borders>
PROMPT </Style>
PROMPT <Style ss:ID="s82">
PROMPT <Borders>
PROMPT <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
PROMPT <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
PROMPT <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
PROMPT <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
PROMPT </Borders>
PROMPT <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#4F81BD"
PROMPT ss:Bold="1"/>
PROMPT <Interior ss:Color="#BFBFBF" ss:Pattern="Solid"/>
PROMPT </Style>
PROMPT </Styles>
----------------------------------------------------
--FIN DE LA CABECERA ARCHIVO XML
----------------------------------------------------

PROMPT <Worksheet ss:Name="MIS TABLAS">

PROMPT <Table ss:ExpandedColumnCount="1000" ss:ExpandedRowCount="10000000" x:FullColumns="1"
PROMPT x:FullRows="1" ss:DefaultColumnWidth="100" ss:DefaultRowHeight="15">

PROMPT <Row ss:Index="5">
PROMPT <Cell ss:StyleID="s82"><Data ss:Type="String">NOMBRE TABLA</Data></Cell>
PROMPT <Cell ss:StyleID="s82"><Data ss:Type="String">TABLESPACE</Data></Cell>
PROMPT <Cell ss:StyleID="s82"><Data ss:Type="String">ULTIMO ANALISIS</Data></Cell>
PROMPT </Row>

SELECT
'<Row>'||
'<Cell ss:StyleID="s81"><Data ss:Type="String">'||table_name||'</Data></Cell>',
'<Cell ss:StyleID="s81"><Data ss:Type="String">'||tablespace_name||'</Data></Cell>',
'<Cell ss:StyleID="s81"><Data ss:Type="String">'||last_analyzed||'</Data></Cell>'
||'</Row>'
  FROM user_tables
/

PROMPT </Table>

PROMPT <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"><PageSetup><Header x:Margin="0.3"/><Footer x:Margin="0.3"/>
PROMPT <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/></PageSetup><Print><ValidPrinterInfo/><PaperSizeIndex>9</PaperSizeIndex>
PROMPT <HorizontalResolution>200</HorizontalResolution><VerticalResolution>200</VerticalResolution></Print><Selected/>
PROMPT <Panes><Pane><Number>3</Number><ActiveRow>1</ActiveRow></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions>

PROMPT </Worksheet>

-----------------------------------------------
PROMPT <Worksheet ss:Name="MIS INDICES">

PROMPT <Table ss:ExpandedColumnCount="1000" ss:ExpandedRowCount="10000000" x:FullColumns="1"
PROMPT x:FullRows="1" ss:DefaultColumnWidth="100" ss:DefaultRowHeight="15">

PROMPT <Row ss:Index="5">
PROMPT <Cell ss:StyleID="s82"><Data ss:Type="String">NOMBRE INDICE</Data></Cell>
PROMPT <Cell ss:StyleID="s82"><Data ss:Type="String">TIPO</Data></Cell>
PROMPT <Cell ss:StyleID="s82"><Data ss:Type="String">TABLESPACE</Data></Cell>
PROMPT <Cell ss:StyleID="s82"><Data ss:Type="String">PROPIETARIO TABLA</Data></Cell>
PROMPT <Cell ss:StyleID="s82"><Data ss:Type="String">TABLA</Data></Cell>
PROMPT </Row>

SELECT
'<Row>'||
'<Cell ss:StyleID="s81"><Data ss:Type="String">'||index_name||'</Data></Cell>',
'<Cell ss:StyleID="s81"><Data ss:Type="String">'||index_type||'</Data></Cell>',
'<Cell ss:StyleID="s81"><Data ss:Type="String">'||tablespace_name||'</Data></Cell>',
'<Cell ss:StyleID="s81"><Data ss:Type="String">'||table_owner||'</Data></Cell>',
'<Cell ss:StyleID="s81"><Data ss:Type="String">'||table_name||'</Data></Cell>'
||'</Row>'
  FROM user_indexes
/

PROMPT </Table>

PROMPT <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"><PageSetup><Header x:Margin="0.3"/><Footer x:Margin="0.3"/>
PROMPT <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/></PageSetup><Print><ValidPrinterInfo/><PaperSizeIndex>9</PaperSizeIndex>
PROMPT <HorizontalResolution>200</HorizontalResolution><VerticalResolution>200</VerticalResolution></Print><Selected/>
PROMPT <Panes><Pane><Number>3</Number><ActiveRow>1</ActiveRow></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions>

PROMPT </Worksheet>
-----------------------------------------------
PROMPT </Workbook>

SPOOL OFF;


---FIN DEL CODIGO

3.- Crea una sesión en tu base de datos a través del SQL*Plus con cualquier usuario.

4.- Invoca el archivo .sql creado en el paso 1
SQL>@xls.sql

5.- El archivo .sql crea otro archivo con nombre "xls.xml" este archivo abrelo con el excel.

6.- Saludos

Conexión entre cliente Oracle y servidor Oracle

Aquí se describen los pasos básicos para poder conectar un cliente Oracle con un servidor Oracle a través del protocolo TCP/IP.

Prerequisitos:

* En el servidor Oracle
  -------------------------
  - Que este corriendo en la misma red que el cliente.
  - Que una base de datos Oracle este instalada.
  - Que este instalado el soporte para el protocolo TCP/IP.
  - Que este configurado un listener.

* En la máquina del cliente
  ------------------------------
- Que este corriendo en la misma red que el servidor Oracle.
- Que tenga instalado un cliente Oracle.
- Que este instalado el soporte para el protocolo TCP/IP.


Tarea 1: Verificar que la red este disponible 

1) Desde el servidor de Oracle

Hacer prueba de loopback, generalmente se utiliza PING.
Generalmente se hace PING a la IP del servidor pero además debemos checar el nombre del servidor y sus alias de DNS (Domain Name System) en el archivo hosts

Unix
-----
$>vi /etc/hosts

Windows
-----------
%>notepad.exe %systemroot%\system32\drivers\etc\hosts

(hosts)
#IP address of server host name    alias
144.25.186.203        sales-server sales.us.acme.com

$%>ping 144.25.186.203

$%>ping sales-server

Los pings deben ser exitosos

2) Desde la máquina que funge como cliente

Realizar las pruebas de conexión hacia el servidor de Oracle utilizando las herramientas PING, FTP o TELNET para protocolos TCP/IP.

Si las operaciones no son exitosas, entonces, verificar que el cableado y que las interfaces de red esten debidamente conectados. Para esto, generalmente se consulta con el administrador de red para la solución de este tipo de problemas.

Tarea 2: Iniciar el servidor de Oracle y su listener

1) Para iniciar la la base de datos o servidor Oracle:

$>export ORACLE_SID=sid
%>set ORACLE_SID=sid

$%>sqlplus /nolog

$%>connect user/password as sysdba

$%>startup database_name pfile=file

2) Para iniciar el listener:

$%>lsnrctl
LSNRCTL>stop listener_name
LSNRCTL>start listener_name

3) Confirmar que este completo el registro de los servicios de la base de datos dentro del listener:

LSNRCTL>services listener_name

Tarea 3: Configurar la conexión entre el cliente y el servidor oracle

Ya comprobada la conectividad entre la máquina cliente y el servidor Oracle, ahora vamos a configurar la conexión entre el cliente y el servidor Oracle.

1) Utilizando easy connect, osea, sin uso de un tnsnames.ora:

CONNECT user/password@host_name|ip[:port]/service_name/instance_name

CONNECT user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<host_name>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))

$%>sqlplus /nolog
sqlplus> CONNECT scott/tiger@sales-server:1523/sales_db
sqlplus> CONNECT scott/tiger@10.4.11.77:1523/sales_db

2) Utilizando un nombre de servicio de red configurado en el tnsnames.ora:

 (Linux $ORACLE_HOME/network/admin/tnsnames.ora  o  Windows %ORACLE_HOME%\network\admin\tnsnames.ora)

    sales=
     (DESCRIPTION=
       (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
       (CONNECT_DATA=
         (SERVICE_NAME=sales.us.acme.com)))

$%>sqlplus /nolog
sqlplus> CONNECT scott/tiger@sales

3) Utilizando Oracle Net Configuration Assistant (netca) para configurar el tnsnames.ora y hacer una prueba de conexión:

$%>netca

Elegir la opcion "Local Net Service Name Configuration" y seleccionar las opciones según sea el caso.

Tarea 4: Conectarse a la base de datos

Desde la máquina del cliente ejecutar SQL*Plus y conectarse a la base de datos según la configuración elegida en la tarea 3:

$%>sqlplus /nolog
sqlplus>connect user/password@net_service_name

Nota:
-------
$>    Indica que debe ejecutarse sobre consola de Linux.
%>    Indica que debe ejecutarse sobre consola de MS-Dos.
$%>    Indica que se puede ejecutar tanto en consola de Linux como en consola de MS-Dos.