Feed aggregator

Question on ORA-12899: value too large

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, We have a migration project which from Sybase SQL Anywhere to Oracle. And there is an issue we still have no perfect solution. In Sybase, When insert/update to a target column, the source string will be auto truncate if the length more t...
Categories: DBA Blogs

right way to grant permissions to developers

Tom Kyte - Tue, 2017-02-07 13:46
hello, i am junior dba with quite a little knoweledge about oracle. lately i wanted to make users for plsql developers who work on production database with the same user-bill(which has DBA role). i wanted to make them their own user for security p...
Categories: DBA Blogs

Error(24,1): PLS-00103: Encountered the symbol "BEGIN"

Tom Kyte - Tue, 2017-02-07 13:46
I wonder where it shows PLS-00103: Encountered the symbol "BEGIN" when i compile the procedure. please help <code> create or replace PROCEDURE GETCTRLEDPTDETAILSBYIPADDRR ( v_ipaddress IN NVARCHAR2 DEFAULT NULL , v_organizationId IN NUM...
Categories: DBA Blogs

Passing multiple value in IN clause in SQL query

Tom Kyte - Tue, 2017-02-07 13:46
Hi I have SQL query which takes around 50000 as input value in IN clause. What is best approach to passing value into IN clause which are more than 1000 in count.
Categories: DBA Blogs

Dynamic SQL

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, I have the following dynamic sql statement, I want to return the rowid of the inserted record into a variable but I get a command not properly ended error. What is the problem? stmt:= 'INSERT INTO ' || destination_table_name || ' ( ' |...
Categories: DBA Blogs

Parting With ocm.rsp Isn’t Really Sweet Sorrow…

Pythian Group - Tue, 2017-02-07 13:00

Now that we’re well into 2017, let’s sit back and ask the question: “What was the best news of 2016 in the Oracle ecosystem?”

I’ll give you a clue.  The best news was something that sneakily appeared between April and October 2016 in your patch maintenance logs :

[root@myclusterdb02 OPatch]# ./opatchauto apply /patches/OCT2016_bundle_patch/24436624/Database/ -oh /u01/app/oracle/product/ -ocmrf /u01/app/oracle/product/
System initialization log file is /u01/app/oracle/product/
Session log file is /u01/app/oracle/product/
WARNING: the option -ocmrf is deprecated and no longer needed. OPatch no longer checks for OCM configuration. It will be removed in a future release.

The interesting part is in that 4th line :

WARNING: the option -ocmrf is deprecated and no longer needed. OPatch no longer checks for OCM configuration. It will be removed in a future release.

This is awesome! We no longer need this annoying ocm.rsp file !

When I discovered this back in November 2016, I searched for an official feature announcement but found nothing (it looks like an opatch 13c new feature backported in 11g and 12c). This ocm.rsp file was still needed when I applied the April 2016 PSU, but from which opatch version was it implemented?

First, I looked at the 2016 dev comments in opatch and (which were the last version when I was applying this Exadata October 2016 PSU) and I sadly discovered that no specific comments were talking about this new feature (even if it would obviously deserve some):

#  opatch  04/08/16  Update CAS_VERSION to
#  opatch  04/11/16  Consolidate all 4 opatch scripts
#  opatch  06/07/16  Calling auto_patch.pl using the perl from Oracle Home Location
#  opatch  06/30/16  bug 23721730 - default memory was too high for 32 bit so removed and disabled opatch_env.sh script call


After a quick look at the code, I found the amazing piece of code that would ease our lives in the future (the function is named ocmOptionDetect()):

# Option 'ocmrf' is deprecated in OPatch 13.3 - Detect and give warning message
if [ "$OCMRF_OPTION" = "1" ] && [ "$VERSION_13_X" = "1" ]; then
echo "WARNING: the option \"-ocmrf\" is deprecated and no longer needed.  OPatch no longer checks for OCM configuration. It will be removed in a future release."


I then performed a few greps in different opatch versions that I had and I found that this feature has been implemented in:

  • or for 11g (I was unable to find any of these versions so I couldn’t check)
  • Between versions and for 12c (again, I was unable to find any of these versions so I couldn’t check)

If you come across any of the opatch versions specified above, please “grep -i ocmrf opatch” it and let me know so I can find when this feature has been implemented.

You can now upgrade all your opatches to become ocm.rsp-free and this (for me) was definitely the best new of 2016!

Categories: DBA Blogs

Setting Invalid Fields for the UI in ADF BC Groovy

Andrejus Baranovski - Tue, 2017-02-07 10:23
What if you have entity level validation rule and want to attach validation error message to specific field. By default this is not possible - all entity level validation error messages are displayed in the popup and are not attached to the fields (differently than attribute level validation rule messages).

Apparently there is a way to achieve such requirement with Groovy expression, this can be executed from entity level validation - adf.error.addAttribute('Salary'). In addAttribute you need to provide attribute name which will be assigned with the error. Complete expression for entity validator:

Result displayed on UI - validation error message is assigned to the field, which was changed:

Download sample application - GroovyADFApp_v2.zip.

Profiles of the Future

Michael Armstrong-Smith - Tue, 2017-02-07 09:37
I've been thinking about the laws of the universe recently and came across a book written by the science fiction master Arthur C. Clarke called Profiles of the Future. Over the course of many years and several revisions he put forward the hypothesis that there are 3 laws for predicting the future. These are:
Clarke's first law
When a distinguished but elderly scientist states that something is possible, he is almost certainly right. When he states that something is impossible, he is very probably wrong.
Clarke's second law
The only way of discovering the limits of the possible is to venture a little way past them into the impossible.
Clarke's third law
Any sufficiently advanced technology is indistinguishable from magic.

The third one has various alternative explanations such as:

Any technology, no matter how primitive, is magic to those who don't understand it ,and
Any sufficiently ancient recovered wisdom or artifact is also indistinguishable from magic
There's a couple of variations I like that are particularly related to the field of computing in which I work. 
These are:
Any sufficiently advanced technology is indistinguishable from a rigged demo, and
Software tests that inexplicably pass or fail are indistinguishable from voodoo
Over the course of my career I've seen the truth of these last two. Many years ago I had the privilege of being invited to the demo of a new piece of software that was slated to be years ahead of its time. It was being shown to us by an executive of that company and what we saw really was a leap beyond anything any of us had seen before that is, until one of us spotted that what appeared to be buttons being clicked on a screen actually looked suspiciously like a PowerPoint presentation moving from one picture to another. The buttons were not depressing and the positioning of some of the buttons moved ever so slightly from one screen to another.
At one point a question was asked from the audience if we could go back to the previous screen to look and see what one of the other buttons could do. Of course this would have been impossible but the presenter was slick and had anticipated such a request when his reply was that we had so much to cover in a short period of time and would love to show that feature in a separate demo after the main presentation.
When the presentation was over, some of us went to ask about that feature to be told that the link back to the database at head office had gone down and therefore we would not be able to see a demo of that feature after all.

CIO Review: The Future of Content is NOW

WebCenter Team - Tue, 2017-02-07 08:59

By this time, we are all bought into the value that a Cloud infrastructure brings. So, if you are in Information Technology (IT), you are looking to best leverage a single, centralized Cloud platform to meet your needs. As you think through your Cloud investments, consider an iterative approach where you start off looking for the biggest bang for your money and an initiative that earns you instant results (and credibility) with the various lines of businesses.

I recently wrote a feature for the CIO Review publication in which I  explored how a Cloud content hub would create value for both IT and the different lines of businesses. In it, I also shared an example of one of our customers, Omni Financiera to discuss how they are choosing to get immediate value from their Cloud investments. Take a read and see if this could be your next step in the Cloud. As always, I would welcome an open dialog and look forward to hearing from you on your thoughts.

Hope you enjoy the feature.

Database 11.2 Extended Support Fee Waived through December 2018

Steven Chan - Tue, 2017-02-07 02:06

Oracle's Lifetime Support policy has three phases:  Premier Support, Extended Support, and Sustaining Support.  For details about coverage during each phase, see:

You can purchase a support plan for your licensed products to obtain Premier Support.  There is an additional fee for Extended Support. 

Extended Support for Database 11.2 runs to December 31, 2020. The Extended Support fee for Oracle Database 11gR2 11.2 has been waived to December 31, 2018.  See:

Related Articles

Categories: APPS Blogs

Links for 2017-02-06 [del.icio.us]

Categories: DBA Blogs

Query for generating resultset with continuos dates for existing data

Tom Kyte - Mon, 2017-02-06 19:26
Dear Tom, I have a table which stores all the account balances with dates. A row is inserted into this table only for those days where the account has transactions. The table looks as below. create table account_balance (account varchar2(16), b...
Categories: DBA Blogs

Oracle AQ and wait delay issue

Tom Kyte - Mon, 2017-02-06 19:26
I wonder if you can shed any light on the following problem we're seeing when we dequeue payloads from AQ. What seems to be happening is whenever we set a delay then all delayed payloads seem to be taking ~3 seconds longer to dequeue than the wait we...
Categories: DBA Blogs

how to use ANSI outer join in subquery

Tom Kyte - Mon, 2017-02-06 19:26
how to write this query in ANSI STANDARD (I mean without using + symbol) select a.ename, a.empno from dept a where exists(select null from emp b where a.deptno=b.deptno(+))
Categories: DBA Blogs

Why i am getting invalid cursor error/

Tom Kyte - Mon, 2017-02-06 19:26
Categories: DBA Blogs

How to identify sql query running against a database

Tom Kyte - Mon, 2017-02-06 19:26
Hi Toms Team, First of all thanks to you for sharing information using "Ask Tom" platform. My question is When we run any sql against a database (I mean simple select sql eg. select 1 from dual) we can see this information by querying against ...
Categories: DBA Blogs

Execute Shell Script from Oracle Database Program.

Tom Kyte - Mon, 2017-02-06 19:26
Hi Experts, I have done the required setup to execute unix OS commands using Java class as mentioned in the link(https://oracle-base.com/articles/8i/shell-commands-from-plsql) from database programs , I was able to run echo command from the Pl/SQL...
Categories: DBA Blogs

DBA_HIGH_WATER_MARK_STATISTICS Dictinoary view has cpu_count. is this value dynamically read while DB startup.

Tom Kyte - Mon, 2017-02-06 19:26
DBA_HIGH_WATER_MARK_STATISTICS Dictinoary view has cpu_count. is this value dynamically read while DB startup. if it's static can it be possible to change. we are moving OS and database disk' from 8 core to 4 core system to fix license issue. so want...
Categories: DBA Blogs

How find deleted row without using flashback

Tom Kyte - Mon, 2017-02-06 19:26
After commit how to find a deleted row from emp table in oracle?
Categories: DBA Blogs

Installing Pervasive and Oracle on the same physical server

Tom Kyte - Mon, 2017-02-06 19:26
My company is moving from Pervasive to Oracle and we are about to install Oracle 12c. We would like to know if there are any issues that we may encounter if we installed Oracle DB on the same physical server the Pervasive DB is on.
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator