Actionable Query for anonymous pl/sql :
set serveroutput on
declare
v_ts timestamp ;
begin
select systimestamp into v_ts from dual;
dbms_output.put_line('Select has been done at ' || V_ts);
end;
I get error ORA-00922 missing or invalid option.
On the input screen it is asking: enter anonymous block with integer bind variable named :result(true=1). What are they asking?
Hmmm... how are you running this in Toad? I merely copied your code into my Toad's (16.1) Editor and it runs successfully...
If you want to use an actionable query, the idea is that if the SQL (or pl/sql) returns true, then the automation will continue, but if it returns false, it will stop.
Based on what I see in your SQL, you just want to run a query. Maybe an "Execute script" action would better suit your needs here.
Let us know what you're trying to achieve.
-John
Yes I did run it as an “Execute Script” without any issue. But I thought this was another option.
Could you provide an example where you could use Actionable Query to run an anonymous pl/sql. I would like to see if I could then use it for something else.
Thank You very much,
Jim Sniatecki
Toad for Oracle Instructor
I’m using it in Automation Designer.
OK. I think I figured it out even though I don't think this is a practical example of using actionable query.
But at least I get the idea.
This ran this successfully in Automation Designer and it did the update to the table:
begin
:result :=1;
update student.empad set salary =6000
where gender='F';
commit;
end;
Hi Jim,
Yeah, that will work, but I think you're misunderstanding the purpose of the actionable query.
It's not to just run some SQL. It's to run a SQL in order to make a yes/no decision about if the next actions in the app will run or not. Think of it as an if..then.
if (actionable query returns true) then
do other actions
else
stop.
If all you want to do is run SQL, you can use an "Execute script" action (on the DB Misc tab) for that.
Thank you.
If you can provide me with a real world type example I would appreciate it. I can use my example for a class but I don’t know if it is realistic.
Jim
Well, I think "Real world" depends on your business needs, but as an example, you could use it to see if a specific value exists in a table before exporting it, then only export if the value is present.
In this video I'm using AD in the SQL mode instead of PLSQL mode.
John, thank you for your great support in my AQ issue.
Here is what I think may be a good class example. The purpose is to ,on session logon,
record the start SCN in case the developer may need to do a flashback table operation.
Here is the query I used and it runs successfully.
DECLARE
v_scn integer ;
v_ts timestamp ;
BEGIN
select systimestamp, timestamp_to_scn(systimestamp) into v_ts, v_scn from dual;
:result :=1;
insert into student.session_start_scn(start_ts,start_scn)
values(v_ts,v_scn);
commit;
END;
Hi Jim,
That's not really using the decision aspect of the actionable query action but it would definitely work.
If you want to run it automatically at login, you can set it up in the login window like this:
-John
Thank you.
Issue is now closed.
Thanks for resolving this for me.
I learned a lot.
Jim
If you're interested, I have a public dropbox folder with a few documents about Automation Designer documents in it.
Forgot to mention , I included the app to run in their session logon. The app executes and returns the message app successfully completed and the table is updated.
Jim
Yes that is how I set it up to run on session logon.
John, first of all I can't thank you enough for the AD enlightenment. I really get it now.
So I put this example together for my TOAD class:
Say a developer wants to signon each morning to a development app and on sigon wants to check to see if a table has been
updated (insert,update, or delete) by executing an Automation Designer app with an Actionable Query and an export dataset to keep
a current backup of it.
The table (e.g. student.empad) has been created with the rowdependencies feature of Oracle.
This means anytime a row gets changed , its ora_rowscn gets updated.
If this returns TRUE then export the table, if false then fail and don't export.
EXISTS (select 1 from student.empad
where to_char(sysdate-1,'ddmmyyyy') =
to_char(scn_to_timestamp(ora_rowscn),'ddmmyyyy'));
I tried this and it seems to work as expected.
Your opinion as to practicality,
Jim
Thank you , that’s great .
