SQL task with Oracle:

When we use the SQL Developer tool with SQL statements, each line is ended with a ; character between each new statement:

For example:

select * from table1;
select * from table2;

That works fine.

When we use this notation in VisualCron SQL Task in the "SQL text query" field we get the following error message:

ORA-00911: invalid character

If we remove the ; at the end of each line, we get the error message:

ORA-00933: command not properly ended

For example:

select * from table1
select * from table2

When we only use one line without ; everything works fine:

select * from table1

What is the good notation for using SQL statements with Oracle?

Hi Peter,

we don't have any Oracle access at the moment but in this case, the reason why you are getting this, is that we process one resultset at a time. The driver probably allows one query at a time.

By the way, are you testing with Direct Oracle connection or the ODBC/OLEDB alternative?

---

We use the direct Oracle connection:

Connection type standard and data provider Oracle.

We use the direct Oracle connection:

Connection type standard and data provider Oracle.

Thanks for the info. Right now you have to make separate queries in each Task.

---

Hi,

I've run into the same problem. After getting my Oracle connection to work, I've been testing running a few queries. Simple queries work fine, such as these:

SELECT * FROM user_master WHERE login_user_id = 'CJTW'
UPDATE item_master SET size_desc = 'TEST UPDATE' WHERE sku_id = '111841717'

Note that neither has a semicolon at the end. They each work when run in a separate task, but unfortunately that won't do. What I'm trying to do now is run a full Oracle script. Unfortunately I can't post the script itself, but I can describe what it does. It uses an Oracle cursor to retrieve some configuration settings from one of the tables, and then loops through that cursor and uses those settings to construct a variety of dynamic SQL update statements. After it's finished constructing each statement, it executes them. At the end, there would have been maybe 20 or 30 update statement run, with a total of around 65K records updated.

The script runs perfectly in SQL Developer. I'd like to set up a task in VC to do this:

1) Log the start of the task to a text file.
2) Execute the script.
3) Log the total records updated to the text file from step 1.
4) Log the end of the task to the text file from step 1.

My problem right now is that it fails on step 2. The other problem is that I don't know how to retrieve the records affected. When I did my test using the simple UPDATE statement above, there was 1 record affected. I tried outputting that using a variable under:

VisualCron Variables --> Jobs --> Active Job --> Tasks --> (name of my SQL task)

In there, I've tried using "Result", "Number of rows in result", and "Output", but none of them were what I need.

Unfortunately, saving this script as a stored procedure within the DB is not an option, so I can't just use a VC task to call a procedure.

Hi,
I am using Oracle direct mode Standard 2 (No oracle client required)

With regard to running Multiple SQL in one task have you tried selecting the checkbox in the bottom left of the SQL screen ?
Checkbox states - "Split and run multiple queries with Semicolon as separator"

If you check that it will allow you to run multiple queries.

With regards to the output I am not sure what result you will get. I would presume you would only get the result of the last query executed.
Update: I just built a task that executed this:
select 'x' from dual;
select 'y' from dual;
update tmp_rbe set materialname = 'test' where materialname = '1';
commit;
I then output the task elements top email and received this :
Output
xy
Number of Rows
1
Query Type:
SQL text query


Matt