Replies to This Discussion

Permalink Reply by Samuel Sprau on August 26, 2013 at 6:06pm

Since Apex is now part of every Oracle database, these Apex utility functions are useful even if you aren't using Apex:

SQL> declare 
  2    v_array apex_application_global.vc_arr2;

   3    v_string varchar2(2000);

   4  begin 
 
  5    -- Convert delimited string to array 
  6    v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');  
 7    for i in 1..v_array.count 
  8    loop 
 9      dbms_output.put_line(v_array(i));

  10    end loop; 
   
 11    -- Convert array to delimited string

  12    v_string := apex_util.table_to_string(v_array,'|'); 
 13    dbms_output.put_line(v_string);

  14  end; 
  / alpha beta gamma delta alpha|beta|gamma|delta  PL/SQL procedure successfully completed.

• ▶ Reply

Permalink Reply by Delila Alli on August 26, 2013 at 6:08pm

The Buffer Cache Hit Ratio is virtually meaningless as a predictor of system efficiency

• ▶ Reply

Permalink Reply by Delila Alli on August 26, 2013 at 6:10pm

You can view table data as of a previous time using Flashback Query, with certain limitations.

Select *   from my_table as of timestamp(timestamp '2008-12-01 15:21:13')

11g has a whole new feature set around preserving historical changes more robustly.

• ▶ Reply

Permalink Reply by Kenton Jordan on August 26, 2013 at 6:20pm

The OVERLAPS predicate is undocumented.

http://oraclesponge.wordpress.com/2008/06/12/the-overlaps-predicate/

• ▶ Reply

Permalink Reply by Devorah Tipler on August 26, 2013 at 6:21pm

If you get the value of PASSWORD column on DBA_USERS you can backup/restore passwords without knowing them:

 ALTER USER xxx IDENTIFIED BY VALUES 'xxxx';

• ▶ Reply

Permalink Reply by Bobbie Caine on August 26, 2013 at 6:24pm

Bypass the buffer cache and read straight from disk using direct path reads.

alter session set "_serial_direct_read"=true;

Causes a tablespace (9i) or fast object (10g+) checkpoint, so careful on busy OLTP systems.

• ▶ Reply

Permalink Reply by Perla Mrozek on August 26, 2013 at 6:26pm

Q: How to call a stored with a cursor from TOAD?

A: Example, change to your cursor, packagename and stored proc name

declare cursor PCK_UTILS.typ_cursor; 
   begin   
     PCK_UTILS.spc_get_encodedstring(   
        'U',    
       10000002,   
        null, 
          'none',   
        cursor); 
  end;

• ▶ Reply

Permalink Reply by Shizuko Phan on August 26, 2013 at 6:28pm

1- The Model Clause (available for Oracle 10g and up)

2- WM_CONCAT for string aggregation

• ▶ Reply

Permalink Reply by Sambasiva Reddy on February 21, 2014 at 1:02pm

Left trim is doing by Oracle automatically. But not Right Trim

Left Trim Ex:

------------------------

SELECT length('ABC    ') ,length('ABC')
FROM DUAL
WHERE  'ABC    ' = 'ABC'

LENGTH('ABC')    LENGTH('ABC')_1
--------------------      ---------------------------------  
7                             3

Right Trim Ex:

-------------------------

SELECT length('    ABC') ,length('ABC')
FROM DUAL
WHERE  '    ABC' = 'ABC'

Output:

No rows returned

• ▶ Reply

▶ Reply to Discussion