Feed aggregator

Generate Days in Month (PIPELINED Functions)

Duncan Mein - Tue, 2009-08-25 08:57
This cool example is not one I can take the credit for but since it is used pretty heavily in our organisation, I thought I would share it as it's not only pretty cool buy also demonstrates how useful Oracle Pipelined functions can be.

In essence a Pipeline table function (introduced in 9i) allow you use a PL/SQL function as the source of a query rather than a physical table. This is really useful in our case to generate all the days in a calendar month via PL/SQL and query them back within our application.

To see this in operation, simply create the following objects:

CREATE OR REPLACE TYPE TABLE_OF_DATES IS TABLE OF DATE;

CREATE OR REPLACE FUNCTION GET_DAYS_IN_MONTH
(
pv_start_date_i IN DATE
)
RETURN TABLE_OF_DATES PIPELINED
IS
lv_working_date DATE;
lv_days_in_month NUMBER;
lv_cnt NUMBER;
BEGIN

lv_working_date := TO_DATE(TO_CHAR(pv_start_date_i, 'RRRRMM') || '01', 'RRRRMMDD');
lv_days_in_month := TRUNC(LAST_DAY(lv_working_date)) - TRUNC(lv_working_date);

PIPE ROW(lv_working_date);

FOR lv_cnt IN 1..lv_days_in_month
LOOP
lv_working_date := lv_working_date + 1;
PIPE ROW (lv_working_date);
END LOOP;

RETURN;

END GET_DAYS_IN_MONTH;
/

Once your objects are successfully complied, you can generate all the days in a month by executing the following query:

SELECT column_value the_date
, TO_CHAR(column_value, 'DAY') the_day
FROM TABLE (get_days_in_month(sysdate));

THE_DATE THE_DAY
------------------------
01-AUG-09 SATURDAY
02-AUG-09 SUNDAY
03-AUG-09 MONDAY
04-AUG-09 TUESDAY
05-AUG-09 WEDNESDAY
06-AUG-09 THURSDAY
07-AUG-09 FRIDAY
08-AUG-09 SATURDAY
09-AUG-09 SUNDAY
10-AUG-09 MONDAY
11-AUG-09 TUESDAY
12-AUG-09 WEDNESDAY
13-AUG-09 THURSDAY
14-AUG-09 FRIDAY
15-AUG-09 SATURDAY
16-AUG-09 SUNDAY
17-AUG-09 MONDAY
18-AUG-09 TUESDAY
19-AUG-09 WEDNESDAY
20-AUG-09 THURSDAY
21-AUG-09 FRIDAY
22-AUG-09 SATURDAY
23-AUG-09 SUNDAY
24-AUG-09 MONDAY
25-AUG-09 TUESDAY
26-AUG-09 WEDNESDAY
27-AUG-09 THURSDAY
28-AUG-09 FRIDAY
29-AUG-09 SATURDAY
30-AUG-09 SUNDAY
31-AUG-09 MONDAY


I hope someone finds this example as useful as we do. The credits go to Simon Hunt on this one as it was "borrowed" from one of his apps. Since I offered to buy him a beer he has promised not to make too big a deal out it :)

As always, you can read up on this topic here

Missing AppsLogin.jsp...

Bas Klaassen - Tue, 2009-08-25 01:20
I am still facing the same problem with my R12 upgrade.When running the post install checks using Rapidwiz , only the JSP and the Login page show errors.For jsp I see 'JSP not responding, waiting 15 seconds and retesting'and the Login Page shows 'RW-50016: Error. -{0} was not created. File= {1}'The strange thing is that all other checks are oke. Even the /OA_HTML/help check !So, the problem is Bas Klaassenhttp://www.blogger.com/profile/04080547141637579116noreply@blogger.com15
Categories: APPS Blogs

R12 upgrade

Bas Klaassen - Sun, 2009-08-23 03:46
I Finally upgraded my 11.5.10.2 environment to R12.I followed the steps mentioned in the different upgrade guides. What do I have runing right now ?- Oracle eBS 12.0.6- Oracle database 10.2.0.4- Oracle tech stack (old ora directory) 10.1.2.3.0- Oracle tech stack (old iAS directory) 10.1.3.4.0So, having no problems during the upgrade proces, I finished starting al services. When trying to login myBas Klaassenhttp://www.blogger.com/profile/04080547141637579116noreply@blogger.com5
Categories: APPS Blogs

OOW any free Discover tickets around?

Claudia Zeiler - Sat, 2009-08-22 20:05
A quick question.

I see that Oracle Open World Discover ticket costs $50. I remember that it cost something in prior years, but that there was some way to get it for free if you signed up by the early bird date. Is there such a thing again this year?

I have seen the 'chance for a full registration', and have entered, but I haven't seen the free 'discover' pass. Is it around?

TYIA

OOW 2009

Fadi Hasweh - Tue, 2009-08-18 06:26
Preparation been made this year for Oracle open world conference 2009. The conference will take place in San Francisco between 11-15 Oct. there is a high chance that I will attend this conference. I will post here again once confirmed so we can meet there if any one is planning to attend. I will use this chance of attending to back to blogging again as its been a while since I last update my blog.
For more information about OOW 2009 please check http://www.oracle.com/openworld/index.html
Fadi

Getting Started with OCCI (Linux Version with RPM Instant Client)

Mark A. Williams - Mon, 2009-08-17 17:29

This is a follow-on post to the "Getting Started with OCCI (Linux Version)" and "Getting Started with OCCI (Windows Version)" posts. In the previous post on getting started with OCCI on Linux, I illustrated using the .zip file method of installing the Oracle Instant Client software. In this post I will cover using the .rpm file downloads rather than the .zip files. Much of the content is intentionally the same, however. This means I will need to use the "root" user to install the .rpm Instant Client packages. With the .zip file method this is not required.

The steps should be general enough to easily mould them to your environment and/or needs.

