Search This Blog

Total Pageviews

Sunday 9 October 2011

Oracle insert into Loop

Oracle insert through Loop




DECLARE

count NUMBER := 0;
total NUMBER := 0;

CURSOR del_record_cur IS
SELECT rowid
FROM .
WHERE

BEGIN
FOR rec IN del_record_cur LOOP
DELETE FROM .
WHERE rowid = rec.rowid;

total := total + 1;
count := count + 1;

IF (count >= 1000) THEN
COMMIT;
count := 0;
END IF;

END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from ..');
END;
/





DECLARE


cnt NUMBER := 0 ;
total NUMBER := 0 ;

CURSOR copy_record_cur IS
SELECT * from emp_anuj ;


BEGIN

FOR rec IN copy_record_cur LOOP
insert into scott.emp_temp
(EMPNO ,
ENAME ,
JOB ,
HIREDATE ,
SAL ,
COMM ,
DEPTNO )
values (
rec.EMPNO ,
rec.ENAME ,
rec.JOB ,
rec.HIREDATE ,
rec.SAL ,
rec.COMM ,
rec.DEPTNO
) ;



total := total + 1;
cnt := cnt + 1;

IF (cnt >= 5000) THEN
COMMIT;

cnt := 0;
END IF;

END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('INSERTED ' || total || ' records from scott.emp_temp');
END;
/




DECLARE

count NUMBER := 0;
total NUMBER := 0;

CURSOR copy_record_cur IS
SELECT *
FROM cccprod1.DM_AUDITTRAIL_S_backup
WHERE TIME_STAMP >'07-DEC-08';
-- rec.


BEGIN

FOR rec IN copy_record_cur LOOP
insert into cccprod1.DM_AUDITTRAIL_S (
R_OBJECT_ID ,
EVENT_NAME ,
EVENT_SOURCE ,
R_GEN_SOURCE ,
USER_NAME ,
AUDITED_OBJ_ID ,
TIME_STAMP ,
STRING_1 ,
STRING_2 ,
STRING_3 ,
STRING_4 ,
STRING_5 ,
ID_1 ,
ID_2 ,
ID_3 ,
ID_4 ,
ID_5 ,
I_IS_REPLICA ,
I_VSTAMP ,
CHRONICLE_ID ,
OBJECT_NAME ,
VERSION_LABEL ,
OBJECT_TYPE ,
EVENT_DESCRIPTION ,
POLICY_ID ,
CURRENT_STATE ,
WORKFLOW_ID ,
SESSION_ID ,
USER_ID ,
OWNER_NAME ,
ACL_NAME ,
ACL_DOMAIN ,
APPLICATION_CODE ,
CONTROLLING_APP ,
ATTRIBUTE_LIST ,
ATTRIBUTE_LIST_ID ,
AUDIT_SIGNATURE ,
AUDIT_VERSION ,
HOST_NAME ,
TIME_STAMP_UTC ,
I_AUDITED_OBJ_CLASS ,
REGISTRY_ID ,
I_IS_ARCHIVED ,
AUDITED_OBJ_VSTAMP )
values
(
rec.R_OBJECT_ID ,
rec.EVENT_NAME ,
rec.EVENT_SOURCE ,
rec.R_GEN_SOURCE ,
rec.USER_NAME ,
rec.AUDITED_OBJ_ID ,
rec.TIME_STAMP ,
rec.STRING_1 ,
rec.STRING_2 ,
rec.STRING_3 ,
rec.STRING_4 ,
rec.STRING_5 ,
rec.ID_1 ,
rec.ID_2 ,
rec.ID_3 ,
rec.ID_4 ,
rec.ID_5 ,
rec.I_IS_REPLICA ,
rec.I_VSTAMP ,
rec.CHRONICLE_ID ,
rec.OBJECT_NAME ,
rec.VERSION_LABEL ,
rec.OBJECT_TYPE ,
rec.EVENT_DESCRIPTION ,
rec.POLICY_ID ,
rec.CURRENT_STATE ,
rec.WORKFLOW_ID ,
rec.SESSION_ID ,
rec.USER_ID ,
rec.OWNER_NAME ,
rec.ACL_NAME ,
rec.ACL_DOMAIN ,
rec.APPLICATION_CODE ,
rec.CONTROLLING_APP ,
rec.ATTRIBUTE_LIST ,
rec.ATTRIBUTE_LIST_ID ,
rec.AUDIT_SIGNATURE ,
rec.AUDIT_VERSION ,
rec.HOST_NAME ,
rec.TIME_STAMP_UTC ,
rec.I_AUDITED_OBJ_CLASS ,
rec.REGISTRY_ID ,
rec.I_IS_ARCHIVED ,
rec.AUDITED_OBJ_VSTAMP
) ;

