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.
