Feed aggregator

Oracle Offline Persistence Toolkit - Simple GET Response Example with JET

Andrejus Baranovski - Sun, 2018-01-28 11:56
We have new tool from Oracle which can help to simplify offline logic implementation for JS apps.  In this post I will describe how to use Oracle Offline Persistence Toolkit with Oracle JET. However Offline Persistence is not constrained by JET usage only, this toolkit is available on NPM and can be integrated with other JS solutions.

I should emphasise - offline toolkit primary role is to enable mobile hybrid apps to work offline. In my opinion, toolkit usage doesn't stop here. It can enable user to continue his work, when internet connection is available, but back-end server goes down. Technically user would remain online in this case, but in practice application will be broken - no response from back-end for REST calls. Offline persistence toolkit could help to solve such cases - user could continue working with local cache, until back-end is down.

If you want to learn how offline toolkit works and how to use its API, go to GitHub page - check readme, add it to your JET app and try to run/test. Hands-on is the best way to learn something new.

I will share few hints and sample app.

As per readme, first of all you should add Offline Persistence Toolkit and PouchDB modules from NPM. Run these commands within JET app directory:

1. npm install @oracle/offline-persistence-toolkit

2. npm install pouchdb pouchdb-find

Next you should follow four simple configuration steps and enable JET app to be able to access offline toolkit API.

Step 1 (standard, when adding any additional module)

Add paths to newly added modules in main.js require block:

Step 2 (standard, when adding any additional module)

Add paths to newly added modules in main-release-paths.js:

Step 3 (standard, when adding any additional module)

Added modules would not be copied to build directory automatically. We need to define copying in oraclejet-build.js. Modules should go to build directory. If you need to copy files from given folder and subfolders, use ** for src:

Build content is located in web directory. Offline toolkit and PouchDB modules should be copied to build directory:

Step 4

Initialize window.PouchDB variable in main.js:

Configuration is complete, now we can use Offline Persistence Toolkit API. Add persistence store manager and other modules:

Simplest option is to rely on default fetch listener from offline toolkit. We need to register store factory and map endpoint which we want to cache with persistence manager. When back-end is available - call will go to back-end and response will be cached. Next time, of back-end is not available - data will be fetched from cache. Toolkit intercepts HTTP(-S) request and stores response, if end-point was configured to be listened:

I'm testing offline toolkit with simple Employees REST end-point call from JET. Toolkit allows to execute this call successfully, even if there is no back-end or no connection (of course - if same call was executed at least once before):

UI part is simple - displaying list, when data is fetched:

Data is fetched, we are online:

Offline toolkit will work, if REST response doesn't include Cache-Control header. Make sure there is no Cache-Control header set in response:

ADF BC REST by default sets Cache-Control header, you can remove it Filter class (defined in ADF BC REST app):

Now I turned my connection to be offline, clicked on Get List button - JS calls REST and instead of getting network error, it executes successfully and returns data from cache through offline toolkit functionality:

You should open details for network call and check initiator. You will see that all calls mapped to offline endpoint are going through persistenceManager.js:

Let's double check - may be we are tricked somehow? Remove offline toolkit registration API in the code and re-run application:

As expected - network error is received and fetch fails. This proves - offline toolkit works :)

Sample JET application with offline toolkit configuration is available on GitHub (run ojet restore and ojet serve).

Temporary Table Questions

Tom Kyte - Sun, 2018-01-28 04:26
Tom, I have a few questions abt temporary table: 1. Which type of temporary table is better to use in a multi-user applications, a global temporary table with on commit preserve rows or a global temporary table without on commit preserve r...
Categories: DBA Blogs

Testing Oracle SQL online

Yann Neuhaus - Sat, 2018-01-27 16:38

Want to test some DDL, a query, check an execution plan? You need only a browser. And you can copy-paste, or simply link, your test-case in a forum, a tweet, an e-mail, a tweet. Here is a small list (expecting to grow from your comments) of free online services which can run with an Oracle Database: SQL Fiddle, Rextester, db<>fiddle and Oracle Live SQL

