Feed aggregator

Does ODP.NET Send Data to the Server for Out PL/SQL Parameters?

Mark A. Williams - Thu, 2008-10-16 19:31

I was recently discussing (and by that I mean trading emails) with a friend about an interesting behavior observed when using PL/SQL "out" VARCHAR2 variables with ODP.NET - in this case it looks like ODP.NET is sending data from the client and to the server for a parameter that is defined as "out" in a PL/SQL procedure. This seems like strange behavior and I don't have a good answer as to why data would be sent from the client to the server for a parameter declared as an "out" parameter.

In any case here's a quick and dirty sample that illustrates this (on my test system anyway):

PL/SQL Procedure

create or replace procedure ptest (p_vc out varchar2) as
begin
  p_vc := rpad ('a', 31999, 'a');
end;
/

C# Sample Console

using System;
using System.Data;
using System.Data.OracleClient;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace Miscellaneous {
  class Program {
    static void Main(string[] args) {
      string constr = "User Id=markwill;" +
                      "Password=oracle;" +
                      "Data Source=orademo;" +
                      "Pooling=false;" +
                      "Enlist=false";

      TestODP(constr);

      TestMS(constr);

      Console.WriteLine();
      Console.Write("ENTER to continue...");
      Console.ReadLine();
    }

    static void TestODP(string constr) {
      Oracle.DataAccess.Client.OracleConnection con = new Oracle.DataAccess.Client.OracleConnection(constr);
      con.Open();

      Oracle.DataAccess.Client.OracleCommand cmd = con.CreateCommand();

      cmd.CommandText = "alter session set events '10046 trace name context forever, level 12'";
      cmd.ExecuteNonQuery();

      cmd.CommandText = "ptest";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.BindByName = true;

      Oracle.DataAccess.Client.OracleParameter p_vc = new Oracle.DataAccess.Client.OracleParameter();
      p_vc.ParameterName = "p_vc";
      p_vc.OracleDbType = OracleDbType.Varchar2;
      p_vc.Size = 32000;
      p_vc.Direction = ParameterDirection.Output;

      cmd.Parameters.Add(p_vc);

      cmd.ExecuteNonQuery();

      Console.WriteLine("p_vc = {0}", p_vc.Value);

      p_vc.Dispose();
      cmd.Dispose();
      con.Dispose();
    }

    static void TestMS(string constr) {
      System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(constr);
      con.Open();

      System.Data.OracleClient.OracleCommand cmd = con.CreateCommand();

      cmd.CommandText = "alter session set events '10046 trace name context forever, level 12'";
      cmd.ExecuteNonQuery();

      cmd.CommandText = "ptest";
      cmd.CommandType = CommandType.StoredProcedure;

      System.Data.OracleClient.OracleParameter p_vc = new System.Data.OracleClient.OracleParameter();
      p_vc.ParameterName = "p_vc";
      p_vc.OracleType = OracleType.LongVarChar;
      p_vc.Size = 32000;
      p_vc.Direction = ParameterDirection.Output;

      cmd.Parameters.Add(p_vc);

      cmd.ExecuteNonQuery();

      Console.WriteLine("p_vc = {0}", p_vc.Value);

      cmd.Dispose();
      con.Dispose();
    }
  }
}

This is pretty straight-forward and simply opens a connection, enables Extended SQL Tracing via setting event 10046, creates a parameter for the output value, and calls the stored procedure.

Looking at the respective trace files I find this for the ODP.NET test:

PARSING IN CURSOR #4 len=28 dep=0 uid=88 oct=47 lid=88 tim=9306513611 hv=1721325005 ad='7ff2eca8c38' sqlid='dk08s2dm9kpfd'
Begin ptest(p_vc=>:v0); End;
END OF STMT
PARSE #4:c=0,e=798,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9306513607
WAIT #4: nam='SQL*Net more data from client' ela= 48 driver id=1413697536 #bytes=44 p3=0 obj#=-1 tim=9306513786
WAIT #4: nam='SQL*Net more data from client' ela= 7 driver id=1413697536 #bytes=24 p3=0 obj#=-1 tim=9306513827
WAIT #4: nam='SQL*Net more data from client' ela= 171 driver id=1413697536 #bytes=4 p3=0 obj#=-1 tim=9306514027
WAIT #4: nam='SQL*Net more data from client' ela= 28 driver id=1413697536 #bytes=49 p3=0 obj#=-1 tim=9306514085
BINDS #4:
Bind#0
  oacdty=01 mxl=32767(32000) mxlc=32000 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=178 siz=32767 off=0
  kxsbbbfp=1c3d8000  bln=32767  avl=32000  flg=05
  value="Ä6¿¿"...
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306514373
WAIT #4: nam='SQL*Net more data to client' ela= 49 driver id=1413697536 #bytes=8216 p3=0 obj#=-1 tim=9306514456
WAIT #4: nam='SQL*Net more data to client' ela= 23 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306514507
WAIT #4: nam='SQL*Net more data to client' ela= 271 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306514805
EXEC #4:c=0,e=1139,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=9306514839
XCTEND rlbk=0, rd_only=1
WAIT #4: nam='SQL*Net message from client' ela= 69517 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306584449
XCTEND rlbk=0, rd_only=1

