Hi Tom,
I need your expertise in this regard.
I´m using a self programmed version control system to upgrade my customers systems.
In it all the changeset with packages, functions, DDL commands, DML commands and all this are saved in BLOBs in the database.
To Update my customers all these files and commands are written in a big sql-file which containt everything.
There are create statements, inserts, updates, alter table,...and all this in it.
As a next step i´m executing this file in a SQL-plus on the customers system.
It works fine but i´m looking for a better way with more control and logging.
So i´m testing how i can execute multiple ddl statements and all these sources dynamically.
I´m giving some examples the sources are stored:
My dbupdaes are stored like this for example
(as one file stored in a blob with multiple ddl statements):
-- Create table
create table T605SCFG
(
betart NUMBER(6) not null,
core_lang VARCHAR2(4) default 'DE' not null,
hostname VARCHAR2(100) not null,
portnumber NUMBER(6) default 17000 not null,
core_name VARCHAR2(30) not null,
core_repl_name VARCHAR2(40) not null
);
-- Add comments to the table
comment on table T605SCFG
is 'Konfiguration für SOLR';
-- Add comments to the columns
comment on column T605SCFG.betart
is 'Mandantnummer des Artikelstamms';
-- Create/Recreate primary, unique and foreign key constraints
alter table T605SCFG
add constraint T605SCFG_PK primary key (BETART, CORE_LANG);
alter table T605SCFG
add constraint T605SCFG_UX1 unique (CORE_NAME);
It´s the same for functions, packages, procedures,...
One more example für a package how it´s stored:
create or replace package shc_ab003_objects is
-- public functions and procedures
-- einen Positionslauf zurückgeben (auf T087poli basierend)
function get_t_ab003_poli(pi_manr number
,pi_ainr number
,pi_aufpos number
,pi_lauf number
) return t_ab003_poli;
.
.
.
.
.
.
end shc_ab003_objects;
/
create or replace package body shc_ab003_objects is
-- Cursor-Getter für T_AB003_POLI
procedure get_t_ab003_poli_cursor(po_cursor out sys_refcursor
,pi_manr in number
,pi_ainr in number
,pi_aufpos in number
,pi_lauf in number default 0
) is
v_lauf number := nvl(pi_lauf, 0);
begin
open po_cursor for
select t_ab003_poli(t1.manr, t1.ainr, t1.lauf, t1.aufpos, t1.menge
,t1.stat, t1.fstat, t1.diffvw, t1.diffew, t1.vkpr
,t1.eipr, t1.raba, t1.arnr, t1.kdnr, t1.fr5
,decode(t2.mwco, null, 0, t2.mwco), t1.baspr
)
from t087poli t1
left outer join t387poli_zusi t2 on (t2.manr = t1.manr and t2.ainr = t1.ainr and t2.aufpos = t1.aufpos and t2.lauf = t1.lauf)
where 1=1
and t1.manr = pi_manr
and t1.ainr = pi_ainr
and t1.aufpos = pi_aufpos
and (v_lauf = 0 or v_lauf = t1.lauf)
order by t1.lauf
;
end;
.
.
.
.
.
.
.
begin
-- Initialisierung
null;
end shc_ab003_objects;
/
Now i´m looking for a way to execute them in the same structure they´re stored.
Maybe there is a way with execute immediate or a dbms-package or anything else which i don´t know...
Big Thanks for your help!