Plsql developer blog: Installing open Office

Example1
==============
SQL> select dbms_xmlgen.getxml(
'select
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
from emp
where deptno=10')
"XML OUTPUT"
from dual;

XML OUTPUT
---------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-JUN-81</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
?continued?
</ROWSET>

Example2
==============
QL> select employee_id, first_name, last_name, phone_number
2 from employees where rownum < 6

EMPLOYEE_ID FIRST_NAME LAST_NAME PHONE_NUMBER
----------- -------------------- ------------------------- --------------------
100 Steven King 515.123.4567
101 Neena Kochhar 515.123.4568
102 Lex De Haan 515.123.4569
103 Alexander Hunold 590.423.4567
104 Bruce Ernst 590.423.4568

set pages 0
set linesize 150
set long 9999999
set head off

SQL> select dbms_xmlgen.getxml('select employee_id, first_name,
2 last_name, phone_number from employees where rownum < 6') xml
3 from dual

XML OUTPUT
---------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPLOYEE_ID>100</EMPLOYEE_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>101</EMPLOYEE_ID>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>102</EMPLOYEE_ID>
<FIRST_NAME>Lex</FIRST_NAME>
<LAST_NAME>De Haan</LAST_NAME>
<PHONE_NUMBER>515.123.4569</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>103</EMPLOYEE_ID>
<FIRST_NAME>Alexander</FIRST_NAME>
<LAST_NAME>Hunold</LAST_NAME>
<PHONE_NUMBER>590.423.4567</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>104</EMPLOYEE_ID>
<FIRST_NAME>Bruce</FIRST_NAME>
<LAST_NAME>Ernst</LAST_NAME>
<PHONE_NUMBER>590.423.4568</PHONE_NUMBER>
</ROW>
</ROWSET>

Advanced Uses for dbms_xmlgen
----------------------------------

SQL> select department_id, department_name,
2 cursor(select first_name, last_name
3 from employees e
4 where e.department_id = d.department_id) emp_row
5 from departments d
6* where rownum < 4

DEPARTMENT_ID DEPARTMENT_NAME EMP_ROW
------------- ------------------------------ --------------------
10 Administration CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

FIRST_NAME LAST_NAME
-------------------- -------------------------
Jennifer Whalen

20 Marketing CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

FIRST_NAME LAST_NAME
-------------------- -------------------------
Michael Hartstein
Pat Fay

30 Purchasing CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

FIRST_NAME LAST_NAME
-------------------- -------------------------
Den Raphaely
Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares

XML Output
=================
SQL> select dbms_xmlgen.getxml('
2 select department_id, department_name,
3 cursor(select first_name, last_name
4 from employees e
5 where e.department_id = d.department_id) emp_row
6 from departments d
7 where rownum < 4
8* ') from dual

<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<EMP_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Jennifer</FIRST_NAME>
<LAST_NAME>Whalen</LAST_NAME>
</EMP_ROW_ROW>
</EMP_ROW>
</ROW>
<ROW>
<DEPARTMENT_ID>20</DEPARTMENT_ID>
<DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
<EMP_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Michael</FIRST_NAME>
<LAST_NAME>Hartstein</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Pat</FIRST_NAME>
<LAST_NAME>Fay</LAST_NAME>
</EMP_ROW_ROW>
</EMP_ROW>
</ROW>
<ROW>
<DEPARTMENT_ID>30</DEPARTMENT_ID>
<DEPARTMENT_NAME>Purchasing</DEPARTMENT_NAME>
<EMP_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Den</FIRST_NAME>
<LAST_NAME>Raphaely</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Alexander</FIRST_NAME>
<LAST_NAME>Khoo</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Shelli</FIRST_NAME>
<LAST_NAME>Baida</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Sigal</FIRST_NAME>
<LAST_NAME>Tobias</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Guy</FIRST_NAME>
<LAST_NAME>Himuro</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Karen</FIRST_NAME>
<LAST_NAME>Colmenares</LAST_NAME>
</EMP_ROW_ROW>
</EMP_ROW>
</ROW>
</ROWSET>

======================================================================

Tips for using dbms_xmlgen
---------------------------------
The dbms_xmlgen package can be extremely useful for quick retrieval of web records.
Simply make a page that accepts input, such as DEPARTMENT_ID, and passes it into your SQL query.
If you direct the SQL*Plus response directly to the screen, and you have an easy XML display program.

The best part of dbms_xmlgen is for quickly formatting reports.
XML Publisher will accept standard XML and allow you to form extremely
detailed reports using templates made in Microsoft Word.

With standard SQL queries tagged using dbms_xmlgen,
XML Publisher you can have a full reporting suite that easily pulls Oracle data with XML tags,
forms it into a PDF, DOC, XLS, or HTML report, and distributes your report via e-mail using its native e-mail capabilities.
This is far easier than the traditional utl_mail or utl_smtp e-mail packages which required specialized invocation code.

Description of the DBMS_XMLGEN package:
-----------------------------------------

PROCEDURE DBMS_XMLGEN.CLEARBINDVALUES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN

PROCEDURE DBMS_XMLGEN.CLOSECONTEXT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN

FUNCTION DBMS_XMLGEN.CONVERT RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
XMLDATA VARCHAR2 IN
FLAG NUMBER IN DEFAULT

FUNCTION DBMS_XMLGEN.CONVERT RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
XMLDATA CLOB IN
FLAG NUMBER IN DEFAULT

FUNCTION DBMS_XMLGEN.GETNUMROWSPROCESSED RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN

PROCEDURE DBMS_XMLGEN.GETXML
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
TMPCLOB CLOB IN/OUT
DTDORSCHEMA NUMBER IN DEFAULT

FUNCTION DBMS_XMLGEN.GETXML RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
DTDORSCHEMA NUMBER IN DEFAULT

FUNCTION DBMS_XMLGEN.GETXML RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQLQUERY VARCHAR2 IN
DTDORSCHEMA NUMBER IN DEFAULT

PROCEDURE DBMS_XMLGEN.GETXMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
TMPXMLTYPE XMLTYPE IN/OUT
DTDORSCHEMA NUMBER IN DEFAULT

FUNCTION DBMS_XMLGEN.GETXMLTYPE RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
DTDORSCHEMA NUMBER IN DEFAULT

FUNCTION DBMS_XMLGEN.GETXMLTYPE RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQLQUERY VARCHAR2 IN
DTDORSCHEMA NUMBER IN DEFAULT

FUNCTION DBMS_XMLGEN.NEWCONTEXT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERYSTRING VARCHAR2 IN

FUNCTION DBMS_XMLGEN.NEWCONTEXT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERYSTRING REF CURSOR IN

FUNCTION DBMS_XMLGEN.NEWCONTEXTFROMHIERARCHY RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERYSTRING VARCHAR2 IN

PROCEDURE DBMS_XMLGEN.REMOVEXSLTPARAM
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
NAME VARCHAR2 IN

PROCEDURE DBMS_XMLGEN.RESTARTQUERY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN

PROCEDURE DBMS_XMLGEN.SETBINDVALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
BINDNAME VARCHAR2 IN
BINDVALUE VARCHAR2 IN

PROCEDURE DBMS_XMLGEN.SETCHECKINVALIDCHARS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
CHK BOOLEAN IN

PROCEDURE DBMS_XMLGEN.SETCONVERTSPECIALCHARS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
REPLACE BOOLEAN IN

PROCEDURE DBMS_XMLGEN.SETINDENTATIONWIDTH
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
WIDTH NUMBER IN

PROCEDURE DBMS_XMLGEN.SETMAXROWS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
MAXROWS NUMBER IN

PROCEDURE DBMS_XMLGEN.SETNULLHANDLING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
FLAG NUMBER IN

PROCEDURE DBMS_XMLGEN.SETPRETTYPRINTING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
PP BOOLEAN IN

PROCEDURE DBMS_XMLGEN.SETROWSETTAG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
ROWSETTAGNAME VARCHAR2 IN

PROCEDURE DBMS_XMLGEN.SETROWTAG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
ROWTAGNAME VARCHAR2 IN

PROCEDURE DBMS_XMLGEN.SETSKIPROWS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
SKIPROWS NUMBER IN

PROCEDURE DBMS_XMLGEN.SETXSLT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
STYLESHEET CLOB IN

PROCEDURE DBMS_XMLGEN.SETXSLT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
STYLESHEET XMLTYPE IN

PROCEDURE DBMS_XMLGEN.SETXSLT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
URI VARCHAR2 IN

PROCEDURE DBMS_XMLGEN.SETXSLTPARAM
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
NAME VARCHAR2 IN
VALUE VARCHAR2 IN

PROCEDURE DBMS_XMLGEN.USEITEMTAGSFORCOLL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN

PROCEDURE DBMS_XMLGEN.USENULLATTRIBUTEINDICATOR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CTX NUMBER IN
ATTRIND BOOLEAN IN DEFAULT

===================================================================================
DBMS_XMLGEN.convert
===================================================================================
DECLARE
v_string VARCHAR2 (32000);
v_converted VARCHAR2 (32000);
v_noConverted VARCHAR2 (32000);
BEGIN
SELECT DBMS_XMLGEN.getXML (
'SELECT *
FROM alm_mesa
WHERE cod_mesa IN (''100000125'', ''100000126'')')
INTO v_string
FROM DUAL;

v_converted := DBMS_XMLGEN.CONVERT (v_string, 0);
DBMS_OUTPUT.PUT_LINE ('v_converted = ' || v_converted);

v_NoConverted := DBMS_XMLGEN.CONVERT (v_string, 1);
DBMS_OUTPUT.PUT_LINE ('v_NoConverted = ' || v_NoConverted);
END;

=======================================================================================

Plsql developer blog

Thursday, 30 July 2015

Installing open Office

No comments:

Post a Comment