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 ||