SQL Fiddle

SQL Fiddle let you build a schema and run DDL on the following databases:

  • Oracle 11gR2
  • Microsoft SQL Server 2014
  • MySQL 5.6
  • Postgres 9.6 and 9.3
  • SQLLite (WebSQL and SQL.js)

As an Oracle user, the Oracle 11gR2 is not very useful as it is a version from 2010. But there’s a simple reason for that: that’s the latest free version – the Oracle XE Edition. And a free online service can run only free software. Now that Oracle plans to release an XE version every year, this should be better soon.

Example: http://sqlfiddle.com/#!4/42960/1/0



Rextester is a service to compile code online, in a lot of languages and also the following databases:

  • Oracle 11gR2
  • Microsoft SQL Server 2014
  • MySQL 5.7
  • PostgreSQL 9.6

Example: http://rextester.com/QCYJF41984

Rextester has also an API where you can run a query and get a JSON answer:

$ curl -s --request POST --data 'LanguageChoice=35 Program=select * from dual' http://rextester.com/rundotnet/api
{"Warnings":null,"Errors":null,"Result":"\u003ctable class=\"sqloutput\"\u003e\u003ctbody\u003e\u003ctr\u003e\u003cth\u003e\u0026nbsp;\u0026nbsp;\u003c/th\u003e\r\n\u003cth\u003eDUMMY\u003c/th\u003e\r\n\u003c/tr\u003e\r\n\u003ctr\u003e\u003ctd\u003e1\u003c/td\u003e\r\n\u003ctd\u003eX\u003c/td\u003e\r\n\u003c/tr\u003e\r\n\u003c/tbody\u003e\u003c/table\u003e\r\n","Stats":"absolute service time: 1,37 sec","Files":null}

The answer has the result as an HTML table:

$ curl -s --request POST --data 'LanguageChoice=35 Program=select * from dual' http://rextester.com/rundotnet/api | jq -r .Result
<table class="sqloutput"><tbody><tr><th> nbsp; nbsp;</th>

Here is my SELECT * FROM DUAL:

$ curl -s --request POST --data 'LanguageChoice=35 Program=select * from dual' http://rextester.com/rundotnet/api | jq -r .Result | lynx -dump -stdin
1 X



db<>fiddle has a very nice interface, easy to link and easy to paste to StackOverflow (click on ‘markdown’)

  • Oracle 11gR2
  • SQL Server 2014 2016 2017, and even 2017 Linux version.
  • MariaDB 10.2
  • SQLite 3.8
  • PostgreSQL 8.4 9.4 9.6 10

Example: http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=948a067dd17780ca65b01243751c2cb0


Oracle Live SQL

Finally, you can also run on the latest release of Oracle, with a service provided by Oracle itself: Live SQL.

  • Oracle 12cR2 (an early build from October 2016)

Example: https://livesql.oracle.com/apex/livesql/s/f6ydueahcslf66dlynagw9s3w



Cet article Testing Oracle SQL online est apparu en premier sur Blog dbi services.

Bash: How to trim/truncate variables / get substrings

