Dymanic Update Queries (Iterative Macro) - Automation

Hello All,

I have a requirement to automate task. We are having around 30 oracle queries. Which i need to automate . I used Iterative Macro and dynamic input to update queried automatically. I am using OCI connection string. Attaching sample workflow for reference with sample data.

Please guide me where I am doing wrong.

Thanks in Advance.

RT_Auto.yxzp

Developer

Iterative Macro

Accepted answers

jdminton

@singh28ashish a couple of notes: the macro in your workflow is set to a batch macro (not iterative) and you don't use a control parameter. I've updated to use a control parameter and an input data tool (versus dynamic, which is not needed in the macro). Also, I did have to change the connection since I don't have access to your Oracle DB, so you will need to connect how you normally would. Use a simple SQL in the input data tool in the macro and select the SQL to be replaced as below.

RT_AUTO.yxzp

All comments

singh28ashish

Yoshiro_Fujimori

Hi @singh28ashish ,

Could you share the error messages ?

Your input data in the excel file has the following two SQL scripts. (New lines are added for readability)

It seems that both scripts are not complete (the column names are blank), which I guess may cause the error on the Oracle DB side.

select Rownum as REFERENCE_CODE,
CTR_NAME as ABBREVIATION,
CTR_ISO_CD as CODE_LABEL,
'Y' as VALID,
'' as DESCRIPTION,
'' as CHILD_REFERENCE_TYPE,
'' as CHILD_ATTRI\nBUTE_TYPE from country

select ROWNUM as REFERENCE_CODE,

SGR_NAME as ABBREVIATION,

upper(regexp_replace(SGR_NAME,'[^a-zA-Z0-9]','_')) as CODE_LABEL,

'Y' as VALID,

'' as DESCRIPTION,

'' as CHILD_REFERENCE_TYPE,

'' as CHILD_ATTRIBUTE_TYPE from sales_Tax_group

singh28ashish

Thanks for your reply @Yoshiro_Fujimori . Query are same and running fine. Just wanted to automate the process so all 20 - 30 queries execute one by one.