• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

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

Aludoq_GPM

Member
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.


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 F5543020ExecutaSQL:)new.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;

 

Attachments

Top