total := total + 1;
count := count + 1;

IF (count >= 5000) THEN
COMMIT;
count := 0;
END IF;

END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('INSERTED ' || total || ' records from CCCPROD1.DM_AUDITTRAIL_S');
END;
/

Oracle Sql col to row

Oracle sqlplus Change Row into Column?
sqlplus Change Row into Column?



CREATE or replace TYPE strings1 AS TABLE OF VARCHAR2(100);
/

CREATE or replace Function CONCAT_LIST1 ( lst IN strings1 )
RETURN VARCHAR2 AS
ret varchar2(1000);
BEGIN
FOR j IN 1..lst.LAST LOOP
ret := ret || lst(j)||',';
END LOOP;
RETURN ret;
END;
/



SELECT dbid,CONCAT_LIST1 (CAST( COLLECT(snap_id||'->'||to_char(begin_interval_time,'dd-mon-yy HH24')||'Hr') AS strings1 )) snap
FROM dba_hist_snapshot
where begin_interval_time between (sysdate -1) and sysdate
group by dbid
;


DBID
----------
SNAP
----------------------------------------------------------------------------------------------------
92516760
33324->08-oct-11 10Hr,33325->08-oct-11 11Hr,33326->08-oct-11 12Hr,33328->08-oct-11 14Hr,33330->08-oc
t-11 16Hr,33332->08-oct-11 18Hr,33334->08-oct-11 20Hr,33336->08-oct-11 22Hr,33338->09-oct-11 00Hr,33
323->08-oct-11 09Hr,33345->09-oct-11 07Hr,33344->09-oct-11 06Hr,33343->09-oct-11 05Hr,33342->09-oct-
11 04Hr,33341->09-oct-11 03Hr,33340->09-oct-11 02Hr,33339->09-oct-11 01Hr,33337->08-oct-11 23Hr,3333
5->08-oct-11 21Hr,33333->08-oct-11 19Hr,33331->08-oct-11 17Hr,33329->08-oct-11 15Hr,33327->08-oct-11
13Hr,





SELECT dbid, snap_id,to_char(begin_interval_time,'dd-mon-yy HH24')||'Hr' begin_time from dba_hist_snapshot
where begin_interval_time between (sysdate -1) and sysdate
order by 2
/

DBID SNAP_ID BEGIN_TIME
---------- ---------- -----------------------
92516760 33323 08-oct-11 09Hr
92516760 33324 08-oct-11 10Hr
92516760 33325 08-oct-11 11Hr
92516760 33326 08-oct-11 12Hr
92516760 33327 08-oct-11 13Hr
92516760 33328 08-oct-11 14Hr
92516760 33329 08-oct-11 15Hr
92516760 33330 08-oct-11 16Hr
92516760 33331 08-oct-11 17Hr
92516760 33332 08-oct-11 18Hr
92516760 33333 08-oct-11 19Hr
92516760 33334 08-oct-11 20Hr
92516760 33335 08-oct-11 21Hr
92516760 33336 08-oct-11 22Hr
92516760 33337 08-oct-11 23Hr
92516760 33338 09-oct-11 00Hr
92516760 33339 09-oct-11 01Hr
92516760 33340 09-oct-11 02Hr
92516760 33341 09-oct-11 03Hr
92516760 33342 09-oct-11 04Hr
92516760 33343 09-oct-11 05Hr
92516760 33344 09-oct-11 06Hr
92516760 33345 09-oct-11 07Hr

23 rows selected.


==


select t,max(sys_connect_by_path(object_name, ' ' )) obj_name
from (select OBJECT_TYPE t, object_name,
row_number() over(partition by OBJECT_TYPE order by object_name) rn
from user_objects
where object_name not like 'BIN%'
)
start with rn = 1
connect by prior rn = rn-1
and prior t = t
group by t;




T obj_name
------------------- ----------------------------------------------------------------------------------------------------
PROCEDURE SHOW_FKEYS
INDEX IDX_EMP_ANUJ NORMAL_T_BIT_BMX PARK_ACRES PARK_DESCR_PHONE PARK_NAME PK_DEPT PK_EMP
TABLE ADDRESSES BONUS DEPT EMP EMP_ANUJ EMP_DUP PARK SALGRADE T TEST T_BIT T_FETCH_FIRST_ROWS XXX


===========================

select OBJECT_TYPE t, object_name,
row_number() over(partition by OBJECT_TYPE order by object_name) rn
from user_objects
where object_name not like 'BIN%'
/

Oracle DBA

anuj blog Archive