SQL Developer a great tool but...

Tools

SQL Developer a great tool but...

Von Tobias Arnhold → 8.21.2013

Actually I'm impressed from the speed (even so it is a Java based application), the easy handling and the integration into APEX.
For example remote debugging possibilities inside an APEX application: http://www.oracle.com/webfolder/technetwork/de/community/apex/tipps/remote-debug/index.html

Currently there are two things I really don't like.

Autocomplete Feature when I open a table and checking the data. 
If I click on the autocomplete it sometimes adds it at the end of my text. Instead of dropping my text and replacing it with the autocompleted text.

View with Trigger
When I use a view with an InsteadOf-Trigger and later I need to update the view. With the SQL Developer View Editor actually it does delete my Trigger. Even the Fast DDL feature does not include the trigger. Hope this is fixed in the next version?
Example: http://www.apex-at-work.com/2013/03/apex-tabular-form-auf-basis-einer-view.html
My workaround is to add the Instead of Trigger as Comment behind my the sql of the view.

Example Merge Procedure

APEX examples

Example Merge Procedure

Von Tobias Arnhold → 8.18.2013

I often have used UPDATE and INSERT statements during my development. In some complex updates especially when I had to select from other tables I sometimes got strange problems.

I even was able to update the wrong data because my statement was not correctly designed. After this experience I decided to switch to MERGE-statements. Merge Statements are easy to read especially when you use difficult select statements inside.

Here is an example package with an MERGE statement inside. It should show you how such an statement could look like and for me it is a good reminder how to design the code.

create or replace 
PACKAGE BODY PKG_MERGE_EXAMPLE AS
/* Package Variables */
  gv_proc_name    VARCHAR2(100);
  gv_action       VARCHAR2(4000);
  gv_ora_error    VARCHAR2(4000);
  gv_custom_error VARCHAR2(4000);
  gv_parameter    VARCHAR2(4000);
  gv_user         VARCHAR2(20) := UPPER(NVL(v('APP_USER'),USER));
  
/* Save errors */
/*
  --------------------------------------------------------
  --  DDL for Table ZTA_ERR_LOG
  --------------------------------------------------------

  CREATE TABLE "ERR_LOG" 
   ( "PROC_NAME" VARCHAR2(200), 
 "ACTION" VARCHAR2(4000), 
 "APP_ID" NUMBER, 
 "APP_PAGE_ID" NUMBER, 
 "APP_USER" VARCHAR2(20), 
 "ORA_ERROR" VARCHAR2(4000), 
 "CUSTOM_ERROR" VARCHAR2(4000), 
 "PARAMETER" VARCHAR2(4000), 
 "TIME_STAMP" DATE
   ) ;
/

*/
PROCEDURE ADD_ERR  IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
     INSERT
     INTO ERR_LOG
      ( PROC_NAME,ACTION,APP_ID,APP_PAGE_ID,APP_USER,ORA_ERROR,CUSTOM_ERROR,PARAMETER,TIME_STAMP )
      VALUES
      ( gv_proc_name,gv_action,nvl(v('APP_ID'),0),nvl(v('APP_PAGE_ID'),0),nvl(nvl(v('APP_USER'),USER),'Unknown'),
        gv_ora_error,gv_custom_error,gv_parameter,sysdate );
     COMMIT;
END;


/* ************************************************************************************************************************************** */
/* Merge Example                                                                                                                          */  
/* ************************************************************************************************************************************** */

PROCEDURE prc_merge_example
IS
BEGIN
  gv_proc_name := 'pkg_merge_example.prc_merge_example';
  gv_parameter := '';
  
  gv_action := 'Merge Data instead of update and insert'; 
  MERGE INTO TBL_MERGE_FACILITY t1
   USING (
          SELECT  t2.id,
                  t2.facility_name,
                  t3.address
                  gv_user as current_user,
                  sysdate as current_timestamp
          FROM TBL_FACILITY t2, TBL_ADDRESS t3
          WHERE t2.address_id = t3.id
          AND   t2.activ = 1
         ) t4
  ON (t1.facility_id = t4.id)
  WHEN MATCHED THEN
    UPDATE SET
      t1.facility_name    = t4.facility_name,
      t1.facility_address = t4.address,
      t1.updated_by       = t4.current_user,
      t1.updated_on       = t4.current_timestamp
  WHEN NOT MATCHED THEN
    INSERT
  (
    facility_id,
    facility_name,
    facility_address,
    created_by,
    created_on
  )
  VALUES
  (
    t4.id,
    t4.facility_name,
    t4.address,
    t4.current_user,
    t4.current_timestamp
  );

  gv_action := 'Updated rows: ' sql%rowcount; 
  
  COMMIT;
EXCEPTION
WHEN OTHERS THEN   
      gv_ora_error := SQLERRM;
      gv_custom_error := 'Internal Error. Action canceled.';
      ROLLBACK;
      ADD_ERR; raise_application_error(-20001, gv_custom_error);
END;
 
END PKG_MERGE_EXAMPLE;

Connect a grouped report to an ungrouped report with virtual ID column

APEX examples

Connect a grouped report to an ungrouped report with virtual ID column

Von Tobias Arnhold → 8.16.2013

Seems to be a simple problem. I have and grouped report where I want to see all facilities on a address.

For example:
Grouped View

Edit	Address	Amount of facilities
x	Germany, Dresden, Dresdner Strasse 1	2
x	Germany, Frankfurt, Frankfurter Strasse 1	3

Detail View

Address	Facility
Germany, Dresden, Dresdner Strasse 1	Computer System EXAXY
Germany, Dresden, Dresdner Strasse 1	Computer System KI
Germany, Frankfurt, Frankfurter Strasse 1	Manufactoring System 007
Germany, Frankfurt, Frankfurter Strasse 1	Manufactoring System 009
Germany, Frankfurt, Frankfurter Strasse 1	Manufactoring System 028

How to achieve this when we do not have a primary key and our key column includes commas which breaks the link. We easily generate an own ID column via an analytical function. In both reports we use an Oracle view to get the data from and our view looks like that:

select   facility_address,
         facility_name,
         dense_rank() over (order by facility_address) as facility_id
from     facilities

Via the function dense_rank and the partition by facility_address we get an unique ID for all facilities based on the address.
The grouped report looks like that:

select   facility_id,
         facility_address,
         count(facility_name) as amount_of_facilities
from     facilities

The detail report looks like that

select   facility_address,
         facility_name
from     facilities
where    facility_id = :P2_FACILITY_ID

Now you need to add a link in the report attributes section inside the grouped report. We set the item :P2_FACILITY_ID with our column #FACILITY_ID#.
That's it.