Archive

Archive for July, 2014

Script to output count for all tables in the database – Oracle

set serveroutput on
exec DBMS_OUTPUT.ENABLE (buffer_size => NULL);

DECLARE

cursor c1 is select table_name from user_tables order by 1;
cnt integer;
BEGIN
FOR C in C1
LOOP
execute immediate ‘select count(*) from ‘ || c.table_name into cnt;
dbms_output.put_line(C.TABLE_NAME || ‘ – ‘ || cnt);
END LOOP;
END;

Categories: Uncategorized

Generate Table and Constraint DDLs – Oracle

Generate Table DDL Oracle

Categories: Uncategorized

Generate data insert script as insert into

Declare
v_table_name varchar2(30) := ‘MY_TABLE’; — Your Tablename
v_column_list varchar2(4000);
v_insert_list varchar2(4000);
v_ref_cur_columns varchar2(4000);
v_ref_cur_query varchar2(4000);
v_ref_cur_output varchar2(4000);
V_Column_Name Varchar2(4000);
Cursor C1 Is Select Column_Name, Data_Type From DBA_Tab_Columns Where Table_Name = V_Table_Name Order By Column_Id;

refcur sys_refcursor;
begin
for i in c1 loop
v_column_list := v_column_list||’,’||i.column_name;
if i.data_type = ‘NUMBER’ then
v_column_name := i.column_name;
elsif i.data_type = ‘DATE’ then
v_column_name :=
chr(39)||’to_date(‘||chr(39)||’||chr(39)’||’||to_char(‘||i.column_name||’,’||chr(39)||’dd/mm/yyyy hh:mi:ss’||chr(39)||’)||chr(39)||’||chr(39)||’, ‘||chr(39)||’||chr(39)||’||chr(39)||’dd/mm/rrrr hh:mi:ss’||chr(39)||’||chr(39)||’||chr(39)||’)’||chr(39);
elsif i.data_type = ‘VARCHAR2’ then
v_column_name := ‘chr(39)||’||i.column_name||’||chr(39)’;
end if;
v_ref_cur_columns := v_ref_cur_columns||’||’||chr(39)||’,’||chr(39)||’||’||v_column_name;
end loop;
v_column_list := ltrim(v_column_list,’,’);
v_ref_cur_columns := substr(v_ref_cur_columns,8);

V_Insert_List := ‘INSERT INTO ‘||V_Table_Name||’ (‘||V_Column_List||’) VALUES ‘;
V_Ref_Cur_Query := ‘SELECT ‘||V_Ref_Cur_Columns||’ FROM ‘||V_Table_Name;

open refcur for v_ref_cur_query;
loop
fetch refcur into v_ref_cur_output;
exit when refcur%notfound;
v_ref_cur_output := ‘(‘||v_ref_cur_output||’);’;
v_ref_cur_output := replace(v_ref_cur_output,’,,’,’,null,’);
v_ref_cur_output := replace(v_ref_cur_output,'(,’,'(null,’);
v_ref_cur_output := replace(v_ref_cur_output,’,,)’,’,null)’);
v_ref_cur_output := replace(v_ref_cur_output,’null,)’,’null,null)’);
v_ref_cur_output := REPLACE(v_ref_cur_output,’,);’,’,null);’);
v_ref_cur_output := v_insert_list||v_ref_cur_output;
dbms_output.put_line (v_ref_cur_output);
end loop;
End;
/

Categories: Uncategorized

Job scheduling in Oracle

exec dbms_scheduler.create_job(‘upd_dosrub_job’, job_type=>’plsql_block’, job_action=>’ dropme_upd_dosrub;’, start_date => ’18-JUL-14 9.00.00PM US/Pacific’,enabled=>true);
exec dbms_scheduler.set_attribute(‘upd_dosrub_job’, ‘max_run_duration’ , interval ’50’ hours);
select * from DBA_SCHEDULER_JOBS where job_name=’upd_dosrub_job’;

Categories: Uncategorized