Category Archives: PL/SQL

Insert file content into oracle table

create or replace
PACKAGE BODY     ORI_POST_FILE AS
    PROCEDURE LOAD_FILE_CONTENT (I_REPORT_TYPE_ID NUMBER , I_FILE_NAME VARCHAR2)
    AS
      src_loc bfile:= bfilename(‘FLAT_MOD_ORI_INCOMING_LOC’,LOAD_FILE_CONTENT.I_FILE_NAME);
      dest_loc BLOB;
      var_seq_id NUMBER;
      var_STATUS_ID NUMBER :=200;
      var_USER_ID NUMBER :=1;
      var_REPORT_SEQUENCE_ID number;
      var_file_size NUMBER :=0;
      begin
        –select REPORT_SEQUENCE_SEQID_SEQ.nextval into var_REPORT_SEQUENCE_ID from dual;
        INSERT INTO REPORT_SEQUENCE(
            , REPORT_TYPE_ID
            , CREATED_TS
            , CREATED_USER_ID
            , STATUS_ID)
        VALUES (I_REPORT_TYPE_ID
        , CURRENT_TIMESTAMP
        , var_USER_ID
        , var_STATUS_ID) returning REPORT_SEQUENCE_ID into var_REPORT_SEQUENCE_ID;

 
       UPDATE ORI.REPORT_SEQUENCE
   SET REPORT_URL = XMLTYPE(‘<Links>
           <Link>/site/generatereport?reportpostid=’|| var_REPORT_SEQUENCE_ID ||'</Link>
         </Links>’)
    WHERE REPORT_SEQUENCE_ID = var_REPORT_SEQUENCE_ID;
  
      dbms_lob.open(src_loc,DBMS_LOB.LOB_READONLY);
      var_file_size := DBMS_LOB.getLength(src_loc);

      –insert into blob_test values(10,empty_blob()) returning docs into dest_loc;
      insert into ORD.report_post (report_sequence_id, file_name, file_size,file_content) values (var_REPORT_SEQUENCE_ID,LOAD_FILE_CONTENT.I_FILE_NAME,var_file_size,empty_blob() )  returning file_content into dest_loc;

      DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
      DBMS_LOB.LOADFROMFILE(
      dest_lob => dest_loc
      ,src_lob => src_loc
      ,amount => DBMS_LOB.getLength(src_loc)
     );
      DBMS_LOB.CLOSE(dest_loc);
      DBMS_LOB.CLOSE(src_loc);
     — Update report_post set file_size = amount where REPORT_SEQUENCE_ID = var_report_sequence_id;
      COMMIT;
    end;
END;

PL/SQL convert time stamp (linux/unix)

SELECT
  SUBJECT,
  RECPTYPE,
  RECPVALUE,
  OCCURREDDT
FROM
  US_EVENT.EVENT
  WHERE
   OCCURREDDT  >=  ROUND(SYSDATE-1 – TO_DATE(’01/01/1970′,’MM/DD/YYYY’))* 86400
   AND
   OCCURREDDT  <  ROUND(SYSDATE – TO_DATE(’01/01/1970′,’MM/DD/YYYY’))* 86400
   AND
   ALERTTYPE  IN  ( 1973, 1974  )

PL/SQL check blocking sessions

—Recipie #1 – find blocking sessions with v$session
SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;
—Recipie #2 – find blocking sessions using v$lock
SELECT
   l1.sid || ‘ is blocking ‘ || l2.sid blocking_sessions
FROM
   v$lock l1, v$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;
/*Recipie #3 – blocking sessions with all available information
The next query prints a few more information, it let’s you quickly see who’s blocking who. Run this query and you can immediately call the colleague who’s locking your table:
*/

SELECT s1.username || ‘@’ || s1.machine
    || ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘
    || s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l2.id2 = l2.id2 ;
–Recipie #4 – identifying blocked objects

SELECT sid, id1 FROM v$lock WHERE TYPE=’TM’;

 
use this scripts i hope its it will help u to get blocking locks

set echo off
set line 10000
column blocker format a11;
column blockee format a10;
column sid format 99999;
column blocker_module format a15 trunc;
column blockee_module format a15 trunc;

alter session set optimizer_mode=rule;