As you can see there are several "SQL*Net more data from client" messages. Of course, these are followed by the sending of data to the client.

Now here is the relevant portion of the trace file for the MS provider test:

PARSING IN CURSOR #3 len=30 dep=0 uid=88 oct=47 lid=88 tim=9306615423 hv=1003497284 ad='7ff27721950' sqlid='42zzxzhxx09u4'
begin ptest(p_vc=>:p_vc); end;
END OF STMT
PARSE #3:c=0,e=1507,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9306615420
BINDS #3:
Bind#0
  oacdty=01 mxl=32767(32000) mxlc=32000 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=178 siz=32767 off=0
  kxsbbbfp=1c3d8000  bln=32767  avl=00  flg=05
WAIT #3: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306615726
WAIT #3: nam='SQL*Net more data to client' ela= 69 driver id=1413697536 #bytes=8216 p3=0 obj#=-1 tim=9306615822
WAIT #3: nam='SQL*Net more data to client' ela= 18 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306615861
WAIT #3: nam='SQL*Net more data to client' ela= 200 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306616081
EXEC #3:c=0,e=612,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=9306616112
XCTEND rlbk=0, rd_only=1
WAIT #3: nam='SQL*Net message from client' ela= 60351 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306676628
XCTEND rlbk=0, rd_only=1

In this case there are no messages from the client and in the bind section there is no "value=" element.

An interesting wrinkle is that if I set the parameter direction to "ParameterDirection.InputOutput" using ODP.NET I do not observe the sending of data from the client.

Why, I wonder, does ODP.NET send data to the server for an output parameter? Ideas? Similar behavior observed?

OOW recap (pt 2)

Carl Backstrom - Wed, 2008-10-15 22:05
Better late than never on this one this job thing really gets in the way of blogging sometimes ;) .

I had two sessions this Oracle World , well I had a session and I helped out on another.

Sessions (Helper)

The session I helped out with a bit was Mark Drake's Oracle Application Express and Oracle XML Database: A Match Made in the Database. This session had it's beginning's way back at Web2.0 Expo where Mark and I shared a demo booth.  XMLDB has a standard sample/demo called XFILES which is a simple yet full featured file management system using all XMLDB features. But it requires using a java server and is kinda slow , well I think it's slow but I might be biased.

So I was like hey Mark! you should build this in APEX since we are both built in features of the database. And lo and behold the his session was born.  I helped on and off with building the demo application , and it was a big learning experience, both with the features XMLDB provides as well as were APEX does not leverage them well, something we will be working on improving.

Mark at OOW asked if I could sit in so if there was any specific APEX questions, and hey I'm a people person so of course I said yes.  The session was very well attended with many people in the audience already familiar with APEX and/or XMLDB.  Mark covered the basics of XMLDB and I quickly went over APEX basic's and then it was all demo. 

I really think the application demo and explanation of how things were created was a hit, it showed the power of XMLDB as well as how APEX can leverage it's features directly out of the databasey.  Just the number of hands that came up with people saying things like

  • Hey I just built that a month a go and yours is cooler.
  • Hey I need to build that in the near future and these are great ideas.
  • Hey can I have that application?

shows that this session hit a sweet spot. A couple things I got from this is that people like that APEX can directly leverage built in database features and want more of it easier, and secondly I need to really make time to clean up that XFILES application so it can be a packaged application.


Sessions (Mine)

My session was titled Web 2.0 using Oracle Application Express and was pretty well attended , especially since I was up against two other APEX sessions at the end of the day.  I like my sessions slide light , demo heavy and with audience participation . Luckily I didn't rewrite my whole demo and slides the day my session as I'm known to do though I was editing right up till the end. 

My presentation had two parts.

A technical part that expected people to know or at least have knowledge of APEX and it's built in javascript framework already.  During this part of the session I went over features in APEX that were used to build Interactive reports.  It goes something like this, in Interactive Reports we have this feature, and this is how it was built

...code ensues...

I like this format since it shows at a low level way how to build features but leaves it up to the developers how they want to leverage it. 

I also stress using the proper debugging tools when building client side features , specifically Firefox with Firebug , though I did point out that newer versions of other browsers are also getting these features.  Basically if your using a browser that doesn't give you proper error message IE 6/7 among others DO NOT use it for development, your making it harder on yourself and wasting time.

You download my application from here to mess around with.

The second part of my presentation is the real fun part where I get to talk about upcoming features in APEX.  So after showing my safe harbor slide , the one that keeps me out of trouble with the boss's , I was able to go over features that we are working on for future release's.

Improvements to the base functions $x() $v() $s() which are the base functions when dealing with page items.

Namespacing of our javascript libraries so that we play nice with other third party javascript libraries.

The use of chaining in our javascript library so that javascript is more compact and reusable.

And finally and my favorite!
Though I've hinted to people off and on for awhile we will  be including in the base jQuery library in our next release! It's all signed of and everything and I'm busy rewriting our code to leverage jQuery underneath, one of the reason's it's been tough for me to get these posts out.

In my next post I will go over how the inclusion of jQuery effect's APEX.  It's both a bigger and yet smaller change than you might think


