Good morning!
I want to create some objects (functions and procedures) from schema "ORGAPP" to "ATLANTE_CONF" ..
I have this query :
select case
when s.line = 1 then
'create or replace '||case
when s.text like '%FUNCTION%' THEN
REPLACE(REPLACE(S.TEXT,'FUNCTION','FUNCTION ATLANTE_CONF.'),' ')
ELSE REPLACE(REPLACE(S.TEXT,'PROCEDURE','PROCEDURE ATLANTE_CONF.'),' ')
end
end
||
case
when s.line > 1 then
replace(s.text,'ORGAPP','ATLANTE_CONF')
end
from all_source s
where s.OWNER = 'ORGAPP'
and s.TYPE in ('FUNCTION','PROCEDURE') ;
The output is all source of distinct objects, all together..
I thought about take a nested table type with a cursor, and then using an EXECUTE IMMEDIATE.
My problem is when I have to separate the objects on the string "cadena" and creating them separately whit dynamic sql...
This is my begin block :
begin
V_OB := tab_obj();
V_OB.EXTEND;
open c_obj;
LOOP
FETCH C_OBJ INTO V_OB(cont);
CADENA := CADENA||CHR(9)||V_OB(cont);
-- how to separate functions on the "cadena" before "execute inmediate"
execute immediate cadena;
cont := cont +1
dbms_output.put_line(CADENA);
CADENA := ' ';
V_OB.TRIM;
END LOOP;
CLOSE C_OBJ;
Right now the situation is a bit different. I think is better copy all objects from a schema to another one on the same instance.
There are a lot of indexes, sequences, triggers, and more dependences... doing it manually would be horrible..
First of all, I'm using version 11g and PL/SQL Developer IDE
I'll try with datapump. It's my first time with this tool...
Thanks!