The Oracle C++ Call Interface, also known as OCCI, is an application programming interface (API) built upon the Oracle Call Interface (OCI - another lower level API from Oracle). One of the goals of OCCI is to offer C++ programmers easy access to Oracle Database in a fashion similar to what Java Database Connectivity (JDBC) affords Java developers. If you would like to learn more about what OCCI is (and isn't), pay a visit to the OCCI documentation on Oracle Technology Network (OTN) here:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm

My goal with this "Getting Started" post is to give you one method of setting up an environment in which you can use OCCI to develop C++ applications under Linux that access Oracle Database. I am not in any way covering all possible scenarios or delving deep into OCCI itself. Please note that the database itself can be on any supported host.

The Environment

Your environment is likely to differ from mine; however, it is important to be familiar with the various components in the environment used here so that you can make adaptations as necessary for your specific environment.

  • Oracle Database Server/Host: oel01 (Oracle Enterprise Linux 32-bit server)
  • Oracle Database: SID value of OEL11GR1, Service Name value of OEL11GR1.SAND, version 11.1.0.7
  • Development Machine: Hostname of "gerrard", Oracle Enterprise Linux 32-bit (2.6.9 kernel)
  • Development IDE: VIM (any text editor or IDE you can configure should work)
  • Oracle Client: Oracle Instant Client with OCCI
Important Information

One of the most crucial attributes of working with OCCI is that you must ensure that all of the components of the development environment and the runtime environment are supported combinations and correct versions. I can not emphasize this enough. If you deviate from this, you will almost certainly find trouble! In order to find the correct combinations of products and versions, see the following links on OTN:

Download the Correct Packages

The Linux packages come in two varieties: a .zip file or a .rpm file. For this install I have used the .rpm files.

From the download links above, you should download the following components to your development machine.

  • Instant Client Package – Basic: oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
  • Instant Client Package - SDK: oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
  • Instant Client Package - SQL*Plus: oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm  (optional, but I always install it)

NOTE: I am using the "Basic" version of the main Instant Client packages and not the "Basic Lite" version for this install. Whilst the "Basic Lite" version is a smaller download, the oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm package wants the full "Basic" rather than the "Basic Lite" package as a prerequisite. You could probably force the install anyway by using "--nodeps" but using the full package is not any more difficult. It also provides additional language support not present in the "lite" package.

Install the Instant Client Packages

Installing the Instant Client packages provided as .rpm files is simply a matter of downloading them and then using rpm in the normal fashion – there's really not too much to go wrong here! I downloaded each of them (3 total) into a directory called "/downloads" on "gerrard". Each of the packages will provide the following files:

[root@gerrard downloads]# rpm -qlp oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
/usr/lib/oracle/11.1/client/bin/adrci
/usr/lib/oracle/11.1/client/bin/genezi
/usr/lib/oracle/11.1/client/lib/libclntsh.so.11.1
/usr/lib/oracle/11.1/client/lib/libnnz11.so
/usr/lib/oracle/11.1/client/lib/libocci.so.11.1
/usr/lib/oracle/11.1/client/lib/libociei.so
/usr/lib/oracle/11.1/client/lib/libocijdbc11.so
/usr/lib/oracle/11.1/client/lib/ojdbc5.jar
/usr/lib/oracle/11.1/client/lib/ojdbc6.jar

[root@gerrard downloads]# rpm -qlp oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
/usr/include/oracle/11.1/client/nzerror.h
/usr/include/oracle/11.1/client/nzt.h
/usr/include/oracle/11.1/client/occi.h
/usr/include/oracle/11.1/client/occiAQ.h
/usr/include/oracle/11.1/client/occiCommon.h
/usr/include/oracle/11.1/client/occiControl.h
/usr/include/oracle/11.1/client/occiData.h
/usr/include/oracle/11.1/client/occiObjects.h
/usr/include/oracle/11.1/client/oci.h
/usr/include/oracle/11.1/client/oci1.h
/usr/include/oracle/11.1/client/oci8dp.h
/usr/include/oracle/11.1/client/ociap.h
/usr/include/oracle/11.1/client/ociapr.h
/usr/include/oracle/11.1/client/ocidef.h
/usr/include/oracle/11.1/client/ocidem.h
/usr/include/oracle/11.1/client/ocidfn.h
/usr/include/oracle/11.1/client/ociextp.h
/usr/include/oracle/11.1/client/ocikpr.h
/usr/include/oracle/11.1/client/ocixmldb.h
/usr/include/oracle/11.1/client/odci.h
/usr/include/oracle/11.1/client/oratypes.h
/usr/include/oracle/11.1/client/ori.h
/usr/include/oracle/11.1/client/orid.h
/usr/include/oracle/11.1/client/orl.h
/usr/include/oracle/11.1/client/oro.h
/usr/include/oracle/11.1/client/ort.h
/usr/include/oracle/11.1/client/xa.h
/usr/lib/oracle/11.1/client/lib/libclntsh.so
/usr/lib/oracle/11.1/client/lib/libocci.so
/usr/lib/oracle/11.1/client/lib/ottclasses.zip
/usr/share/oracle/11.1/client/cdemo81.c
/usr/share/oracle/11.1/client/demo.mk
/usr/share/oracle/11.1/client/occidemo.sql
/usr/share/oracle/11.1/client/occidemod.sql
/usr/share/oracle/11.1/client/occidml.cpp
/usr/share/oracle/11.1/client/occiobj.cpp
/usr/share/oracle/11.1/client/occiobj.typ
/usr/share/oracle/11.1/client/ott

[root@gerrard downloads]# rpm -qlp oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm
/usr/bin/sqlplus
/usr/lib/oracle/11.1/client/bin/sqlplus
/usr/lib/oracle/11.1/client/lib/glogin.sql
/usr/lib/oracle/11.1/client/lib/libsqlplus.so
/usr/lib/oracle/11.1/client/lib/libsqlplusic.so

I then installed each of them as follows:

rpm -ivh oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
rpm -ivh oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
rpm -ivh oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm

Configure The Environment

To facilitate easily using the new installation I create a file I can source to set my environment correctly. I use the csh (well, tcsh really) as my primary shell and I created a file called "oic11.csh" in my home directory to setup the environment for me:

[markwill@gerrard ~]$ cat oic11.csh
#
setenv ORACLE_BASE /usr/lib/oracle
setenv ORACLE_HOME ${ORACLE_BASE}/11.1/client
setenv LD_LIBRARY_PATH ${ORACLE_HOME}/lib
set path = (${ORACLE_HOME}/bin /usr/local/bin /bin /usr/bin /usr/X11R6/bin ~/bin)

If you are using the bash shell, you may find something like the following helpful:

[markwill@gerrard ~]> cat oic11.env
export ORACLE_BASE=/usr/lib/oracle
export ORACLE_HOME=$ORACLE_BASE/11.1/client
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:~/bin

I can then set my environment to use the new install as follows (in csh):

[markwill@gerrard ~]> source ./oic11.csh

This could be done as follows for bash:

[markwill@gerrard ~]> . ./oic11.env

I also created a configuration file for the dynamic linker and updated the links/cache as follows:

[root@gerrard ~]# echo "/usr/lib/oracle/11.1/client/lib" > /etc/ld.so.conf.d/oic.conf
[root@gerrard ~]# /sbin/ldconfig


Create a Simple Test Project

All the setup work is now complete and the environment is configured! If needed, you can use the following (very!) basic application as a simple test to verify things are working as expected. Again, this is a simple example only to verify things are setup correctly. It is not intended to be a complete template for "proper" code development, etc. Be sure to set the environment correctly!

I created a "Projects" directory under my home directory and then a directory called "Employees" under the "Projects" directory. I then used VIM to create the Employees.h and Employees.cpp files.

Here's the content of the Employees.h file on my system:


/*
 * A simple OCCI test application
 * This file contains the Employees class declaration
 */

#include <occi.h>
#include <iostream>
#include <iomanip>

using namespace oracle::occi;
using namespace std;

class Employees {
public:
  Employees();
  virtual ~Employees();

  void List();

private:
  Environment *env;
  Connection  *con;

  string user;
  string passwd;
  string db;
};

Here's the content of the Employees.cpp file on my system:


/*
 * A simple OCCI test application
 * This file contains the Employees class implementation
 */

#include "Employees.h"

using namespace std;
using namespace oracle::occi;

int main (void)
{
  /*
   * create an instance of the Employees class,
   * invoke the List member, delete the instance,
   * and prompt to continue...
   */

  Employees *pEmployees = new Employees();

  pEmployees->List();

  delete pEmployees;

  cout << "ENTER to continue...";

  cin.get();

  return 0;
}

Employees::Employees()
{
  /*
   * connect to the test database as the HR
   * sample user and use the EZCONNECT method
   * of specifying the connect string. Be sure
   * to adjust for your environment! The format
   * of the string is host:port/service_name

   */

  user = "hr";
  passwd = "hr";
  db = "oel01:1521/OEL11GR1.SAND";

  env = Environment::createEnvironment(Environment::DEFAULT);

  try
  {
    con = env->createConnection(user, passwd, db);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();

    exit(EXIT_FAILURE);
  }
}

Employees::~Employees()
{
  env->terminateConnection (con);

  Environment::terminateEnvironment (env);
}

void Employees::List()
{
  /*
   * simple test method to select data from
   * the employees table and display the results
   */

  Statement *stmt = NULL;
  ResultSet *rs = NULL;
  string sql = "select employee_id, first_name, last_name " \
               "from employees order by last_name, first_name";

  try
  {
    stmt = con->createStatement(sql);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();
  }

  if (stmt)
  {
    try
    {
      stmt->setPrefetchRowCount(32);

      rs = stmt->executeQuery();
    }
    catch (SQLException& ex)
    {
      cout << ex.getMessage();
    }

    if (rs)
    {
      cout << endl << setw(8) << left << "ID"
           << setw(22) << left << "FIRST NAME"
           << setw(27) << left << "LAST NAME"
           << endl;
      cout << setw(8) << left << "======"
           << setw(22) << left << "===================="
           << setw(27) << left << "========================="
           << endl;

      while (rs->next()) {
        cout << setw(8) << left << rs->getString(1)
             << setw(22) << left << (rs->isNull(2) ? "n/a" : rs->getString(2))
             << setw(27) << left << rs->getString(3)
             << endl;
      }

      cout << endl;

      stmt->closeResultSet(rs);
    }

    con->terminateStatement(stmt);
  }
}

To build the simple test I created a simplistic Makefile:

[markwill@gerrard Employees]> cat Makefile
Employees: Employees.cpp
  g++ -o Employees Employees.cpp \
  -I/usr/include/oracle/11.1/client \
  -L$(ORACLE_HOME)/lib -lclntsh -locci

debug: Employees.cpp
  g++ -ggdb3 -o Employees Employees.cpp \
  -I/usr/include/oracle/11.1/client \
  -L$(ORACLE_HOME)/lib -lclntsh -locci

clean:
  rm -f Employees

NOTE: The indented lines are tabs and not spaces in the Makefile

Whilst certainly not destined to win any awards for Makefile creativity it suffices for the purpose at hand.

I then built the application in debug mode by typing "make debug".

Executing the sample should result in output as follows:

[markwill@gerrard Employees]> ./Employees

ID      FIRST NAME            LAST NAME
======  ====================  =========================
174     Ellen                 Abel
166     Sundar                Ande
130     Mozhe                 Atkinson
105     David                 Austin
204     Hermann               Baer
116     Shelli                Baida
167     Amit                  Banda
172     Elizabeth             Bates

[ snip ]

120     Matthew               Weiss
200     Jennifer              Whalen
149     Eleni                 Zlotkey

ENTER to continue...

If you are new to using OCCI on Linux and have used the .rpm file method of installing the Instant Client components, perhaps the above will be helpful in getting started!

AR.HZ_LOCATIONS_N15 is UNUSABLE

Madan Mohan - Mon, 2009-08-17 03:55
Problem;
*******
AR.HZ_LOCATIONS_N15 is UNUSABLE .

SQL> select index_name,owner,status from dba_indexes where status='UNUSABLE';

INDEX_NAME OWNER STATUS
------------------------------ ------------------------------ --------
HZ_LOCATIONS_N15 AR UNUSABLE


Solution
********
a) delete user_sdo_geom_metadata
where table_name='HZ_LOCATIONS'
and COLUMN_NAME='GEOMETRY';

b) drop index AR.HZ_LOCATIONS_N15 force;

c) cd $AR_TOP/patch/115/sql
sqlplus ar/ar @ARHGEOIS.sql
sqlplus apps/apps @ARHGEOID.sql

