• Increase font size
  • Default font size
  • Decrease font size

Oracle useful code fragments

Here are some useful code fragments:

 

 

CREATE TABLE

CREATE TABLE SUMO_ZAVINAC_1005 (
ROW_ID            VARCHAR2(15),
PROD_RID          VARCHAR2(15),
START_DATE        DATE,
PAR_VALUE         VARCHAR2(150),
CAT_RID           VARCHAR2(15)
); 

DB LINK

DROP DATABASE LINK llink;

CREATE DATABASE LINK llink CONNECT TO user 
IDENTIFIED BY password USING '(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = funny.com)
(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = llink)
)
)';

SELECT   *
FROM     dual@llink




EXCEPTION BLOCK

BEGIN
FOR i in 1 .. 10 LOOP
BEGIN
INSERT into table...;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
gv_err_msg := replace(substr (SQLERRM,1,200),'ORA-20001: ',NULL);
UPDATE table_name SET status_field = gv_err_msg WHERE ROWID = v_res.ROWID;
COMMIT;

IF 
(gv_err_msg IN (
'ORA-01013:user requested cancel of current operation', 
'ORA-00028: your session has been killed')) 
THEN
RETURN;
END IF;
END;
END LOOP;
EXCEPTION WHEN OTHERS THEN
exception routine for main block
END;


CURSOR and BULK COLLECT

1:
2: PROCEDURE procName IS
3: TYPE                 t_rid_type  IS TABLE OF VARCHAR2(15);
4: TYPE                 t_date_type IS TABLE OF DATE;
5:
6: CURSOR c_cursor IS
7: SELECT   tab.ROW_ID,
8: tab.START_DT,
9: tab.END_DT,
10: tab.T_ID,
11:
12: FROM     myTable           tab;
13:
14: lv_prod_rid          t_rid_type;
15: lv_start_dt          t_date_type;
16: lv_end_dt            t_date_type;
17: lv_t_rid             t_rid_type;
18: lv_monitoring_id     PLS_INTEGER;
19: BEGIN
20:
21:
22: --open curson and start filling table with data
23: OPEN c_cursor;
24: LOOP
25: EXIT WHEN c_cursor%NOTFOUND;
26:
27: --get 500 records
28: FETCH c_cursor BULK COLLECT INTO
29: lv_prod_rid,      lv_start_dt,      lv_end_dt,     lv_t_rid,    
30: LIMIT 500;
31:
32: --insert them into table
33: FORALL i IN 1 .. lv_prod_rid.count
34: INSERT INTO DestTable
35: (PROD_RID)
36: VALUES
37: (lv_prod_rid(i));
38: COMMIT;
39:
40: END LOOP;
41:
42: --all data identified = close cursor
43: CLOSE c_cursor;
44:
45:
46: EXCEPTION
47: WHEN OTHERS THEN
48: ROLLBACK;
49: gv_err_msg := substr(SQLERRM,1,200);
50: -- now log error: gv_err_msg
51: END procName;  
52:



EXTENSION TABLES

1:
2: -- vytvorenie ext. tabulky do suboru.
3: create table exp_xml_data_1
4: organization external
5: ( type oracle_datapump
6: default directory rp_log_path
7: location ('xml_data_ext.csv')
8: ) as select * from xml_data
9:
10: select * from exp_xml_data
11:
12: select *
13: from ALL_DIRECTORIES
14:
15: --nacitanie dat z ext tab. do internej
16: -- !!! subor musi mat prava na citanie napr. CHMOD 777
17: create table xml_data_1001
18: ( id varchar2(15),
19: xml_data clob
20: )
21: organization external
22: ( type oracle_datapump
23: default directory CDT_DIR
24: location ('xml_data_ext.csv')
25: );
26:
27: select *
28: from xml_data_1001
29:
30: drop table xml_data_1001  
31:



RAISE ERROR

1:
2: gc_rej1   CONSTANT VARCHAR2(255) := 'reject 1: unique duplicity in input data';
3:
4: RAISE_APPLICATION_ERROR(-20001, gc_rej1);
5:



WRITE TO OUTPUT

1:
2: dbms_output.put_line('');  
3:



GET LOCKED OBJECTS

1:
2: select  oracle_username || ' (' || s.osuser || ')' username
3: ,  s.sid || ',' || s.serial# sess_id
4: ,  owner || '.' ||  object_name object
5: ,  object_type
6: ,  decode(  l.block
7: ,  0, 'Not Blocking'
8: ,  1, 'Blocking'
9: ,  2, 'Global') status
10: ,  decode(v.locked_mode
11: ,  0, 'None'
12: ,  1, 'Null'
13: ,  2, 'Row-S (SS)'
14: ,  3, 'Row-X (SX)'
15: ,  4, 'Share'
16: ,  5, 'S/Row-X (SSX)'
17: ,  6, 'Exclusive', TO_CHAR(lmode)) mode_held
18: from  v$locked_object v
19: ,  dba_objects d
20: ,  v$lock l
21: ,  v$session s
22: where   v.object_id = d.object_id
23: and   v.object_id = l.id1
24: and   v.session_id = s.sid
25: order by oracle_username
26: ,  session_id
27:



GET SIZE OF TABLE

1:
2: SELECT UPPER('MY_TABLE'),
3: s.segment_type,
4: SUM(s.bytes)/1048576 megs
5: FROM   dba_segments s
6: WHERE  (s.segment_name,s.segment_type)
7: IN (SELECT t.table_name,
8: 'TABLE'
9: FROM   all_tables t
10: WHERE  t.table_name = UPPER('MY_TABLE')
11: UNION
12: SELECT i.index_name,
13: 'INDEX'
14: FROM   all_indexes i
15: WHERE  i.table_name = UPPER('MY_TABLE')
16: )
17: GROUP BY s.segment_type
18: ORDER BY 1 DESC

 

 

Add comment

No bad words.


Security code
Refresh


New articles

Differential Equations - Simple spring model Machine Learning and Robotics
Read More 1696 Hits
Kalman Filter implementation Machine Learning and Robotics
Read More 2476 Hits
Inverse kinematics Machine Learning and Robotics
Read More 3026 Hits
gpsim tutorial Machine Learning and Robotics
Read More 6092 Hits

New articles

Differential Equations - Simple spring model Machine Learning and Robotics
Read More 1696 Hits
Kalman Filter implementation Machine Learning and Robotics
Read More 2476 Hits
Inverse kinematics Machine Learning and Robotics
Read More 3026 Hits
gpsim tutorial Machine Learning and Robotics
Read More 6092 Hits
Design by i-cons.ch / etosha-namibia.ch