In this blog we’ll see how a single initialization block can be used to initialize multiple session variables. This can be useful in scenarios where you have a large number of session variables to be initialized. Though initialization of a single block usually completes in a fraction of a second, as the number of blocks increases the time required to initialize all the variables can become sizeable, especially if you have specified an execution precedence for the initialization blocks.
We’ll see how we can implement a simple workaround to this by initializing all the session variables using a single initialization block by using the UNION query to combine all the blocks together.
Let’s look at this simple scenario.
I need to initialize two session variables:
1. PRODUCT_VAR
The initialization block of PRODUCT_VAR is:
SELECT ‘PROD_VAR’, PRODUCT_WID FROM EMPLOYEE_PRODUCT
WHERE LOGIN = ‘:USER’
2.LEVEL
The initialization block of LEVEL is:
SELECT ‘LEVEL’, LEVEL_1 FROM EMPLOYEE_LEVEL WHERE LOGIN = ‘:USER’
Both these variables are row-wise initialized.
We can combine both these initialization blocks into a single one using the UNION query as follows:
SELECT ‘PROD_VAR’, PRODUCT_WID FROM EMPLOYEE_PRODUCT
WHERE LOGIN = ‘:USER’
UNION
SELECT ‘LEVEL_1′, LEVEL_1 FROM EMPLOYEE_LEVEL WHERE LOGIN = ‘:USER’
This single block will initialize both the variables by executing a single query.
Posts