Oracle Open World follow up

Oracle Optimizer Team - Tue, 2008-10-14 15:40
We were delighted to see so many people turn up for our Open World session - Inside the 11g Optimizer - so early on Tuesday morning! A lot of people have been asking where they can find more information on the topics covered especially the demos that were shown. You can find similar worked examples for most of the new 11g Optimizer features on the Oracle By Example website. You can also get more information on SQL Plan Management in the following white paper. Our blog entry from December 2007 has more information on Adaptive Cursor Sharing while the January 2008 entry gives more details on the enhancements made to statistics. We hope you enjoyed Oracle Open World as much as we did!

Oracle Open World follow up

Inside the Oracle Optimizer - Tue, 2008-10-14 15:40
We were delighted to see so many people turn up for our Open World session - Inside the 11g Optimizer - so early on Tuesday morning! A lot of people have been asking where they can find more information on the topics covered especially the demos that were shown. You can find similar worked examples for most of the new 11g Optimizer features on the Oracle By Example website. You can also get more information on SQL Plan Management in the following white paper. Our blog entry from December 2007 has more information on Adaptive Cursor Sharing while the January 2008 entry gives more details on the enhancements made to statistics. We hope you enjoyed Oracle Open World as much as we did!
Categories: DBA Blogs, Development

SQL Developer Data Modeling Update

Jared Still - Mon, 2008-10-13 11:56
Oracle has released an 'early adopter' version of the the data modeling enhancements to SQL Developer.

See the OTN article for details.

I haven't tried it yet, it will be interesting to see just how well it works.
Categories: DBA Blogs

What Do a Few Bits Matter

Edward Whalen - Mon, 2008-10-13 09:27
I’ve mentioned several times that I strongly recommend running Oracle for Windows using 64-bit. I’ve had a number of questions about this, so I wanted to clarify the importance of 64-bit Oracle on Windows. If you look back, you will see that this is the subject of my first blog back in May of 2006.

Oracle on Microsoft Windows is unlike any other platform that Oracle supports. When Oracle was originally ported to windows, the decision was made to take advantage of Windows threads. A thread, sometimes known as a lightweight process shares the same context as the calling process, thus the context switch is replaced by a thread switch (which uses much less CPU resources). With Oracle for windows, all traditional processes (background and server) are implemented as threads. This provides for more efficient processing, but causes another problem.

Within the thread model, all threads within a process share the same virtual memory address space. With Windows 32-bit, this virtual address space is 3 GB in size (with /3GB flag, 2 GB otherwise). Thus all of the Oracle processes share the same 3 GB virtual memory limit. In a process environment such as Unix, each process has its own 3 GB virtual memory address space. This limitation can cause the virtual memory space to be completely used, thus causing failures. The symptom of this in 32-bit Oracle is a failure to allocate memory for new dedicated server processes.

This problem has been solved with the 64-bit version of Oracle for Windows. The 64-bit version of Oracle for Windows supports a 16 Terabyte virtual memory limit. In addition, memory above 4 GB can be accessed directly, indirect data buffers need not be used. This makes the use of memory above 4 GB much more efficient and faster.
When implementing Oracle with an SGA that is more than 4 GB in size I always recommend the use of large memory pages. Large memory pages are much more efficient for large SGAs and makes a huge performance difference, but that’s going to be the subject of my next blog. For information on how to use large memory pages in Oracle on Windows see Metalink note 422844.1.

The limitations in this article lead me to strongly recommend the use of 64-bit Oracle on 64-bit Windows. So, "Just say no, to Oracle on 32-bit Windows".

R12 Maintain Employee Data using a Single Responsibility in Multi Business Group

RameshKumar Shanmugam - Sun, 2008-10-12 21:48
There will be always a question from the customers who are in an Global environment (in an multi Business Group environment) " How to maintain employee data using a single responsibility"
---Yes this was a constraint in 11.5.10, now in R12 this is made possible. Now customers will be able to maintain employees using the single responsibility across business group.

This functionality is Out of box with the product.
To leverage this functionality following are the pre-requistes/ Setups
  • HR module should be in Multi Buisness Group Environment
  • HR Organization hierarchy should be using Global hierarchy
  • HR Security Profile should be defind using the Global Security Profile form
  • Attach the global security profile to the HR: Security Profile at the Responsibility level

Once the above setup steps are completed, follow the below navigation

Global HRMS Manager > Maintaining Using Template > Maintain Employees

(M) View > find



Using the Find screen, In the Business Group Name field you will be able to select the Business group that you want to access the employee data and click find to retrieve the employee information.


Important Note: Using this method you will only be able to maintain the existing employee data. If you create any new employees using this responsibility, the employee data will be created in the default BG to which the responsibility is attached



Categories: APPS Blogs

Oracle Metalink Notes for Oracle Apps DBAs.

Sabdar Syed - Sun, 2008-10-12 08:50
Dear Friends,

Here are the list of Oracle Metalink Notes, I do refer regularly. Moreover, these are very informative. So, I thought of sharing those note ids here with you.

Note: You have to have an account in Oracle Metalink to access notes.

Installation

