Is your IDE quite enough?

I've used a number IDE tools since begining of my career as Oracle Developer. Frankly, i have not seen any IDE tool quite enough for light-hearted development. I've used Quest Software TOAD, Quest Software SQL Navigator, TOra, Allround Automations PL/SQL Developer, Oracle SQL Developer IDE and (i)SQL*Plus command-line tool. This days the only I continue to use in daily work are Allround Automations PL/SQL Developer and SQL*Plus.

The thing I'm going to share is simple idea as do the things as the most people do otherwise you're getting more changes for failure then for success.

Riskproof solution is usually something we should pay for. What does it actaully mean?

Well, in the context of the IDE tools it means that paying for some tool we actaully paying for a bunch of features to give us facilities for effective, qualitative, light-hearted development/tracing/debugging... whatever. What's going on in reality, hmmm...

Well, the most important is that IDE is exactly a bunch of features. So before any design/implementation it necessary to think about do the means corespond to wishes and what are the risks?.

The company issued the IDE tools want to sell as many as it possible. Their sales directly depends on a bunch of features/solutions product intend to resolve in customer's hands.

What does the IDE development company do if the feature required by the most developers is very expensive to be issued? Probably company do invest in such feature because it gives facility to attract more customers and of course more sales.

What does the company do if the feature is very expensive to be issued and not hotly required by customers, probably the company wont to do anything :)

But what does company intend to do if the initial development enviroment provides features that expansive to be build in IDE tool and not hotly required among developers, but required to support the features of initial enviroment?. Seems there is no easy answer.

Back to the risks idea, as developer doing the risky thing he's agreed with the high probability of failure but in case of success he could get some additinal reward for success doing something that no many people/IDE tools/tools whatever can do. Will his manager be happy with tons of hours spent by developer to "invent" the solution? :)

In point of view Oracle database as initial environment and IDE tools, the existing risks are that IDE tool could not provide the facilities to do necessary actions.

The example I recently came across is that non of the IDE tools I listed could correctly work with object types and collections even Oracle released it long ago. The action that I tried to do is to simple export the data as DML statement from the table with column of object and collection type. Non of the tools listed before really did it for me! It turns out, it was just a tip of the iceberg.

Here some initial case "for entrance" for your lovely IDE:

drop table t
/

create or replace type COORDINATES as object
(
X NUMBER,
Y NUMBER,
Z NUMBER
)
/       

create or replace type COORD_ELEM_ARRAY as varray (1048576) of number
/        

create table t 
(SDO_POINT       COORDINATES,
SDO_ELEM_INFO   COORD_ELEM_ARRAY
)
/

declare
l_array COORD_ELEM_ARRAY := COORD_ELEM_ARRAY();
begin
for v_idx in 1 .. 10
loop
select level LVL bulk collect into l_array from DUAL connect by level <= TRUNC(DBMS_RANDOM.VALUE(1, 1000));
        insert into T
        values
   (COORDINATES(DBMS_RANDOM.VALUE(1, 10), DBMS_RANDOM.VALUE(1, 10), DBMS_RANDOM.VALUE(1, 10)),
    cast(l_array as COORD_ELEM_ARRAY));
    end loop;
end;
/

commit
/

Let's do export data in IDE: PL/SQL Developer 8.0 :

Table T containts one or more COORDINATES columns. Cannot export in SQL format.

Anyway it's better to say "i'm not able to do it" then do the things like the following instance : TOAD 9.0:

INSERT INTO T ( SDO_POINT, SDO_ELEM_INFO ) VALUES ((7.59790885944101; 7.69515627936729; 3.30055431652344), (1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64; 65; 66; 67; 68; 69; 70; 71; 72; 73; 74; 75; 76; 77; 78; 79; 80; 81; 82; 83; 84; 85; 86; 87; 88; 89; ; ; ; ; ; ; ; ; ; ; ));
...

Oracle SQL Developer 2.1.1 :

INSERT INTO T (SDO_POINT,SDO_ELEM_INFO) values (MOAC_DM.COORDINATES(7.59790885944101193695690786927340040402,7.69515627936729390702847190739102900367,3.30055431652344062870531595337988496736),MOAC_DM.COORD_ELEM_ARRAY('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89'));

...

Seems it's the only IDE able to do such simple thing. Oki, but let's a bit complicate the task like following:

drop table t
/

drop type  COORD_OBJECT
/


create or replace type COORDINATES as object
(
    X NUMBER,
    Y NUMBER,
    Z NUMBER
)
/       