select a.inst_id,
(select username from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker,
a.sid,
(select module from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker_module ,
‘ is blocking ‘ “IS BLOCKING”,
b.inst_id,
(select username from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee,
b.sid ,
(select module from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee_module
from gv$lock a, gv$lock b
where
–Modified to take care of issue where sid blocked on difft instances are not showing
— Original Script
/* a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.inst_id = b.inst_id
and a.id2 = b.id2
*/
— Modified script
a.block <>0 and
b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
and a.sid <> b.sid
order by 1, 2
/
alter session set optimizer_mode=choose;

 

—————–==============================
—————–==============================

 

 

 

 

 

 
select * from ec_branch_hurdles
where book_size is not null

select count(*)
–delete
from ec_branch_hurdles where report_id>20130101;
commit;

select * from ec_report_date_details order by batch_id desc;

–delete from ec_employee_branches where report_id =20130426;
–delete from ec_et_employees where report_id =20130426 ;
select * from ec_acct_credits where report_id =20130426 ;
select * from ec_employee_hurdles  where report_id =20130426 ;
select * from ec_branch_costs  where report_id =20130426 ;
select * from ec_branch_hurdles  where report_id =20130426 ;

set linesize 140
set pages 100
col username       format a20
col “SID,SESSION#” format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# “SID,SESSION#”
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       0, ‘None’
,       1, ‘Null’
,       2, ‘Row-S (SS)’
,       3, ‘Row-X (SX)’
,       4, ‘Share’
,       5, ‘S/Row-X (SSX)’
,       6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username,session_id;

PL/SQL run time tunning

create or replace
PROCEDURE SP_UPDATE_BPSA_MARGIN_RISK_ADV
AS
var_cusip BPSA_MARGIN_RISK.cusip%type;
var_thirtydayaverage BPSA_MARGIN_RISK.average_daily_volume%type;
var_counter number;
var_max_date VARCHAR2(20);
var_sql varchar2(1000);
BEGIN
  var_counter :=0;
 
    –DBMS_OUTPUT.PUT_LINE(‘Started updating Cusips at ‘ || to_char(SYSDATE,’YYYYMMDD HH:MI:SS’) );
   
    SELECT MAX(PROCESS_DATE)
    INTO var_max_date
    FROM BPSA_MARGIN_RISK;
   
    — Declare cusip cursor
    DECLARE CURSOR CUSIP_CURSOR IS
    SELECT distinct cusip,thirtydayaverage
      FROM vw_daily_average_volume
     WHERE vw_daily_average_volume.cusip in ( select distinct cusip from BPSA_MARGIN_RISK where process_date =var_max_date)
       AND created_ts = (select max(created_ts) from vw_daily_average_volume);
     
    BEGIN

      — Open  Curosor
      OPEN CUSIP_CURSOR;
     
      LOOP
     
          Fetch CUSIP_CURSOR into var_cusip, var_thirtydayaverage;
         
          EXIT when CUSIP_CURSOR%NOTFOUND;
         
          var_sql :=’update BPSA_MARGIN_RISK set average_daily_volume = :1 where process_date = :2 and cusip = :3 and average_daily_volume is null’;
         
          execute immediate var_sql using var_max_date, var_cusip, var_cusip;
         
          /*
          update BPSA_MARGIN_RISK
             set average_daily_volume = var_thirtydayaverage
           where process_date = var_max_date
             and cusip = var_cusip
             and var_cusip is null;
          */
         
          –DBMS_OUTPUT.PUT_LINE(var_counter || ‘. Cusip:’ || ‘ ‘ || var_cusip);
          — var_counter := var_counter + 1;
         
      END LOOP;
   
      COMMIT;
     
      –DBMS_OUTPUT.PUT_LINE(var_counter || ‘Cusips updated by’ || to_char(SYSDATE,’YYYYMMDD HH:MI:SS’) );
   
    CLOSE CUSIP_CURSOR;
   
  END;
 
END SP_UPDATE_BPSA_MARGIN_RISK_ADV;

 

create or replace
PROCEDURE SP_UPDATE_BPSA_MARGIN_RISK_ADV
AS
var_cusip BPSA_MARGIN_RISK.cusip%type;
var_thirtydayaverage BPSA_MARGIN_RISK.average_daily_volume%type;
var_counter number;
var_max_date VARCHAR2(20);
var_sql varchar2(4000);

BEGIN

  var_counter :=0;
      
MERGE into BPSA_MARGIN_RISK  a

USING( SELECT distinct cusip,thirtydayaverage
        FROM vw_daily_average_volume
        WHERE vw_daily_average_volume.cusip in ( select distinct cusip from BPSA_MARGIN_RISK
                                              where process_date =( SELECT MAX(PROCESS_DATE) FROM BPSA_MARGIN_RISK)
                                             )
          AND created_ts = (select max(created_ts) from vw_daily_average_volume)
     )t1 
  ON (
      a.cusip = t1.cusip and
       a.process_date = (SELECT MAX(PROCESS_DATE) FROM BPSA_MARGIN_RISK)
  )
  WHEN matched then
  UPDATE set a.average_daily_volume = NVL(a.average_daily_volume,t1.thirtydayaverage);
      COMMIT;
 
END;

PL/SQL Merge statement for data update

MERGE into Product_Sales  a

USING( SELECT distinct cusip,thirtydayaverage
        FROM vw_daily_volume
        WHERE vw_daily_volume.cusip in ( select distinct cusip from Product_Sales
                                              where process_date =( SELECT MAX(PROCESS_DATE) FROM Product_Sales)
                                             )
          AND created_ts = (select max(created_ts) from vw_daily_volume)
     )t1 
  ON (
      a.cusip = t1.cusip and
       a.process_date = (SELECT MAX(PROCESS_DATE) FROM Product_Sales)
  )
  WHEN matched then
  UPDATE set a.average_daily_volume = NVL(a.average_daily_volume,t1.thirtydayaverage);