Ok, Dimitri,
I'm sorry for this quite unhandy example - but it does what it's
supposed to do...
Regards, Thomas
>> I get this error
>>
>> Statement failed, SQLSTATE = 2F000
>> sort record size of 187704 bytes is too big
>> -Error while parsing procedure PKG$.P_SOMEPROCEDURE's BLR
>>
>> on recreating the package body.
>
> It has nothing to do with the package/procedure size, it's about size of
> the explicit or implicit sorting set ("select * order/group by" etc)
> inside that procedure.
>
>> Creating the procedure outside a package works fine.
>
> That's weird. A test case would be appreciated.
>
> Dmitry
--
Mit freundlichen Grüßen,
Thomas Beckmann
Diplom-Informatiker
Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckm...@assfinet.de <mailto:thomas.beckm...@assfinet.de>
ASSFINET-Logo
*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
i...@assfinet.de <mailto:i...@assfinet.de> • www.assfinet.de
<http://www.assfinet.de/>
Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331
Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.
create domain D_DBENTITY as char(31) character set UNICODE_FSS collate
UNICODE_FSS;
create domain D_VC120 as varchar(120) character set NONE default '' collate
NONE;
create domain D_SQL_STMT as varchar(12500) character set NONE collate NONE;
set term ^ ;
create or alter procedure P_RBLD_VW (TBL D_DBENTITY) returns (RESULT D_VC120)
as
declare variable V D_SQL_STMT;
declare variable TI D_SQL_STMT;
declare variable TU D_SQL_STMT;
declare variable TD D_SQL_STMT;
declare variable CV D_SQL_STMT;
declare variable CI D_SQL_STMT;
declare variable CU D_SQL_STMT;
declare variable CD D_SQL_STMT;
begin
for with recursive
CTE_FLDLST as
(select
TBL,
I,
FLD_P,
FLD_S,
FLD_IP,
FLD_IV,
FLD_U
from (select first 1
rf.RDB$RELATION_NAME as TBL,
rf.RDB$FIELD_POSITION as I,
cast(trim(rf.RDB$FIELD_NAME) as D_SQL_STMT) as FLD_P,
cast('x.' || trim(rf.RDB$FIELD_NAME) as D_SQL_STMT) as FLD_S,
cast(iif(f.RDB$COMPUTED_BLR is null, trim(rf.RDB$FIELD_NAME), null)
as D_SQL_STMT) as FLD_IP,
cast(iif(f.RDB$COMPUTED_BLR is null, 'new.' ||
trim(rf.RDB$FIELD_NAME), null) as D_SQL_STMT) as FLD_IV,
cast(iif(f.RDB$COMPUTED_BLR is null, trim(rf.RDB$FIELD_NAME) ||
'=new.' || trim(rf.RDB$FIELD_NAME), null) as D_SQL_STMT) as FLD_U
from (select
rf.RDB$RELATION_NAME
from RDB$RELATION_FIELDS rf
where rf.RDB$RELATION_NAME = :TBL and rf.RDB$RELATION_NAME starting
with 'T_' and rf.RDB$FIELD_SOURCE = 'D_PK_UUID') t
join RDB$RELATION_FIELDS rf on rf.RDB$RELATION_NAME =
t.RDB$RELATION_NAME and rf.RDB$SYSTEM_FLAG = 0
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE
order by rf.RDB$FIELD_POSITION)
union all select
TBL,
rf.RDB$FIELD_POSITION as I,
coalesce(FLD_P || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0,
trim(rf.RDB$FIELD_NAME), '') as FLD_P,
coalesce(FLD_S || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0, 'x.' ||
trim(rf.RDB$FIELD_NAME), '') as FLD_S,
coalesce(FLD_IP || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0 and
f.RDB$COMPUTED_BLR is null, trim(rf.RDB$FIELD_NAME), '') as FLD_IP,
coalesce(FLD_IV || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0 and
f.RDB$COMPUTED_BLR is null, 'new.' || trim(rf.RDB$FIELD_NAME), '') as FLD_IV,
coalesce(FLD_U || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0 and
f.RDB$COMPUTED_BLR is null, trim(rf.RDB$FIELD_NAME) || '=new.' ||
trim(rf.RDB$FIELD_NAME), '') as FLD_U
from CTE_FLDLST t
join RDB$RELATION_FIELDS rf on rf.RDB$RELATION_NAME = t.TBL and
rf.RDB$FIELD_POSITION = I + 1
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE)
select
RESULT,
'create or alter view ' || VW || '(' || x'0a' ||
' ' || FLD_P || ')' || x'0a' ||
'as' || x'0a' ||
'select' || x'0a' ||
' ' || FLD_S || x'0a' ||
'from ' || TBL || ' x' || x'0a' ||
'join T_MANDANT_UID m on m.UID = x.UID and m.FK_MANDANT =
coalesce(RDB$GET_CONTEXT(''USER_SESSION'', ''ASS$MANDANT''), '''');' as V,
'create or alter trigger ASS$VWI_' || left(TBL, 25) || ' for ' || VW || '
active before insert position 10' || x'0a' ||
'as' || x'0a' ||
'begin' || x'0a' ||
' insert into T_' || TBL || ' (' || x'0a' ||
' ' || FLD_IP || ')' || x'0a' ||
' values (' || x'0a' ||
' ' || FLD_IV || ');' || x'0a' ||
'end' as TI,
'create or alter trigger ASS$VWU_' || left(TBL, 25) || ' for ' || VW || '
active before update position 10' || x'0a' ||
'as' || x'0a' ||
'begin' || x'0a' ||
' update T_' || TBL || ' set' || x'0a' ||
' ' || FLD_U || x'0a' ||
' where UID = old.IUD;' || x'0a' ||
'end' as TU,
'create or alter trigger ASS$VWD_' || left(TBL, 25) || ' for ' || VW || '
active before delete position 10' || x'0a' ||
'as' || x'0a' ||
'begin' || x'0a' ||
' delete from T_' || TBL || ' where UID = old.IUD;' || x'0a' ||
'end' as TD,
'comment on view ' || VW || ' is ''VIEW WITH MANDANT FILTER (DO NOT
TOUCH)''' as CVW,
'comment on trigger ASS$VWI_' || left(TBL, 25) || ' is ''VIEW TRIGGER (DO
NOT TOUCH)''' as CI,
'comment on trigger ASS$VWU_' || left(TBL, 25) || ' is ''VIEW TRIGGER (DO
NOT TOUCH)''' as CU,
'comment on trigger ASS$VWD_' || left(TBL, 25) || ' is ''VIEW TRIGGER (DO
NOT TOUCH)''' as CD
from (select first 1
TBL || ': View ...' as RESULT,
trim(TBL) as TBL,
trim(substring(TBL from 3)) as VW,
FLD_P,
FLD_S,
FLD_IP,
FLD_IV,
FLD_U
from CTE_FLDLST order by I desc)
into :RESULT, :V, :TI, :TU, :TD, :CV, :CI, :CU, :CD
do
begin
suspend;
RESULT = TBL || ': View and trigger recreated';
begin
in autonomous transaction do execute statement :V;
in autonomous transaction do execute statement :TI;
in autonomous transaction do execute statement :TU;
in autonomous transaction do execute statement :TD;
in autonomous transaction do execute statement :CV;
in autonomous transaction do execute statement :CI;
in autonomous transaction do execute statement :CU;
in autonomous transaction do execute statement :CD;
when any do
RESULT = TBL || ': View and trigger creation *failed*';
end
suspend;
end
end^
set term ; ^
set term ^ ;
create or alter package PKG$
as
begin
procedure P_RBLD_VW (TBL D_DBENTITY) returns (RESULT D_VC120);
end^
set term ; ^
set term ^ ;
recreate package body PKG$
as
begin
procedure P_RBLD_VW (TBL D_DBENTITY) returns (RESULT D_VC120)
as
declare variable V D_SQL_STMT;
declare variable TI D_SQL_STMT;
declare variable TU D_SQL_STMT;
declare variable TD D_SQL_STMT;
declare variable CV D_SQL_STMT;
declare variable CI D_SQL_STMT;
declare variable CU D_SQL_STMT;
declare variable CD D_SQL_STMT;
begin
for with recursive
CTE_FLDLST as
(select
TBL,
I,
FLD_P,
FLD_S,
FLD_IP,
FLD_IV,
FLD_U
from (select first 1
rf.RDB$RELATION_NAME as TBL,
rf.RDB$FIELD_POSITION as I,
cast(trim(rf.RDB$FIELD_NAME) as D_SQL_STMT) as FLD_P,
cast('x.' || trim(rf.RDB$FIELD_NAME) as D_SQL_STMT) as FLD_S,
cast(iif(f.RDB$COMPUTED_BLR is null, trim(rf.RDB$FIELD_NAME), null)
as D_SQL_STMT) as FLD_IP,
cast(iif(f.RDB$COMPUTED_BLR is null, 'new.' ||
trim(rf.RDB$FIELD_NAME), null) as D_SQL_STMT) as FLD_IV,
cast(iif(f.RDB$COMPUTED_BLR is null, trim(rf.RDB$FIELD_NAME) ||
'=new.' || trim(rf.RDB$FIELD_NAME), null) as D_SQL_STMT) as FLD_U
from (select
rf.RDB$RELATION_NAME
from RDB$RELATION_FIELDS rf
where rf.RDB$RELATION_NAME = :TBL and rf.RDB$RELATION_NAME starting
with 'T_' and rf.RDB$FIELD_SOURCE = 'D_PK_UUID') t
join RDB$RELATION_FIELDS rf on rf.RDB$RELATION_NAME =
t.RDB$RELATION_NAME and rf.RDB$SYSTEM_FLAG = 0
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE
order by rf.RDB$FIELD_POSITION)
union all select
TBL,
rf.RDB$FIELD_POSITION as I,
coalesce(FLD_P || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0,
trim(rf.RDB$FIELD_NAME), '') as FLD_P,
coalesce(FLD_S || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0, 'x.' ||
trim(rf.RDB$FIELD_NAME), '') as FLD_S,
coalesce(FLD_IP || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0 and
f.RDB$COMPUTED_BLR is null, trim(rf.RDB$FIELD_NAME), '') as FLD_IP,
coalesce(FLD_IV || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0 and
f.RDB$COMPUTED_BLR is null, 'new.' || trim(rf.RDB$FIELD_NAME), '') as FLD_IV,
coalesce(FLD_U || ',', '') || iif(rf.RDB$SYSTEM_FLAG = 0 and
f.RDB$COMPUTED_BLR is null, trim(rf.RDB$FIELD_NAME) || '=new.' ||
trim(rf.RDB$FIELD_NAME), '') as FLD_U
from CTE_FLDLST t
join RDB$RELATION_FIELDS rf on rf.RDB$RELATION_NAME = t.TBL and
rf.RDB$FIELD_POSITION = I + 1
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE)
select
RESULT,
'create or alter view ' || VW || '(' || x'0a' ||
' ' || FLD_P || ')' || x'0a' ||
'as' || x'0a' ||
'select' || x'0a' ||
' ' || FLD_S || x'0a' ||
'from ' || TBL || ' x' || x'0a' ||
'join T_MANDANT_UID m on m.UID = x.UID and m.FK_MANDANT =
coalesce(RDB$GET_CONTEXT(''USER_SESSION'', ''ASS$MANDANT''), '''');' as V,
'create or alter trigger ASS$VWI_' || left(TBL, 25) || ' for ' || VW ||
' active before insert position 10' || x'0a' ||
'as' || x'0a' ||
'begin' || x'0a' ||
' insert into T_' || TBL || ' (' || x'0a' ||
' ' || FLD_IP || ')' || x'0a' ||
' values (' || x'0a' ||
' ' || FLD_IV || ');' || x'0a' ||
'end' as TI,
'create or alter trigger ASS$VWU_' || left(TBL, 25) || ' for ' || VW ||
' active before update position 10' || x'0a' ||
'as' || x'0a' ||
'begin' || x'0a' ||
' update T_' || TBL || ' set' || x'0a' ||
' ' || FLD_U || x'0a' ||
' where UID = old.IUD;' || x'0a' ||
'end' as TU,
'create or alter trigger ASS$VWD_' || left(TBL, 25) || ' for ' || VW ||
' active before delete position 10' || x'0a' ||
'as' || x'0a' ||
'begin' || x'0a' ||
' delete from T_' || TBL || ' where UID = old.IUD;' || x'0a' ||
'end' as TD,
'comment on view ' || VW || ' is ''VIEW WITH MANDANT FILTER (DO NOT
TOUCH)''' as CVW,
'comment on trigger ASS$VWI_' || left(TBL, 25) || ' is ''VIEW TRIGGER
(DO NOT TOUCH)''' as CI,
'comment on trigger ASS$VWU_' || left(TBL, 25) || ' is ''VIEW TRIGGER
(DO NOT TOUCH)''' as CU,
'comment on trigger ASS$VWD_' || left(TBL, 25) || ' is ''VIEW TRIGGER
(DO NOT TOUCH)''' as CD
from (select first 1
TBL || ': View ...' as RESULT,
trim(TBL) as TBL,
trim(substring(TBL from 3)) as VW,
FLD_P,
FLD_S,
FLD_IP,
FLD_IV,
FLD_U
from CTE_FLDLST order by I desc)
into :RESULT, :V, :TI, :TU, :TD, :CV, :CI, :CU, :CD
do
begin
suspend;
RESULT = TBL || ': View and trigger recreated';
begin
in autonomous transaction do execute statement :V;
in autonomous transaction do execute statement :TI;
in autonomous transaction do execute statement :TU;
in autonomous transaction do execute statement :TD;
in autonomous transaction do execute statement :CV;
in autonomous transaction do execute statement :CI;
in autonomous transaction do execute statement :CU;
in autonomous transaction do execute statement :CD;
when any do
RESULT = TBL || ': View and trigger creation *failed*';
end
suspend;
end
end
end^
set term ; ^
------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