Note: 452120.1 - How to locate the log files and troubleshoot RapidWiz for R12
Note: 329985.1 - How to locate the Rapid Wizard Installation log files for Oracle Applications 11.5.8 and higher
Note: 362135.1 - Configuring Oracle Applications Release 11i with Oracle10g Release 2 Real Application Clusters and Automatic Storage Management
Note: 312731.1 - Configuring Oracle Applications Release 11i with 10g RAC and 10g ASM
Note: 216550.1 - Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 279956.1 - Oracle E-Business Suite Release 11i with 9i RAC: Installation and Configuration using AutoConfig
Note: 294932.1 - Recommendations to Install Oracle Applications 11i
Note: 403339.1 - Oracle 10gR2 Database Preparation Guidelines for an E-Business Suite Release 12.0.4 Upgrade
Note: 455398.1 - Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 11i
Note: 402311.1 - Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Microsoft Windows
Note: 405565.1 - Oracle Applications Release 12 Installation Guidelines

AD Utilities

Note: 178722.1 - How to Generate a Specific Form Through AD utility ADADMIN
Note: 109667.1 - What is AD Administration on APPS 11.0.x ?
Note: 112327.1 - How Does ADADMIN Know Which Forms Files To Regenerate?
Note: 136342.1 - How To Apply a Patch in a Multi-Server Environment
Note: 109666.1 - Release 10.7 to 11.0.3 : What is adpatch ?
Note: 152306.1 - How to Restart Failed AutoInstall Job
Note: 356878.1 - How to relink an Applications Installation of Release 11i and Release 12
Note: 218089.1 - Autoconfig FAQ
Note: 125922.1 - How To Find Oracle Application File Versions

Cloning

Note: 419475.1 - Removing Credentials from a Cloned EBS Production Database
Note: 398619.1 - Clone Oracle Applications 11i using Oracle Application Manager (OAM Clone)
Note: 230672.1 - Cloning Oracle Applications Release 11i with Rapid Clone
Note: 406982.1 - Cloning Oracle Applications Release 12 with Rapid Clone
Note: 364565.1 - Troubleshooting RapidClone issues with Oracle Applications 11i
Note: 603104.1 - Troubleshooting RapidClone issues with Oracle Applications R12
Note: 435550.1 - R12 Login issue on target after cloning
Note: 559518.1 - Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone
Note: 216664.1 - FAQ: Cloning Oracle Applications Release 11i

Patching

Note: 225165.1 - Patching Best Practices and Reducing Downtime
Note: 62418.1 - PATCHING/PATCHSET FREQUENTLY ASKED QUESTIONS
Note: 181665.1 - Release 11i Adpatch Basics
Note: 443761.1 - How to check if a certain Patch was applied to Oracle Applications instance?
Note: 231701.1 - How to Find Patching History (10.7, 11.0, 11i)
Note: 60766.1 - 11.0.x : Patch Installation Frequently Asked Questions
Note: 459156.1 - Oracle Applications Patching FAQ for Release 12
Note: 130608.1 - AdPatch Basics
Note::60766.1 - Patch Installation FAQ (Part 1)

Upgrade

Note: 461709.1 - Oracle E-Business Suite Upgrade Guide - Plan
Note: 293166.1 - Previous Versions of e-Business 11i Upgrade Assistant FAQ
Note: 224875.1 - Installation, Patching & Upgrade Frequently Asked Questions (FAQ's)
Note: 224814.1 - Installation, Patching & Upgrade Current Issues
Note: 225088.1 - Installation, Patching & Upgrade Patches Guide
Note: 225813.1 - Installation, Patching & Upgrade Setup and Usage Guide
Note: 224816.1 - Installation, Patching & Upgrade Troubleshooting Guide
Note: 216550.1 - Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note: 362203.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
Note: 423056.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.2)
Note: 726982.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0.3)
Note: 452783.1 - Oracle Applications Release 11i with Oracle 11g Release 1 (11.1.0)
Note: 406652.1 - Upgrading Oracle Applications 11i DB to DB 10gR2 with Physical Standby in Place
Note: 316365.1 - Oracle Applications Release 11.5.10.2 Maintenance Pack Installation Instructions
Note: 418161.1 - Best Practices for Upgrading Oracle E-Business Suite

Printer

Note: 297522.1 - How to investigate printing issues and work towards its resolution ?
Note: 110406.1 - Check Printing Frequently Asked Questions
Note: 264118.1 - Pasta Pasta Printing Setup Test
Note: 200359.1 - Oracle Application Object Library Printer Setup Test
Note: 234606.1 - Oracle Application Object Library Printer Initialization String Setup Test
Note: 1014599.102 - Subject: How to Test Printer Initialization Strings in Unix

Performance

Note: 390137.1 - FAQ for Collections Performance
Note: 216205.1 - Database Initialization Parameters for Oracle Applications Release 11i
Note: 169935.1 - Troubleshooting Oracle Applications Performance Issues
Note: 171647.1 - Tracing Oracle Applications using Event 10046
Note: 153507.1 - Oracle Applications and StatsPack
Note: 356501.1 - How to Setup Pasta Quickly and Effectively
Note: 333504.1 - How To Print Concurrent Requests in PDF Format
Note: 356972.1 - 11i How to troubleshoot issues with printers

