Wednesday, August 29, 2012

pl sql

========================comma seperated to rows OR used IN clause==========================



WITH T AS (SELECT &V_USERID AS USERID FROM DUAL)
SELECT TRIM(REGEXP_SUBSTR (USERID, '[^,]+', 1, LEVEL)) ID FROM T
CONNECT BY regexp_substr(USERID, '[^,]+', 1, level) is not null
/

Enter value for v_userid: '1000,10001,10002,10003,10004'

ID
-------
1000
10001
10002
10003
10004



===========================converting days \into week====================================


SELECT TRUNC(TO_DATE('sysdate'),'d'),
TRUNC(TO_DATE('sysdate'),'Y'),
7+TRUNC(TO_DATE('sysdate'),'d')-
TRUNC(TO_DATE('sysdate'),'Y') AS S,
CEIL((7+(TRUNC(TO_DATE('sysdate'),'d'))-
TRUNC(TO_DATE('sysdate'),'Y'))/7) as a FROM DUAL;


=====================================Changing Timezone=================================


set serveroutput on size 1000000;

declare
 v_sql          varchar(10000);
BEGIN
   FOR t IN (SELECT ut.table_name, utc.column_name
               FROM user_tab_columns utc join user_tables ut on utc.table_name=ut.table_name
              WHERE utc.data_type = 'DATE' and utc.table_name not like 'AD_%')
   LOOP
           v_sql:=         'UPDATE '
                        || t.table_name
                        || ' SET '
                        || t.column_name
                        || ' = TO_DATE(TO_CHAR((FROM_TZ(CAST('
                        || t.column_name
                        || ' AS TIMESTAMP), ''+5:30'') AT TIME ZONE ''&TO_TIMEZONE'')'
                        || ',''DD-MM-RRRR HH24:MI:SS''),''DD-MM-RRRR HH24:MI:SS'')';
                       
              EXECUTE IMMEDIATE v_sql;
              COMMIT;
              DBMS_OUTPUT.put_line('..'||v_sql||';');
              DBMS_OUTPUT.put_line('COMMIT;');
                       
   END LOOP;
END;


=======================================================================================
Posted by at

6 comments:

  1. Gurjeet SinghJanuary 29, 2013 at 4:08 PM


    with t1 as( select 10*a as aa ,10*(a+1) as bb, rownum as num1 ,date1
    from (select row_number() over (order by 1) as a,sysdate-120 as date1
    from dual connect by level < (select round(count(date_1)/10) from t)))
    select date1+aa , date1+bb from t1

    ReplyDelete
    Replies
    1. Gurjeet SinghJanuary 29, 2013 at 4:21 PM

      ::::::::::::::::table for above query:::::::::::::::::::
      create table t (date_1 date, price number(10));

      BEGIN
      FOR i IN 1 .. 225 LOOP
      insert into t (date_1 , price ) values (TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)),DBMS_RANDOM.value(0,26));
      END LOOP;
      END;
      /

      update t set date_1 = to_date(sysdate-365);

      Delete
  2. AnonymousJanuary 29, 2013 at 6:04 PM

    ---------------------------------
    -----by j4info.blogspot.com
    ---------------------------------


    select aa,bb,(select sum(price) from t where date_1 between tttg.aa and tttg.bb ) as total_price from
    ( select (date1 + (10*a)) as aa ,(date1 + (10*(a+1))) as bb, rownum as num1 ,date1
    from (select row_number() over (order by 1) as a,sysdate-120 as date1
    from dual connect by level < 13)) tttg

    ReplyDelete
  3. Navneet SinghJanuary 30, 2013 at 5:23 PM

    select aa, bb, sum(price), min(price), max(price)
    from
    (select (date1 + (10*a)) as aa ,(date1 + (10*(a+1))) as bb, rownum as num1 ,date1
    from
    (select a-1 as a, date1
    from
    (select row_number() over (order by 1) as a,sysdate-120 as date1
    from
    dual connect by level < 14))) tttg,
    (select price, date1 as dt
    from
    t) b
    where b.dt between tttg.aa and tttg.bb
    group by aa, bb
    order by 1;

    ReplyDelete
  4. Navneet SinghJanuary 31, 2013 at 6:24 PM

    Postgresql for above

    select aa, bb, sum(price), min(price), max(price)
    from
    (select (dt+cast((10*a) as int)) as aa ,(dt + cast((10*(a+1)) as int)) as bb--, rownum as num1 ,date1
    from
    (select ac-1 as a, dt
    from
    (select row_number() over (order by 1) as ac, current_date-120 as dt from t limit 13) as at) as bt) as ct,
    (select price, date1 as dt
    from
    t) b
    where b.dt between ct.aa and ct.bb
    group by aa, bb
    order by 1;

    ReplyDelete
  5. AnonymousFebruary 22, 2013 at 6:28 PM



    CREATE TABLE STATS_USER
    (
    USER_ID VARCHAR2(30),
    SESSION_ID NUMBER(8),
    HOST VARCHAR2(30),
    LOGON_TIME TIMESTAMP,
    LOGOFF_TIME TIMESTAMP
    );

    CREATE OR REPLACE TRIGGER
    logoff_audit_trigger
    BEFORE LOGOFF ON DATABASE
    DECLARE
    puser VARCHAR2(20) := USER;
    pcount NUMBER(2);
    BEGIN
    SELECT COUNT(1) INTO pcount FROM stats_user WHERE user_id = puser;
    IF pcount>=1 THEN
    UPDATE stats_user SET LOGOFF_TIME = SYSDATE WHERE user_id= puser;
    ELSE
    INSERT INTO stats_user VALUES(
    user,
    sys_context('USERENV','SESSIONID'),
    sys_context('USERENV','HOST'),
    null,
    sysdate
    );
    end if;
    end;
    /


    create or replace trigger
    logon_audit_trigger
    BEFORE LOGOFF ON DATABASE
    declare
    puser varchar2(20) := user;
    pcount number(2);
    Begin
    select count(1) into pcount from stats_user where user_id = puser;
    if pcount>=1 then
    update stats_user set LOGON_time = SYSDATE WHERE user_id= puser;
    ELSE
    INSERT INTO stats_user values(
    USER,
    SYS_CONTEXT('USERENV','SESSIONID'),
    SYS_CONTEXT('USERENV','HOST'),
    SYSDATE,
    null
    );
    end if;
    end;
    /

    ReplyDelete

Subscribe to: Post Comments (Atom)