Feed aggregator

How To Control Row Removal in ADF BC

Andrejus Baranovski - Sat, 2016-06-11 11:28
There is a flaw in ADF BC remove operation. Row could be successfully removed in ADF BC - it dissapears from UI as well, but if there is DB integrity constraint violation - row is not removed in DB and error message is displayed to the user. This could be misleading to the user, he sees message about failed removal, but at the same time row is not present anymore.

Easier to explain with example. Let's imagine we want to remove IT_PROG job (there are employees assigned with this job and row removal would fail in DB):

On delete, row is removed from ADF BC and UI, but operation fails in DB and error is returned:

ADF BC completes row removal before row is really removed in DB. This is why ADF BC doesn't really know about failed row removal and is not able to keep it.

Technically such behavior is valid, but hardly understandable for business user. If row is not removed it should stay in the application.

We could solve it by overriding doDML method and executing row removal through custom method defined in VO implementation class. In doDML we can catch DML constraint exception during delete and execute refresh for removed row. Removed row is accessible in doDML, it is not dead yet. Calling refresh would allow to fix row state. We should throw exception and catch it in VO implementation class method, to refresh rowset and set back current row:

Custom method to execute remove in VO implementation class. Current row key is saved before remove and restored in case of exception. We must call executeQuery to refresh rowset, before setting back current row. In this example, commit is called right after row is removed. This allows to produce DB constraint error and process it in the context of VO. You may implement similar logic from global commit method, in such case you would need to include information about VO to be refreshed into exception message (raised in doDML):

Try to remove the same IT_PROG row now:

Failed row removal message is displayed and row remains in the application:

Download sample application - ADFDeleteControlApp.zip.

GoldenGate 12.2 checkprm and oggerr

Michael Dinh - Sat, 2016-06-11 10:05

At first, I was wondering what’s the true benefit of the new feature (checkprm) since there exists CHECKPARAMS which does this.

For someone lazy like me, it means not having to modify code to add and remove CHECKPARAMS from GoldenGate parameter files.

Once, there was oerr and now there is oggerr to look up error codes.

Reference: Oracle GoldenGate Release Notes for 12c (

$ checkprm dirprm/e_hawk.prm

2016-06-11 07:29:47  INFO    OGG-02095  Successfully set environment variable ORACLE_SID=hawk.

2016-06-11 07:29:47  INFO    OGG-02095  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/11.2.0/se_1.

(e_hawk.prm) line 15: Parameter [UPDATEMETADATA] is not valid for this configuration.

2016-06-11 07:29:47  INFO    OGG-10139  Parameter file dirprm/e_hawk.prm:  Validity check: FAIL.

$ cat dirprm/e_hawk.prm

EXTRACT e_hawk
EXTTRAIL ./dirdat/aa
INCLUDE ./dirprm/global_ggenv_se.inc
-- max_sga_size 1G per IE or IR process then add 25%, not to exeed 3.5G
-- max_sga_size > 3.5G not recommended
-- parallelism 1 is for standard edition database
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 128, parallelism 1)

$ checkprm dirprm/e_hawk.prm

2016-06-11 07:30:31  INFO    OGG-02095  Successfully set environment variable ORACLE_SID=hawk.

2016-06-11 07:30:31  INFO    OGG-02095  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/11.2.0/se_1.

2016-06-11 07:30:31  INFO    OGG-10139  Parameter file dirprm/e_hawk.prm:  Validity check: PASS.

Runtime parameter validation is not reflected in the above check.

$ oerr ora 01403

01403, 00000, "no data found"
// *Cause: No data was found from the objects.
// *Action: There was no data from the objects which may be due to end of fetch.

$ oggerr ogg 06439

06439, 00000, "No unique key is defined for table {0}. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key."
// *{0}: tableObjName (Object name)
// *Cause:  No unique key is defined for the specified table.
// *Action: Use a KEYCOLS clause in TABLE or MAP to define a key that contains
//          unique values. Using a defined key ensures uniqueness and improves
//          performance.

