I enjoyed the answers and questions about hidden features in sql server

What can you tell us about Oracle?

Views: 178

Reply to This

Replies to This Discussion

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.

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

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.

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';

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.

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;

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

2- WM_CONCAT for string aggregation

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 to Discussion

RSS

Oracle Jobs in US

© 2024   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service