Harvest : Get last file version

Imagine this situation:

1. You have two package P1 and P2 in the same stage, and both P1 (who is to be installed first) and P2 have an attached file F1;
2. In P1 the F1 file is in version 1, but in P2 is in version 3;

When I try to execute P1 and I try to get F1 file, Harvest return me F1 in version 3, when it has supposed to return version 1 of the file.

So take care when promoting 2 or more packages, to the same stage, with different version of the same file. Harvest only get the last version of it, regardless what version you included in your package.

WHy MINUS does NOT EXISTs in our queries?

Read this great (and old) article about MINUS vs NOT EXISTS operators.

There really isn’t just one right way to design queries. In some cases, you really are better off using Not Exists. In many cases, however, you should use the set operator MINUS. Once you understand the principles, you can easily choose the best method for your particular case.

Joins : Please talk ANSI SQL with me...

People, who had to port a Microsoft SQL Server or Access database over to Oracle, know what I'm talking about. This article show us a good example of ANSI SQL joins and also show us a few good reasons why we should use ANSI join syntax instead of classic and specific join syntax :

1. You don't need to specify the join condition in a natural join;
2. You can do more with ANSI SQL join syntax than with classic Oracle syntax;
3. There is no performance penalty;
4. In the long run you will see more of it, and less of the older syntax;
5. It's portable;
6. It's easier to understand what is happening .

Bottom line is :

So join me in creating portable SQL joins that are easy to read, do more than you can with Oracle syntax, and don't slow you down.

S_POSTN_CON : Description

This table associates a contact with positions that can acess it. This relation is suported by column con_id (par_row_id from s_contact) and postn_id (row_id from s_postn).
This is a many-to-many relationship : A contact can have more than one position associated, and a position can be related with multiple contacts.
Keep in mind that con_id is foreign key from s_party.

SQL Navigator : Code Templates

After trying for a while to use SQL Navigator from Quest to develope my pl/sql procedures and queries, I've found a new (for me) feature : Code templates. Tired of write allways the same kind of code structures ? Use code templates. How? It's easy. Just take a look at this help page from navigator help. (As a good developer that I am, I never read help files).

You can program Key combinations from "Ctrl+Shift+A" to "Ctrl+Shift+Z" with text up to 255 characters in length.
To program shortcut keys

1. From the View menu, choose Preferences.
2. Under the Code Editors preference heading, expand General, and then scroll down to the Templates category.
3. Where you see the words , click and then view or change shortcut key assignments as required.

Try it, and watch your development times drop a little bit !!!

PL/SQL : Check script for object owners

In real life development environments, you develope a script logged as UserX, and someone in Support Team will install it in production environment logged as UserZ.
So far, nothing unusual happens. But, if we are talking about oracle scripts, there's something that you have to deal with, Schemas, If you didn't reference all you objects with full name, SchemaX.TableA for instance, when UserZ tries to install it, he will not find TableA in is schema.

In simple scripts, with few lines, it's easy to manually check for errors, but try to do it in a scripy with thousand of lines...

So I've developed this procedure to "parse" my code looking for this kind of errors

FUNCTION CHECK_OWNER_IN_SQL(
p_list varchar2,
p_del varchar2
) return boolean
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
l_value varchar2(32767);
keyword varchar2(10000);
existsObject integer;
result boolean;
begin
result := false;
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
keyword := substr(l_list,1,l_idx-1);
select count(*) into existsObject from user_objects where lower(object_name) = lower(keyword);

if existsObject > 0 then
DBMS_OUTPUT.Put_Line( 'Possible error in ' keyword);
result := true;
end if;

l_list := substr(l_list,l_idx+length(p_del));
else
keyword := l_list;
select count(*) into existsObject from user_objects where lower(object_name) = lower(keyword);

if existsObject > 0 then
DBMS_OUTPUT.Put_Line( 'Possible error in ' keyword );
result := true;
end if;
exit;
end if;
end loop;
return result;
end;



--Possible values for p_type are :
-- DATABASE LINK,FUNCTION,INDEX,PACKAGE,PACKAGE BODY,PROCEDURE,SEQUENCE,SYNONYM,TABLE,TRIGGER,TYPE,VIEW

PROCEDURE CHECK_OWNER(
p_owner IN varchar2,
p_type IN varchar2,
p_name IN varchar2,
p_sql IN varchar2 := NULL
)
IS
cursor lines
is
Select text from all_source where lower(owner) = lower(p_owner) and lower(type) = lower(p_type) and lower(name) = lower(p_name) order by line;
sqltext varchar2(32000);
result boolean;
linha integer;
begin
if p_sql is not null then
result := siebel.check_owner_in_sql(p_sql , ' ');
else
linha := 1;
for line in lines loop
sqltext := TRIM(line.text);
IF substr(sqltext,1,2) <> '--' then
if siebel.check_owner_in_sql(sqltext , ' ') then
DBMS_OUTPUT.Put_Line( 'Linha ' linha ': ' sqltext);
end if;
end if;
linha := linha + 1;
end loop;
if linha = 1 then
DBMS_OUTPUT.Put_Line( 'Não foi encontrado o objecto a validar');
end if;
end if;
end;

After create this two procedures, you have only to execute the last one, and watch for possible warnings in your output window.

How to relate a contact whit their accounts

Contact accounts can be found in S_PARTY_PER intersection table. This table relates person_id column (par_row_id from s_contact) with party_id column (par_row_id from s_org_ext). Both person_id and party_id are foreign keys from s_party.