How to…? – Scotas and Apex

This example  shows how easily you can add typical free text searching features such as faceting, more like this, auto-complete among others using Scotas Push Connector.

The example was tested with:


  • You already have installed Scotas PC.
  • You have created one user, in our case “scott”.
  • You have craeted a table FILM_ADMIN_TEST (Download here).
  • Solr instance running in localhost, port 8983.

Step one – Indexing

  • as sysdba,  grant role PCUSER to the user scott. 

                      SQL> grant PCUSER to scott;

  • as scott user, create an PC index on FILM_ADMIN_TEST table

SQL> CREATE INDEX FILM_ADMIN_TEST_SIDX ON SCOTT.FILM_ADMIN_TEST(TEXT) INDEXTYPE IS PC.SOLR PARAMETERS(‘{Updater: “localhost@8983″, Searcher: “localhost@8983″, CommitOnSync:true, SyncMode:OnLine, LockMasterTable:false, IncludeMasterColumn:false, HighlightColumn:” \”text_t\””, DefaultColumn:”text”, ExtraCols:”TEXT \”text_t\”,GENRE \”genre_s\”,COUNTRY \”country_s\”,LANGUAGE \”language_s\”,DIRECTED_BY \”directed_by_s\””}’);

Note: this sentence create an index over the field “text” and also, we included other fields to get facets.

Step two – Apex, the demo application should be ready to use on the Oracle XE installation.

  • Edit Apex Appplication login into

  • Select Application Builder ->Create
  • Enter a name for the application.

Step 01


  • Create a new Page

Select a Report form type, and use SQL query in the Page Source.  Insert the Sql query with the Scotas facets.


SELECT QRYTEXT AS LANGUAGE, HITS AS HITS FROM TABLE( facet_to_tbl( json_list( json( SOLRPUSHCONNECTOR.FACET(‘FILM_ADMIN_TEST_SIDX’, ‘*’, ‘facet.field=language_s’).FIELDS).get(1))))

Note: Push Connector returns facets in json format. To use facets as a table, we provide a the function facet_to_tbl.

  • Select add page

Select the button “Add Page”, to create this page. After that, you should see the page in the list.


  • Create tabs


  • Shared components


  • Application Attributes


  • User interface


  • Create Application


Step two – Run application

Run the application using your username/password, in our case scott/tiger.

The page will look like:

here yo for example doing the result clickable to select Laguage, or filtering by range.

Conclusion: This is only a simple example to use Scotas features with Appex. From now you can make use of  the best of each technologies.

{ Comments are closed! }