Others

Note: 189367.1 - Best Practices for Securing the E-Business Suite
Note: 403537.1 - Best Practices For Securing Oracle E-Business Suite Release 12
Note: 454616.1 - Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2
Note: 394692.1 - Oracle Applications Documentation Resources, Release 12
Note: 370274.1 - New Features in Oracle Application 11i
Note: 130183.1 - How to Get Log Files from Various Programs for Oracle Applications
Note: 285267.1 - Oracle E-Business Suite 11i and Database FAQ
Note: 453137.1 - Oracle Workflow Best Practices Release 12 and Release 11i
Note: 398942.1 - FNDCPASS Utility New Feature ALLORACLE
Note: 187735.1 - Workflow FAQ - All Versions

Oracle Applications 11i/12 Online Documentation

http://www.oracle.com/technology/documentation/applications.html

Note: Above are only the few important notes, there are bunch of notes on many other issues and topics in the metalink. I would suggest one to refer the metalink for more note ids.

Leave your comments if you feel these Oracle Metalink Notes help you in solving your Application issues.

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

Oracle SQL Developer Data Modeling - Early Adopter Release

Oracle Apex Notebook - Fri, 2008-10-10 10:37
 The long waited Data Modeling functionality for Oracle SQL Developer is here. Oracle SQL Developer Data Modeling runs on Microsoft Windows XP and Vista, Linux and Mac OS X. This is a first Early Adopter release, not a final product and I'm sure that the team behind the development, would appreciate all the feedback we can give them.
Categories: Development

Oracle Buys Primavera

Project Directions - Thu, 2008-10-09 09:08

See the full press release here.

 

Oracle Buys Primavera Creates First, Comprehensive Enterprise Project Portfolio Management Solution for Project-Intensive Industries
Redwood Shores, CA – October 8, 2008
News Facts:

Today, Oracle announced it has agreed to acquire Primavera Software, Inc., a leading provider of Project Portfolio Management (PPM) solutions, to accelerate its momentum in delivering mission-critical operational applications.

Primavera’s PPM software helps companies propose, prioritize and select project investments, and plan, manage and control the most complex projects and project portfolios.

Together with Oracle’s applications and infrastructure software, Oracle expects to provide the first comprehensive enterprise project portfolio management solution that helps companies allocate the best resources, reduce costs, meet delivery dates and ultimately make better decisions, all by using real-time data.

Oracle Enterprise PPM will be tailored to project-intensive industries such as engineering and construction, aerospace and defense, utilities, oil and gas, manufacturing, and professional services.

Primavera employees and management are expected to join Oracle to form a global business unit (GBU) focused on Enterprise PPM, and to help ensure a smooth transition for Primavera customers and partners. Primavera’s CEO Joel Koppelman is expected to lead the business unit as Senior Vice President and General Manager.

The transaction is subject to customary closing conditions and is expected to close in the second half of 2008. Until the deal closes, each company will continue to operate independently. Financial details of the transaction were not disclosed.


Agile PLM - Web Proxy Server Installation

Aviad Elbaz - Tue, 2008-10-07 11:01

It has been a while since I've posted about Agile PLM stuff..

In this post I'll go through implementing and configuring an Oracle Agile PLM Web Proxy Server.

Previous related posts:

Agile PLM 9.2.2.1 - Part I - Introduction
Agile PLM 9.2.2.1 – Part II – Database Node Installation
Agile PLM 9.2.2.1 – Part III – Application Node Installation


First, who needs a Web Proxy server for Agile PLM?

In order to make Agile PLM accessible to external users, a Web Proxy server should be installed to allow connections from the internet.
This solution include a new server on DMZ behind a firewall which forwards calls to the internal Agile application server.

(* Image from Oracle Agile documentation)

The Agile Web Proxy Server installation consists of the following steps:

  • Installing and Configuring the IIS
  • Install the Agile Web proxy files on the IIS server in the DMZ
  • Configuring IIS as a Proxy Server for Agile PLM
  • Configuring IIS as a Proxy Server for Agile File Manager
  • Create new external File Manager
     

Now I'll show a detailed procedure to install Agile Web Proxy Server on Windows using IIS (Microsoft Internet Information Server).
*** When installing on Unix/Linux OS you'll use Apache as the web proxy server.

Installing and Configuring the IIS

  1. Install IIS on the new Agile Proxy Server (will be called agiledmz)
  2. Login to agiledmz server with administrator user
  3. Navigate to: Start -> Administrative Tools -> Internet information services (IIS) Manager
  4. Right click on Web Sites -> new -> Web Site -> Next
     

     
  5. Enter description: Agile PLM Web Site -> Next
     
  6. Enter the following:
    Ip: All unussigned
    port: 80
    host: null -> Next
  7. Path: C:\Inetpub\wwwroot -> Next
     
  8. Check the Read option only -> Next
     
  9. Click Finish  
  10. Right click on Agile PLM Web Site -> properties
  11. Select tab Directory Security -> Under 'Authentication and Access Control' click on Edit -> Uncheck the 'Integrated Windows authentication'
     
  12. Disable the default Web Site:  Right Click on Defaule Web Site -> Stop
     