You'll be prompted to enter a value, choose AR
Enter the value for value 1 : AR


Verify the Object by running the below query as APPS.

select index_name,status from dba_indexes where table_name='HZ_LOCATIONS' and index_name='HZ_LOCATIONS_N15';

Rgds,
Madan

From Oracle to a Successful Vineyard

Virag Sharma - Sun, 2009-08-16 11:33

Last Month , I was there in Nasik ( One Small Town near Mumabi ). We roam around the city and visited many places in town. Our Driver suggested to visit Sual Vineyard. I am not very found of wine ,

But had very good time with friends in Napa Valley, CA, US, So thought let me experience Indian Vineyard.In Vineyard , we learned that owner of Sula Vineyard Mr. Rajeev is graduated in Economics and Industrial Engineering from Stanford and was working with Oracle in the Silicon Valley. Latter he left

Job and started Vineyard in India , today Sula is one of the well know Wine band in India. Story look interesting , so thought, to share with all(Links are given below ).


From Oracle to a Successful Vineyard: Rajeev Samant of Sula Wines
http://www.delhiwineclub.com/Interview/interview_with_rajeev2.asp
http://www.pagalguy.com/plugins/p2_news/printarticle.php?p2_articleid=912
Categories: DBA Blogs

Getting Started with OCCI (Linux Version)

Mark A. Williams - Sat, 2009-08-15 19:09

This is a follow-on post to the "Getting Started with OCCI (Windows Version)" with the not too minor change of swapping out Windows for Linux as the development machine. Much of the content is intentionally the same, however. For this particular "walkthrough" (for lack of a better term) I illustrate creating a "private" installation of the Oracle software. That is, I extract the software under my user's home directory. This has the advantage that it is completely separate from any other user's software. Of course, it also has the disadvantage that if many users have the same software installed there will be duplication. As this machine has no other "normal" user accounts (i.e. non-system accounts such as "root") other than my account this is not an issue for me.

The steps should be general enough to easily mould them to your environment and/or needs.