$ oggerr ogg 01296

01296, 00000, "Error mapping from {0} to {1}"
// *{0}: sourceTable (Qualified table name)
// *{1}: targetTable (Qualified table name)
// *Cause:  The mapping of the specified source and target tables failed.
// *Action: Examine the accompanying messages that provide details about the
//          mapping failure, and resolve the problem based on those messages.
//          If the problem persists, contact Oracle Support.

Putting SQL in the corner with Javascript in SQLCL

Kris Rice - Fri, 2016-06-10 15:03
Here's a pretty small javascript file that allows for running sql in the background of your current sqlcl session.  This is a trivial example of a sql script that has a sleep in it to simulate something taking time. It also prints the SID to show it's a different connection than the base. select 'DB SID ' ||sys_context('USERENV','SID') || ' is going to sleep' bye from dual; begin

Tuning SQL with Javascript in SQLCL

Kris Rice - Fri, 2016-06-10 15:03
In case anyone missed it, #sqlcl has the ability to run javascript. This opens a lot of options.  Here's a simple example that shows how to using javascript. Open a new Database Connection Collect stats on the base connection Do work on the main connection Collect stats again Only Print the stats that changed In SQL Developer, the autotrace feature has for a long time selected the session

Carbonated SQLCL - aka SODA support

Kris Rice - Fri, 2016-06-10 15:03
     Oracle Database introduced JSON support.  This is supported in REST Data Services already and now in the latest SQLCL.  This means ORDS can serve up the access to client applications and developers can have a command line way to interact and test features/queries/... in a nut shell a shell for JSON document storage. To use this feature the database will be to have patch #20885778

SQLCL - Blob loading ALL the files

Kris Rice - Fri, 2016-06-10 15:03
The github repo for all the things the DB Tools team does will be posted here https://github.com/oracle/Oracle_DB_Tools This will include sqlcl , sqldev, modeler, ords , and who knows what other things we have up our sleeves to post.  This repo is going to be made up of  examples and getting started things like a new extention for sqldev, custom transformation for modeler, ords integration with

AskTom's print_table - SQLCL version

Kris Rice - Fri, 2016-06-10 15:03
There was an idea logged over on community.oracle.com to add AskTom style print_table to sqlcl. Here's how easy it is now that there's scripting in sqlcl.  This is a simple js file that loops over the rows and columns and prints them.  When using the executeReturnListofList the first row is the name of the columns for reference.  With that data there it's quite simple to customize any output.

SQLCL - Blob loading from a file

Kris Rice - Fri, 2016-06-10 15:03
There is a ton of ways having new scripting in sqlcl can help.  One example is a simpler way to load a local file into a BLOB in a column. Prior to this the only options I know of would be to write custom code or move the files to the server and use utl_file or xdb or some combination of things like this. Enter SQLCL and javascript method #1. Here's a simple script which loads a local file

SQLcl - Oct 13th Edition

Kris Rice - Fri, 2016-06-10 15:03
Another build of SQLCL is out.  This one has lots of great things in it.  There's fix in there to work with Cygwin for the unlucky people that are still on windows. Now the new stuff:  SCRIPT This new command can radically change things you can do in the tool.  The script command runs a script that can be any language that is JSR-223 enabled.  The best list I can find is here: https://

SQLcl - Oct 5th Edition

Kris Rice - Fri, 2016-06-10 15:03
It was time for another push of the new sql command line , sqlcl.  Here's the changes in the latest build.  There are a number of bugs fixed relating to connecting to idle databases, alias feedback, and picking up the $ORACLE_HOME's jdbc driver when available.  There is only a couple functional changes. First is that now the jdbc connect string itself can be specified.  This opens up a number

SQLCL - More secure, now with REST !

