How to proper insert SQL in Run Command with Oracle Database

Help Studio
, ,
1

In a Oracle Database I have a table with 5 columns, one of them is epochtime. I want to change the epochtime into unixtime.
This I want to do in 3 steps:

  • Create a new column time
  • Convert epochtime to time
  • Delete column epochtime

Therefore I used a Run Command with the query:

"ALTER TABLE tradehistory_input
ADD (time DATE);

UPDATE tradehistory_input
SET time = TO_DATE(‘19700101’, ‘YYYYMMDD’) + (epochtime / 1000) / 86400;

ALTER TABLE tradehistory_input
DROP COLUMN epochtime;"

But then I get an error because of the semicolons after the commands. So I tried to seperate the command and combine them with a +, like:

"
ALTER TABLE btc_tradehistory_input
ADD (time DATE)
"
+
"
UPDATE btc_tradehistory_input
SET time = TO_DATE(‘19700101’, ‘YYYYMMDD’) + (epochtime / 1000) / 86400
"
+
"
ALTER TABLE btc_tradehistory_input
DROP COLUMN epochtime
"
But that does Studio not accept.

The only option that works is to use 3 Run Command-activities for each command separately. However that can’t be the only solution, i hope…
I have another query left which is much, much longer…

So if you can help me with this, you are my HERO of today!!

Kind regards,

Bert-Jan Groeneveld

2

Can anyone please help me? Many thanks!

3

Is there really no one out there how can help me? Please…

4

Impatient aren’t we? :stuck_out_tongue:

I can’t immagine you want to use RPA to do this. Altering a table would be a one time action. Using Oracle SQL Editor for it should make life easier.

If the ‘data’ needs to be converted, convert the data in UiPath before inserting it.

An alternative approach, if for some reason this or your other queryset is to be done with UiPath, I reccommend creating a stored procedure in your DB, and call that procedure from UiPath instead of the loose queries. It’d even be somewhat safer.

5

Jeroen,

Many thanks for your reply! Sorry for my impatience.
The reason why I build the robot this way is because it is much faster to let SQL convert it instead of letting UiPath do it.
But my main question is: Is it possible to run several queries in one Run Command activity using Oracle SQL? And if it is possible: how can I do this?
I have also used MS SQL Server and then it is possible. However MS SQL is crashed on my laptop and I can’t connect between my laptop and desktop anymore. Therefore I use Oracle now.

With MS SQL Server you can just use a query like:

INSERT INTO tablename1 (id, name, age) VALUES (1,‘Me, Myself and I’,99);
INSERT INTO tablename2 (id, product_name, price) VALUES (1,‘bread’,3);

The only thing you have to change is to remove the last semicolon. This doesn’t work with Oracle. It returns an error regarding the first query/command you give (In my case ALTER) like: Sequence: ORA-01735: invalid ALTER TABLE option.
So I am very curious if it’s possibel with Oracle SQL also and how.
Thanks!

6

Not sure about the syntax, sorry. You may need to check the PL/SQL syntax for it instead of direct queries. I’d google it, but that’s something you can also do :slight_smile:

And as said: setting your sequence of queries as a stored procedure will definitely solve your problem. Since you then only have a single SQL invoke from your robot.

7

I agree that stored procedures probably will work beter. Thanks for your help!

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.