Archive For The “DB – Oracle” Tutte le categorie:

Multiplying Using a Sum in Oracle

Here’s a example of how multiplying using logarithms: select exp(sum(ln(cd_deposito))) from anag_depositi where cd_deposito in (98,92, 95) ln(x*y) = ln(x) + ln(y) ==> exp(ln(x*y)) = exp(ln(x)) + exp(ln(y)) ==> using sum operator to implement multiplication

Versioning

SELECT ps_ordine, ps_variazione_ordine, versions_xid, versions_starttime, versions_endtime, versions_operation FROM sigo_ordini_mercati VERSIONS BETWEEN TIMESTAMP TO_DATE(‘201602181000’, ‘YYYYMMDDHH24MISS’) AND TO_DATE(‘201602181200’, ‘YYYYMMDDHH24MISS’) WHERE dt_consegna=’20160218′ and cd_pdv=1 and cd_deposito=95 and cd_int=’0350000035′ ORDER BY versions_starttime

Currently running SQL statements

SELECT SUBSTR(V$SESSION.USERNAME, 1, 8) USERNAME, V$SESSION.OSUSER OSUSER, V$SQLAREA.DISK_READS DISK_READS, V$SQLAREA.BUFFER_GETS BUFFER_GETS, SUBSTR(V$SESSION.LOCKWAIT, 1, 10) LOCKWAIT, V$SESSION.PROCESS PID, V$SESSION_WAIT.EVENT EVENT, V$SQLAREA.SQL_TEXT SQL, V$SQLAREA.SQL_FULLTEXT SQL_FULL FROM V$SESSION_WAIT, V$SQLAREA, V$SESSION WHERE V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND V$SESSION.SID = V$SESSION_WAIT.SID(+) AND V$SESSION.STATUS = ‘ACTIVE’ AND V$SESSION_WAIT.EVENT != ‘client message’ ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;

DBMS_LOCK (using lock in a stored)

DECLARE v_lockhandle VARCHAR2(200); v_RET INTEGER; BEGIN — INIZIO TEST LOCK IF v_lockhandle IS NULL THEN dbms_lock.allocate_unique(‘LOCK_MOV’, v_lockhandle); END IF; IF dbms_lock.request(v_lockhandle, dbms_lock.x_mode, 0) != 0 THEN SP_INS_LOG_ERROR(v_DS_PROC, 0, ‘Procedura gia” in uso [LOCK]’, null, ‘system’, ‘N’, ‘ORACLE’); RETURN; END IF; — FINE TEST LOCK — LINE 1 — — LINE 2 — — LINE N…

Sum previous row values in oracle

with t as ((SELECT ‘0’ ordine, ‘2’ mtlevel, v.cd_int, NULL dt_tem, v.cd_grm || ‘ ‘ || v.cd_int nr_tem, NULL cd_clifor, v.ds_referenza_interna ds_descrizione, null nr_lotto, ‘E’ cd_tipo_movimento, NVL(gi.ps_netto, 0) qta, NVL(TAB_MOV.ps_movimento_inizioanno, 0) tqta FROM anag_referenze_ext v LEFT OUTER JOIN SIGO_INVENTARIO gi ON gi.cd_deposito = :CD_DEPOSITO AND gi.cd_int = v.cd_int AND gi.dt_inventario = TO_CHAR(TO_DATE(:ADATA), ‘YYYY’) – 1…

Managing errors

BEGIN — … — — statements — — … — RAISE_APPLICATION_ERROR(-20000, ‘Custom error’); /*-20000 .. -20999*/ –RAISE NO_DATA_FOUND; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; DBMS_OUTPUT.put_line(‘Nessun dato trovato’); WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.put_line(sqlerrm); END;

Oracle schema

QUERYING CONSTRAINTS SELECT co.table_name, co.column_name, co.position, c.constraint_name, c.constraint_type, c.index_name, c.status, c.last_change FROM user_constraints c INNER JOIN user_cons_columns co ON c.constraint_name = co.constraint_name AND c.owner = co.owner WHERE c.constraint_type = ‘P’ AND co.table_name= ‘ANAG_AREE’ ORDER BY co.table_name, co.position; [It’s possible to use <all_constraints, all_cons_columns> replacing <user_constraints, user_cons_columns> to view all onwners] C – Check constraint on…

LIST OF DAYS IN THE MONTH

SELECT to_char(dt_giorno, ‘DAY’) ds_giorno, dt_giorno FROM (SELECT TO_DATE(TO_CHAR(LEVEL, ’09’) || TO_CHAR(EXTRACT(MONTH FROM to_date(‘201301′ || ’01’, ‘YYYYMMDD’)), ’09’) || TO_CHAR(EXTRACT(YEAR FROM to_date(‘201301′ || ’01’, ‘YYYYMMDD’)), ‘9999’), ‘DDMMYYYY’) dt_giorno FROM dual WHERE ROWNUM <= EXTRACT(DAY FROM LAST_DAY(to_date(‘201301′ || ’01’, ‘YYYYMMDD’))) CONNECT BY LEVEL = ROWNUM) ORDER BY dt_giorno

SPLIT STRING AND USE IT IN WHERE CLAUSE

CREATE OR REPLACE PROCEDURE SP_TMP_CERCA_CON_SPLIT(p_CD_GRMS varchar2) as BEGIN — PROCEDURA D’ESEMPIO PER FARE UNA “IN” CON VALORI PASSATI IN INPUT COME STRINGA SEPARATI DA VIRGOLA INSERT into TMP_ARRAY(char30) select g.ds_grm from anag_gruppi_merceologici g where g.cd_grm in (select regexp_substr(p_CD_GRMS,'[^,]+’, 1, level) from dual connect by regexp_substr(p_CD_GRMS, ‘[^,]+’, 1, level) is not null); COMMIT; END; https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

SEND MAIL

LINUX SERVER MAIL CLIENT SHELL create or replace procedure SP_SENDMAIL(pPROC varchar2, pTITLE varchar2, pTEXT varchar2, pATTACH varchar2, pHTML varchar2) AS v_COMMAND varchar2(8000); v_FROM varchar2(256); v_TO varchar2(256); v_CC varchar2(256); v_BCC varchar2(256); v_CALL number; v_DS_ERR varchar2(8000); begin — TODO: Questa procedura va modificata drasticamente, utilizzando il package UTL_MAIL [è più recente e nuovo rispetto a UTL_SMTP] v_COMMAND…

Go Top