The Oracle C++ Call Interface, also known as OCCI, is an application programming interface (API) built upon the Oracle Call Interface (OCI - another lower level API from Oracle). One of the goals of OCCI is to offer C++ programmers easy access to Oracle Database in a fashion similar to what Java Database Connectivity (JDBC) affords Java developers. If you would like to learn more about what OCCI is (and isn't), pay a visit to the OCCI documentation on Oracle Technology Network (OTN) here:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm

My goal with this "Getting Started" post is to give you one method of setting up an environment in which you can use OCCI to develop C++ applications under Linux that access Oracle Database. I am not in any way covering all possible scenarios or delving deep into OCCI itself. Please note that the database itself can be on any supported host.

The Environment

Your environment is likely to differ from mine; however, it is important to be familiar with the various components in the environment used here so that you can make adaptations as necessary for your specific environment.

  • Oracle Database Server/Host: oel01 (Oracle Enterprise Linux 32-bit server)
  • Oracle Database: SID value of OEL11GR1, Service Name value of OEL11GR1.SAND, version 11.1.0.7
  • Development Machine: Hostname of "gerrard", Oracle Enterprise Linux 32-bit (2.6.9 kernel)
  • Development IDE: VIM (any text editor or IDE you can configure should work)
  • Oracle Client: Oracle Instant Client with OCCI
Important Information

One of the most crucial attributes of working with OCCI is that you must ensure that all of the components of the development environment and the runtime environment are supported combinations and correct versions. I can not emphasize this enough. If you deviate from this, you will almost certainly find trouble! In order to find the correct combinations of products and versions, see the following links on OTN:

Download the Correct Packages

The Linux packages come in two varieties: a .zip file or a .rpm file. I have used the .zip files since I will be creating a private install under my user's home directory. The .rpm files require a user with the ability to install them (such as "root") and use directories such as "/usr/lib" and "/usr/bin". I want to keep this install as simple as possible and the .zip files work well for this.

From the download links above, you should download the following components to your development machine. I downloaded them to my home directory.

  • Instant Client Package – Basic Lite: instantclient-basiclite-linux32-11.1.0.7.zip
  • Instant Client Package - SDK: instantclient-sdk-linux32-11.1.0.7.zip
  • Instant Client Package - SQL*Plus: instantclient-sqlplus-linux32-11.1.0.7.zip  (optional, but I always install it)

NOTE: I am using the "Basic Lite" version of the main Instant Client packages. This is a smaller download and meets my needs; however, if you require character set or language support not provided by the "Basic Lite" version of Instant Client you should use the "basic" version instead. See the Instant Client home page (link above) for additional information.

Install the Instant Client Packages

Installing the Instant Client packages provided as .zip files is simply a matter of unzipping them – not much to go wrong here! I unzipped each of them (3 total) in my user's home directory on "gerrard". A new directory (instantclient_11_1) will be created and all the necessary files/directories will be under this single directory. This machine has no other Oracle software installed. Unzipping the files should result in the following:

[markwill@gerrard ~/instantclient_11_1]> find .
.
./libsqlplusic.so
./libclntsh.so.11.1
./ojdbc6.jar
./sqlplus
./adrci
./libnnz11.so
./SQLPLUS_README
./libociicus.so
./ojdbc5.jar
./sdk
./sdk/demo
./sdk/demo/occiobj.cpp
./sdk/demo/occidemo.sql
./sdk/demo/occiobj.typ
./sdk/demo/occidemod.sql
./sdk/demo/demo.mk
./sdk/demo/occidml.cpp
./sdk/demo/cdemo81.c
./sdk/SDK_README
./sdk/ott
./sdk/ottclasses.zip
./sdk/include
./sdk/include/orid.h
./sdk/include/occiAQ.h
./sdk/include/oratypes.h
./sdk/include/nzerror.h
./sdk/include/oci1.h
./sdk/include/ocikpr.h
./sdk/include/ocidef.h
./sdk/include/oci.h
./sdk/include/ort.h
./sdk/include/ocixmldb.h
./sdk/include/ocidfn.h
./sdk/include/odci.h
./sdk/include/ori.h
./sdk/include/occiCommon.h
./sdk/include/occi.h
./sdk/include/ociapr.h
./sdk/include/occiObjects.h
./sdk/include/occiControl.h
./sdk/include/xa.h
./sdk/include/ocidem.h
./sdk/include/oci8dp.h
./sdk/include/ociextp.h
./sdk/include/occiData.h
./sdk/include/ociap.h
./sdk/include/nzt.h
./sdk/include/orl.h
./sdk/include/oro.h
./libocci.so.11.1
./libocijdbc11.so
./genezi
./BASIC_LITE_README
./libsqlplus.so
./glogin.sql
[markwill@gerrard ~/instantclient_11_1]>


Create Links

To ensure proper operation of SQL*Plus and linking executables, two links should be created in the "instantclient_11_1" directory. The first is the Oracle Client Shared Library and the second is the OCCI Library:

ln -s ./libclntsh.so.11.1 ./libclntsh.so
ln -s ./libocci.so.11.1  ./libocci.so

After creating the links you should see the following when performing an "ls" on them:

[markwill@gerrard ~/instantclient_11_1]> ls -l libclntsh.so
lrwxrwxrwx  1 markwill markwill 19 Aug 15 19:17 libclntsh.so -> ./libclntsh.so.11.1

[markwill@gerrard ~/instantclient_11_1]> ls -l libocci.so
lrwxrwxrwx  1 markwill markwill 17 Aug 15 19:17 libocci.so -> ./libocci.so.11.1


Configure The Environment

To facilitate easily using the new installation I create a file I can source to set my environment correctly. I use the csh (well, tcsh really) as my primary shell and I created a file called "oic11.csh" in my home directory to setup the environment for me:

[markwill@gerrard ~]> cat oic11.csh
#
setenv ORACLE_BASE /home/markwill
setenv ORACLE_HOME ${ORACLE_BASE}/instantclient_11_1
setenv LD_LIBRARY_PATH ${ORACLE_HOME}
set path = (${ORACLE_HOME} /usr/local/bin /bin /usr/bin /usr/X11R6/bin ~/bin)

If you are using the bash shell, you may find something like the following helpful:

[markwill@gerrard ~]> cat oic11.env
export ORACLE_BASE=/home/markwill
export ORACLE_HOME=$ORACLE_BASE/instantclient_11_1
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$ORACLE_HOME:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:~/bin

I can then set my environment to use the new install as follows (in csh):

[markwill@gerrard ~]> source ./oic11.csh

This could be done as follows for bash:

[markwill@gerrard ~]> . ./oic11.env

Create a Simple Test Project

All the setup work is now complete and the environment is configured! If needed, you can use the following (very!) basic application as a simple test to verify things are working as expected. Again, this is a simple example only to verify things are setup correctly. It is not intended to be a complete template for "proper" code development, etc. Be sure to set the environment correctly!

I created a "Projects" directory under my home directory and then a directory called "Employees" under the "Projects" directory. I then used VIM to create the Employees.h and Employees.cpp files.

Here's the content of the Employees.h file on my system:

/*
* A simple OCCI test application
* This file contains the Employees class declaration
*/

#include <occi.h>
#include <iostream>
#include <iomanip>

using namespace oracle::occi;
using namespace std;

class Employees {
public:
  Employees();
  virtual ~Employees();

  void List();

private:
  Environment *env;
  Connection  *con;

  string user;
  string passwd;
  string db;
};

Here's the content of the Employees.cpp file on my system:

/*
* A simple OCCI test application
* This file contains the Employees class implementation
*/

#include "Employees.h"

using namespace std;
using namespace oracle::occi;

int main (void)
{
  /*
   * create an instance of the Employees class,
   * invoke the List member, delete the instance,
   * and prompt to continue...
   */

  Employees *pEmployees = new Employees();

  pEmployees->List();

  delete pEmployees;

  cout << "ENTER to continue...";

  cin.get();

  return 0;
}

Employees::Employees()
{
  /*
   * connect to the test database as the HR
   * sample user and use the EZCONNECT method
   * of specifying the connect string. Be sure
   * to adjust for your environment! The format
   * of the string is host:port/service_name

   */

  user = "hr";
  passwd = "hr";
  db = "oel01:1521/OEL11GR1.SAND";

  env = Environment::createEnvironment(Environment::DEFAULT);

  try
  {
    con = env->createConnection(user, passwd, db);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();

    exit(EXIT_FAILURE);
  }
}

Employees::~Employees()
{
  env->terminateConnection (con);

  Environment::terminateEnvironment (env);
}

void Employees::List()
{
  /*
   * simple test method to select data from
   * the employees table and display the results
   */

  Statement *stmt = NULL;
  ResultSet *rs = NULL;
  string sql = "select employee_id, first_name, last_name " \
               "from employees order by last_name, first_name";

  try
  {
    stmt = con->createStatement(sql);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();
  }

  if (stmt)
  {
    try
    {
      stmt->setPrefetchRowCount(32);

      rs = stmt->executeQuery();
    }
    catch (SQLException& ex)
    {
      cout << ex.getMessage();
    }

    if (rs)
    {
      cout << endl << setw(8) << left << "ID"
           << setw(22) << left << "FIRST NAME"
           << setw(27) << left << "LAST NAME"
           << endl;
      cout << setw(8) << left << "======"
           << setw(22) << left << "===================="
           << setw(27) << left << "========================="
           << endl;

      while (rs->next()) {
        cout << setw(8) << left << rs->getString(1)
             << setw(22) << left << (rs->isNull(2) ? "n/a" : rs->getString(2))
             << setw(27) << left << rs->getString(3)
             << endl;
      }

      cout << endl;

      stmt->closeResultSet(rs);
    }

    con->terminateStatement(stmt);
  }
}

To build the simple test I created a simplistic Makefile:

[markwill@gerrard Employees]> cat Makefile
Employees: Employees.cpp
  g++ -o Employees Employees.cpp \
  -I$(ORACLE_HOME)/sdk/include \
  -L$(ORACLE_HOME) -lclntsh -locci

debug: Employees.cpp
  g++ -ggdb3 -o Employees Employees.cpp \
  -I$(ORACLE_HOME)/sdk/include \
  -L$(ORACLE_HOME) -lclntsh -locci

clean:
  rm -f Employees

NOTE: The indented lines are tabs and not spaces in the Makefile

Whilst certainly not destined to win any awards for Makefile creativity it suffices for the purpose at hand.

I then built the application in debug mode by typing "make debug".

Executing the sample should result in output as follows:

[markwill@gerrard Employees]> ./Employees

ID      FIRST NAME            LAST NAME
======  ====================  =========================
174     Ellen                 Abel
166     Sundar                Ande
130     Mozhe                 Atkinson
105     David                 Austin
204     Hermann               Baer
116     Shelli                Baida
167     Amit                  Banda
172     Elizabeth             Bates

[ snip ]

120     Matthew               Weiss
200     Jennifer              Whalen
149     Eleni                 Zlotkey

ENTER to continue...

If you are new to using OCCI on Linux, perhaps the above will be helpful in getting started!

ODP.NET, VB, and "Conversion from type 'OracleDecimal' to type 'Integer' is not valid."

Mark A. Williams - Fri, 2009-08-14 22:18

It starts innocently enough: you have a PL/SQL procedure or function that returns a number (either as an OUT parameter or a return value respectively) to your VB client application. You know for a fact that the returned value will be less than the possible maximum value for the CLR type Int32 (represented as Integer in your VB code). It's dead easy and nothing can go wrong here, right? Except that from the title of this post you already know something is going to go unexpectedly. But let's not get too far ahead.

In order to understand what may (or may not) go wrong here, let's create a PL/SQL function that is guaranteed to return a valid Int32 value:

create or replace function getone return number
as
begin
  return 1;
end;
/

This function does nothing but return the value "1" (as a number) to the caller and that number is clearly an acceptable Int32 value – there's no trickery here.

We can use trusty old SQL*Plus to verify that it does what it says it does:

SQL> select getone from dual;

    GETONE
----------
         1

1 row selected.

SQL>

It sure does return the expected value, so let's create a simple VB project to test this out. I'm using ODP.NET version 10.2.0.4 for this experiment and here's the first version of the VB code:

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

Module Module1
  Sub Main()
    ' change connection string for your environment
    Dim constr As String = "User Id=hr;" & _
                           "Password=hr;" & _
                           "Data Source=oel11gr1;" & _
                           "Enlist=false;" & _
                           "Pooling=false"

    ' create and open connection
    Dim con As OracleConnection = New OracleConnection(constr)
    con.Open()

    ' create command and setup to call test function
    Dim cmd As OracleCommand = con.CreateCommand()
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "getone"

    ' create parameter for the test function return value
    Dim retval As OracleParameter = New OracleParameter()
    retval.Direction = ParameterDirection.ReturnValue
    retval.OracleDbType = OracleDbType.Int32

    ' add the parameter to the parameters collection
    cmd.Parameters.Add(retval)

    ' execute the function
    cmd.ExecuteNonQuery()

    ' get the return value in a local variable
    Dim my_one As Integer = retval.Value

    ' basic cleanup
    retval.Dispose()
    cmd.Dispose()
    con.Dispose()
  End Sub
End Module

NOTE: I added a reference to the ODP.NET assembly to the project.

After entering the above code I built the project and got the all-important "success" message:

[ snip ]
ConversionTest -> C:\My Projects\Test\VB\ConversionTest\bin\Debug\ConversionTest.exe
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

So, let's go ahead and run this in debug mode from the IDE and see what happens…

Oops. It stops on the "Dim my_one As Integer = retval.Value" source line and pops up a dialog indicating that "System.InvalidCastException was unhandled". Digging a bit deeper we see "Conversion from type 'OracleDecimal' to type 'Integer' is not valid."

What? The code compiled with no problem, so what does this mean? Quickly looking at the code again we see that we declared the parameter as "OracleDbType.Int32" which should be able to handle the function return value with no issues, and we also see "Integer" which is the VB flavor of Int32! But why are we seeing "OracleDecimal" in the output and why is that being converted to Integer? We didn't declare an OracleDecimal in our code.

One thing we might try is going to the project properties, clicking the Compile tab, and setting "Option strict:" to "On". OK, done. Now, let's recompile. Hmm, now the project does not compile due to the error: Option Strict On disallows implicit conversions from 'Object' to 'Integer'.

Yuck. There was an implicit conversion that wasn't reported during the first compile due to the default of "Option strict" being "Off". OK, so now we have determined that there is a conversion problem happening, but that doesn't really explain why. For that we can consult the ODP.NET documentation and in particular the section that discusses Parameter Binding. In summary, that part of the documentation details how setting the OracleDbType or the DbType property of an OracleParameter object determines the data type of the output parameter. As stated in the documentation, "ODP.NET allows applications to obtain an output parameter as either a .NET Framework type or an ODP.NET type."

Ahhh, so we got an OracleDecimal as the return value because we specified OracleDbType.Int32 as the parameter type. Since we want an Int32 (or Integer) as the return value we can change the code to set the DbType instead of the OracleDbType:

retval.DbType = DbType.Int32

Now recompile and everything will work great. Except that you may get the following error: Option Strict On disallows implicit conversions from 'Object' to 'Integer'.

If "Option strict" is left "On" you will receive the above message. This is because the OracleParameter.Value is returned as an Object. An easy way out is to set "Option strict" back to "Off" and the implicit conversion will be done behind the scenes and the code will work as expected albeit because of a hidden conversion. To take the explicit route you can change the declaration of "my_one" as follows:

Dim my_one As Integer = DirectCast(retval.Value, Int32)

Now you are getting the value back as an Int32/Integer (wrapped in an object) and explicitly (rather than implicitly) extracting the value via DirectCast as an Int32/Integer. With the implicit conversion the compiler will inject a call to a conversion function into the code and with the DirectCast method an "unbox" operation will take place.

Incidentally, the same sort of thing would need to happen with C#; however, the compiler would warn you of this during the initial compile run since it doesn't do the implicit conversion that VB would try if "Option strict" is "Off".

If you are having conversion problems in your code with "output" or "return values" keep in mind that setting OracleDbType vs. DbType will determine whether a .NET Framework type or an Oracle provider type is returned. Also, the current ODP.NET Beta (11.1.7.10) exposes a new property for the OracleParameter class: OracleDbTypeEx  This property allows you to bind values using the OracleDbType but will return values as .NET types.

Oracle JHeadstart 11.1.1 Technical Preview 2 Now Available

JHeadstart - Tue, 2009-08-11 23:17

Oracle JHeadstart 11.1.1 Technical Preview 2 is now available for download (build 11.1.1.0.46).
This release is compatible with JDeveloper 11 release 1 (11.1.1.1.0), as well as with the two JDeveloper Boxer releases (11.1.1.0.1 and 11.1.1.0.2).
Customers who own a JHeadstart supplement option license can download it from the Consulting Supplement Option portal.

An evaluation version of JHeadstart 11.1.1 is not yet available. We plan to make an evaluation version available in the fall of calendar year 2009.

In addition to many small enhancements, the following features have been added to the second technical preview:


  • Support for Stretching Layouts: JHeadstart now fully supports stretching options for ADF Faces Rich Client components.
    Through a new group-level property Enable Stretching, you can stretch the generate group content to fill the available space. Whether or not stretching
    will actually occur depends on some additional conditions that must be met. See the help description of this new group property in the application definition editor, and the
    JHeadstart Developer's guide for more info.

  • New Table Overflow Styles: The Table Overflow Style group property has two new allowable values: 'Right with Splitter' and 'Below with Splitter', which are
    useful settings when you want to generate multiple panes with stretching layouts that adjust based on the position of the splitter.

  • New Allowable Values for Same Page Display Position: The Same Page Display Position group property has two new allowable values: 'At the Right of Parent Group with Splitter'
    and 'Below Parent Group with Splitter', which are useful settings when you want to generate multiple panes with stretching group layouts that adjust
    based on the position of the splitter.
  • New Allowable Values for Region Container Layout: The Layout property of a region container has two new allowable values: 'Horizontal with Splitter'
    and 'Vertical with Splitter', which are useful settings when you want to generate multiple panes with stretching region layouts that adjust
    based on the position of the splitter.
  • Ability to Add Bindings to PageDefinition using Velocity Templates: It is now possible to add bindings and iterators to the page definition using
    custom velocity templates. Here are some examples:

    ${JHS.pageDefGenerator.addBinding($JHS.page,"myBinding","#CUSTOM_BINDING()")}
    ${JHS.pageDefGenerator.addExecutable($JHS.page,"myIterator","#CUSTOM_BINDING()")}
    ${JHS.pageDefGenerator.addParameter($JHS.page,"myParam","#CUSTOM_BINDING()")}


    The custom binding macro simply contains the XML you want to add to the page definition. This macro can be defined within the same
    velocity template.
  • Flex items: The 10.1.3 functionality of Flex Items (items added dynamically at runtime) is now re-implemented for release 11. The region that
    contains the flex items is now a reusable task flow, providing you with more flexibility and re-use of code.

  • File up/download: File upload, file download and display of images is now supported in release 11. It supports Intermedia (ORDSYS) column types, as well
    as BLOB column types

  • XML Reformatting: There is a new application-level property Reformat all XML Files which will reformat all generated XML files (except for the page definitions),
    giving you more readable and consistent files accross your application.

  • Upgrading from 10.1.3: When opening a 10.1.3 application definition file, JHeadstart will migrate this to release 11 upon your approval.

Click here for a list of features added in the first technical preview:

For a complete list of all existing features in JHeadstart 10.1.3, use this link.

Categories: Development

Understanding DBMS_STATS.SET_*_PREFS procedures

Oracle Optimizer Team - Tue, 2009-08-11 21:25
In previous Database releases you had to use the DBMS_STATS.SET_PARM procedure to change the default value for the parameters used by the DBMS_STATS.GATHER_*_STATS procedures. The scope of any changes that were made was all subsequent operations. In Oracle Database 11g, the DBMS_STATS.SET_PARM procedure has been deprecated and it has been replaced with a set of procedures that allow you to set a preference for each parameter at a table, schema, database, and Global level. These new procedures are called DBMS_STATS.SET_*_PREFS and offer a much finer granularity of control.

However there has been some confusion around which procedure you should use when and what the hierarchy is among these procedures. In this post we hope to clear up the confusion. Lets start by looking at the list of parameters you can change using the DBMS_STAT.SET_*_PREFS procedures.

  • AUTOSTATS_TARGET (SET_GLOBAL_PREFS only)
  • CASCADE
  • DEGREE
  • ESTIMATE_PERCENT
  • METHOD_OPT
  • NO_INVALIDATE
  • GRANULARITY
  • PUBLISH
  • INCREMENTAL
  • STALE_PERCENT

As mentioned above there are four DBMS_STATS.SET_*_PREFS procedures.

  1. SET_TABLE_PREFS

  2. SET_SCHEMA_PREFS

  3. SET_DATABASE_PREFS

  4. SET_GLOBAL_PREFS


The DBMS_STATS.SET_TABLE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for the specified table only.

The DBMS_STATS.SET_SCHEMA_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the existing objects in the specified schema. This procedure actually calls DBMS_STATS.SET_TABLE_PREFS for each of the tables in the specified schema. Since it uses DBMS_STATS.SET_TABLE_PREFS calling this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREF values for all parameters.

The DBMS_STATS.SET_DATABASE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the user defined schemas in the database. This procedure actually calls DBMS_STATS.SET_TABLE_PREFS for each of the tables in each of the user defined schemas. Since it uses DBMS_STATS.SET_TABLE_PREFS this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREF values for all parameters. It is also possible to include the Oracle owned schemas (sys, system, etc) by setting the ADD_SYS parameter to TRUE.

The DBMS_STATS.SET_GLOBAL_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for any object in the database that does not have an existing table preference. All parameters default to the global setting unless there is a table preference set or the parameter is explicitly set in the DBMS_STATS.GATHER_*_STATS command. Changes made by this procedure will affect any new objects created after it has been run as new objects will pick up the GLOBAL_PREF values for all parameters.

With GLOBAL_PREFS it is also possible to set a default value for one additional parameter, called AUTOSTAT_TARGET. This additional parameter controls what objects the automatic statistic gathering job (that runs in the nightly maintenance window) will look after. The possible values for this parameter are ALL,ORACLE, and AUTO. ALL means the automatic statistics gathering job will gather statistics on all objects in the database. ORACLE means that the automatic statistics gathering job will only gather statistics for Oracle owned schemas (sys, sytem, etc) Finally AUTO (the default) means Oracle will decide what objects to gather statistics on. Currently AUTO and ALL behave the same.

In summary, DBMS_STATS obeys the following hierarchy for parameter values, parameters values set in the DBMS_STAT.GATHER*_STATS command over rules everything. If the parameter has not been set in the command we check for a table level preference. If there is no table preference set we use the global preference.

Understanding DBMS_STATS.SET_*_PREFS procedures

Inside the Oracle Optimizer - Tue, 2009-08-11 21:25
In previous Database releases you had to use the DBMS_STATS.SET_PARM procedure to change the default value for the parameters used by the DBMS_STATS.GATHER_*_STATS procedures. The scope of any changes that were made was all subsequent operations. In Oracle Database 11g, the DBMS_STATS.SET_PARM procedure has been deprecated and it has been replaced with a set of procedures that allow you to set a preference for each parameter at a table, schema, database, and Global level. These new procedures are called DBMS_STATS.SET_*_PREFS and offer a much finer granularity of control.

However there has been some confusion around which procedure you should use when and what the hierarchy is among these procedures. In this post we hope to clear up the confusion. Lets start by looking at the list of parameters you can change using the DBMS_STAT.SET_*_PREFS procedures.

  • AUTOSTATS_TARGET (SET_GLOBAL_PREFS only)
  • CASCADE
  • DEGREE
  • ESTIMATE_PERCENT
  • METHOD_OPT
  • NO_INVALIDATE
  • GRANULARITY
  • PUBLISH
  • INCREMENTAL
  • STALE_PERCENT

As mentioned above there are four DBMS_STATS.SET_*_PREFS procedures.

  1. SET_TABLE_PREFS

  2. SET_SCHEMA_PREFS

  3. SET_DATABASE_PREFS

  4. SET_GLOBAL_PREFS


The DBMS_STATS.SET_TABLE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for the specified table only.

The DBMS_STATS.SET_SCHEMA_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the existing objects in the specified schema. This procedure actually calls DBMS_STATS.SET_TABLE_PREFS for each of the tables in the specified schema. Since it uses DBMS_STATS.SET_TABLE_PREFS calling this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREF values for all parameters.

The DBMS_STATS.SET_DATABASE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the user defined schemas in the database. This procedure actually calls DBMS_STATS.SET_TABLE_PREFS for each of the tables in each of the user defined schemas. Since it uses DBMS_STATS.SET_TABLE_PREFS this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREF values for all parameters. It is also possible to include the Oracle owned schemas (sys, system, etc) by setting the ADD_SYS parameter to TRUE.

The DBMS_STATS.SET_GLOBAL_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for any object in the database that does not have an existing table preference. All parameters default to the global setting unless there is a table preference set or the parameter is explicitly set in the DBMS_STATS.GATHER_*_STATS command. Changes made by this procedure will affect any new objects created after it has been run as new objects will pick up the GLOBAL_PREF values for all parameters.

With GLOBAL_PREFS it is also possible to set a default value for one additional parameter, called AUTOSTAT_TARGET. This additional parameter controls what objects the automatic statistic gathering job (that runs in the nightly maintenance window) will look after. The possible values for this parameter are ALL,ORACLE, and AUTO. ALL means the automatic statistics gathering job will gather statistics on all objects in the database. ORACLE means that the automatic statistics gathering job will only gather statistics for Oracle owned schemas (sys, sytem, etc) Finally AUTO (the default) means Oracle will decide what objects to gather statistics on. Currently AUTO and ALL behave the same.

In summary, DBMS_STATS obeys the following hierarchy for parameter values, parameters values set in the DBMS_STAT.GATHER*_STATS command over rules everything. If the parameter has not been set in the command we check for a table level preference. If there is no table preference set we use the global preference.
Categories: DBA Blogs, Development

How we faster the process of converting a non-ASM single-instance database to RAC database with ASM using RCONFIG tool?

Sabdar Syed - Sat, 2009-08-08 16:07
I have been given with a challenging task to convert one of our critical production databases, which is of 1 TB (Terabyte) in size, to Oracle 10g RAC with ASM storage option. Even though, there are many methods and tools available to perform this activity, I have preferred to use the RCONFIG tool.

We prepared an input XML file required for RCONFIG tool, and run the RCONFIG utility as follows:

$ cd /oracle/ora102/db_1/assistants/rconfig/sampleXMLs
$ rconfig ConverToRAC.xml
When we start the RCONFIG tool to convert the database to RAC, the RCONFIG tool initially moves all the non-ASM database files to ASM disk files, for this RCONFIG tool internally invokes RMAN utility to backup the target database to the ASM disk groups, eventually the database is converted to RAC using RCONFIG.

The conversion took almost 9 hours to complete the process, because during the conversion RMAN used only one channel per data file to backup to ASM disks. There was no chance of improving the RMAN copy process by allocating more channels in the input XML file, and also Oracle doesn’t recommend doing other changes in the input XML file.

One thing was observed during the RMAN copy that RMAN is using target database control file instead of recovery catalog, and also using the RMAN default preconfigured settings for that database.

To know the RMAN default preconfigured settings for the database:

$ export ORACLE_SID=MYPROD
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 5 10:21:05 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: MYPROD (DBID=1131234567)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default
Here we see that the PARALLELISM is 1 (default), that’s why the RMAN using only one channel during backing up the non-ASM datafiles to ASM Disk Groups, and were taking 9 hours to complete the backup.

We have changed the PRALLELISM count to 6 (it depends upon number of CPUs you have in the server).

Solution:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 6;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default
After changing the PARALLELISM count to 6, the RMAN has allocated 6 channels and the conversion process has improved greatly and reduced the downtime drastically to 4 Hours 30 minutes.

Following is the extract of rconfig.log file, this file is located under:

$ORACLE_HOME/db_1/cfgtoolslogs/rconfig
............................................................................
............................................................................
............................................................................
[17:17:16:43] Log RMAN Output=RMAN> backup as copy database to destination '+DATA_DG';
[17:17:16:53] Log RMAN Output=Starting backup at 04-AUG-09
[17:17:16:258] Log RMAN Output=using target database control file instead of recovery catalog
[17:17:16:694] Log RMAN Output=allocated channel: ORA_DISK_1
[17:17:16:698] Log RMAN Output=channel ORA_DISK_1: sid=866 devtype=DISK
[17:17:17:9] Log RMAN Output=allocated channel: ORA_DISK_2
[17:17:17:13] Log RMAN Output=channel ORA_DISK_2: sid=865 devtype=DISK
[17:17:17:324] Log RMAN Output=allocated channel: ORA_DISK_3
[17:17:17:327] Log RMAN Output=channel ORA_DISK_3: sid=864 devtype=DISK
[17:17:17:637] Log RMAN Output=allocated channel: ORA_DISK_4
[17:17:17:641] Log RMAN Output=channel ORA_DISK_4: sid=863 devtype=DISK
[17:17:17:967] Log RMAN Output=allocated channel: ORA_DISK_5
[17:17:17:971] Log RMAN Output=channel ORA_DISK_5: sid=862 devtype=DISK
[17:17:18:288] Log RMAN Output=allocated channel: ORA_DISK_6
[17:17:18:293] Log RMAN Output=channel ORA_DISK_6: sid=861 devtype=DISK
[17:17:20:416] Log RMAN Output=channel ORA_DISK_1: starting datafile copy
[17:17:20:427] Log RMAN Output=input datafile fno=00053 name=/oradata/MYPROD/users_01.dbf
[17:17:20:532] Log RMAN Output=channel ORA_DISK_2: starting datafile copy
[17:17:20:544] Log RMAN Output=input datafile fno=00021 name=/oradata/MYPROD/ users_02.dbf
[17:17:20:680] Log RMAN Output=channel ORA_DISK_3: starting datafile copy
[17:17:20:694] Log RMAN Output=input datafile fno=00022 name=/oradata/MYPROD/ users_03.dbf
[17:17:20:786] Log RMAN Output=channel ORA_DISK_4: starting datafile copy
[17:17:20:800] Log RMAN Output=input datafile fno=00023 name=/oradata/MYPROD/ users_04.dbf
[17:17:20:855] Log RMAN Output=channel ORA_DISK_5: starting datafile copy
[17:17:20:868] Log RMAN Output=input datafile fno=00024 name=/oradata/MYPROD/ users_05.dbf
[17:17:20:920] Log RMAN Output=channel ORA_DISK_6: starting datafile copy
[17:17:20:930] Log RMAN Output=input datafile fno=00011 name=/oradata/MYPROD/ users_06.dbf
............................................................................
............................................................................
............................................................................
[21:29:5:518] Log RMAN Output=Finished backup at 04-AUG-09
............................................................................
............................................................................
............................................................................

[21:39:10:723] [NetConfig.startListenerResources:5] started Listeners associated with database MYPROD
[21:39:10:723] [Step.execute:255] STEP Result=Operation Succeeded
[21:39:10:724] [Step.execute:284] Returning result:Operation Succeeded
[21:39:10:724] [RConfigEngine.execute:68] bAsyncJob=false
[21:39:10:725] [RConfigEngine.execute:77] Result= < version="1.1">


&ltConvertToRAC&gt
&ltConvert&gt
&ltResponse&gt
&ltResult code="0" &gt
Operation Succeeded
&lt/Result&gt
&lt/Response&gt
&ltReturnValue type="object"&gt
&ltOracle_Home&gt
/oracle/ora102/db_1
&lt/Oracle_Home&gt
&ltSIDList&gt
&ltSID&gtMYPROD1&lt\SID&gt
&ltSID&gtMYPROD2&lt\SID&gt
&lt\SIDList&gt &lt/ReturnValue&gt
&lt/Convert&gt
&lt/ConvertToRAC&gt&lt/RConfig&gt

Note: For the sake of look and feel format, the above output has been trimmed neatly. You can also observer that 6 channels were being allocated, timings of backup start and end, and the success code end of the rconfig.log file.

References:

To know more about RCONFIG tool and other Metalink references on it, please take a look at the below blog post written by Mr. Syed Jaffar Hussain.

http://jaffardba.blogspot.com/2008/09/my-experience-of-converting-cross.html

Oracle 10g R2 Documentation information on RCONFIG:

http://download.oracle.com/docs/cd/B19306_01/install.102/b14205/cvrt2rac.htm#BABBAAEH

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

The Humble PL/SQL Dot

Tahiti Views - Sat, 2009-08-08 14:20
Like many other languages, PL/SQL has its own "dot notation". If we assume that most people can intuit or easily look up things like the syntax for '''IF/THEN/ELSIF''', that means that first-timer users might quickly run into dots and want to understand their significance.The authoritative docs on the dots is in the Oracle Database 11g PL/SQL Language Reference, in particular Appendix B, How PL/John Russellhttp://www.blogger.com/profile/17089970732272081637noreply@blogger.com2

Detecting Corrupt Data Blocks

Jared Still - Thu, 2009-08-06 16:50
Or more accurately, how not to detect corrupt data blocks.

This thread on Oracle-L is regarding lost writes on a database.

One suggestion was made to use the exp utility to export the database, thereby determining if there are corrupt blocks in the database due to disk failure. I didn't give it much thought at first, but fellow Oak Table member Mark Farnham got me thinking about it.

Using exp to detect corrupt blocks, or rather, the absence of corrupt blocks may work, but then again, it may not. It is entirely possible to do a  full table scan on a table successfully, as would happen during an export, even though the blocks on disk have been corrupted.

This can be demonstrated by building a table, ensuring the contents are cached, then destroying the data in the data file, followed by a successful export of the table.

Granted, there are a lot of mitigating factors that could be taken into consideration as to whether or not this would happen in a production database. That's not the point: the point is that it could happen, so exp is not a reliable indicator of the state of the data files on disk.

This test was performed on Oracle 10.2.0.4 EE on RH Linux ES 4. Both are 32 bit.

First create a test tablespace:

create tablespace lost_write datafile '/u01/oradata/dv11/lost_write.dbf' size 1m
extent management local
uniform size 64k
/



Next the table LOST_WRITE is created in the tablespace of the same name. This will be used to test the assertion that a successful export of the table can be done even though the data on disk is corrupt.

create table lost_write
cache
tablespace lost_write
as
select * from dba_objects
where rownum <= 1000
/

begin
dbms_stats.gather_table_stats(user,'LOST_WRITE');
end;
/

select tablespace_name, blocks, bytes
from user_segments
where segment_name = 'LOST_WRITE'
/


TABLESPACE_NAME BLOCKS BYTES
------------------------------ ---------- ----------
LOST_WRITE 16 131072

1 row selected.



Next, do a full table scan and verify that the blocks are cached:

select * from lost_write;

Verify in cache:
select file#,block#,class#, status
from v$bh where ts# = (select ts# from sys.ts$ where name = 'LOST_WRITE')
order by block#
/

FILE# BLOCK# CLASS# STATUS
---------- ---------- ---------- -------
40 2 13 xcur
40 3 12 xcur
40 9 8 xcur
40 10 9 xcur
40 11 4 xcur
40 12 1 xcur
40 13 1 xcur
40 14 1 xcur
40 15 1 xcur
40 16 1 xcur
40 17 1 xcur
40 18 1 xcur
40 19 1 xcur
40 20 1 xcur
40 21 1 xcur
40 22 1 xcur
40 23 1 xcur




Now swap the bytes in the file, skipping the first 2 oracle blocks
Caveat: I don't know if that was the correct # of blocks, and I didn't spend any time trying to find out
Also, I belatedly saw that count probably should have been 22 rather than 16, but the results still served the purpose of corrupting the datafile, as we shall see in a bit.

What this dd command is doing is using the same file for both input and output, and rewriting blocks 3-18, swapping each pair of bytes.

dd if=/u01/oradata/dv11/lost_write.dbf of=/u01/oradata/dv11/lost_write.dbf bs=8129 skip=2 count=16 conv=swab,notrunc



The effect is demonstrated by this simple test:

jkstill-19 > echo hello | dd
hello
0+1 records in
0+1 records out
[ /home/jkstill ]

jkstill-19 > echo hello | dd conv=swab
ehll
o0+1 records in
0+1 records out


Now we can attempt the export:

exp tables=\(jkstill.lost_write\) ...

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table LOST_WRITE 1000 rows exported
Export terminated successfully without warnings.
19 > echo hello | dd
hello
0+1 records in
0+1 records out
[ /home/jkstill ]

jkstill-19 > echo hello | dd conv=swab
ehll
o0+1 records in
0+1 records out


So, even though the data on disk has been corrupted, the export succeeded. That is due to the table being created with the CACHE option, and all the blocks being cached at the time of export. It may not be necessary to use the CACHE option, but I used it to ensure the test would succeed.

Now let's see what happens when trying to scan the table again. First the NOCACHE option will be set on the table, then a checkpoint.

10:42:45 dv11 SQL> alter table lost_write nocache;

10:43:02 dv11 SQL> alter system checkpoint;

Now try to scan the table again:

10:43:14 ordevdb01.radisys.com - js001292@dv11 SQL> /
select * from lost_write
*
ERROR at line 1:
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: '/u01/oradata/dv11/lost_write.dbf'



A corollary conclusion can drawn from this example.

If you do discover bad data blocks, you just might be able to do an export of the tables that are in the affected region before doing any recovery. This might be a belt and suspenders approach, but DBA's are not generally being known for taking unnecessary chances when possible data loss is on the line.
Categories: DBA Blogs

Empezando con OCCI

Mark A. Williams - Tue, 2009-07-28 17:45

Luis Neri was kind enough to translate my "Getting Started with OCCI (Windows Version)" post into Spanish. Below is his translation of the original post. Thanks very much to Luis for this. However, please note that I do not speak Spanish, so I won't be able to respond to any comments in that language.

- Mark


 


 

La Oracle C++ Call Interface, también conocida como OCCI, es una API construida sobre otras API's de bajo nivel de Oracle. Uno de los objetivos de OCCI es ofrecer a los programadores de C++ una forma de acceso fácil a las bases de datos de Oracle en una forma similar a la que tienen los programadores de Java con "Java Database Connectivity (JDBC) ". Este documento trata de dar una vista rápida para empezar con esta tecnología, la cual puede ser incorporada a los desarrollos y aplicaciones GIS del SIRAN; si se desea, existe más información en la documentación en línea de Oracle.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm

Este "Empezando con OCCI" tiene el objeto de dar un método para la puesta del ambiente el cual usa OCCI en los desarrollos con C++ bajo Windows para acceder a las bases de datos de Oracle.

El ambiente

El ambiente en que se realizarón las pruebas para la puesta en funcionamiento de esta tecnología es el que abajo se detalla, tenga en cuenta que a este ambiente se pueden hacer pequeñas adaptaciones para que funcione en el suyo en particular.

  • Oracle Database Client: Oracle 11.0.2
  • Oracle Database: Oracle 10.2
  • Development Machine: Usser3 Geoware, Windows Vista Home Premium 32 bits
  • Development IDE: Microsoft Visual C++ 2008 Profesional (Windows SDK also installed) SP1
  • Oracle Client: Oracle Instant Client with OCCI

Información importante

Uno de las características más importantes al trabajar con OCCI es que se debe asegurar que todos los componentes del ambiente de desarrollo y de runtime están soportados en las combinaciones y tener las versiones correctas. Se hace un fuerte énfasis en esto, si usted no observa con atención esta recomendación seguramente encontrara problemas. Para lograr esto por favor encuentre las combinaciones correctas de las versiones en la pagina de Oracle Technology Network (OTN).

Descargue los componentes correctos

Al momento de escribir este documento se encontraron para el ambiente descrito arriba los siguientes componentes

  • OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit])
  • Instant Client Version 11.1.0.6

