Parameterize Schema/Owner Name in SAP BODS SQL scripting function

Scenario: Requirement was to parameterize a schema name in SQL function in BODS scripting language. Schema name should be fetched from the datastore connection and used dynamically in job.

 You can follow the below steps to get this done,

Step 1: First fetch the schema name from the datastore name. Declare a variable in workflow, say $LV_TableOwnerDB to store the schema name. WE can use db_owner pre-built function for the same as below,

# Get the schema/owner name from from datastore using alias name.

$LV_TableOwnerDB = db_owner( 'DATASTORE_NAME', 'ALIAS_NAME');

 print('TABLE OWNER  = '||$LV_TableOwnerDB);

Now $LV_TableOwnerDB variable would store the schema name of your target database.

Step 2: Now declare another variable where you store the SQL Script to be executed.

$LV_Temp = 'UPDATE '||CAST($LV_TableOwnerDB,'VARCHAR(15)')||'.TABLE_NAME SET ACTIVE_FL =\'Y\' WHERE ACTIVE_FL=\'N\'';

Print($LV_Temp);

Step 3: Now pass the variable with SQL stored as argument to the sql function as the second parameter below.

sql('DATASTORE_NAME',$LV_Temp);

Suppose the Datatstore name is 'EDW_HR' alias name defined in datastore is 'ALIAS_1', the script would be as below,

# Get the schema/owner name from from datastore using alias name.

$LV_TableOwnerDB = db_owner( 'EDW_HR', 'ALIAS_1'); print('TABLE OWNER  = '||$LV_TableOwnerDB);
$LV_Temp = 'UPDATE '||CAST($LV_TableOwnerDB,'VARCHAR(15)')||'.TABLE_NAME SET ACTIVE_FL =\'Y\' WHERE ACTIVE_FL=\'N\'';Print($LV_Temp);
sql('EDW_HR',$LV_Temp);

Let us know if this helped. :)