Run plsql from .bat

Can you please be a bit more specific and give us some code examples that shows us what you're working with?

Hi,

Attached below

text-array.png

text-array-2.png

The error occurs when I connect SQLExecutor as it is looking for a Table not an array/list.

===========================================================================

SQLExecutor_<Rejected>(TeeFactory): SQLExecutor_<Rejected>: Termination Message: 'SQLExecutor output a <Rejected> feature.'

text-array-error.png

SELECT "NTF" FROM @Value(text_line_data)

The aim is to loop through the list (shown in test-array-2), passing each element to a procedure.

The first thing that strikes me is that you'll need some double quotes around the table name, e.g.

SELECT "NTF" FROM "@Value(text_line_data)"

But do you really have a table called "0261-D"?

I moved the @Value(text_line_data) ie 0261-D

SELECT @Value(text_line_data)

FROM "NTF"

Feature Type: `FEATURE_TYPE'

Attribute(encoded: utf-8) : `NTF' has value `0261-D'

Attribute(string) : `_reader_error' has value `An error occurred while accessing a table

Still getting an error because "NTF" is an array NOT a table.

How do I get the array into a loop that I can pass to PLSQL+??

You still need some double quotes around the column name:

SELECT "@Value(text_line_data)"
FROM "NTF"

But does the table "NTF" contain a column called "0261-D"? To me that looks more like a value, in which case you'll probably want something like

select *
from "NTF"
where my_column_name = "@Value(text_line_data)"

You'll of course have to substitue my_column_name for whatever your lookup column is called.

You are correct about the Syntax. Still get the same error!!

The problem is that SQLExecutor needs to read a table. An array is not a Table in Oracle.

The question is still HOW to get an array into a Loop to run a procedure??

@clives_online

I use this technique. Looping trough lines in a txt/csv and setting an environment value.

Maybe it's helpful for you.

In your bat file:

FOR /F "usebackq delims=" %%i IN ("%YourTextArray%") DO (

SET YourrelevantValue=%%i

do some stuff if needed

)

In yourscript.fmw you then can use a EnvironmentVariableFetcher to get the value "YourrelevantValue" needed to kick of your procedures.

text-array-error.png

Thanks for the illustration. If I understand correctly, your anonymous PL/SQL block (on the right side of your illustration) iterates over the NTF table and returns all the values of the NTF column (see "SELECT NTF FROM NTF;" in the PL/SQL)

Would using a SQLCreator with the following SQL give you something similar as what you've got on the left side of your illustration? (Notice that there's no ; at the end of the SQL statement)

SELECT NTF FROM NTF

In the SQLCreator, expose the attribute "NTF", it would look like this:

This should return one feature for each record in the "NTF" table, and each feature will contain an attribute "NTF" with a value such as "0261-D", etc.

Of course, I have no access to your database so I can't test it, but that's what would happen according to my understanding of the situation.

Is this closer to what you had in mind?

SQLCreateator fails because I am trying to read an array outside of ORACLE.

The frustration is trying to get Oracle to read an ARRAY and loop through the Array NOT a Table.

If I was reading a Table, Oracle would be happy and return an Array!!

I suspect you'll have to find a way to either CAST the array as a table and SELECT from it, or maybe to call an Oracle function that returns the array values as a concatenated string which you can then split into individual features in FME (e.g. using the AttributeSplitter).