Estos componentes se deben descargar en la máquina de desarrollo. Yo lo hice en el escritorio.

  • OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit]) - occivc9win32_111060.zip
  • Instant Client Package - Basic: instantclient-basic-win32-11.1.0.6.0.zip
  • Instant Client Package - SDK: instantclient-sdk-win32-11.1.0.6.0.zip
  • Instant Client Package - SQL*Plus: instantclient-sqlplus-win32-11.1.0.6.0.zip  (Opcional, pero se recomienda instalarlo)

Instalación de Instant Client

La instalación es tan simple como descomprimir el archivo, descomprímalo sobre C:\ , el resultado será una carpeta C:\instantclient_11_1 con las subcarpetas "sdk", "vc8" y "vc7", las cuales no serán utilizadas en el desarrollo sobre nuestro ambiente.


 

Instalación del OCCI Package

Al igual que la instalación de Instant Client, los paquetes Occi pueden ser descomprimidos, pero sin embargo en vez de crear un directorio sobre c:\, los descomprimí sobre el escritorio, una vez descomprimidos, me desvié un poco de lo quel archivo occivc9_111060_readme.txt dice, y realice los siguiente:

Cree un directorio "vc9" dentro del directorio "sdk" como sigue:

C:\instantclient_11_1\sdk\lib\msvc\vc9

