Query to search a particular word/string in toad/oracle

Scheduler agent is part of database Oracle Home. Anyway must be manually deployed; there is no way to use the database as agent. I think that the documentation is misleading on this case. To help you I will write steps to be performed for agent configuration. I suppose that on the central database you have already enabled HTTP port (etc.). On the remote database (the one where agent have to be configure):

cd $ORACLE_HOME\bin
extjobo -createagentzip execution_agent.zip

Unzip the file into a directory under the $ORACLE_HOME\scheduler.

cd $ORACLE_HOME\scheduler
unzip execution_agent.zip

Modify the file named schagent.conf located in the $ORACLE_HOME\scheduler\execution_agent directory setting the Scheduler Agent port number to listen for connections. (the one you should have already set with dbms_xdb.sethttpport() )

PORT=<schagent_port>

You have to run the root.sh to fix the uid. (You can follow the README file in the execution_agent directory and run the root.sh). NOTE Verify a directory named /wallet exists under /execution_agent/data before running root.sh. If not create them manually and then run root.sh.

Register the database with.

set ORACLE_SID=<your sid>
cd $ORACLE_HOME\scheduler\execution_agent\bin
schagent -registerdatabase <db_host> <db_http_port>

You will be asked to input the registration password. Last start manually the agent:

schagent -start

These steps will install and configure the agent. Now you can create credentials on the central database and create the database destination. Database destination can exists along with a remote agent destination.

One method is to use the MULTI_COLUMN_DATASTORE. Below is an example:

drop table t1 purge;

create table t1 (column1 varchar2(100), column2 varchar2(100));
insert into t1 values ('HELLO', 'WORLD');
insert into t1 values ('SAY', 'HELLO');
insert into t1 values ('SOMETHING', 'ELSE');
insert into t1 values ('WORLD', 'CUP');
commit;

Create the datastore with the desired columns:

begin
ctx_ddl.create_preference('t1_multi_column_datastore', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('t1_multi_column_datastore', 'columns', 'column1, column2');
end;
/

Create the Text index using this datastore:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore');

Or if you want the index maintained on commit:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore sync(on commit)');

Searching for the words HELLO or WORLD:

select * from t1 where contains (column1, 'HELLO or WORLD') > 0;

COLUMN1    COLUMN2  
---------- ----------
HELLO      WORLD     
SAY        HELLO     
WORLD      CUP     

Notice that you do not need to specify all columns in the query, but you need to specify an operator between words.