Install the Agile Web proxy files on the IIS server in the DMZ

  1. Make sure IIS is up and running (Go to services screen - services.msc - and check that "IIS Admin Service" process is in Started state)
     
  2. Execute setup.exe from [INSTALLATION_BASE_DIR]\PN00168\Windows directory
     
  3. Click Next -> accept the license -> Next
     
  4. Enter User name, Company name and License key -> Next
     
  5. Check the Web Proxies only -> Next
     
  6. Specify directory on which agile files will be installed (D:\Agile\Agile9221)-> Next
     
  7. Select Oracle Application Server 10g -> Next
     
  8. Enter the Web Proxy server name (or IP) and port (80) -> Next
     
  9. Enter the server name and port on which Agile application is installed (The Agile Application Server) -> Next
     
  10. Keep the default "Agile" for Agile Application Server Virtual Path-> Next
     
  11. Keep the default "Use internal user account (ifsuser)" -> Next
     
  12. Keep the default "Filemgr" for Agile Primary File Manager’s Virtual Path -> Next
     
  13. Enter Agile's File Manager host name and port (might be the same as Agile Application Server)-> Next
     
  14. Click on Install
  15. Click on Done
     

Configuring IIS as a Proxy Server for Agile PLM

  1. Navigate to: Start > Administrative Tools > Internet Information Services (IIS) Manager
  2. Right click on Agile PLM Web Site -> properties
  3. Click the Home Directory tab -> update the execute permissions to Scripts and Executables
     
  4. Click the ISAPI Filter tab -> click Add 
  5. Filter name: oproxy , Executable: D:\Agile\Agile9221\AgileProxies\oracle_proxy.dll -> Ok -> Ok
     
  6. Right click on Agile PLM Web Site -> New -> Virtual Directory -> Next
     
  7. Alias: oproxy -> Next
     
  8. Path: D:\Agile\Agile9221\AgileProxies -> Next
     
  9. Check the Read and Execute -> Next -> Finish
     
  10. Select the Web Service Extensions -> Select the All Unknown ISAPI Extensions -> Click Allow -> Click Yes
     
  11. Close IIS Manager window
  12. Restart IIS from services screen
  13. Right Click on Agile PLM Web Site -> properties -> select the ISAPI Filters tab -> you should see a green arrow pointing up for oproxy.
     

 

Configuring IIS as a Proxy Server for Agile File Manager

  1. Login to the File Manager server
  2. Note the Connector Port number from [AGILE_HOME]\Tomcat\conf\server.xml
     
    <!-- Define a Coyote/JK2 AJP 1.3 Connector on port 8009 -->
        <Connector port="8009" enableLookups="false" redirectPort="8443" debug="0" protocol="AJP/1.3"/>
  3. Edit jk2.properties from [AGILE_HOME]\Tomcat\conf
    Add the following line at the end and save the file:
    channelSocket.port=8009    

  4. Login to agiledmz - (The Web Proxy Server)
  5. Navigate to: Start > Administrative Tools > Internet Information Services (IIS) Manager
  6. Right click on Agile PLM Web Site -> properties
  7. Click the ISAPI Filter tab -> click Add
  8. Filter name: Jakarta IIS Connector , Executable: D:\Agile\Agile9221\AgileProxies\isapi_redirect.dll -> Ok -> Ok
     
  9. Right click on Agile PLM Web Site -> New -> Virtual Directory -> Next
     
  10. Alias: Jakarta -> Next
  11. Path: D:\Agile\Agile9221\AgileProxies -> Next
  12. Check the Read and Execute -> Next -> Finish
     
  13. Open the workers.properties file in [AGILE_HOME]\AgileProxies and make sure the File Manager server is correct in the following entry:
     
    worker.ajp13w.host=<File Manager host name>    
  14. Restart IIS 
  15. Right Click on Agile PLM Web Site -> properties -> select the ISAPI Filters tab -> you should see green arrows pointing up for both oproxy and jakarta.
     

 

Create new external File Manager

  1. Open the Agile Java Client
  2. Select 'Admin' Tab -> expand 'Server Settings' -> open 'Locations'
  3. Select the 'File Manager' tab -> click on the 'new' icon (you must have license for unlimited File Managers to enable it)
  4. Enter the following:
    Name - iFS_External
    File Manager URL - Use the web proxy server name (registered on the web) or IP, e.g. http://agile.company.domain/Filemgr/AttachmentServlet or http://111.11.11.111/Filemgr/AttachmentServlet
    Enabled - Yes
    Primary File Server - No
     
  5. Click on 'Ok'
  6. Expand the 'User Settings' -> open 'Users' -> find the user you want to use the external iFS and open his settings -> select the 'Preferences' tab -> update the 'Preferred File Manager' to 'iFS_External'.
     

*** Important Note
Agile architecture compels you to configure specific File Manager for each user.
Users configured to use the internal iFS will be able to utilize the File Manager from within the network only! They'll be unable to utilize the external iFS when login to Agile through the Agile Web Proxy Server.
The same behaviour for users configured to use the external iFS - They will be able to utilize the external iFS only.

 
Now you can login to agile from the internet through the Agile Web Proxy Server.
Use the following URL to get the login screen:  
http://[Agile Web Server Proxy IP or Name]/Agile/PLMServlet

