select xmltransform(xmltype('
100
1
'),xmltype(q'{
}'))
from dual
вторник, 10 мая 2016 г.
Example of using XSLT transformation
четверг, 14 апреля 2016 г.
Work test of DETERMINISTIC function
SQL> set timing off;
SQL> def cnt = 10000000;
SQL>
SQL> create or replace package detest as
2 function test1(p in varchar2) return varchar2 DETERMINISTIC;
3 function test2(p in varchar2) return varchar2 DETERMINISTIC;
4 end;
5 /
Package created.
SQL>
SQL> create or replace package body detest as
2 vCache varchar2( 2000 );
3
4 function test1(p in varchar2) return varchar2
5 as
6 begin
7 if p = 's' then
8 return 't';
9 else
10 return null;
11 end if;
12 end;
13
14 function test2(p in varchar2) return varchar2
15 as
16 begin
17 if p = 's' then
18 if vCache is null then
19 select 't' into vCache from ALL_OBJECTS where rownum < 2;
20 end if;
21 return vCache;
22 else
23 return null;
24 end if;
25 end;
26
27 end;
28 /
Package body created.
SQL>
SQL> set timing on;
SQL>
SQL> declare
2 vDummy varchar2(2000);
3 begin
4 for i in 1..&cnt
5 loop
6 vDummy := detest.test1('z');
7 vDummy := detest.test1('s');
8 end loop;
9 end;
10 /
old 4: for i in 1..&cnt
new 4: for i in 1..10000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.25
SQL>
SQL> declare
2 vDummy varchar2(2000);
3 begin
4 for i in 1..&cnt
5 loop
6 vDummy := detest.test2('z');
7 vDummy := detest.test2('s');
8 end loop;
9 end;
10 /
old 4: for i in 1..&cnt
new 4: for i in 1..10000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
SQL>
SQL> set timing off;
SQL>
SQL> create or replace package detest as
2 function test1(p in varchar2) return varchar2;
3 function test2(p in varchar2) return varchar2;
4 end;
5 /
Package created.
SQL>
SQL> set timing on;
SQL>
SQL> declare
2 vDummy varchar2(2000);
3 begin
4 for i in 1..&cnt
5 loop
6 vDummy := detest.test1('z');
7 vDummy := detest.test1('s');
8 end loop;
9 end;
10 /
old 4: for i in 1..&cnt
new 4: for i in 1..10000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.77
SQL>
SQL> declare
2 vDummy varchar2(2000);
3 begin
4 for i in 1..&cnt
5 loop
6 vDummy := detest.test2('z');
7 vDummy := detest.test2('s');
8 end loop;
9 end;
10 /
old 4: for i in 1..&cnt
new 4: for i in 1..10000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.46
SQL> spool off
пятница, 18 марта 2016 г.
How to generate hierarchy query to string
select listagg(txt) within group (order by rn)
from (
select
isn, parentisn, rn,
shortname ||
decode(nxtlvl, lvl + 1, '(', '') ||
decode(nxtlvl, lvl, ',', '') ||
decode(nxtlvl, lvl + 1, '', lvl, '', ltrim(lpad(')', lvl - nxtlvl, ')'))) txt
from (
select t.*, lead(lvl) over(order by rn) nxtlvl
from (
select isn, parentisn, shortname, rownum rn, level lvl
from dicti d
start with isn = 8167
connect by prior isn = parentisn
order siblings by shortname) t))
пятница, 19 февраля 2016 г.
Out SYS_REFCURSOR into console
create or replace PROCEDURE grachevcur(
p_refcursor IN OUT SYS_REFCURSOR
)
AS
l_desc DBMS_SQL.DESC_TAB ;
l_cols BINARY_INTEGER ;
l_cursor BINARY_INTEGER ;
v_varchar2 VARCHAR2( 4000 ) ;
v_number NUMBER ;
v_date DATE ;
l_data varchar2( 32767 ) ;
l_columnValue VARCHAR2( 32767 ) ;
l_processedRows Number := 0;
BEGIN
/* Convert refcursor "parameter" to DBMS_SQL cursor... */
l_cursor := DBMS_SQL.TO_CURSOR_NUMBER( p_refcursor );
/* Describe the cursor... */
DBMS_SQL.DESCRIBE_COLUMNS( l_cursor, l_cols, l_desc );
/* Define columns to be fetched. We're only using V2, NUM, DATE for example...
for a complete list of the col_types this link is accessible.
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45504
http://forums.oracle.com/forums/thread.jspa?threadID=912475
if not a usable type, will throw new exception
*/
FOR i IN 1 .. l_cols LOOP
IF l_desc(i).col_type = 2 THEN
DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_number);
ELSIF l_desc(i).col_type = 12 THEN
DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_date);
ELSif l_desc(i).col_type = 01 or l_desc(i).col_type = 96 then
DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_varchar2, 4000);
else
--raise an exception if the user's query contains a datatype not (yet) supported by this procedure
RAISE_APPLICATION_ERROR(-20000, 'Invalid Data Type for conversion to delimited file. {' || l_desc(i).col_name || '}');
END IF;
END LOOP;
/* -- print out the column names if desired
FOR i IN 1 .. l_cols LOOP
dbms_output.put_line('** ' || l_desc(i).col_name) ;
END LOOP;
*/
/* Fetch all data... */
WHILE DBMS_SQL.FETCH_ROWS(l_cursor) > 0 LOOP
dbms_output.put_line('LINE: ' || l_processedRows || '');
FOR i IN 1 .. l_cols LOOP
if l_desc(i).col_type = 12 THEN --we are in a date
DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_date);
v_varchar2 := to_char(v_date , 'dd-MON-yyyy' ) ;
elsif l_desc(i).col_type = 2 THEN --we are in a number
DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_number);
v_varchar2 := to_char(v_number) ;
else --treat it as a string (should be varchar2,char,etc)
DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_varchar2);
IF v_varchar2 IS NOT NULL THEN
v_varchar2 := '"' || v_varchar2 || '"' ;
ELSE
v_varchar2 := '';
END IF ;
end if ;
dbms_output.put_line(' ' ||l_desc(i).col_name || '=>' || v_varchar2) ;
END LOOP;
l_processedRows := l_processedRows + 1 ;
END LOOP;
dbms_sql.close_cursor(l_cursor);
dbms_output.put_line('I found and processed ' || l_processedRows || ' rows .');
END;
/
Подписаться на:
Сообщения (Atom)