Hi fellows, tip of how to use a dynamic sql within JDE 8.0 7334

[Aludoq_GPM]

Few years ago, i'd a trouble with performance to extract data from large table (like Cardex).
At this time we've used a mix approach with Oracle SQL call directly by the JDE, putting the data to a temporally table to be showing in JDE.
The picture in attach show how this solution works.

View attachment 18483​

Table description F5543020:
Name Tipo
---------- ----------------------
TSUKID Number Pk
TSDESC2000 Varchar2(2000)
TSCOMMENT Varchar2(2000)
TSCNLS Varchar2(2000)
TSCURRENT Varchar2(2000)
--
CREATE OR REPLACE TRIGGER "TESTDTA"."F5543020_BEFORE_INSERT"
BEFORE INSERT ON F5543020 FOR EACH ROW
BEGIN F5543020ExecutaSQLnew.TSUKID,
:new.TSDESC2000,
:new.TSCOMMENT,
:new.TSCNLS,
:new.TSCURRENT);
END;
--
CREATE OR REPLACE PROCEDURE "TESTDTA"."F5543020EXECUTASQL"(
pUkid IN NUMBER,
pSQL1 IN VARCHAR2,
pSQL2 IN VARCHAR2,
pSQL3 IN VARCHAR2,
pSQL4 IN VARCHAR2 )
AS
BEGIN
EXECUTE immediate (rtrim(pSQL1,' ')||' '||
rtrim(pSQL2,' ')||' '||
rtrim(pSQL3,' ')||' '||
rtrim(pSQL4,' '));
END;