/*
CREATE OR REPLACE PROCEDURE test_new_8(xsd clob, xml clob, b out VARCHAR2, c out VARCHAR2)
AS LANGUAGE JAVA
NAME 'TestNew_8.testNew_8(oracle.sql.CLOB , oracle.sql.CLOB , java.lang.String [], java.lang.String[])';

*/
create or replace and compile java source named TestNew_8 as

import javax.xml.parsers.SAXParser; import java.sql.Clob; import java.io.StringReader;
import javax.xml.parsers.SAXParserFactory; import java.sql.SQLException; import java.io.Writer;
import java.lang.String; import java.io.Reader;
import org.xml.sax.Attributes; import oracle.sql.CLOB; import java.io.BufferedReader;
import org.xml.sax.SAXException; import java.io.IOException;
import org.xml.sax.helpers.DefaultHandler; import java.io.InputStream;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.ByteArrayInputStream;

import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;
import javax.xml.validation.Validator;

import java.io.File;
import java.io.StringReader;
import javax.xml.transform.stream.StreamSource;

import java.io.FileReader;
import java.lang.SecurityException;

import org.w3c.dom.ls.LSInput;
import org.w3c.dom.ls.LSResourceResolver;

public class TestNew_8 {
public static void testNew_8( oracle.sql.CLOB xsd
, oracle.sql.CLOB xml
, java.lang.String [] b
, java.lang.String [] c
)
throws SQLException, IOException, SAXException {

String start = "start" ;

try {


try {
// File inputFile = new File("//u99//ibsodc//utlfile//ibs//tmp//T_MAP_9962164.log ");

BufferedReader reader = new BufferedReader( new FileReader ("//u99//ibsodc//utlfile//ibs//tmp//T_MAP_9962164.log"));
// BufferedReader reader = new BufferedReader( new FileReader ("//localhost//inst_102_cft_bss.log"));

String line = null;
StringBuilder stringBuilder = new StringBuilder();


while( ( line = reader.readLine() ) != null ) {
stringBuilder.append( line );

}

b[0] = line;
//



SAXParserFactory factory = SAXParserFactory.newInstance();
SAXParser saxParser = factory.newSAXParser();
Reader is = xml.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
// b[0] = "start";
c[0] = "start";


//********************************** 19/12/2019 ***************************************

SchemaFactory schemaFactory = SchemaFactory.newInstance(" http://www.w3.org/2001/XMLSchema");
// StringReader reader_xsd = new StringReader(s);
// StreamSource streamSource = new StreamSource(reader_xsd);
StreamSource streamSourceXSD = new StreamSource(xsd.getCharacterStream());
// Создание валидатора
Schema schema = schemaFactory.newSchema(streamSourceXSD);
Validator validator = schema.newValidator();
StreamSource streamSourceXML = new StreamSource(xml.getCharacterStream());
validator.validate(streamSourceXML);

//***************************************************************************

c[0] = "SAXException Is valid";
}
catch (SAXException | SQLException | IOException ex) {
c[0] = "SAX|SQL|IO Is invalid. Err: " + ex + "!" ;
}

c[0] = "Exception Is valid";
}
catch (Exception e) {
c[0] = "Exception Is invalid. Err: " + e ;
}

}

}


--###################################################


declare
b varchar2(32000);
c varchar2(32000);
v_clob_xml clob;
v_clob_xsd clob;
t number;
begin

v_clob_xsd := '<?xml version="1.0" encoding="UTF-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs=" http://www.w3.org/2001/XMLSchema">
<xs:element name="CIT_REQUEST">
<xs:element name="SYTEM">
<xs:complexType>
<xs:sequence>
<xs:element name="book" maxOccurs="unbounded" minOccurs="0">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute type="xs:string" name="genre" use="optional"/>
<xs:attribute type="xs:date" name="publicationdate" use="optional"/>
<xs:attribute type="xs:string" name="ISBN" use="optional"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';

select c_XML
into v_clob_xml
from ibs.z#mts_xsd_control
where c_num = 2;


test_new_8( v_clob_xsd, v_clob_xml, b, c);
t := length(b);
dbms_output.put_line('...............Вывод test_new_8.........');
dbms_output.put_line('Вывод b...'||b);
dbms_output.put_line('Вывод c...'||c);
dbms_output.put_line('Длина t...'||t);

end;

goliaf,

declare
type t_my_tab is table of varchar2(2000) INDEX BY PLS_INTEGER;
my_tab t_my_tab;
idx integer;
begin


select code
bulk collect into my_tab
from t_map_1 order by user_name asc fetch first 2 row with ties;
idx:= my_tab.first;

while idx is not null
loop
dbms_output.put_line(idx || ' = ' ||my_tab(idx));
idx := my_tab.next(idx);
end loop;



end;

goliaf,
-- Если нашли одну запись выбираем ее
-- Если нашли больше одной записи, то сортируем и выбираем вторую
-- Вывод поля varchar2(4000)
declare
type t_my_tab is table of varchar2(4000) index by pls_integer;
my_tab t_my_tab;
idx integer;

begin

select lead(object_name,1,object_name) over( order by object_id asc )
bulk collect into my_tab
from all_objects
where owner = 'SYS'
order by object_id asc
fetch first 2 row with ties;

dbms_output.put_line('Выбрали: ' || my_tab(1));
idx:= my_tab.first;
while idx is not null
loop
dbms_output.put_line('Массив.....'||idx || ' = ' ||my_tab(idx));
idx := my_tab.next(idx);
end loop;




exception when no_data_found then
dbms_output.put_line(' Данные не найдены ' );
end;

goliaf,
SELECT XMLSERIALIZE(
DOCUMENT
XMLELEMENT(
"ORGANIZATION",
XMLELEMENT(
"DEPARTMENTS",
XMLAGG(
XMLELEMENT(
"DEPARTMENT",
XMLELEMENT(
"NUMBER",
D.DEPTNO
),
XMLELEMENT(
"NAME",
MAX(D.DNAME)
),
XMLELEMENT(
"LOCATION",
MAX(D.LOC)
),
XMLELEMENT(
"EMPLOYEES",
XMLAGG(
XMLELEMENT(
"EMPLOYEE",
XMLELEMENT(
"EMPNO",
E.EMPNO
),
XMLELEMENT(
"ENAME",
E.ENAME
),
XMLELEMENT(
"JOB",
E.JOB
),
XMLELEMENT(
"SAL",
E.SAL
)
)
)
)
)
)
)
)
INDENT
SIZE = 2
) ORGANIZATION
FROM DEPT D,
EMP E
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO