How to get achieve getting nextval of sequence from Oracle db, Current date in Eastern Timezone and fire insert statement to the DB with this details

Question

How to get achieve getting nextval of sequence from Oracle db, Current date in Eastern Timezone and fire insert statement to the DB with this details

Firthouse M G 60

Hello Team, I have a requirement where I need to fetch the nextval of sequence from on-prem Oracle DB save it in a variable , Get the current time in Eastern time zone and save it in a variable and using this fire a insert statement to update ETL scheduler status in Oracle database table. My ETL Table has ETL_ID - Sequence value from Oracle ETL_NAME - Passed in Insert statement Start_Date - Date stores in variable END_DATE - Null in the insert query Completed - N - flag Once I insert this in DB and run my pipeline after finishing it update the same row with END_DATE - Date stored after pipeline completion and Completed - Y - flag.
How to achieve this in ADF. I was trying with Script and lookup activity but facing issues with variables. My Oracle table has date as data type for date columns. Please suggest best possible way to achieve this.

Smaran Thoomu 22,195 Reputation points Microsoft External Staff

2024-01-24T11:46:07.0566667+00:00
Hi @Firthouse M G ,

Welcome to Microsoft Q&A platform and thanks for posting your question.

To achieve this requirement, you can use the following approach:

Use a Lookup activity to fetch the nextval of the sequence from the on-prem Oracle DB. In the Lookup activity, you can use the following query to fetch the nextval:

SELECT <sequence_name>.NEXTVAL FROM DUAL

Replace <sequence_name> with the name of your sequence.

Use a Set Variable activity to save the fetched nextval in a variable.

Use a Set Variable activity to get the current time in Eastern time zone and save it in a variable. You can use the following expression to get the current time in Eastern time zone:

convertTimeZone(utcnow(), 'UTC', 'Eastern Standard Time')

Use a Copy activity to insert the ETL scheduler status in the Oracle database table. In the Copy activity, you can use the following query to insert the data:

INSERT INTO <table_name> (ETL_ID, ETL_NAME, Start_Date, END_DATE, Completed) VALUES (<nextval_variable>, '<etl_name>', '<start_date_variable>', NULL, 'N')

Replace <table_name> with the name of your ETL table, <nextval_variable> with the name of the variable that contains the fetched nextval, <etl_name> with the name of your ETL, and <start_date_variable> with the name of the variable that contains the current time in Eastern time zone.

After the pipeline finishes, use a Set Variable activity to get the current time in Eastern time zone and save it in a variable. Use a Copy activity to update the ETL scheduler status in the Oracle database table. In the Copy activity, you can use the following query to update the data:

UPDATE <table_name> SET END_DATE = '<end_date_variable>', Completed = 'Y' WHERE ETL_ID = <nextval_variable>

Replace <table_name> with the name of your ETL table, <end_date_variable> with the name of the variable that contains the current time in Eastern time zone, and <nextval_variable> with the name of the variable that contains the fetched nextval.

Note: Make sure to configure the Oracle linked service in ADF with the correct connection details and credentials. Also, ensure that the data types of the variables match the data type.

I hope this helps! Let me know if you have any further questions.
Firthouse M G 60 Reputation points

2024-01-25T06:02:26.1333333+00:00

Thank you Smaran - But issue here is getting current timestamp which should be equivalent of date data type in Oracle and storing it in a variable in ADF and using it in a insert statement. I need the format. How to use a variable in insert and update statement in ADF?. How to get date in a variable?
Firthouse M G 60 Reputation points

2024-01-25T10:11:53.08+00:00
Posting my implementation:

Lookup Activity is for SELECT <sequence_name>.NEXTVAL FROM DUAL Set variable1:- is for saving output of the the sequence Variable name Seq and value

@int(activity('Lookup1').output.firstRow.NEXTVAL)

Set varaiable2:- is for saving the date. Variable name Start_Date and value and it is string

@formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time'),'yyyy-MM-dd')

Script activity: To Insert a row in the destination table.

@Insert into Schema_Nam.Table_Name(ETL_RUN_ID,ETL_NAME,PROCESS_DATE,LOAD_START_DATE,COMPLETED_IND)Values(variables('Seq'),'Full Load',variables('Start_Date'),variables('Start_Date'),'N')

Error: "ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00936: missing expression" How to write a Insert and update statement with variable output from set variable activity and how to change string value to date in insert statement for date columns.

Accepted answer

0 additional answers

Your answer

Firthouse M G 60 Reputation points

2024-01-25T06:02:26.1333333+00:00

Thank you Smaran - But issue here is getting current timestamp which should be equivalent of date data type in Oracle and storing it in a variable in ADF and using it in a insert statement. I need the format. How to use a variable in insert and update statement in ADF?. How to get date in a variable?
Firthouse M G 60 Reputation points

2024-01-25T10:11:53.08+00:00

Posting my implementation:

Lookup Activity is for SELECT <sequence_name>.NEXTVAL FROM DUAL Set variable1:- is for saving output of the the sequence Variable name Seq and value

@int(activity('Lookup1').output.firstRow.NEXTVAL)

Set varaiable2:- is for saving the date. Variable name Start_Date and value and it is string

@formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time'),'yyyy-MM-dd')

Script activity: To Insert a row in the destination table.

@Insert into Schema_Nam.Table_Name(ETL_RUN_ID,ETL_NAME,PROCESS_DATE,LOAD_START_DATE,COMPLETED_IND)Values(variables('Seq'),'Full Load',variables('Start_Date'),variables('Start_Date'),'N')

Error: "ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00936: missing expression" How to write a Insert and update statement with variable output from set variable activity and how to change string value to date in insert statement for date columns.

Answer 1

Smaran Thoomu 22,195 Microsoft External Staff

Hi @Firthouse M G

You could try the following steps to address those specific points you've raised.

Getting Current Timestamp in Oracle Date Format: Use the following expression in a Set Variable activity to get the current timestamp in Eastern Time Zone and format it as Oracle DATE.

  @formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time'), 'YYYY-MM-DD')

Using Variables in Insert and Update Statements:
Enclose variables in @{} within your SQL statements.

  INSERT INTO ... VALUES (@{variables('Seq')}, 'Full Load', @{variables('Start_Date')}, ...)
  UPDATE ... SET END_DATE = @{variables('End_Date')}, ... WHERE ETL_RUN_ID = @{variables('Seq')}

Storing Date in a Variable: Use the formatDateTime expression to format the date as needed and store it in a variable.

  @formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time'), 'YYYY-MM-DD')

Firthouse M G 60 Reputation points

2024-01-27T11:52:52.7833333+00:00

@Smaran Thoomu - It Worked!. Thank you for your support
Smaran Thoomu 22,195 Reputation points Microsoft External Staff

2024-01-28T07:20:45.93+00:00

@Firthouse M G I'm happy to hear it worked for you! If this answers your query, please do click Accept Answer and Up-Vote to let others know it was helpful. And, of course, feel free to reach out if you have any further questions.
Smaran Thoomu 22,195 Reputation points Microsoft External Staff

2024-01-29T18:02:51.7033333+00:00

@Firthouse M G Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

How to get achieve getting nextval of sequence from Oracle db, Current date in Eastern Timezone and fire insert statement to the DB with this details

0 additional answers

Your answer