Set bind variables from table

SQL Optimizer SQL Optimizer for Oracle
1

Hi,

SQL Optimizer for Oracle.

In help "Set Bind Variables" states that it is possible to add bind variables from the table. I do not get it how.

If I have :b0, :b2, :b3, :b4, :b5 bind variables in a query. Have created table XXDV_BLREP_PERF_BINDS

CREATE TABLE XXDV_BLREP_PERF_BINDS
(
ORDER_BY NUMBER,
B0 NUMBER,
B1 NUMBER,
B2 NUMBER,
B3 VARCHAR2(8),
B4 VARCHAR2(8),
B5 NUMBER
);

Table is filled with data and order_by column has values 1...14. Filled dialog like:

Bind_from_table.jpeg
Bind_from_table.jpeg990×522 99.8 KB

but got an Error:

ORA-00904: "B5": invalid identifier
ORA-06512: at line 217

Mine question is how to fill dialog properly to be able to use binds values from the table on the right.
Brg
Damir

p.s.
Help will be much better to have small examples, like in this case

2

Just small info…

Look number and varchar2 column values … they are formatted but have no clue why!?

3

xx.png
xx.png1279×940 350 KB

You can select the variable and then click the perferred value on the data grid as the bind value. Each variable accept one bind value only.

4

Hi Richard,
Thx for your reply-now is clear.

But have to say that when I fill all data from the right side and then start Query Rewrite process and wants to change values, then I need to start new Query Rewrite and then all values are gone so I have to fill data again (for gathering from the table).

I would like to see something like in Code tester, where you put values and retrieve them automatically one by one in new session or whatever.
In this way I can test 10-20 bind variables automatically. Is that somehow now possible?

Brg
Damir

5

Yes, we have this idea already. There will be two functions. One is to test run multiple set of bind values, another one is to explore bind sensitive values.

6

Great!

If I can add one more idea .... which I would use a lot.

When you open trace file browser, analyze one trace (binds=true) and click on the one statement from the list, it would be really nice to be able to send that command with all binds (there may be dozen of them!) into "Toad SQL Optimizer" in one click.

SQL_OPtimizer_integration.jpeg
SQL_OPtimizer_integration.jpeg1086×768 201 KB

This would be really nice Toad integration step forward in mine opinion.

Brg

Damir

7

Thank you for the suggestion. I have added it to our backlog and will definitely consider it in the future.

Thanks again,

Alex