/*
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

goliaf,
--1 инициализация wm_concat
--declare r number := rtl.open(); begin executor.set_context('WM_CONCAT_DELIM',chr(10)); end;
/*
create or replace procedure wm_concat_init(p_separator in varchar2 default ',')
is
r number := rtl.open();
begin
executor.set_context('WM_CONCAT_DELIM', p_separator);
end;
*/
/*
begin
wm_concat_init(chr(10));
end;
/
*/
--2 скрипт получения объектов

select
'VER2'||chr(10)||
'REM Список элементов'||chr(10)||
'REM IBS@IBSODC'||chr(10)||
'REM Версия ТЯ: 7.4.5.4'||chr(10)||
'REM Версия Администратора проектов: 6.93.0.107'||chr(10)||
''||chr(10)||
wm_concat(c_type_obj||' '||c_class||' '||regexp_replace(c_obj,'chr(13)|chr(10)',' ')) c_pck
from
(

select
c_modified_time as c_modified_time,
user_modified,
c_class_id as c_type_obj,
meth_class as c_class,
case
when c_class_id in ('ATR','IDX', 'TYPE') then
c_text
else meth_name
end as c_obj,
c_text
from
(
select
x.sname as c_class_meth
, x.c_modified_time as c_modified_time
, x.Class_Id as c_Class_Id
, x.meth_class as meth_class
, x.meth_name as meth_name
, x.action as c_action
, x.topic as c_topic
, x.what as c_what
, x.text as c_text
, user_modified

from
(
with ls as
(
select /*+ MATERIALIZE*/ *
from
(
SELECT
a1.CODE ID, a1.O_TYPE Class_Id,
m.user_modified user_modified,
a1.USER_ID C_USER,
a1.TIME C_TIME,
nvl(m.modified, a1.TIME) c_modified_time,
case when a1.WHAT in ('DROP','DELETE') then 'DROP' when a1.WHAT in ('CREATE','INSERTED') then 'ADD' when a1.WHAT in ('ERROR','COMPILE','UPDATED','REBUILDED') then 'EDIT' else 'OTHER' end ACTION,
a1.TOPIC TOPIC,
a1.SNAME SNAME,
a1.WHAT WHAT,
a1.TEXT TEXT,
a1.meth_class,
a1.meth_name
from (
select b1.TIME TIME, b1.USER_ID USER_ID, b1.CODE CODE, b1.TOPIC TOPIC, b1.O_TYPE O_TYPE, b1.SNAME SNAME, b1.TEXT TEXT, b1.WHAT WHAT, FIRST_VALUE(b1.WHAT) over (partition by b1.USER_ID, b1.O_TYPE, b1.CODE order by case when b1.WHAT in ('DROP','DELETE','CREATE','INSERTED') then 1 when b1.WHAT in ('ERROR','COMPILE','UPDATED','REBUILDED') then 2 else 5 end asc, b1.TIME desc) MADE, FIRST_VALUE(b1.TIME) over (partition by b1.USER_ID, b1.O_TYPE, b1.CODE, b1.WHAT order by b1.TIME desc) MADETIME
, trim(regexp_replace(replace(replace(b1.SNAME, chr(10),''), chr(13), ''),'([a-zA-Z0-9_#]*)\.([a-zA-Z0-9_#]*)(.*)|( - .*)','\1')) meth_class
, trim(regexp_replace(replace(replace(b1.SNAME, chr(10),''), chr(13), ''),'([a-zA-Z0-9_#]*)\.([a-zA-Z0-9_#]*)(.*)|( - .*)','\2')) meth_name
from (
select c1.TIME TIME, c1.USER_ID USER_ID, c1.METHOD_ID CODE, 'M' TOPIC, 'METH' O_TYPE, c1.SNAME SNAME, c1.NAME TEXT, c1.ACTION WHAT
from DIARY_METHODS c1
union all
select d1.TIME TIME, d1.USER_ID USER_ID, d1.METHOD_ID CODE, d1.TOPIC TOPIC, 'METH' O_TYPE, d1.MNAME SNAME, d1.TEXT TEXT, d1.ACTION WHAT
from DIARY_PARAM_VARS d1
union all
select e1.TIME TIME, e1.USER_ID USER_ID, e1.METHOD_ID CODE, 'F' TOPIC, 'METH' O_TYPE, e1.MNAME SNAME, e1.NAME TEXT, e1.ACTION WHAT
from DIARY_FORMS e1
union all
select f1.TIME TIME, f1.USER_ID USER_ID, f1.CRITERIA_ID CODE, 'C' TOPIC, 'CRIT' O_TYPE, f1.SNAME SNAME, f1.NAME TEXT, f1.ACTION WHAT
from DIARY_CRITERIA f1
union all
select g1.TIME TIME, g1.USER_ID USER_ID, g1.CLASS_ID CODE, 'B' TOPIC, 'TYPE' O_TYPE, g1.CLASS_ID SNAME, g1.NAME TEXT, g1.ACTION WHAT
from DIARY_STORAGE g1
union all
select h1.TIME TIME, h1.USER_ID USER_ID, h1.CLASS_ID CODE, h1.TOPIC TOPIC, 'IDX' O_TYPE, h1.CLASS_ID SNAME, h1.TEXT TEXT, h1.ACTION WHAT
from DIARY_STORAGE_EXT h1
union all
select i1.TIME TIME, i1.USER_ID USER_ID, i1.CLASS_ID CODE, i1.TOPIC TOPIC, 'ATR' O_TYPE, i1.CLASS_ID SNAME, i1.TEXT TEXT, i1.ACTION WHAT
from DIARY_ATTRIBUTES i1
) b1
where (&P_USER_BEG is null or UPPER(b1.USER_ID) = UPPER(&<name = "P_USER_BEG" default ="'IBS.MBelin'"> ))
and b1.time >= &<name = "P_DATE_BEG" default ="to_date('25/09/2015','DD/MM/YYYY')">
order by b1.USER_ID,b1.O_TYPE,b1.CODE,b1.TIME
) a1 , methods m
where a1.WHAT = a1.MADE and a1.MADETIME = a1.TIME
and a1.meth_class = m.class_id (+)
and a1.meth_name = m.short_name (+)
and (m.user_modified is null or instr(a1.USER_ID, m.user_modified) > 0)
)
--where trunc(c_time) = trunc(c_modified_time)
-- where what <> 'COMPILE'
order by c_time
)
select * from ls
where (&<name = "P_NUM_ZI" default ="''"> is null or (class_id <> 'METHODS' or (class_id = 'METHODS' and exists (select 1 from sources b where b.name = ls.id and instr(b.text, &P_NUM_ZI) > 0 ))))
-- не дистрибутив
and not exists(select 1 from OBJECTS_OPTIONS zz where zz.class_id = ls.meth_class and zz.short_name = ls.meth_name and rownum = 1)
and ls.c_modified_time >= &<name = "P_DATE_BEG" default ="to_date('26/09/2017','DD/MM/YYYY')">
order by c_modified_time desc
) x
-- group by x.sname
)
order by c_modified_time desc
)

test