Kris Rice - Fri, 2016-06-10 15:03
A new SQLCL build was just posted go grab it and kick the tires.  There are well over 100 bug fixes in there so it's better than ever.  Also there's some new things. More Secure    Imagine you have an api that is called and a password or something is used in the parameters.  We use exec MY_API(...)  and it works just fine.  However consider if someone with access to v$sql they just got

SQLcl , yet again

Kris Rice - Fri, 2016-06-10 15:03
By the Numbers There's a new SQLcl out for download.  In case, there are too many to keep track of the build numbers are quite easy to tell if you have the latest.  The build posted today is  sqlcl- Here's what we are doing 4.2.0 <-- doesn't matter at all 15     <- year 167   <- day in julian 0827 <- time the build was done So yes, this build was done today at 8am

Repeating another SQLCL EA release

Kris Rice - Fri, 2016-06-10 15:03
    It's been a month and a lot of bugs have been fixed in that time and new features added. That adds up to time for another EA for our new sqlcl.  The third sqlcl EA is out now and can be found here on the SQL Developer 4.1 download page.     Not sure what this sqlcl thing is ? Check here. LOAD:  Making CSV loading to the db very easy Here's a very quick example of the possibilities.

3 new things about sdsql

Kris Rice - Fri, 2016-06-10 15:03
New Name ! The first is a new name this EA it's named sqlcl for sql command line.  However, the binary to start it up is simply sql.  Nothing is easier when you need to run some sql than typing 'sql' and hitting enter. #./sql klrice/klrice@//localhost/orcl SQLcl: Release 4.1.0 Beta on Fri Jan 30 12:53:05 2015 Copyright (c) 1982, 2015, Oracle. All rights reserved. Connected to: Oracle

SDSQL - Output Formats

Kris Rice - Fri, 2016-06-10 15:03
   In SQL Developer, Jeff has shown in many tips & tricks talks and here on his blog how to get data from any select into a few formats.  The most common I would say is csv.   Nothing here has changed. Well, almost.  What will be new is the html format is getting a facelift and a json format is being added neither are in the version of sdsql or sql developer that are on otn yet     As expected

SDSQL - Pre and Post Commands

Kris Rice - Fri, 2016-06-10 15:03
  This is in the process of evolving but should be enough for anyone to give it a whirl.  The idea is that for any given statement you may want to run something before and possible after.   Here's a trivial example.  I'm issuing 2 statements 1) a drop table and 2) creating a table of select * from v$mystat before each command.  Then after I'm doing sql that shows me the changes from that point

Aliases with sdsql and simpler CTAS

Kris Rice - Fri, 2016-06-10 15:03
First, we just put up a new build of sdsql.  Go get it or the last thing here will not work.   SQL is a great and verbose language so there's many ways to shorten what we have to type.  As simple as a view or saving a script to call later with @/path/to/sessions.sql  SDSQL is taking it a step further and we added aliases to the tool.  Almost as if right on queue, John asked if we could add them.

DIY SQCL Commands

Kris Rice - Fri, 2016-06-10 15:03
As mentioned once or twice or 100 times, sqlcl exposes javascript scripting with nashorn to make things very scriptable.  To learn more on Nashorn itself there's a lot of great write ups such as http://www.oracle.com/technetwork/articles/java/jf14-nashorn-2126515.html  So far, the scripting examples have been along the lines of conditional or looping of existing sqlcl commands. Here's an

SQLCL Monitoring itself with Longops

Kris Rice - Fri, 2016-06-10 15:03
Longops is a great way to monitor things that take some time to do work.  There's an easy example of using longops on oracle-base here.  I borrowed the script from there and put it into a file named my_slow_thing.sql.  Now here's a nice example of what's possible with sqlcl. The easy way to using this is add it to your login.sql which is what I did. Of course the script could run at anytime

Assertions in a future Oracle release

Jeff Moss - Fri, 2016-06-10 14:34

I just found this link on OTN to vote for including assertions in a future release of the Oracle database.

A great idea – please vote for it.

One of the most important votes this month…well, I do live in England! Smile


Subscribe to Oracle FAQ aggregator