Cree una carpeta "vc9" dentro de "instantclient_11_1" como sigue:

C:\instantclient_11_1\vc9

Borre el archivo oraocci11.dll y oraocci11.sym del directorio C:\instantclient_11_1. Estos archives no están hechos para construir o compilar con Visual Studio 2008 y como se dijo anteriormente es importante que se empaten las versiones.

Se extrajeron los archivos OCCI en la carpeta del escritorio moviendo los dos archivos a la carpeta C:\instantclient_11_1\sdk\lib\msvc\vc9 antes creada:

  • oraocci11.lib
  • oraocci11d.lib

de la misma carpeta en el escritorio, se mueven los siguientes archivos a la carpeta C:\instantclient_11_1\vc9 antes creada:

  • oraocci11.dll
  • oraocci11.dll.manifest
  • oraocci11d.dll
  • oraocci11d.dll.manifest


 

Finalmente se borra el archivo oraocci11.lib de:

C:\instantclient_11_1\sdk\lib\msvc

Otra vez este archivo no es compatible con nuestro ambiente.

Despues de realizar estos pasos, los archivos .lib deben estar bajo la carpeta C:\instantclient_11_1\sdk\lib\msvc y los archivos .dll y .manifest deben estar bajo la carpeta C:\instantclient_11_1. Esto pasos parecen trabajo extra, pero resultan de una separación completa de las varias versiones OCCI y lo hace más fácil y explicito.

