Toad World® Forums

Can I pass an automation variable into a script?

type my_type is ref cursor return my_table%rowtype;

my_cursor_var my_type;

my_table_rec my_table%rowtype;

begin

some_random_proc (#VarA#, my_cursor_var);

fetch my_cursor_var into my_table_rec;

if (my_cursor_var%FOUND) then – fetch succeeded

dbms_output.put_line (

'Test ID = ’ || #VarA# ||

', Name = ’ || my_table_rec.name);

else

dbms_output.put_line ('Test ID ’ || #VarA# ||

’ not found.’);

end if;

close my_cursor_var;

end;

For each row in a spreadsheet, I would like to call an Oracle stored procedure that returns a ref cursor.
I created a spreadsheet, and a connection from TDA to display those values.
I used automation to loop through the dataset. I can set variables for each column in the spreadsheet. When it runs, the log shows me the values from each row being assigned. This is all good so far.
The next step would be to call the stored procedure. That takes more than a select statement. It takes a PL/SQL block. I can enter the code to do that, and tell the automation to “execute script”. I can show the text of that below. At the right moment during execution, I want it to replace my variable, VarA, with the value from the spreadsheet. This PL/SQL runs correctly if I use a hard-coded value such as 1, 2, 3, 4 or 5. How can I make it reference my automation variable?
I’m getting a syntax error, that VarA is unknown.
Is this possible? Is there a syntax to enable this?
Thanks for your advice about that.
Steve

declare ', Returned Key = ’ || my_table_rec.id ||

You can use automation variables and SQL statement bind vars. For example if you have a automation bind var named A you can then execute the following statement and we will bind the value of A.

select * from table where colname = :A

You can try this in the PL/SQL block but I don’t think our binding will work on blocks of code. Instead you can do what you want by using the Find & replace activity. It will look for all occurances of a specific string and replace with the value of the bind var.

Debbie