create or replace type COORD_ELEM_ARRAY as varray (1048576) of VARCHAR2(32)
/        


create or replace type COORD_OBJECT as object
(
    SDO_NAME      VARCHAR2(32),
    SDO_POINT  COORDINATES,
    SDO_ELEM_INFO COORD_ELEM_ARRAY
)
/

create table t 
(SDO_OBJECCT  COORD_OBJECT)
/

declare
    l_array COORD_ELEM_ARRAY := COORD_ELEM_ARRAY();
begin
    for v_idx in 1 .. 10
    loop
        select level LVL bulk collect into l_array from DUAL connect by level <= TRUNC(DBMS_RANDOM.VALUE(1, 1000));
        insert into T
        values
   (COORD_OBJECT(to_char(trunc(DBMS_RANDOM.VALUE(1, 10))), COORDINATES(DBMS_RANDOM.VALUE(1, 10), DBMS_RANDOM.VALUE(1, 10), DBMS_RANDOM.VALUE(1, 10)), 
    cast(l_array as COORD_ELEM_ARRAY)));
    end loop;
end;
/

Oracle SQL Developer 2.1.1:

INSERT "T" (SDO_OBJECCT) values ();
...

Hmmm... seems it's too complicated for Oracle SQL Developer. Even it retrives accurate number of rows but with one empty column. So is your "instrument" quite enough for the action you doing?! Doing your own insert statement generator to grab the data you may measure noses with following:

The INSERT statement in Oracle SQL has a limit of 999 arguments. Therefore, you cannot create a variable-length array of more than 999 elements using the SDO_GEOMETRY constructor inside a transactional INSERT statement; however, you can insert a geometry using a host variable, and the host variable can be built using the SDO_GEOMETRY constructor with more than 999 values in the SDO_ORDINATE_ARRAY specification. (The host variable is an OCI, PL/SQL, or Java program variable.) Oracle® Spatial Developer's Guide 11g Release 1 (11.1)

It's means that you could not insert more then 999 collection members at once in SQL statement. The things are getting more complicated. Who does create collection less then 999 items inside?! Do you?! Well, the workaround could be in use PL/SQL like following:

create or replace package COORD_ELEM_ARRAY_UTIL as
    g_coord_elem_array COORD_ELEM_ARRAY := COORD_ELEM_ARRAY();

    procedure ADD_COORD_ELEM_ARRAY(p_value COORD_ELEM_ARRAY);
    procedure PURGE_COORD_ELEM_ARRAY;

end COORD_ELEM_ARRAY_UTIL;
/

create or replace package body COORD_ELEM_ARRAY_UTIL as
    procedure ADD_COORD_ELEM_ARRAY(p_value COORD_ELEM_ARRAY) as
        l_cnt NUMBER;
    begin
        l_cnt := g_coord_elem_array.count;
        for l_idx in 1 .. p_value.count
        loop
           g_coord_elem_array.extend;
           g_coord_elem_array(l_cnt + l_idx) := p_value(l_idx);
        end loop;
    end ADD_COORD_ELEM_ARRAY;

    procedure PURGE_COORD_ELEM_ARRAY as
    begin
        g_coord_elem_array.delete;
    end PURGE_COORD_ELEM_ARRAY;

begin
    null;
end COORD_ELEM_ARRAY_UTIL;
/

begin  COORD_ELEM_ARRAY_UTIL.PURGE_COORD_ELEM_ARRAY; end;    
/                                                                                   

declare
    l_arry COORD_ELEM_ARRAY;
begin
    select * bulk collect into l_arry from table(COORD_ELEM_ARRAY( COLLECTION_MEMBERS ));
    COORD_ELEM_ARRAY_UTIL.ADD_COORD_ELEM_ARRAY(l_arry);
end;
/

...


declare
    l_array COORD_ELEM_ARRAY := COORD_ELEM_ARRAY_UTIL.G_COORD_ELEM_ARRAY;
begin
    insert into T
    values
        (COORD_OBJECT('1',
    COORDINATES(DBMS_RANDOM.VALUE(1, 10), DBMS_RANDOM.VALUE(1, 10), DBMS_RANDOM.VALUE(1, 10)),
     COORD_ELEM_ARRAY_UTIL.G_COORD_ELEM_ARRAY ));
end;
/

commit
/


begin  COORD_ELEM_ARRAY_UTIL.PURGE_COORD_ELEM_ARRAY; end;  
/

BUT! It's usually better to simplify the things then gets all that annoyning stuff. Do the things in traditinal way at least there is a less chance to lose.