Tips de Oracle Mx: 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

12 comentarios:

Anónimo16 de diciembre de 2010, 10:22
Hola john, me ha gustado este uso del SPOOL del sql*plus.
Y me pregunto si sería posible añadir una nueva columna numérica y fórmula en el pie del fichero "XLS.XML" con el sumatorio con el suma total.
He añadido una nueva columna al EXCEL y la interpreta como un Texto y no como un numérico.

Gracias.
Javier
ResponderEliminar
Respuestas
JuanM21 de diciembre de 2010, 22:47
Hola Javier, es sencillo para poner una columna con tipo de dato numérico, sólo debes poner
''||

Saludos!
ResponderEliminar
Respuestas
Anónimo10 de enero de 2013, 11:05
Muchísimas gracias por esta maravilla de código!! me ha costado un poco implementar luego el tema del declare, begin y end pero conseguido.
;)
ResponderEliminar
Respuestas
Unknown17 de mayo de 2013, 18:24
hola como puedo hacer para que muestre los datos de cada columna, osea que haga lo mismo solo que en vez de poner los tablespace, table_name ... ponga los datos de cada columna?
ResponderEliminar
Respuestas
john je@n17 de mayo de 2013, 20:12
Buen dia Fito, postes tu consulta y te ayudo a implementarlo.

saludos
ResponderEliminar
Respuestas
Unknown18 de mayo de 2013, 17:29
es justo en esta parte

SELECT
''||
''||table_name||'',
''||tablespace_name||'',
''||last_analyzed||''
||''
FROM user_tables
/

ahi tomas los campos de la tabla de dicho usuario y los muestra, justamente el tablename, el tables_space ,etc. Pero quisiera saber como poder hacer para que en vez de mostrar eso mostrar los datos de una columna en espeficica. Por ejemplo que la columna sea DIAS y que muestre los DATOS de esa columna de la tabla del usuario
ResponderEliminar
Respuestas
Unknown18 de mayo de 2013, 17:30
mmm no salio completo pero es la parte donde muestra los table name y tables space
ResponderEliminar
Respuestas
Unknown18 de mayo de 2013, 19:24
o mejor dicho mostrar los datos de una tabla del usuario que escoja a la hora de correre el xls.sql desde el sqlplus. Y no la de user tables
ResponderEliminar
Respuestas
Unknown19 de mayo de 2013, 0:27
Hola ya pude resolver, gracias por tu codigo sirvio de mucho, cualquier cosa te pregunto, GRACIAS!!
ResponderEliminar
Respuestas
Unknown20 de septiembre de 2015, 14:30
hola. este codigo es muy efectivo pero cambio los valores para adaptarlo a mi tabla me sale un error de log. Como puedo solucionar esto? de ante mano gracias
ResponderEliminar
Respuestas
Unknown23 de noviembre de 2016, 7:54
como hago desde un procedimiento ?
ResponderEliminar
Respuestas

Añadir comentario

Tips de Oracle Mx

viernes, 3 de diciembre de 2010

SQL*Plus con salida XLS

12 comentarios:

Seguidores

Páginas recomendadas