Hi
     What is the point in using DBC.SYSEXECSQL('<SQL Query>') when we can execute SQL queries with out using it in STORED PROCEDURE. Does using SYSEXECSQL provide any additional advantage? I am in a fix whether to use it or not. I am not sure if it will create any bottleneck on the system. Please help. THanks.
 

Hi All,

Recently someone told me that using Dynamic SQL in TD procedure is not a good practice for performance reasons. Is that true? Does it mean that for every procedure call procedure needs to re-compile?

Is there any way to avoid dynamic sql completely? Parameterizing query is something which I can not avoid.

Thanks

Hi,

I am executing a SQL using DBC.SYSEXECSQL in a Store Procedure .

Can any one suggest me way to capture the activity count of sql executed 

Regards

Anand Louis

I'm attempting to develop a stored procedure that takes a table name as a parameter, and performs column-by-column updates to whatever table is passed in.

I have the dynamic SELECT query working correctly:

SET CURSOR_SQL = 'SELECT * FROM ' || P_TABLE_NAME || ' ORDER BY rsid;';
PREPARE CURSOR_STATEMENT FROM CURSOR_SQL;
OPEN CDC_CURSOR;
/* logic */
CLOSE CDC_CURSOR;

Now I'm at the point where I need to fetch the cursor data into local variables...but I can't create table-specific local variables because I don't know what tables will be passed into this procedure.

SET dyn_sql =
'Select TableName into hTableName from ' || TRIM(i_FullWorkTableName) ||
';'
;
call dbc. SysExecSQL(:dyn_sql)
;

SET dyn_sql =
'call sysdba.spETL_Table_' || hTableName|| '(:i_FullStageTableName);'
;
call dbc. SysExecSQL(:dyn_sql)
;

I get this when above is called. compiles correctly.

5568 SQL statement is not supported within a stored procedure.

What I am trying to do is call a stored procedure to work on specific table. I can get table name as an input parameter or sel from a table.
call spetl_a
call spetl_b
call spetl_c