Next post I'll show how to secure connections to Agile through the Proxy server using SSL (https) connection.

You are welcome to leave a comment and/or share your experience with Agile installation.

Aviad

Categories: APPS Blogs

AIOUG - TechNight in Bangalore

Virag Sharma - Mon, 2008-10-06 23:27

The All India Oracle User Group with a 4 lakh -strong developer base and a community of seven-lakh users in India is organising a second meeting in Bangalore on
October 22 to share experiences and provide insights into shape of things to come. It is the second largest technology network of professionals for Oracle after the US. Soon these TechNights will be held in other parts of India, such as Pune, Mumbai and Delhi.

Like several successful Oracle user groups around the world, AIOUG is also a not for profit organization formed by Oracle users for Oracle users. Let’s join hands across the country to make AIOUG a global lead user group.

AIOUG first TechNight held at Hyderabad, Please click here to see TechNight pictures
Categories: DBA Blogs

Demo Application from Oracle World (and ODTUG too)

Carl Backstrom - Mon, 2008-10-06 12:41
As promised I've put up the export of my demo application from Oracle World , for people who were at ODTUG in New Orleans it's pretty much the same application I showed there.

Thanks, for everybody who showed up I had a great time and hopefully was able to give people some ideas. I'll see what the rules are for me putting my slides on slideshare or something like that , but at some point Oracle will putting up the slides and a recording of the session.

Download the Application

Edit

The application will only work in Firefox, but that is only because it was built specifically to show Firebug features.

If you remove anything that says console.log or console.dir it will work in other browsers.

Regards,
Carl

My Blog Feedback Page

Sabdar Syed - Fri, 2008-10-03 18:37
Dear Blog Readers,

I wanted to have a feedback page as a post in my blog where my blog viewers can post their valuable comments and suggestion about my blog and articles.

In order to serve you better with required information in my blog, please leave your comments/suggestions/doubts/questions/requirements/ etc.

Link for comments here - Feedback Page !

Please check the comments submitted in the comments item below.

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

OOW recap (pt 1)

Carl Backstrom - Thu, 2008-10-02 14:06
Hi all it's been a week now since the end of Oracle World and finally have enough time to write up a brief recap of my experience of OOW.

HOL (Hands On Labs)
I helped out in a couple of the APEX Hands on Labs starting with the very first one at 10:30 am on Sunday and they seemed to run fairly smooth , some computer problems but were able to work through them, and were very well attended. Seeing some of the issues people ran into was very informative. Some are just places where we can improve the Hands on Labs themselves and others were where we can improve product, for that information alone I'm glad I volunteered to helped out.

Sessions (Not Mine)
I almost never have enough time for sessions at Oracle World , mainly because I like working the demo grounds, and usually completely ignore my scheduled demo ground times and just hang out what can I say I'm a people person :D , but I did make it to a few.

I was in Joel Kallman's APEX + Globalization session, which was very well attended, especially for a Sunday. Since globalization isn't something I usually have to deal with I always learn something new from these sessions. One thing I did learn about APEX is you don't have to take globalization into account from the very beginning of development comparative to some other development enrollments, though a bit of forethought helps, but can decide to translate and globalize your applications as needed.

The other session I was able to sit through in it's entirety was Raj Mattamal's Security: Writing Custom Authentication Schemes for Application Express session, and as always Raj put on a very enjoyable and informative session.

In fact if one thing ,at least for me, stands out about Joel's and Raj's sessions it's how well both of them communicate some complex very ideas and how easily some of these complex ideas can be implemented with APEX.

Demo Grounds
The demo grounds are my favorite part of Oracle World. Since I work from home usually most my communication , even with the APEX development team, is email / IM / phone calls. Oracle World not only allows me to remember what the people I work with look like but meet people I know from forums,blogs,etc face to face.

I also like demoing APEX, it's fun to show someone who's never seen APEX just how fast you can build and edit a complete working application , APEX pretty much sells itself in the 5 minute demo, and to see how they react when they hear they already own APEX with their current database license.

And for people that have seen or using APEX already I like demoing the new stuff. This OOW the new stuff was Websheets (interactive reports with built in edit and more) and Dynamic Actions (declarative javascript functionality).

As a developer's working on bugs and functionality on these features sometimes we lose track of how cool/useful/needed some of these things are. But to watch people reactions as we show them the stuff they can't play with yet ;) and to get comments and suggestions about the features (good or bad) really makes it worth it.

OOW recap (pt2) I'll go over the sessions I had a hand in , some miscellaneous events over the week.

The blog post after that will be about our plans with APEX + jQuery , it kinda deserves it's own post.

Undocumented Oracle Functions

Jared Still - Wed, 2008-10-01 18:58
Undocumented functions in Oracle are always fun, and you just may find something useful.

The caveat of course is that they are undocumented. They can change without notice between releases or patch levels, so building apps that depend on them may be unwise.

They are often quite useful from a DBA perspective when used in SQL scripts.

Here are a few that I've played with. These are all found in Oracle 10.2.0.3