Dietrich Schroff - Sat, 2018-01-27 12:53
In a comment to one of the most useless commands postings was shown how to use the rev command to get the last component of  the path out of a log file:
cat log | grep pattern | rev | cut -d '/' -f 1 | revIf you only have one variable filled with a path, you can get the last component very easy:
schroff@zerberus:~$ myvar=/ab/cd/ef/gh
schroff@zerberus:~$ echo ${myvar##*\/}

## removes the longest matching string for the pattern "*\/" from the beginning (the slash has to be escaped with a backslash).

schroff@zerberus:~$ echo ${myvar#*\/}
ab/cd/ef/gh# removes the shortest matching pattern. Here only the starting "/"

% and %% removes everything from the end up to the pattern:
schroff@zerberus:~$ echo ${myvar%\/*}
schroff@zerberus:~$ echo ${myvar%%\/*}Here the content of the bash manpage:
Remove matching prefix pattern.  The word is expanded to produce a pattern just
as in pathname expansion.  If the pattern matches the beginning of the value of
parameter,  then the result of the expansion is the expanded value of parameter
with the shortest matching pattern (the ``#'' case)  or  the  longest  matching
pattern (the ``##'' case) deleted.  If parameter is @ or *, the pattern removal
operation is applied to each positional parameter in turn, and the expansion is
the resultant list.  If parameter is an array variable subscripted with @ or *,
the pattern removal operation is applied to each member of the array  in  turn,
and the expansion is the resultant list.

Remove matching suffix pattern.  The word is expanded to produce a pattern just
as in pathname expansion.  If the pattern matches a  trailing  portion  of  the
expanded  value  of parameter, then the result of the expansion is the expanded
value of parameter with the shortest matching pattern (the ``%'' case)  or  the
longest  matching  pattern  (the ``%%'' case) deleted.  If parameter is @ or *,
the pattern removal operation is applied to each positional parameter in  turn,
and  the  expansion  is  the resultant list.  If parameter is an array variable
subscripted with @ or *, the pattern removal operation is applied to each  mem‐
ber of the array in turn, and the expansion is the resultant list.

How to capture user and encrypted password to be used in alter user statement after duplication of database

Tom Kyte - Sat, 2018-01-27 10:06
We perform a duplicate database from prod to test weekly and have a script that captures the current user permissions on dev via the dbms.metadata.get_ddl package that generates DDL to another file to be used after the duplication to re-create any us...
Categories: DBA Blogs

SQLcl spool numeric field as Text

Tom Kyte - Sat, 2018-01-27 10:06
Hello Tom, I love SQLcl for spooling data to a CSV-File for using in Excel. But i have trouble with comma inside a field. This is my SQL-Statement: <code> SET LINESIZE 3000 SET TRIMSPOOL ON SET PAGESIZE 0 SET SERVEROUTPUT ON SET VERIFY...
Categories: DBA Blogs

Identifying PL/SQL function call from SQL

Tom Kyte - Fri, 2018-01-26 16:06
Hello, Firstly, thanks for fantastic job You're doing - i (and i hope many others) really appreciate it. I wanted to ask if there is an automated way to identify PL/SQL functions called from SQL. I would like to prepare list of "candidates" for ...
Categories: DBA Blogs

How to find sql_id of PL/SQL

Tom Kyte - Fri, 2018-01-26 16:06
Dear Team, How to find the sql_id associated with execution of a particular pl/sql procedure on a given day in past to see problem. means how can i find pl/sql sql_id and its last execution date and time. Thanks Pradeep
Categories: DBA Blogs

Performing updates to multiple columns from two tables

Tom Kyte - Fri, 2018-01-26 16:06
Hi, I have two tables. Table A and Table B. I'm trying to perform an update on 1 column from table A and 2 columns from table B. I can of course do this with two separate update statements. But I would like to know whether it's possible to perform...
Categories: DBA Blogs

Error ORA-29861 on XDB.XMLIndex

Tom Kyte - Fri, 2018-01-26 16:06
Categories: DBA Blogs

What do I need to import Oracle dmp file from Oracle RAC into an offline standalone laptop? What application(s) is necessary?

Tom Kyte - Fri, 2018-01-26 16:06
Aloha Tom, I used to work with Oracle (years ago), I've been working on virtualization and SQL server from past 15 years, now I have a task at my new job that I cannot figure out what would be best and required for it. They have exported Oracle RAC...
Categories: DBA Blogs

Running Total

Tom Kyte - Fri, 2018-01-26 16:06
Hello Sir, Could you give a simple example of running total on emp table FOr example, running total of sum (sal + comm) department wise? thanks
Categories: DBA Blogs

Pausing scheduled jobs

Tom Kyte - Fri, 2018-01-26 16:06
I am trying to determine a way where we can schedule time periods for when select dbms_scheduler jobs will automatically get "paused". I came up with a procedure (see LiveSQL Link) that works fine when run as an anonymous block. It successfully dis...
Categories: DBA Blogs

SAN and Wildcard Certificates Certified with EBS 12.1

Steven Chan - Fri, 2018-01-26 12:22

We are pleased to announce that Subject Alternative Name (SAN) and Wildcard Certificates are now certified with Oracle E-Business Suite 12.1 when enabling TLS.

Note: We are currently in the process of certifying SAN and Wildcard Certificates with Oracle E-Business Suite Release 12.2.  Monitor or subscribe to this blog for the latest in Oracle E-Business Suite technology certification announcements.

What are SAN and Wildcard Certificates?

The use of the SAN field in a certificate request (CSR) allows you to specify multiple host names to be protected by a single public key certificate. Use of SAN will also allow for using a single certificate for multiple domains.

A Wildcard Certificate is a public key certificate which can be used with multiple sub-domains of a domain.

Note: The latest releases of some browsers (e.g., Google Chrome) now require a SAN extension. Check your browser to determine if SAN is required.

How do you deploy SAN or Wildcard Certificates?

In the CSR, you may use the subjectAltName value and optionally use the wildcard character in the SAN field.

  • Example 1: SAN field entry for the CSR:

subjectAltName = DNS:www.example.com,DNS:example.com

  • Example 2: SAN field entry with a wildcard for the CSR:

subjectAltName = DNS:*.example.com

If you have already enabled TLS, you may need to redo your CSR using the SAN field. Check with your CA regarding their specific requirements for adding SAN. If you have not enabled TLS, simply follow the instructions for enabling TLS using the SAN field accordingly.

Note: We highly recommend that all customers migrate to TLS. If you have not already migrated to TLS, please do so as soon as possible.

For complete instructions, refer to the following My Oracle Support Knowledge Document:

Related Articles References
Categories: APPS Blogs

Before/after insert,update,delete trigger working on SQL*Plus but not in SQL Developer

Tom Kyte - Thu, 2018-01-25 21:46
Hello, So I have this very basic trigger script <code>set SERVEROUTPUT ON; create or replace trigger bi_superheroes before insert ON superheroes for each row ENABLE Declare v_user varchar2(20); Begin Select user into v_user from dual; ...
Categories: DBA Blogs

How to determine the exact cause of this error: ORA-07445

Tom Kyte - Thu, 2018-01-25 21:46
Hello, I am receiving the following error in the alert log of my DB: Errors in file /u01/app/oracle/admin/oraprod/udump/oraprod_ora_239365.trc: <b>ORA-07445: exception encountered: core dump [unknown] [SIGILL] [Illegal opcode] [0x000000000] [] []...
Categories: DBA Blogs

Find Column Size

Tom Kyte - Thu, 2018-01-25 21:46
Hi, Is there an accurate way of determining the size of a column in a table. Mutiplying the DATA_LENGTH from dba_tab_columns for that column with the number of rows in the table makes sense? Actually, we got request from App team that they want...
Categories: DBA Blogs

Default date part of TO_DATE function

Tom Kyte - Thu, 2018-01-25 21:46
Hi AT-Team, LiveSQL seems to be broken, so sorry no test case there. We are building a system in which developers are converting strings holding time-only data to a DATE in Oracle SQL in order to build queries involving the time of day later on...
Categories: DBA Blogs

Security model for SQL Tuning sets

Tom Kyte - Thu, 2018-01-25 21:46
Hello Tom, Is it possible for a user without DBA priv and Select privs on base tables to create SQL Tuning Tasks? what if we create a procedure under another user with DBA priv and grant the user execute on the procedure? Thx for the Supp...
Categories: DBA Blogs

Exchange partition issue

Tom Kyte - Thu, 2018-01-25 21:46
Hi, We have exchanged partition of the landing table with the history table using the below statement <code>ALTER TABLE lnd.hist_tbl EXCHANGE PARTITION v_partname WITH TABLE lnd.curr_tbl WITHOUT VALIDATION; v_partname - max partition name ...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator