Here is exable from book how to use this UDF/SP COMBO:
WITH temp1 AS
(SELECT tabschema
,tabname
FROM syscat.tables
WHERE tabschema = 'FRED'
AND type = 'S'
AND status = 'C'
AND tabname LIKE '%DEPT%'
)
SELECT CHAR(tab.tabname,20) AS tabname
,stm.sqlcode AS sqlcode
,CHAR(stm.sqltext,100) AS sqltext
FROM temp1 AS tab
,TABLE(execute_immediate(
'REFRESH TABLE ' ||
RTRIM(tab.tabschema) || '.' || tab.tabname
))AS stm
ORDER BY tab.tabname
WITH UR;
lenygold wrote:
>Thank you very much SERGE for your help.
>I found example in Graeme Birchall COOKBOOK wich i think exactly what i
need
>for SQL
>check in triggers:
>
>• User query joins to table function - sends DML or DDL statement to be
>executed.
>• Table function calls stored procedure - sends statement to be
executed.
>• Stored procedure executes statement.
>• Stored procedure returns SQLCODE of statement to the table function.
>• Table function joins back to the user query a single-row table with
two
>columns: The
>SQLCODE and the original input statement.
>
>--#SET TERMINATOR !
>CREATE PROCEDURE execute_immediate (IN in_stmt VARCHAR(1000)
>,OUT out_sqlcode INTEGER)
>LANGUAGE SQL
>MODIFIES SQL DATA
>BEGIN
>DECLARE sqlcode INTEGER;
>DECLARE EXIT HANDLER FOR sqlexception
>SET out_sqlcode = sqlcode;
>EXECUTE IMMEDIATE in_stmt;
>SET out_sqlcode = sqlcode;
>RETURN;
>END!
>
>--#SET TERMINATOR !
>CREATE FUNCTION execute_immediate (in_stmt VARCHAR(1000))
>RETURNS TABLE (sqltext VARCHAR(1000)
>,sqlcode INTEGER)
>LANGUAGE SQL
>MODIFIES SQL DATA
>BEGIN ATOMIC
>DECLARE out_sqlcode INTEGER;
>CALL execute_immediate(in_stmt, out_sqlcode);
>RETURN VALUES (in_stmt, out_sqlcode);
>END!
>
>Then i tryied to test it:
>
>select 1,stm.sqlcode as sqlcode, CHAR(stm.sqltext,100) as sqltext
> from sysibm.sysdummy1
> ,table(execute_immediate('select * from emp_screen_edit')) as
stm;
>
>and got the followung error:
>sqlstate: 429BL
>The function "EXECUTE_IMMEDIATE" (specific "SQL080715180239600") modifies
SQL
>data and is invoked in an illegal context. Reason code = "3
>3. The table function is preceded by a table reference which is not
> referenced by a function argument.
>Serge please help.
>Thank's in advance
>Leny G.
>
>>> I have an edit trigger:
>>>
>[quoted text clipped - 8 lines]
>>Cheers
>>Serge
--
Message posted via http://www.dbmonster.com
|
