Valid Query in Oracle SQL developer is invalid in SSIS

Question

Here's how my query looks like :

Edit (I have near to none experience with PLSQL, I just read on some post if you're using Begin/End in your script, you no longer can use plain select, you should insert that result into somewhere! ) can this be the reason the query doesn't work in SSIS? )

   Variable x1 Number
   Variable x2 Number
   Variable x3 Number




   Variable x20 Number


   Begin  select user_defined_function(parameters) into x1 from dual; 
    select user_defined_function(parameters) into x2 from dual; 
    select user_defined_function(parameters) into x3 from dual; 


    select user_defined_function(parameters) into x20 from dual; 

   End;
    / 

    with Q1 as (....) 
    , Q2 as (....)
    , Q3 as (...)

    Select * from Q1,Q2,Q3 joining together

The query works in SQL developer, however I have to use execute script instead of f5. otherwise it will prompt for variable values.

As I suspected, the query in its current format doesn't work in SSIS. I get the error INVALID SQL STATEMENT 900

Do you intend on providing the variable values at runtime? Often with Oracle I've had to resort to a dynamically generated SQL string. — Nick.Mc
– Nick.Mc, Commented Nov 10, 2020 at 21:53
@Nick.McDermaid the variable values are supposed to be retrieved via the select statements user defined functions. I won't enter anything — Ariox66
– Ariox66, Commented Nov 10, 2020 at 21:56
@Nick.McDermaid does this mean what I think it means ? The lack of support for parameterized queries in the various Oracle connection managers is an unfortunate limitation in SSIS. — Ariox66
– Ariox66, Commented Nov 10, 2020 at 22:03
Is not the un-balance between the four Begin and the only one End the problem? — Marmite Bomber
– Marmite Bomber, Commented Nov 10, 2020 at 22:23
@MarmiteBomber Thanks for picking that up, I fixed it here. my actual script didn't have this issue — Ariox66
– Ariox66, Commented Nov 11, 2020 at 1:41

Marmite Bomber · Accepted Answer · 2020-11-11 10:34:58Z

1

You may use additional CTO for passing the results of the user_defined_function into the main query, such as

with  
x1 as (
   select user_defined_function(parameters) as x1 from dual), 
x2 as (
   select user_defined_function(parameters) as x2 from dual),  
...

In your main query instead of using

column1 = x1

you would write

column1 = (select x1 from x1)

This approach eliminates the need of variables and PL/SQL blocks, and use a pure SQL

answered Nov 11, 2020 at 10:34

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ariox66 Over a year ago

Yeah, apparently you can't have PLSQL blocks with plain select in ODBC connections of SSIS

Marmite Bomber Over a year ago

Actually you are using the SQL*Plus syntax of VARIABLE which would not work quite sure. You may try with DECLARE to define variable which would be PL/SQL

Collectives™ on Stack Overflow

Valid Query in Oracle SQL developer is invalid in SSIS

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related