Feed aggregator

Inserting a decimal which has no decimal into the table with a decimal point

Tom Kyte - Fri, 2016-07-22 14:46
Hi Tom, I want to insert a decimal number which has no decimal point into a table with a decimal point. Is there a predefined function or a way to do this? Ex: (46926831654) should be inserted as (469268.31654) I dont want to use the string...
Categories: DBA Blogs

Determining current database name

Tom Kyte - Fri, 2016-07-22 14:46
Which (if either) is preferable for determining the current database name: ORA_DATABASE_NAME or sys_context('USERENV', 'DB_NAME') ? Thanx, D
Categories: DBA Blogs

Visibility on package objects

Tom Kyte - Fri, 2016-07-22 14:46
Hello, I have package that create objects and variables on the package header spec, such as: CREATE OR REPLACE PACKAGE Test_Package AS bVariable BOOLEAN := FALSE; END Test_Package; Am I correct in assuming that Test_Package.bVari...
Categories: DBA Blogs

Its all about DRG(Data Relationship Governance)

OracleApps Epicenter - Fri, 2016-07-22 10:47
You know, Oracle Data Relationship Management (DRM) is a web-based user-friendly platform for users to manage enterprise dimensions. The application provides many features such as SOX-compliant auditing, powerful and highly customizable business rule enforcement, versioning capabilities for storing historical views of dimensions, and multiple integration formats. Within DRM , DRG adds configurable, collaborative workflow to […]
Categories: APPS Blogs

Data governance

OracleApps Epicenter - Fri, 2016-07-22 10:25
Data governance is not a technology or a system but a set of processes and the organizational structure that governs the usage of all data assets of an enterprise. Data governance represents the convergence of data quality, data management, data policies,business process management, and risk management surrounding the handling of information as an asset within […]
Categories: APPS Blogs

APEX IDE for Shakespeare Programming Language (SPL)

Tony Andrews - Fri, 2016-07-22 08:04
Recently I came across an esoteric programming language called The Shakespeare Programming Language (SPL) and become rather fascinated by it.  It's big, and it's clever, but it's not terribly useful or practical.  But this year is the 400th anniversary of Shakespeare's death, which adds some relevance I suppose. Here is an example of an SPL program taken from the SPL docs.  All it does is Tony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2016/07/apex-ide-for-shakespeare-programming.html

VirtualBox 5.1.2

Tim Hall - Fri, 2016-07-22 07:31

You know a major new release is going to be rapidly followed by a bunch of fixes, so about 9 days after the release of VirtualBox 5.1 we now have VirtualBox 5.1.2.

I have got it running on my Windows 7 desktop and work and it seems fine. I’ll try OSX El Crapitan and Oracle Linux 6 hosts when I get home.



Update: Works fine on Mac (El Craptian) and Oracle Linux 6 hosts too.

VirtualBox 5.1.2 was first posted on July 22, 2016 at 1:31 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Dynamic Java in PeopleCode

Jim Marion - Fri, 2016-07-22 04:06

The PeopleCode language has a lot of features and functions. But sometimes, it seems there are tasks we need to accomplish that are just out of reach of the PeopleCode language. It is at these times that I reach for Java. I have written a lot about Java, so I'm sure many of you already know how to mix Java with PeopleCode. While certainly a rational solution, one of the pain points of a Java solution is managing custom Java on the app and process scheduler servers. Each time you update your compiled class (or jar) files, you have to restart the server. That might be OK in a stable production environment, where you don't intend to change code often, but in development, it is a real pain! Likewise, maintaining custom Java class and jar files through upgrades can be a little sketchy. Specifically, if you redeploy PeopleTools or rewrite psconfig, then it is possible you may miss some of your custom Java code. PeopleBooks tells us how to setup psconfig for custom Java classes, but again, that is just one more thing to manage through upgrades. Now, imagine being able to update your custom Java code with a Data Mover script. Further, imagine being able to run custom Java without making any changes to your application server. Imagine what it would be like to run Java without having to beg (or bribe) your admin for a "no customization" exception. It is possible today. The answer: Use JavaScript to interface between PeopleCode and the delivered Java Runtime Environment. Through the embedded Mozilla Rhino JavaScript script engine of Java, we have full, dynamic access to the JRE. When and how would you use this? Let's review some examples.

Custom HTTP Connections

For various reasons, some customers choose not to implement Integration Broker. These customers find themselves requiring integration, but without IB's networking features. An alternative to %IntBroker.ConnectorRequestURL is to use Java's HttpURLConnection.I strongly discourage this approach, but the question arises. The JRE is there, well integrated with PeopleCode, and ready for use. From PeopleCode, it is possible to create a Java URLConnection using CreateJavaObject("java.net.URL", "http...").openConnection(). A problem arises when we try to invoke methods of a HttpURLConnection, the real return value of URL.openConnection. Unfortunately, PeopleCode doesn't see it that way, which leads down the reflection path (we don't want to go there). This is where JavaScript can help us. JavaScript doesn't mind that URL.openConnection returned an HttpURLConnection even though it said it would just return a URLConnection. Here is an example:

var result = (function() {
// declare pointers to Java methods to make it easier to invoke the methods
// by name later
var URL = Packages.java.net.URL;
var InputStreamReader = Packages.java.io.InputStreamReader;
var BufferedReader = Packages.java.io.BufferedReader;
var StringBuilder = Packages.java.lang.StringBuilder;

var serverAddress = new URL(

// Creates an HttpURLConnection, but returns URLConnection. If I was using
// PeopleCode, PeopleCode would see this as a URLConnection. To invoke
// HttpURLConnection methods, I would need to resort to reflection. This is
// the power of JavaScript in this scenario...
var connection = serverAddress.openConnection();

// ... for example, setRequestMethod is NOT a method of URLConnection. It is
// a method of HttpURLConnection. PeopleCode would throw an error, but
// JavaScript recognizes this is an HttpURLConnection and allows the method
// invocation

// Timeout in milliseconds

// Finally, make the connection

// Read the response
var reader = new BufferedReader(
new InputStreamReader(connection.getInputStream()));
var sb = new StringBuilder();
var line;

while ((line = reader.readLine()) !== null) {
sb.append(line + '\n');

// Return the response to PeopleCode. In this case, the response is an XML
// string
return sb;

Excel Spreadsheets

PeopleTools 8.55+ has a PeopleCode API for Excel, which means this solution is now irrelevant. I'm listing it because not everyone is up to PeopleTools 8.55 (yet). If you use this idea to build a solution for 8.54 and later upgrade, Oracle recommends that you switch to the PeopleCode Excel API. The solution will still work with 8.55+, but just isn't recommended post 8.54.

This solution uses the Apache POI library that is distributed with PeopleTools 8.54+ to read and write binary Microsoft Excel files. As with the networking solution above, it is possible to use POI directly from PeopleCode, but a little difficult because POI uses method overloading in a manner that PeopleCode can't resolve. Furthermore, POI uses methods that return superclasses and interfaces that PeopleCode can't cast to subclasses, leading to awful reflection code. Here is an example that reads a spreadsheet row by row, inserting each row into a staging table for later processing.

// endsWith polyfill
if (!String.prototype.endsWith) {
String.prototype.endsWith = function(searchString, position) {
var subjectString = this.toString();
if (typeof position !== 'number' || !isFinite(position) ||
Math.floor(position) !== position ||
position > subjectString.length) {
position = subjectString.length;
position -= searchString.length;
var lastIndex = subjectString.indexOf(searchString, position);
return lastIndex !== -1 && lastIndex === position;

// open a workbook, iterate over rows/cells, and then insert them into a
// staging table
var result = (function() {
// declare pointers to Java methods to make it easier to invoke the methods
// by name
var FileInputStream = Packages.java.io.FileInputStream;

var HSSFWorkbook = Packages.org.apache.poi.hssf.usermodel.HSSFWorkbook;
var Workbook = Packages.org.apache.poi.ss.usermodel.Workbook;
var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook;

// declare a PeopleCode function
var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;

// internal "helper" function that will identify rows inserted into
var guid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,
function(c) {
var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
return v.toString(16);

// open a binary Microsoft Excel file
var fis = new FileInputStream(fileName);

var workbook;

if(fileName.toLowerCase().endsWith("xlsx")) {
workbook = new XSSFWorkbook(fis);
} else if(fileName.toLowerCase().endsWith("xls")) {
workbook = new HSSFWorkbook(fis);

var sheet = workbook.getSheetAt(0);
var rowIterator = sheet.iterator();
var roleName,

// iterate over each row, inserting those rows into a staging table
while (rowIterator.hasNext()) {
row = rowIterator.next();
roleName = row.getCell(0).getStringCellValue();
descr = row.getCell(1).getStringCellValue();

// TODO: turn this into a stored SQL definition, not hard coded SQL
// notice that the SQLExec parameters are wrapped in an array
[guid, roleName, descr]

// return the unique identifier that can later be used to select the rows
// inserted by this process
return guid;


Here is an example of writing/creating a Microsoft Excel spreadsheet:

var result = (function() {
// import statements
var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook;
var FileOutputStream = Packages.java.io.FileOutputStream;

// variable declarations
var workbook = new XSSFWorkbook();
var sheet = workbook.createSheet("Countries");
var fileName = "c:/temp/countries.xlsx";

var row = sheet.createRow(0);
var cell = row.createCell(0);

cell.setCellValue("United States of America");
cell = row.createCell(1);

row = sheet.createRow(1);
cell = row.createCell(0);
cell = row.createCell(1);

row = sheet.createRow(1);
cell = row.createCell(0);
cell = row.createCell(1);

var fos = new FileOutputStream(fileName);

return "Created workbook " + fileName;


JSON Parsing

If your goal is to convert a JSON string into SQL insert statements, then this is a very painless alternative:

/* Sample JSON data that will be selected from a record definition
{"emplid": "KU0001", "oprid": "HCRUSA_KU0001"},
{"emplid": "KU0002", "oprid": "HCRUSA_KU0002"},
{"emplid": "KU0003", "oprid": "HCRUSA_KU0003"}

var result = (function() {
var CreateRecord = Packages.PeopleSoft.PeopleCode.Func.CreateRecord;
var Name = Packages.PeopleSoft.PeopleCode.Name;
var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;

// example of how to reference a PeopleCode record definition from
// JavaScript. Later we will select JSON_DATA from this table
var rec = CreateRecord(new Name('RECORD', 'NAA_SCRIPT_TBL'));

var count = 0;
var json_string;
var json;

var guid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
return v.toString(16);

// Select JSON string from a table. Normally this would come from a variable,
// a service, etc. Here it makes a great example of how to select rows from
// a record definition
rec.GetField(new Name('FIELD', 'PM_SCRIPT_NAME')).setValue('JSON_TEST_DATA');
json_string = rec.GetField(new Name('FIELD', 'HTMLAREA')).getValue();

// now convert that received string into an object.
json = JSON.parse(json_string);

// Iterate over json data and...
json.forEach(function(item, idx) {
// ... insert into a staging table
// notice the array wrapper around SQLExec bind values
[guid, item.emplid, item.oprid]
count += 1;

return "Inserted " + count + " rows";


I could go on and on with examples of creating zip files, encrypting information, base64 encoding binary data, manipulating graphics using Java 2D, etc, but I think you get the idea.

Links for 2016-07-21 [del.icio.us]

Categories: DBA Blogs


Michael Dinh - Thu, 2016-07-21 21:14

I was faced with a very strange situation.

When using SETENV (ORACLE_SID = db01), replicat ABEND with  OGG-00664  OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available

When using SETENV (ORACLE_HOME=/u01/app/oracle/product/, replicat starts without any issues.

I know what you are thinking, why not set both ORACLE_SID and ORACLE_HOME and you are correct since this is best practices.

The client already knows this and wanted to troubleshoot why it was failing?

Any ideas? I scoured the internet and MOS and finally found that using get_env was the key to troubleshooting.

Tried tracing the replicat and did not work since replicat abended.

NOTE:  Environment variables are all set correctly from the OS.


getenv (ORACLE_HOME)
ORACLE_HOME = /u01/app/oracle/product/

getenv (ORACLE_SID)

2016-07-21 18:33:46 ERROR OGG-00664 OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory).

SETENV (ORACLE_HOME=/u01/app/oracle/product/

getenv (ORACLE_HOME)
ORACLE_HOME = /u01/app/oracle/product/

getenv (ORACLE_SID)

Notice, there are 2 different ORACLE_HOMEs being used.

Bad one is ORACLE_HOME = /u01/app/oracle/product/ since the HOME does not exists.

Wait a minute, how is it possible the HOME does not exists and where did Goldengate get this information ???

Let’s find the installation log.
/u01/app/oraInventory/logs> grep "MANAGER_PORT" installActions2016*.log|uniq
installActions2016-05-09_04-57-53PM.log:INFO: Setting value for the property:MANAGER_PORT in the bean:OGGInstallSettings
installActions2016-05-09_04-57-53PM.log: MANAGER_PORT              7809                                       
installActions2016-05-09_04-57-53PM.log: MANAGER_PORT              7809                                                                    
installActions2016-05-09_04-57-53PM.log:INFO: adding the variable MANAGER_PORT to command line args table
installActions2016-05-09_04-57-53PM.log:INFO: Setting variable 'MANAGER_PORT' to '7809'. Received the value from the command line.
installActions2016-05-09_04-57-53PM.log:INFO: This variable MANAGER_PORT is not added to the global context map

/u01/app/oraInventory/logs> grep DATABASE_HOME installActions2016-05-09_04-57-53PM.log
INFO: Setting value for the property:DATABASE_HOME in the bean:OGGInstallSettings
 DATABASE_HOME             /u01/app/oracle/product/  
 DATABASE_HOME             /u01/app/oracle/product/                               
INFO: adding the variable DATABASE_HOME to command line args table
INFO: Setting variable 'DATABASE_HOME' to '/u01/app/oracle/product/'. Received the value from the command line.
INFO: This variable DATABASE_HOME is not added to the global context map

There it is, Goldengate is using ORACLE_HOME defined from the installation.

DATABASE_LOCATION is not required, so why set it?

Admittedly, I have have been setting DATABASE_LOCATION but will not be doing so any more.

# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.

Lastly, I have not attempted to reproduce the error. Too lazy. May be you can help?

TRIM function in 12c

Tom Kyte - Thu, 2016-07-21 20:26
Hello, Tom! I've a lack of undestanding of 'trim' fuction's IN parameters. It's not explicitly said in the docs, that only a single character can be used as trim_character. However, an attempt to use a syntax like this: SELECT TRIM(BOTH 'A...
Categories: DBA Blogs

Implicit vs explicit cursors

Tom Kyte - Thu, 2016-07-21 20:26
Hi sir how are you....here i got a small doubt.i hope u can reply me.The doubt was....select * from tb...when i was select the data it gave me total records(100),so in this case what we will call it.I mean it is an implicit cursor or an explicit curs...
Categories: DBA Blogs

Design question

Tom Kyte - Thu, 2016-07-21 20:26
Hello AskTom Team, Hope you could help me with this. Business case: 1) We have system of records which we provide to customers. We refresh this data on demand (could be push or pull) on customer's on-premise instance 2) Cusotmer can overrid...
Categories: DBA Blogs

database corrupted - ORA-09925: Unable to create audit trail file

Tom Kyte - Thu, 2016-07-21 20:26
Actually when my database is running at that time I change my parameter file.after changing I shutdown my database.And again I tried to connect to my database as sysdba. But I cannnot connect to that database.It shows some errors. Then what is the s...
Categories: DBA Blogs

DBMS_SCHEDULER jobs - How Job_Priority attribute works?

Tom Kyte - Thu, 2016-07-21 20:26
Hi, Request your help to understand working of Job_Priority attribute that can be set for jobs created via DBMS_SCHEDULER. From my tests it seems that the attribute behavior is somehow linked to the number of jobs scheduled. However, looks like...
Categories: DBA Blogs


Tom Kyte - Thu, 2016-07-21 20:26
what is PMON?
Categories: DBA Blogs

Identifying ASM "orphans"

Tom Kyte - Thu, 2016-07-21 20:26
We've been migrating several DBs from their old Oracle 10G platforms to our new Oracle SuperClusters (SC). The older DBs were flatfile based, but the new SC-based DBs are using ASM. Several of the migrations initially failed, and some of the older DB...
Categories: DBA Blogs

parallel statement queueing - how to synchronize sessions?

Tom Kyte - Thu, 2016-07-21 20:26
Hi Tom-Team, I want to process statements in parallel by setting "parallel_degree_policy=auto". They are a mixture of DML and DDL statements. In this context, the order in which statements execute, is undefined. I know that I can amend that (parti...
Categories: DBA Blogs

Selectively purge the SQLAREA... terrible idea?

Tom Kyte - Thu, 2016-07-21 20:26
Hi "Tom"- We have an outside vendor's product hitting our database and never using bind variables. Every statement is unique. We can't change it, we're stuck with it. This outside program is a very high volume. These constantly unique statem...
Categories: DBA Blogs

Modernize Oracle Forms: Go Mobile! A Webcast You Won't Want to Miss!

WebCenter Team - Thu, 2016-07-21 12:36

Authored by Dan Brooks, Product Marketing Manager, Oracle Mobile

As many of you know, Oracle Forms provides the backbone for hundreds of thousands of business applications. However, in today's modern work environment, employees want fast, flexible, easy-to-use mobile apps that can connect to their company's backend data infrastructure.  But how can you take your legacy Forms applications and convert them into what employees expect without rebuilding your entire system at a considerable cost?  

Join us for a live webinar, on Thursday, August 4th at 1pm EDT / 10am PDT, to learn how Oracle Gold Partner, AuraPlayer, can leverage your organization’s existing Oracle Forms systems and Oracle Mobile Cloud Service to create mobile API’s without redevelopment cost or risk.  With this solution, employees can be more more productive - anywhere, anytime!

Martin Jarvis, Oracle Sr. Director of Mobile Market Development, and Mia Urman, CEO of AuraPlayer, will cover the following:

  • How to extend your Oracle Forms applications without having to change a single line of code, while production systems remain untouched
  • How to deliver on a platform that's built on open standards and connects to any app and to any backend system
  • How to deliver modern, on-device apps that use native services (camera, GPS, etc.) for richer Forms capabilities
Sign up for this live webinar today!

Follow us: @OracleMobile

Join the conversation on LinkedIn


Subscribe to Oracle FAQ aggregator