doT.sYs

You can select list of tables from all the different schemas in your oracle database, here’s how

SELECT all_tables FROM all_tab_columns;

or get all the table details

SELECT * FROM all_tab_columns;

Here are some helpful tips to remember when dealing with oracle.

I. Use the “flashback technology” when you accidentally commit a mistake with your production data.
(altering entire table contents, corrupted table data, or worst dropping unintended table).

- First thing to do is to enable flashback on your database.

ALTER DATABASE FLASHBACK ON;

- Restoring database to its good state.

FLASHBACK DATABASE TO RESTORE POINT bef_damage;

- Restoring dropped table.

FLASHBACK TABLE [TABLE_NAME] TO BEFORE DROP;

- Restoring table to its good state.

FLASHBACK TABLE [TABLE_NAME] TO TIMESTAMP TO_TIMESTAMP('[DATE_TIME]');

II. Manipulate date and time display
Aside from to_date and to_timestamp functions , you could also alter the date and time display in your database through the use of this code below.

ALTER SESSION SET NLS_DATE_FORMAT = '[DATE_FORMAT]'

Split, Join and DateDiff are just few Oracle functions that can aid you in data manipulation.

Function Definition

Split - split a result by “character specified (by default, comma)”
into multiple results.

e.g. “A,B,C” –> results 3 records of “A”,”B”, and “C”.

Join - join multiple records into one result by
“character specified (by default, comma)”.

e.g. “1″,”2″,”3″ –> results 1 record of “1,2,3″.

DateDiff - Differentiate two dates.

Function Code

create or replace function split(
          p_list varchar2,
          p_del varchar2 := ','
) return split_tbl pipelined
           is
          l_idx    pls_integer;
          l_list   varchar2(32767) := p_list;
          l_value  varchar2(32767);
    begin
          loop
          l_idx := instr(l_list,p_del);
          if l_idx > 0 then
             pipe row(substr(l_list,1,l_idx-1));
             l_list := substr(l_list,l_idx+length(p_del));
          else
             pipe row(l_list);
             exit;
          end if;
        end loop;
     return;
 end split;

create or replace function join
 (
       p_cursor sys_refcursor,
       p_del varchar2 := ','
)  return varchar2
      is
          l_value   varchar2(32767);
          l_result  varchar2(32767);
     begin
        loop
             fetch p_cursor into l_value;
               exit when p_cursor%notfound;
           if l_result is not null then
                l_result := l_result || p_del;
           end if;
               l_result := l_result || l_value;
        end loop;
      return l_result;
end join;

create or replace function datediff( p_what in varchar2,
      p_d1   in date,
      p_d2   in date ) return number
   as
       l_result    number;
   begin
        select (p_d2-p_d1) * decode( upper(p_what),  'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )  into l_result from dual;
	
return l_result;
end;

* Note : To use the split and join function, you must execute this code.

create or replace type split_tbl as table of varchar2(32767)