These functions have one thing in common - they have a prefix of SYS_OP_

Some of these appear to be identical to documented functions.

I don't know of any official explanation regarding the purpose of undocumented functions that seem to mimic documented functions. It could be that the source for the documented functions are separate from those that are documented, ensuring that functionality will not change for an undocumented function that is used in the Oracle kernel, even though its documented doppelganger may change in future releases.

In any case, undocumented functions are always interesting, and here are a few to play with.

Just keep in mind that these are undocumented, and as such may change or disappear entirely in future releases

sys_op_vacand - Return the binary AND of two raw values. Results are in hex

SELECT sys_op_vecand(hextoraw('FF'),hextoraw('FE')) from dual;
FE

16:13:12 SQL>SELECT sys_op_vecand(hextoraw('C3'),hextoraw('7E')) from dual;
42

sys_op_vecor - Return the binary OR of two raw values. Results are in hex

16:14:39 SQL>SELECT sys_op_vecor(hextoraw('FF'),hextoraw('FE')) from dual;
FF

sys_op_vecxor - Return the binary XOR of two raw values. Results are in hex

16:14:39 SQL>SELECT sys_op_vecor(hextoraw('FF'),hextoraw('FE')) from dual;
FF

sys_op_vecbit - Return the value of the bit at position N in a raw value

The return value is 0 or 1

This is an interesting function as it can be used to determine the value of bits in a number. If for instance some flags are stored in a bit vector and you need to know the value of the 3 bit, this is an easy way to do it.

I believe the upper limit on the number of bits is 127.

prompt
define decnum=10
prompt &&decnum dec = 1010 bin

16:16:27 SQL>select 'Bit 0 is ' || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),0) from dual;
Bit 0 is 0

16:16:27 SQL>select 'Bit 1 is ' || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),1) from dual;
Bit 1 is 1

16:16:27 SQL>select 'Bit 2 is ' || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),2) from dual;
Bit 2 is 0

16:16:27 SQL>select 'Bit 3 is ' || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),3) from dual;
Bit 3 is 1

sys_op_bitvec - This appears to be for used to build a bit vector, but I haven't figured out
how to use it. Please let me know if you do.

sys_op_map_nonnull - This has been thouroughly discussed on Eddie Awad's blog:
sys_op_map_nonnull discussion

sys_op_descend - Returns value as it would be used in a descending index. This is essentially
reverse() function with output in hex.

16:32:41 SQL>select sys_op_descend('ABC') from dual;
BEBDBCFF

sys_op_undescend - The inverse of sys_op_descend. Well, almost

17:12:59 SQL>select sys_op_undescend(sys_op_descend('ABC')) from dual
17:12:59 2 /

414243

Notice the output is in the original order, but in decimal rather than hex.


sys_op_dump - dump the data from an ADT/UDT (Abtract/User Data Type)

16:54:13 SQL>CREATE OR REPLACE TYPE my_adt AS OBJECT (
16:54:13 2 last_name varchar2(30),
16:54:13 3 first_name varchar2(30),
16:54:13 4 id number(6)
16:54:13 5 )
16:54:13 6 /
16:54:13 SQL>
16:54:13 SQL>
16:54:13 SQL>select sys_op_dump(my_adt('still','jared',234987)) from dual;

('still','jared',234987)

I don't use objects in the database, but this would likely be useful for someone that does.

sys_op_guid - this appears to be identical to sys_guid

17:00:50 SQL>select sys_guid(), sys_op_guid() from dual;

52BA7CF06BB488ECE040010A7C646200 52BA7CF06BB588ECE040010A7C646200
Categories: DBA Blogs

It's been busy and $700 billion bail-out

Peter Khos - Tue, 2008-09-30 10:12
Hi there,It's been busy. I couldn't even made it to OOW this year (even though I was given the approval to attend). I looked at the various draft post that I have had (never did get a chance to finish) over the last 12 months, it has been horrendous. Anyhow, just wanted to put in a couple of points re: $700b bailout asked by the Bush Administration. Wow! $700b! That's more than the GDP (GrossPeter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com1

Point

Sergio's Blog - Tue, 2008-09-30 10:10
Categories: DBA Blogs

Search Through Region Source in Application Express

Sergio's Blog - Tue, 2008-09-30 09:46

As I was working to migrate an application and its theme over to Application Express 3.1.2 from 2.1.2 I needed to move some images around and update the references accordingly. I remembered that the Application Builder has a feature to search through region source, which came in handy.

Here's where you can find it:

I could have used this type of feature to search through templates as well. If anyone from the APEX team is reading this...

Categories: DBA Blogs

My OpenWorld 2008: 21 Set

Oracle Apex Notebook - Mon, 2008-09-29 12:31
Today I had the chance to attend 4 sessions. Two of them were Hands on Labs from Oracle Develop. Also, it was nice to put some faces in the names I’m used meet on the net. I met Carl Backstrong, Joel Kallman, David Peake, Dimitri Gielis, Francis Minault, John Scott and Patrick Wolf. Session 1: Hands-on Lab: Extending the Oracle Application Express Framework with Web 2.0 - APEX Development Team
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator