Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

October 28, 2008

We lost Carl..

Filed under: Oracle Other — H.Tonguç Yılmaz @ 7:05 am

This morning I read the tweets of this very bad news..

Carl’s OTN Apex forum activity, blog and Apex examples were always my guides through my Apex adventures..

I still can not believe this, he left comments on my blog two weeks ago..

We all will miss you Carl..

blog : http://carlback.blogspot.com/
apex examples : http://apex.oracle.com/pls/otn/f?p=11933:5

John Scott

aejes @oracletechnet a big big loss. I had the pleasure of counting Carl as a friend. Can’t believe it. My thoughts are with his family about 1 hour ago from twitterrific in reply to oracletechnet

Fuad Arshad

fuadar @eddieawad yes @carlback will definitely be missed about 5 hours ago from TwitterFox in reply to eddieawad

Justin Kestelyn

oracletechnet More info about @carlback; he will be missed. http://snurl.com/4rd4a [www_lasvegassun_com] about 7 hours ago from web

Eddie Awad

eddieawad Linda, a friend of @carlback sent out this FB msg: he [Carl] died early sunday morning around 3am in a bad car accident. about 7 hours ago from web

Eddie Awad

eddieawad What! @carlback passed away! RIP. http://bit.ly/33y0Dv about 7 hours ago from web

Fuad Arshad

fuadar wow RIP @carlback about 8 hours ago from TwitterFox

Justin Kestelyn

oracletechnet @carlback Carl Backstrom, RIP. Good guy, a terrible loss. about 9 hours ago from web in reply to carlback

Dan Norris

dannorris Re @carlback: http://friendfeed.com/las about 9 hours ago from twhirl

Dan Norris

dannorris @fuadar @topperge Message said it was early Sunday morning, the 26th. about 10 hours ago from twhirl in reply to fuadar

Fuad Arshad

fuadar @dannorris i see the last tweet form @carlback is oct 24th about 10 hours ago from TwitterFox in reply to dannorris

Dan Norris

dannorris I had a message from someone I don’t know that @carlback died in a car accident over the weekend. Corroboration? RIP @carlback if true. about 10 hours ago from twhirl


October 26, 2008

Qualifying PL/SQL code example to improve readability and NOT to invalidate

Filed under: Oracle How To — H.Tonguç Yılmaz @ 8:27 pm

-- a good example for qualifying
PACKAGE scope_demo
   g_global   NUMBER;

   PROCEDURE set_global (number_in IN NUMBER);
END scope_demo;

PACKAGE BODY scope_demo
   PROCEDURE set_global (number_in IN NUMBER)
      l_salary  NUMBER := 10000;
     l_count   PLS_INTEGER;

         l_inner   PLS_INTEGER;
         SELECT COUNT (*)
           INTO set_global.l_count
           FROM employees e
          WHERE e.department_id = local_block.l_inner
            AND e.salary > set_global.l_salary;
      END local_block;

      scope_demo.g_global := set_global.number_in;
   END set_global;
END scope_demo; 

For a closer look of the advantages of qualifying please check Qualifying All References Thread on Best Practice PL/SQL with Steven Feuerstein of OTN.

October 22, 2008

INFO: 2 Day Oracle Index Internals Seminar with Richard Foote at Istanbul

Filed under: Oracle Seminars — H.Tonguç Yılmaz @ 8:05 pm

Richard Foote will be in Istanbul for a two days seminar next month. This is an important opportunity, so if you still didn’t you can book here.

Also you can visit here for a general outline of the seminar. As usual I will be publishing my seminar notes, until 13-14 November you can check the previous ones if you like of course. :)

October 12, 2008

OWB resources versus APEX resources

Filed under: Oracle Business Intelligence — H.Tonguç Yılmaz @ 8:02 pm

Just like Apex OWB also has a very beneficial OTN forum and a Utility Exchange point to share tips and tricks. Both have Installation, Users Guides and OBEs.

But OWB has a corporate blog where as Apex(as far as I know) does not have a corporate one yet and as of today OWB does not have a 2 Day+ Developer’s Guide like Apex.

October 4, 2008

Understand Your Data with Oracle’s In-Database Statistical Functions

Filed under: Oracle Business Intelligence — H.Tonguç Yılmaz @ 8:20 pm

Especially after 10g Oracle’s in-database functionalities like Analytics, Grouping and XML Functions, Regular Expressions and Model Clause are much more popular. Since they are in-database, all are scalable, performant and parallelizable, as a result knowing them makes a lot of difference. Here is a documentation linked list of 10.2 functions, unlike other statistical software vendors all these functionalities are FREE inside the Enterprise Edition license.

There are two more categories which requires mentioning: Oracle’s In-Database Data Mining and Statistical Functions. These statistical functions include descriptive statistics, hypothesis testing, correlations analysis, test for distribution fit, cross tabs with Chi-square statistics, analysis of variance(ANOVA), and Pareto analysis. Also DBMS_STAT_FUNCS.SUMMARY procedure summarizes all the basic descriptive statistics for a table’s attribute;

-- stats_mode: for most common value
select median(salary), stats_mode(salary) from employees ;

-------------- ------------------
          6200               2500

select stats_t_test_one(salary, 1000) two_sided_p_value from employees ;


select corr_s(employee_id, salary) coefficient from employees ;


