@Philipp Elmer wrote:
allow me just two small comments on the SQL-code:
there is no need for a dbms_lob.substr on OVW_VValue because it's not a LOB, it's actualle a varchar2(200), the reason being that it's indexed.
when you specify the OH_Client and the full OH_Name (without wildcards), like in your query, there is no need to specify OH_Otype or OH_Deleteflag, because the unique key UK_OH_Name on OH makes sure that the combination of Client and Name must be unique.
Thanks. I have updated the query.
WITH VaraContent AS (
SELECT OVW_VValue AS Key,
DBMS_LOB.SUBSTR(OVW_Value1, 4000, 1) AS Value1,
DBMS_LOB.SUBSTR(OVW_Value2, 4000, 1) AS Value2,
DBMS_LOB.SUBSTR(OVW_Value3, 4000, 1) AS Value3,
DBMS_LOB.SUBSTR(OVW_Value4, 4000, 1) AS Value4,
DBMS_LOB.SUBSTR(OVW_Value5, 4000, 1) AS Value5
FROM &OH#, &OVW#
WHERE OH_IDnr = OVW_OH_Idnr
AND OH_Client = &UC4_Client_ID#
AND OH_Name = '&Migration_Data_VARA#'
)
select Key,Value1,Value2,Value3,Value4,Value5 from VaraContent
&SQL_Predicate#
Original Message:
Sent: 06-07-2019 03:30 AM
From: Philipp Elmer
Subject: Data written to static VARA object with :PUT_VAR not immediately accessible
Hi Michael,
allow me just two small comments on the SQL-code:
- there is no need for a dbms_lob.substr on OVW_VValue because it's not a LOB, it's actualle a varchar2(200), the reason being that it's indexed.
- when you specify the OH_Client and the full OH_Name (without wildcards), like in your query, there is no need to specify OH_Otype or OH_Deleteflag, because the unique key UK_OH_Name on OH makes sure that the combination of Client and Name must be unique.
Cheers,
Philipp
------------------------------
Philipp Elmer
Original Message:
Sent: 06-06-2019 09:49 AM
From: Michael Lowry
Subject: Data written to static VARA object with :PUT_VAR not immediately accessible
This might be relevant: The GET_VAR
statement does not access the static VARA directly. Instead, it accesses an EXEC VARA that runs an SQL job to look for rows in the static VARA matching specific criteria. The SQL looks like this:WITH VaraContent AS (
SELECT DBMS_LOB.SUBSTR(OVW_VValue, 4000, 1) AS Key,
DBMS_LOB.SUBSTR(OVW_Value1, 4000, 1) AS Value1,
DBMS_LOB.SUBSTR(OVW_Value2, 4000, 1) AS Value2,
DBMS_LOB.SUBSTR(OVW_Value3, 4000, 1) AS Value3,
DBMS_LOB.SUBSTR(OVW_Value4, 4000, 1) AS Value4,
DBMS_LOB.SUBSTR(OVW_Value5, 4000, 1) AS Value5
FROM &OH#, &OVW#
WHERE OH_IDnr = OVW_OH_Idnr
AND OH_OType = 'VARA'
AND OH_Client = &UC4_Client_ID#
AND OH_DeleteFlag = 0
AND OH_Name = '&Migration_Data_VARA#'
)
select Key,Value1,Value2,Value3,Value4,Value5 from VaraContent
&SQL_Predicate#
I can imagine that the AE returns the correct values immediately even if the values have not yet been committed to the DB, but only if the following two criteria are met:
- the
:PUT_VAR
and GET_VAR
are run by the same WP (e.g., in the same SCRI); and - the
:PUT_VAR
and GET_VAR
directly access exactly the same VARA object.
In my case, an ordinary GET_VAR
against the static VARA won't let me return just the data I want, so I had to go via the EXEC VARA & SQL job.