Para especificar que versión de librerías OCCI usar, añada estas carpetas al path del sistema. Estas dos carpetas deben estar al inicio del path:

C:\instantclient_11_1\vc9;C:\instantclient_11_1;{y lo demás del path…}

Configuración de Visual Studio

El ambiente Windows ha sido configurado para el uso de los nuevos paquetes OCCI y Instan Client (los adecuados para nuestro ambiente), pero antes de empezar un desarrollo en Visual Studio, es necesario establecer algunas opciones. Sin estas opciones Visual Studio es incapaz de encontrar los archivos correctos y construir aplicaciones. Hay dos opciones las cuales necesitan ser especificadas:

  • Include files – permite a Visual Studio encontrar los header files para OCCI
  • Library files – permite a Visual Studio encontrar los library files para OCCI

UsandoVisual C++ 2008, las rutas de menu donde se especifican estas opciones son:

  • Tools –> Options… expanda el nodo "Projects and Solutions", seleccione "VC++ Directories", bajo "Show directories for:" seleccione "Include files", doble-click bajo la ultima entrada para abrir una nueva pantalla e introduzca la ruta "C:\instantclient_11_1\sdk\include" presione enter
  • Bajo "Show directories for:" seleccione"Library files", doble-click bajo la ultima entrada para abrir una nueva pantalla e introduzca la ruta "C:\instantclient_11_1\sdk\lib\msvc\vc9" and presione enter
  • Presione OK para guardar las opciones.

