oracle-tricks.md

oracle / convert date to epoch

-- https://dbfiddle.uk/_DO-kBb6
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

WITH test_data ( date_value ) AS (
  SELECT DATE '1970-01-01' + INTERVAL '3' HOUR FROM DUAL
)
SELECT date_value,
       ( CAST(
           FROM_TZ(
             CAST( date_value AS TIMESTAMP ),     -- Cast to timestamp
             'Europe/Berlin'                      -- Convert to expected TZ
           )
           AT TIME ZONE 'UTC'                     -- Convert TZ to UTC
           AS DATE                                -- Cast back to DATE data type
         )
         - DATE '1970-01-01'
       )*24*60*60 AS epoch_time
FROM   test_data

select distinct session_id, ( CAST(
           FROM_TZ(
             CAST( mydate AS TIMESTAMP ),
             'America/Buenos_Aires'
           )
           AT TIME ZONE 'UTC'
           AS DATE
         )
         - DATE '1970-01-01'
       )*24*60*60*1000 AS epoch_time  from NC_KML_RECONS_GEOMETRY where session_id=9154377733413532798
       
       --PKGUTILS.ID_TO_DATE(session_id)

try to convert geometry

select '2002||8307||NULL|NULL|NULL||1|1003|1||'|| REPLACE(REPLACE( SUBSTR(s.geometry, 0, LENGTH(s.geometry) - 1), ';',','),',','|') ||'||'  from NC_KML_RECONS_GEOMETRY s where s.object_id = 9151723751313890716;

convert to geom and to wkt

select SDO_UTIL.TO_WKTGEOMETRY(geom) from (
   select SDO_UTIL.FROM_CLOB('2002||8307||NULL|NULL|NULL||1|2|1||'|| REPLACE(REPLACE( SUBSTR(s.geometry, 0, LENGTH(s.geometry) - 1), ';',','),',','|') ||'||') geom  from NC_KML_RECONS_GEOMETRY s where s.object_id = 9151723751313890716
)

geojson

{
    "type": "LineString",
    "coordinates": [
        [ -58.910790, -34.461534 ],
        [ -58.910790, -34.885854 ],
        [ -58.899804, -34.890380 ],
        [ -58.073083, -34.453571 ],
        [ -58.071709, -34.461534 ]
    ]
}

wkt

LINESTRING (-58.91079 -34.461534, -58.91079 -34.885854, -58.899804 -34.89038, -58.073083 -34.453571, -58.071709 -34.461534)

select ''||session_id||';'||epoch_time||';'||object_id||';'||length||';'||update_count as csv from (
    select 
        t.session_id session_id, 
        ( CAST(
           FROM_TZ(
             CAST( PKGUTILS.ID_TO_DATE(session_id) AS TIMESTAMP ),
             'America/Buenos_Aires'
           )
           AT TIME ZONE 'UTC'
           AS DATE
         )
         - DATE '1970-01-01'
       )*24*60*60*1000 AS epoch_time,
        t.object_id object_id,
        SDO_GEOM.SDO_LENGTH(t.geometry,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, 10),MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, 10))) length,
        t.update_count update_count 
   from (
        select s.session_id session_id, r.object_id object_id, SDO_UTIL.FROM_CLOB('2002||8307||NULL|NULL|NULL||1|2|1||'|| REPLACE(REPLACE( SUBSTR(r.geometry , 0, LENGTH(r.geometry ) - 1), ';',','),',','|') ||'||') geometry, s.update_count
        from NC_KML_RECONS_GEOMETRY r,
             (
                select max(session_id) keep(dense_rank first order by session_id desc) session_id, object_id, count(session_id) update_count  from NC_KML_RECONS_GEOMETRY s where session_id > 100000 group by object_id
            ) s
            where r.object_id = s.object_id and r.session_id = s.session_id and r.sdo_type = 2002
    ) t 
    order by length desc, update_count desc
);

Sql plus. Prevent line cropping

SET LONG 200000
set LONGCHUNKSIZE 200000
set LINESIZE 32767

Connect to sql plus:

sqlplus "user/pass@(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=...))(ADDRESS=(PROTOCOL=TCP)(HOST=...)(PORT=...)))" 

Script in sql plus:

spool c:\out.csv;
select abc from xyz;
spool off

Long sql:

-- variant 1
with
  function wait_for(p_s number) return number is
  begin
    dbms_lock.sleep(p_s);
    return 1;
  end;
select wait_for(10) from dual

-- variant 1.1
CREATE FUNCTION wait_for(p_s number) 
   RETURN NUMBER 
   IS 
   BEGIN 
      dbms_lock.sleep(p_s);
      return 1;
    END;
/

select wait_for(2) from dual;

-- variant 2
select (
select count(*) from xmltable('1 to 1000'), xmltable('1 to 1000')
) cnt
from xmltable('1 to 1000')
, xmltable('1 to 1000')

-- variant 3
begin
while systimestamp < … loop
null;
end;
end;

-- variant 4
create or replace function alch_sleep_test return number is
begin
dbms_lock.sleep(10);
return 1;
end;
/

select alch_sleep_test from dual;


-- variant 5
select * from blablalbla for update

Monitor active requests

while true
do
sqlplus -S "AAAAA/BBBBB@(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DBG125))(ADDRESS=(PROTOCOL=TCP)(HOST=CCCCC.netcracker.com)(PORT=1524)))" << 'EOF'
  SET FEEDBACK OFF;
  SET SERVEROUTPUT ON;
  SET TERMOUT OFF; 
  DECLARE
      TYPE ROWTYPE IS RECORD (
           status     v$session.status%TYPE,
           module     v$session.module%TYPE,
           client     v$session.CLIENT_IDENTIFIER%TYPE
      );
      TYPE ROWSTYPE IS TABLE OF ROWTYPE; 
      row ROWTYPE;
      rows ROWSTYPE;
  BEGIN
     SELECT sess.status as status, sess.module module, sess.CLIENT_IDENTIFIER as client 
     BULK COLLECT INTO rows
     FROM v$session sess
     WHERE sess.type = 'USER' AND sess.status = 'ACTIVE' AND sess.module like '%mvt%';
     IF rows.count() != 0
     then
        dbms_output.put_line( '--------------------------' || CURRENT_TIMESTAMP());
        FOR i IN rows.FIRST .. rows.LAST LOOP
           row := rows(i);
           dbms_output.put_line( row.status || '   ' || row.module || '   ' || row.client);
        END LOOP;    
     ELSE
        dbms_output.put_line('No requests');     
     END IF;
  END;
  /
  EXIT; 
EOF
sleep 1;
done

select * from (
SELECT s.object_id, s.geometry, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(s.geometry, 0.00000000000000001) res
FROM nc_spatial s where s.geometry.SDO_GTYPE = 2003
) where res like '13348%'