Dec 30, 2016

Oracle ADF


Oracle ADF Enterprise Application Development - Made Simple: Successfully


As such from the Oracle Certified Associate, Java SE 7 Programmer I certification exam topics, in my honest opinion ADF developers need to know *all* of the following topics:

  • Java Basics
  • Working with Java Data Types
  • Using Operators and Decision Constructs
  • Creating and Using Arrays
  • Using Loop Constructs
  • Working with Methods and Encapsulation
  • Working with Inheritance
  • Handling Exceptions
I might have a few people argue with me on the list above, particularly inheritance and exceptions. But in my experience ADF developers who don't know about inheritance and in particular type casting, as well as exception handling in general will struggle.  In reality all of the topics above are Java basics taught to first year IT undergraduates, so nobody should be surprised by the list.
When we move to the Java SE 7 Programmer II exam topics, the list is as follows.  You'll note the numbers next to each topic, 1 being mandatory, 2 not mandatory but knowledge in this area will certainly help most projects, and 3 not required.
  • 1- Java Class Design
  • 1- Java Advanced Class Design
  • 1 -Object-Oriented Principles
  • 2 - String Processing
  • 1 - Exceptions
  • 3 - Assertions
  • 2 - Java I/O Fundamentals
  • 2 - Java File I/O
  • 1 - Building Database Applications with JDBC
  • * - Threads
  • * - Concurrency
  • * - Localization
In the #1 list there's no surprises but maybe JDBC. From my own personal experience even though ADF BC & EJB/JPA abstracts away from knowing the language of the database, at customer sites frequently I've had to build solutions that need to interface with legacy database PL/SQL using JDBC. Your site might not have this requirement, but the next site you work at probably will.
The #2 list is more interesting. String processing is useful because without some internal knowledge of the standard Java APIs you can write some poorly performing code . Java I/O is not an uncommon requirement, being able to read/write uploaded/downloaded files to WLS.
 

As for the #3 list, assertions simply don't work in the Java EE world that ADF runs.

Finally the topics marked with stars require special explanation. First localization, often called internationalization really depends on the requirements of your project. For me sitting down in Australia, I've never worked on a system that requires any type of localization support besides some daylight saving calculations. For you, this requirement might be totally the opposite if you sit in Europe, so as a requirement it depends.
 

Then the topics of threading and concurrency. Threading and concurrency are useful topics only because there "be demons in thar" (best said in a pirate voice) for future Java projects. ADF actually isolates programmers from the issues of threading and concurrency. This isolation is risky as it may give ADF programmers a false belief they can code anything Java. You'll quickly find issues of thread safety and collection classes that support concurrency are a prime concern for none-ADF Java solutions.


So do you need to be an expert Java programmer for ADF? The answer is no. But a reasonable level of Java is required. And this can be capped off with the more Java you know, of course this will be beneficial, and not just for your ADF project!



Dev OPS








Aug 11, 2016

Lists the arguments of procedures & functions

In ALL_ARGUMENTS table lists the arguments of the procedures and functions that are accessible to the current user


In DBA_ARGUMENTS table lists the arguments of the procedures and functions that are available in the database.


In USER_ARGUMENTS  table lists the arguments of the procedures and functions that are owned by the current user. This view does not display the OWNER column.


select distinct owner , package_name,object_name,   argument_name, data_type, in_out, data_length,
character_set_name, type_owner, type_name, type_subname,  pls_type, char_length, char_used from all_arguments

Jul 28, 2016

DBA related queries & scripts


select * from v$system_event;  -- query to get the oracle system event

select * from ( select d.*,rank() over(order by cpu_time desc) rk from v$sqlstats d ) where rk <=5; -- SQL to get the largest time taking process

SELECT value  FROM sys.v_$parameter WHERE name = 'user_dump_dest'; -- To get the location of SQL dump


Oracle Segment Advisor, can identify objects that have space for reclamation or objects that have too much row chaining. The function dbms_space.asa_recommendations verify if there are recommendations for improvement in a particular segment.

SELECT * FROM table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));


To get the size of tablespace

DECLARE
 ub NUMBER;
 ab NUMBER;
BEGIN
  dbms_space.create_table_cost('DYNAMIC',28,250000,0,ub,ab);
  dbms_output.put_line('Used Bytes: ' || TO_CHAR(ub)||' | Alloc Bytes: ' || TO_CHAR(ab));
END;


Determines the cost of creating an index on an existing table

CREATE TABLE t ( person_id  NUMBER(5), first_name VARCHAR2(30), last_name  VARCHAR2(30));


 DECLARE
  ub  NUMBER;
  ab  NUMBER;
 BEGIN
   dbms_space.create_index_cost('CREATE INDEX t_pid ON t(person_id)', ub, ab);
   dbms_output.put_line('Used Bytes: ' || TO_CHAR(ub));
   dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(ab));
 END;


Jun 5, 2016

SQL to avoid the logical duplicate


Query to avoid the logical duplicate records .(Technically these records are not duplicate ie: we can avoid records using the distinct keyword)
 
create table LOGICAL_DUP
( source      VARCHAR2(100),
  destination VARCHAR2(100),
  distance    NUMBER
) ;

insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('AA', 'BB', 5000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('CC', 'DD', 1000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('FF', 'GG', 1500);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('AA', 'CC', 3000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('GG', 'FF', 1500);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('BB', 'AA', 5000); 

commit;
 
Output required is 
 
 AA  BB  5000
 AA  CC  3000
 CC  DD  1000
 FF  GG  1500
select min(a.source),max(a.destination),a.distance from LOGICAL_DUP a
where a.source  in (select b.destination from logical_dup b where 
b.source=a.destination)group by a.distance

union

select min(a.source),max(a.destination),a.distance from LOGICAL_DUP a
where a.source not  in (select b.destination from logical_dup b 
where b.source=a.destination)group by a.distance ;
 
OR 
 
select source,
       destination,
       distance
from (select source, 
             destination, 
             distance,
             row_number() over(partition by greatest(source, destination),
 least(source, destination) order by source, destination) rn
      from logical_dup)
where rn = 1 ;
 

May 13, 2016

Oracle Partitioning 2

Composite partitioning is a partitioning technique that combines some of the other partitioning methods. The table is initially partitioned by the first data distribution method and then each partition is sub- partitioned by the second data distribution method.

The following composite partitions are available:
  •  Range-hash partitioning was introduced in Oracle 8i
  • Range-list partitioning was introduced in Oracle 9i
  •  Range-range partitioning was introduced in Oracle 11g
  •  List-range partitioning was introduced in Oracle 11g
  •  List-hash partitioning was introduced in Oracle 11g
  •  List-list partitioning was introduced in Oracle 11g
  •  Interval-range partitioning was introduced in Oracle 11g
  •  Interval-list partitioning was introduced in Oracle 11g
  •  Interval-hash partitioning was introduced in Oracle 11g
  •  Hash-hash partitioning was introduced in Oracle 11gR2


For More details










SUB-PARTITIONS


Utilized most often when partition strategy does not provide small enough partition units to achieve maintenance goals. Sub-partitions further divide table based another column with partitions.