Cree un proyecto simple de prueba

Ya que se ha hecho todo la puesta en funcionamiento el ambiente está configurado!, Utilice la siguiente proyecto como prueba para verificar que todo funciona como se espera. Otra vez, este es un simple ejemplo para verificar que las cosas funcionan correctamente. No es un templeate de desarrollo.

Cree en Visual C++ 2008 un proyecto seleccionando File –> New –> Project… del menú principal, seleccione "Win32" como tipo de proyecto, seleccione "Win32 Console Application", dele un nombre al proyecto (yo usé prueba_occi), seleccione un carpeta donde guardar deseleccione "Create directory for solution", y presione OK.

Presione Next en el Wizard, deseleccione Precompiled header, presione Empty project, y presione Finish.

En el explorador de la solucion, click-izquierdo en Header Files, seleccione Add, seleccione New Item…

En Add New Item, seleccione Header File (.h), introduzca Employees.h (o cualquier otro nombre) en nombre y presione Add.

/*
* A simple OCCI test application
* This file contains the Employees class declaration
*/

#include <occi.h>
#include <iostream>
#include <iomanip>

using namespace oracle::occi;
using namespace std;

class Employees {
public:
  Employees();
  virtual ~Employees();

  void List();

private:
  Environment *env;
  Connection  *con;

  string user;
  string passwd;
  string db;
};

en Solution Explorer, click-izquierdo en Source Files, seleccione Add, seleccione New Item…

en Add New Item, seleccione C++ File (.cpp), introduzca Employees.cpp ((o cualquier otro nombre) en nombre y presione Add.

Este es el contenido de mi archivo en el sistema:

/*
* A simple OCCI test application
* This file contains the Employees class implementation
*/

#include "Employees.h"

using namespace std;
using namespace oracle::occi;

int main (void)
{
  /*
   * create an instance of the Employees class,
   * invoke the List member, delete the instance,
   * and prompt to continue...
   */

  Employees *pEmployees = new Employees();

  pEmployees->List();

  delete pEmployees;

  cout << "ENTER to continue...";

  cin.get();

  return 0;
}

Employees::Employees()
{
  /*
   * connect to the test database as the HR
   * sample user and use the EZCONNECT method
   * of specifying the connect string. Be sure
   * to adjust for your environment! The format
   * of the string is host:port/service_name
   */

  user = "hr";
  passwd = "hr";
  db = "oel01:1521/OEL11GR1.SAND";

  env = Environment::createEnvironment(Environment::DEFAULT);

  try
  {
    con = env->createConnection(user, passwd, db);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();

    exit(EXIT_FAILURE);
  }
}

Employees::~Employees()
{
  env->terminateConnection (con);

  Environment::terminateEnvironment (env);
}

void Employees::List()
{
  /*
   * simple test method to select data from
   * the employees table and display the results
   */

  Statement *stmt = NULL;
  ResultSet *rs = NULL;
  string sql = "select employee_id, first_name, last_name " \
               "from employees order by last_name, first_name";

  try
  {
    stmt = con->createStatement(sql);
  }
  catch (SQLException& ex)
  {
    cout << ex.getMessage();
  }

  if (stmt)
  {
    try
    {
      stmt->setPrefetchRowCount(32);

      rs = stmt->executeQuery();
    }
    catch (SQLException& ex)
    {
      cout << ex.getMessage();
    }

    if (rs)
    {
      cout << endl << setw(8) << left << "ID"
           << setw(22) << left << "FIRST NAME"
           << setw(27) << left << "LAST NAME"
           << endl;
      cout << setw(8) << left << "======"
           << setw(22) << left << "===================="
           << setw(27) << left << "========================="
           << endl;

      while (rs->next()) {
        cout << setw(8) << left << rs->getString(1)
             << setw(22) << left << (rs->isNull(2) ? "n/a" : rs->getString(2))
             << setw(27) << left << rs->getString(3)
             << endl;
      }

      cout << endl;

      stmt->closeResultSet(rs);
    }

    con->terminateStatement(stmt);
  }
}


 

Antes de construir el ejemplo (build), se necesita añadir la librería OCCI a la lista de entradas del linker:

Seleccione Project –> prueba_occi Properties... del menu (Sustituya el nombre por el proio si es necesario)

Expanda el nodo Configuration Properties, expanda el nodo Linker, seleccione Input item, introduzca "oraocci11d.lib" para debug build o "oraocci11.lib" para release build.

Seleccione Build –> Build Solution del menú para construir la solución. Si todo está puesto correctamente no debería haber errores. Si existen error busque donde pueden estar y corrija. La pantalla de ejecución seria como esta:

ID      FIRST NAME            LAST NAME
======  ====================  =========================
174     Ellen                 Abel
166     Sundar                Ande
130     Mozhe                 Atkinson
105     David                 Austin
204     Hermann               Baer
116     Shelli                Baida
167     Amit                  Banda
172     Elizabeth             Bates

[ snip ]

120     Matthew               Weiss
200     Jennifer              Whalen
149     Eleni                 Zlotkey

ENTER to continue...

Si eres nuevo en el uso de OCCI sobre Windows con Visual Studio 2008, quizá el ejemplo de arriba puede ser de ayuda al inicio.

Joe's Blog: 15 Mintues of Fame

Joe Fuda - Sat, 2009-07-25 03:00

They say everyone gets at least 15 minutes of fame in their lifetime. Here's my total to-date.

1 minute (Middle School): my picture and some artwork appeared in The Toronto Star after I won their weekly cartoon contest for kids

30 seconds (High School): I was pictured in The Etobicoke Guardian performing a welding demonstration at a local shopping mall (this doesn't count for a full minute because a welding mask covered my face in the picture)

1 minute (University): I was pictured in The Toronto Star again, this time they caught me with my arm dyed purple, pants rolled up, and wearing a yellow hard hat as I waded through a fountain in front of Toronto City Hall during a University of Toronto Engineering hazing ritual

Yesterday a couple of minutes were added to that total when I was featured in Oracle's Innovation Showcase. As part of our 100-day countdown to Oracle OpenWorld Oracle is posting interviews with 100 of its top innovators. I was "Innovator of the Day" this past Friday, though I'm still listed there today too for some reason. I guess if you're lucky enough to be picked on a Friday then you become "Innovator of the Weekend" by default. You can find the full interview at this link, where it will reside even after my visage fades from the spotlight of the main showcase page.

So let's see, that leaves me with 10 minutes and 30 seconds of future fame left. I wonder what The Toronto Star will catch me doing next?


...

Will the Optimizer Team be at Oracle Open World 2009?

Oracle Optimizer Team - Thu, 2009-07-23 20:18
With only two and a half months to go until Oracle Open World in San Francisco, October 11-15th, we have gotten several requests asking if we plan to present any session at the conference.

We have two session and a demo station in the Database campground at this year's show. We will give a technical presentation on What to Expect from the Oracle Optimizer When Upgrading to Oracle Database 11g and the Oracle Optimizer Roundtable.

The technical session, which is on Tuesday Oct 13 at 2:30 pm, gives step by step instructions and detailed examples of how to use the new 11g features to ensure your upgrade goes smoothly and without any SQL plan regressions.

The roundtable, which is on Thursday Oct. 15th at 10:30 am, will give you a first hand opportunity to pose you burning Optimizer and statistics questions directly to a panel of our leading Optimizer developers. In fact if you plan to attend the roundtable and already know what questions you would like to ask, then please send them to us via email and we will be sure to include them. Other wise, you can hand in your questions at our demo station at any stage during the week, or as you enter the actual session. Just be sure to write your questions in clear block capitals!

We look forward to seeing you all at Oracle Open World.

Will the Optimizer Team be at Oracle Open World 2009?

Inside the Oracle Optimizer - Thu, 2009-07-23 20:18
With only two and a half months to go until Oracle Open World in San Francisco, October 11-15th, we have gotten several requests asking if we plan to present any session at the conference.

We have two session and a demo station in the Database campground at this year's show. We will give a technical presentation on What to Expect from the Oracle Optimizer When Upgrading to Oracle Database 11g and the Oracle Optimizer Roundtable.

The technical session, which is on Tuesday Oct 13 at 2:30 pm, gives step by step instructions and detailed examples of how to use the new 11g features to ensure your upgrade goes smoothly and without any SQL plan regressions.

The roundtable, which is on Thursday Oct. 15th at 10:30 am, will give you a first hand opportunity to pose you burning Optimizer and statistics questions directly to a panel of our leading Optimizer developers. In fact if you plan to attend the roundtable and already know what questions you would like to ask, then please send them to us via email and we will be sure to include them. Other wise, you can hand in your questions at our demo station at any stage during the week, or as you enter the actual session. Just be sure to write your questions in clear block capitals!

We look forward to seeing you all at Oracle Open World.

Categories: DBA Blogs, Development

Pages

Subscribe to Oracle FAQ aggregator