set serveroutput on
	s1 DBMS_STAT_FUNCS.SummaryType;
    -- must be a numeric column
	DBMS_STAT_FUNCS.SUMMARY(p_ownername=> 'HR', p_tablename=> 'EMPLOYEES', p_columnname=>'SALARY', p_sigmavalue=>3, s=> s1);
	dbms_output.put_line('SUMMARY STATISTICS');
	dbms_output.put_line('Count: '||s1.count);
	dbms_output.put_line('Min: '||s1.min);
	dbms_output.put_line('Max: '||s1.max);
	dbms_output.put_line('Range: '||s1.range);
	dbms_output.put_line('Mean: '||round(s1.mean));
	dbms_output.put_line('Mode Count: '||s1.cmode.count);
	dbms_output.put_line('Mode: '||s1.cmode(1));
	dbms_output.put_line('Variance: '||round(s1.variance));
	dbms_output.put_line('Stddev: '||round(s1.stddev));
	dbms_output.put_line('Quantile 5 '||s1.quantile_5);
	dbms_output.put_line('Quantile 25 '||s1.quantile_25);
	dbms_output.put_line('Median '||s1.median);
	dbms_output.put_line('Quantile 75 '||s1.quantile_75);
	dbms_output.put_line('Quantile 95 '||s1.quantile_95);
	dbms_output.put_line('Extreme Count: '||s1.extreme_values.count);
	dbms_output.put_line('Extremes: '||s1.extreme_values(1));
	dbms_output.put_line('Top 3: '||s1.top_5_values(1)||','||s1.top_5_values(2)||','||s1.top_5_values(3));
	dbms_output.put_line('Bottom 3: '||s1.bottom_5_values(5)||','||s1.bottom_5_values(4)||','||s1.bottom_5_values(3));
Count: 107
Min: 2100
Max: 24000
Range: 21900
Mean: 6462
Mode Count: 1
Mode: 2500
Variance: 15283141
Stddev: 3909
Quantile 5 2500
Quantile 25 3100
Median 6200
Quantile 75 8900
Quantile 95 12700
Extreme Count: 1
Extremes: 24000
Top 3: 24000,17000,17000
Bottom 3: 2100,2200,2200

PL/SQL procedure successfully completed.

After 11g mining the unstructured data is possible with these functions. All these in-database functionality other than SQL have additional Java APIs, so cost is not the only advantage as SQL and Java are the most popular standard IT platforms of today. With all these in-database functionality Oracle database now is a sophisticated, scalable and secure analytical engine more than ever. :)


ps: 2nd BIWA summit presentations are available at http://www.oraclebiwa.org and currently it is free to join Oracle BIWA SIG. :)

DBMS_COMPARISON supplied package after 11g Release 1

Filed under: Oracle 11g New Features,Oracle How To — H.Tonguç Yılmaz @ 12:24 pm

I discussed 10g’s ORA_HASH SQL function to determine if two Oracle tables’ data are equal or not with this post before, especially if you are working with huge tables and they are on different locations this method is very effective.

In the same post I also mentioned some favorite schema and data comparison methods used within the community, so this one is a following post to that post to mention the new 11g supplied package; DBMS_COMPARISON.

After a fast startup demonstration based on psoug’s library I saw that this new feature also uses ORA_HASH SQL function. :) Also for the schema comparison part which dictionary checks are made can be followed inside a 10046 SQL trace as demonstrated below;

conn hr/hr



CREATE UNIQUE INDEX ui_depts ON depts(department_id) NOLOGGING ;

DELETE FROM depts WHERE department_id = 10;

1 row deleted.


ALTER SESSION SET max_dump_file_size=unlimited;
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET statistics_level = ALL ;

ALTER SESSION SET tracefile_identifier = dbms_comparison ;


exec dbms_comparison.create_comparison(comparison_name=>'compare_test', schema_name=>'HR', object_name=>'DEPTS', dblink_name=>NULL,  remote_schema_name=>'HR', remote_object_name=>'DEPARTMENTS',  scan_percent=>90);

set serveroutput on

  retval dbms_comparison.comparison_type;
  IF dbms_comparison.compare('compare_test', retval, perform_row_dif=>TRUE) THEN
    dbms_output.put_line('No Differences');
    dbms_output.put_line('Differences Found');
Differences Found

PL/SQL procedure successfully completed.


select value from v$diag_info where name = 'Default Trace File' ;


CMD > tkprof /u01/app/oracle/diag/rdbms/tong/tong/trace/tong_ora_4402_DBMS_COMPARISON.trc tong_ora_4402_DBMS_COMPARISON.txt sys=yes waits=yes sort=execpu

set null @
set colsep |
set linesize 2500

select *  FROM  user_comparison_row_dif ;




COMPARE_TEST                  |         4|@                 |AAARADAAFAAAAA3AAA
DIF|04-OCT-08 PM

exec dbms_comparison.drop_comparison(comparison_name=>'compare_test') ;

There are several constraints to use this method like the local database that runs the subprograms in the DBMS_COMPARISON package must be at least 11.1 and the remote database must be 10.1 at least. Non-Oracle databases are not supported and the database character sets must be the same for the databases being compared. But I am pretty sure with feature releases this package will be enhanced and be used with other database features.

ps: During my unconference session at OOW08 – APEX Test Drive – I mentioned that Apex 3 on 11.1 under Utilities tab also has a GUI support to use this new supplied package.

Create a free website or blog at WordPress.com.