SQL Passing Parameters execute query

Help
1

Could someone clarify how the passing of parameters in execute query works? I understand that you can call the parameters using “?” however you have to keep your parameters in the correct order. I have tried the “@” symbol as well.

Here is an example:

“SELECT a.col1, a.col2
FROM table a
WHERE a.col3 = @acctName”

Parameter:
Name: acctName
Direction: In
Type: String
Value: someString

where someSting = “String”

However, I either get back errors when I do this @acctName, or an empty table if I do this ‘@acctName’

2 Likes
2

Hi,

  1. Hope you have used EXECUTE QUERY and not EXECUTE NON-QUERY activity, then the below query would return a data table as output.
    “select a.L_Name, a.F_Name from Employees_data a WHERE a.Id = @Id”

  2. use the for each row to iterate through the table.

The above query i tried and worked for me .
let me know if that worked

3

Hey @ashley11 ,

I tried that method of running the query, but unfortunately it was not successful. The details of that were explained above. The error referenced is an ORA-00936 missing expression error.

4

hi,
I could find these links which might be of help to you

hope this would help you

5

@ashley11
I have read through these, but am not seeing a correlation. Do you see anything in the code referenced above that you think could be causing this issue?

6

what i feel after going through those links is the query requires proper formatting try this once

“SELECT a.col1, a.col2”+System.Environment.NewLine+“FROM table a”+System.Environment.NewLine+“WHERE a.col3 = @acctName” instead of

“SELECT a.col1, a.col2
FROM table a
WHERE a.col3 = @acctName”

7

@ashley11
The goal is to not have to re-write the code to make it one solid string, but instead to be able to call the parameter in the current code.

I tried the above suggestion, and it also appears to not be working.

8

Where you able to solve this issue. I too am facing a similr issue.
If I pass hard coded values in my filtering condition, the execute query activity provides expected number of rows in return. But if I pass the same filtering conditions through parameters, the execute query activity provides zero rows. Not sure where the issue is.

10

What I ended up doing was adding the parameters in order that they appear in the code, and for every parameter in the code I replaced with a ?

1 Like
11

Thanks a Ton David!! Actually I was also using ‘?’ even before I saw your reply, but when playing with multiple IN arguments, you statement “adding the parameters in order that they appear in the code” solved my issue.

Regards,
Nishant

PS: I am still struggling with out paramenters, I am using For each row activity even though my SQL query output has only one cell of data. For Example.
“Select ORDERED_ITEM From OE_ORDER_LINES_ALL
Where ITEM_ID=123213;”
Do you have an idea of how can I take out ORDERED_ITEM as an OUT argument?.

12

you should use execute-non-query(run command), type of command: Text, and use “:” in your parameters names in sql instead “@”:
“insert into some_table (some_column) values (:param1)”
and add params in Parameters tab (params order is more important then their names)

13

I have the same problem,too.

I have tried ‘@param’ , @param , ? , :param .
Finally, :param is correct.

My database is Oracle, and studio ver is 2022.10.

Thanks

1 Like