Feed aggregator

A first glance at IDS Scheer's ARIS Business Architect - Oracle Business Process Architect

Clemens Utschig - Wed, 2006-10-11 13:32
These days I am attending a training on ARIS' tools, with Oracle colleaques from all around US, tought by IDS and Oracle together. A while ago Oracle oem'ed ARIS tools for its upcoming Business Process Analysis (BPA) Suite, as mentioned in this press release.

This training should be a great opportunity to expand my knowledge on business driven process design. Especially visualization of processes and ownerships seems very important, when evangelizing on the human factor, and impacts from SOA projects on organizations - one of my key interests.



The end of the whole story can be a business process, created by the business, that is transformed into BPEL, and refined by a member of the IT department to get executed.

Aside BPA is one key piece when talking about round trip methodologies, namely Business Process Management (BPM), covering the whole cycle from analysis, through design, simulation, to production and refining, a never ending loop of seeking for business performance excellence.

Oracle Data Mining 10gR2 Code Generation Release Now Available on OTN

Marcos Campos - Tue, 2006-10-10 19:24
I have just received this from Product Management:We are pleased to announce the newOracle Data Mining 10gR2 Code Generation releaseis now available for download (RTM) on OTN.This new ability to go directly from a data analyst building predictive models to having working in-database PL/SQL code for implementing a complete data mining solution is unrivaled in the industry. There is no data Marcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com4
Categories: BI & Warehousing

Debugging PL/SQL from ASP.NET and Visual Studio

Christian Shay - Tue, 2006-10-10 10:15

[Update March 2015: This very popular blog post is quite a bit out of date after 9 years! It is no longer particularly feasible to use only one instance of Visual Studio for PL/SQL and ASP.NET debugging. We now recommend you use two instances of VS along with "External Application Debugging".

To get the latest details on how to use PL/SQL debugging with Visual Studio have a look at the Oracle Developer Tools for Visual Studio (ODT) online help. From Visual Studio, find the ODT help, find the "Oracle PL/SQL Debugger" section, then "PL/SQL Debugging Setup", then go through the"Debugging Setup Checklist". - CS]


Lots of you are using ASP.NET and the Oracle Data Provider for .NET to create web applications that access Oracle. When those applications call a PL/SQL stored procedure, wouldn't it be great to just step right into the PL/SQL and begin debugging with the live parameter values still intact?

As mentioned in my earlier blog entries, we now have a fully integrated PL/SQL debugger built right into Visual Studio which is included with the free Oracle Developer Tools for Visual Studio .NET (ODT).

Today I will explain how to configure Visual Studio and your ASP.NET project to get started debugging your PL/SQL stored procedures, functions and triggers from ASP.NET.

Background
Before I start - a brief word about the way Oracle's PL/SQL debugging architecture works. When you connect from any Oracle 9.2 or later client application, Oracle's client libraries check to see if a ORA_DEBUG_JDWP environment variable is set. If it is, the client passes this environment value along with the connect information (eg Oracle user/pass) to Oracle. This ORA_DEBUG_JDWP variable is set using the following format:

SET ORA_DEBUG_JDWP=host=hostname;port=portnum
where hostname is the machine where the PL/SQL debugger (eg Visual Studio) is located, and portnum is a TCP/IP port it is listening on. When Oracle database receives this information, it immediately attempts to connect to the debugger on the port number specified. If there is no listener running on the port, this connection will fail and an error will be returned to the client.

When you debug directly from Oracle Explorer in Visual Studio (manually typing in the parameter values for a SP and so on) ODT takes care of passing this host and port information for you. ODT also silently starts listening on the port. You aren't even aware that any of this this is happening. This is documented in the online help as "Direct Database Debugging".

Similarly, when you call PL/SQL from .NET code, all you need to do is turn on "Oracle Application Debugging" (a checkbox in the "Tools" menu). The Oracle Application Debugging checkbox sets the environment variable in the Visual Studio process space and starts up the debugger listener, all without the user having to know or care about these details. You just check it, and away you go, debugging from .NET code into PL/SQL and back out again. This is documented in the online help as "Oracle Application Debugging".

When you need to debug PL/SQL from ASP.NET, if you are using the "ASP.NET Development Web Server" which is the default IIS webserver testbed for Visual Studio 2005, things are still pretty simple. You can still use the "Oracle Application Debugging" checkbox, although with a couple very important caveats (see below).

If you have an independent IIS webserver instead, things get just a little bit more complicated. In this case, the IIS Web Server process is technically external to the Visual Studio process. This is no problem since ODT lets you call the SP's you want to debug from ANY external process located on ANY machine, as long as that ORA_DEBUG_JDWP environment variable is set. It just means that you need to set this environment variable yourself and start the listener yourself, rather than relying on ODT to do it for you automatically. This is documented in the online help as "External Application Debugging".

Setup Instructions - Using ASP.NET Development Web Server

Here are the setup Steps for when you are using the ASP.NET Development server (a built in IIS web server for Visual Studio 2005). For most people, this will probably be the way to go:

1) Perform the generic setup steps 1 through 9 from my earlier blog entry and test via Oracle Explorer ("Step Into") that at least Direct Debugging is working. You should skip step #7, which does not apply to ASP.NET projects. As per these steps, the "Oracle Application Debugging" checkbox should be checked, the Options page should be configured and breakpoints should be set.

2) If the ASP Development Server is running (you will see an icon in your toolbar) you need to stop it before you begin debugging to force it be respawned so that it can pick up the Visual Studio environment variables. It's important to note that you need to stop the ASP Development Server every time you check or uncheck the "Oracle Application Debugging" option. You don't need to stop it every single time you run your app though. However, it's important to emphasize that you must stop the ASP Development Server when you are all done with PL/SQL debugging and have unchecked the "Oracle Application Debugging" - if you don't, your code will get connect errors.



3) Begin debugging!

Setup Instructions - Modifying the ASP.NET code

Setup Steps for situations where you are not using the ASP.NET Development Server and it is ok to add some code and rebuild your ASP.NET app:

1) Perform the generic setup steps 1 through 6 from my earlier blog entry and test via Oracle Explorer ("Step Into") that at least Direct Debugging is working.

2) If "Oracle Application Debugging" is checked off under the Tools menu, be sure to uncheck it.

3) Add code to your ASP.NET app to set the environment variable at the process level. You need to do this at a point in the code before a connection is opened. In C# this looks something like:

Environment.SetEnvironmentVariable("ORA_DEBUG_JDWP", "host=mymachine;port=8888", EnvironmentVariableTarget.Process);
4) It's a good idea to add code after the Oracle connection is closed that removes this environment variable. Since here we are setting this environment variable at a process level, it should not interfere with any other Oracle client apps running on your system. However it is important to note that if you do set this environment variable at something other than a process level (eg system level), you must be sure to unset it. All other Oracle apps on your system that see this environment variable will attempt to begin debugging everytime they connect and will get errors.

Environment.SetEnvironmentVariable("ORA_DEBUG_JDWP", "", EnvironmentVariableTarget.Process);
5) Set a breakpoint at some point BEFORE the first Oracle connection is made.

6) Start debugging your ASP.NET application and run to this breakpoint.

7) When the breakpoint hits, choose "Start Oracle External Application Debugger" from the "Tools" menu of Oracle Explorer. Provide it the same port number you gave in the environment variable above (in my case it was "8888"). Obviously your firewall cannot be blocking this port.



8) Set a breakpoint in the PL/SQL (double click on the procedure or function in Oracle Explorer to bring up the editor).

9) Set a breakpoint in your ASP.NET code AFTER the call to the stored procedure or function in ASP.NET to halt once control returns to ASP.NET

10) Begin debugging!

Setup Instructions - No Modification of ASP.NET code

In some cases, you might not wish to modify and rebuild the ASP.NET. So you can't set the ORA_DEBUG_JDWP environment variable in code like we did above. In those cases, you can set a systemwide environment variable and bounce IIS, but this is a bit more tedious:

In the steps above, replace steps 3 and 4 with the following:

3) Set ORA_DEBUG_JDWP as a system environment variable. Go to Control Panel, System and choose the "Advanced" tab. Click on the "Environment Variables" button. Set the value in the section labeled "System Variables". Make sure when you are done with your debugging session to unset this system wide environment variable because as mentioned earlier, it will interfere with other Oracle client applications on your system!



4) Stop and start the IIS Service from the Services Console (Control Panel->Administrative Tools) so that it picks up this environment variable.



....Then proceed with the rest of the steps in the list!

If you have additional questions about PL/SQL debugging, feel free to post to the Oracle Dev Tools forum!

Happy coding!

SOA 10.1.3.1 Hints - Episode 2

Clemens Utschig - Mon, 2006-10-09 16:43
Following up on my Series SOA 10.1.3.1 Hints today I'll shed some light on the new and improved HTTP binding.

As you know, we always offered a way to start a process instance not just via SOAP or the Java API - but also through some kind of REST like binding.

With the 10.1.3.1 release we switched XML parsing to Oracle's XDK and also added some new features to make this again properly working.

1) HTTP post can also have a form posted and not just plain xml (REST) - for this to work you need to specify the following in the header of the http request
content="application/x-www-form-urlencoded; charset=iso-8859-1"

2) A namespace and message part can be specified in the request by using
<input type="hidden" name="msg_part" value="payload">
<input type="hidden" name="namespace" value="http://services.otn.com">
where msg_part maps to the part of the input message type (usually payload) and namespace to the target namespace of the element (as defined in the wsdl).

On a side note, the whole http get works only for not nested elements, in case of complex (=nested) types you have to use POST with fully qualified xml.

SOA 10.1.3.1 Hints - Episode 1

Clemens Utschig - Fri, 2006-10-06 13:42
Over the next weeks I plan to publish tips and tricks around the usage of the upcoming SOA suite, based on questions I see on the forums on OTN.

The first one is about adding properties to bpel.xml (our deployment descriptor) - either on a partnerlink(binding) , as a preference , or configuration .
We strongly recommend to NOT do this in the source view of the file itself, as it could cause sync problems, reflected in loosing them.

Instead use the visual BPEL designer offered. For properties on a partnerlink, simply double-click on it, and choose the properties tab. For process wide preference, click the descriptor icon on the top of the modeler, and add them there.

Sun Calendar in Thunderbird

Siva Doe - Thu, 2006-10-05 06:20

I am using Thunderbird as my email client, as the UI is consistent across all platforms (Solaris/sparc, Linux/x86, Mac/PPC). Till now, it was missing integration with Sun's calendar server. Many times I started up Evolution when I needed to work with meeting appointments.
Now there is a solution (at least for Linux & Mac). Enter Mozilla Lightning. The lightning-wcap.xpi has to be downloaded and using Thunderbird's install tool, installed this addon. After restarting Thunderbird, now I am able to use Thunderbird to my mail as well as calendar client.
Now, what would it make to make a Solaris/sparc, Solaris/x86 equivalent of lightning-wcap.xpi? Any volunteers? I will post more on this later, after attempting for Solaris.

Siva

OpenSolaris PPC

Siva Doe - Thu, 2006-10-05 06:04

Wow. It is happening. I am one of the very eager user waiting for a PPC port of OpenSolaris. I just hope I can run the upcoming port in my G3 Powerbook (Pismo) and dump the current Ubuntu Linux PPC and dual boot with OpenSolaris instead.

Siva

Oracle XMLDB: Create XML data with ease

Wijaya Kusumo - Mon, 2006-10-02 21:23
It is a fact that we, as software developer, will encounter more and more data in XML format. Either we receive xml data or actually need to produce one for information sharing with other system (web service, legacy system, .net vs Java, etc...). XMLDB of Oracle database (version 9i Release 2 and above) provides us with a convenience data type (XMLType) to store xml data, as well as the full

All good things come to an end

Stefan Roesch - Fri, 2006-09-22 22:33
After a more than seven great years at Oracle Corporation I have decided to resign and pursue new opportunities. In July I started to work for Microsoft Corporation.stefan roeschhttp://www.blogger.com/profile/15182787826739074738noreply@blogger.com0

PL/SQL Debugging OraMag Article

Christian Shay - Wed, 2006-09-13 19:56
Check out Mark Williams' latest article in Oracle Magazine which takes you step by step through PL/SQL Debugging in Visual Studio. It also includes some sample code which you can use to try out PL/SQL debugging yourself!

This sample code shows one of the most compelling uses of this debugger -- stepping seamlessly from .NET code into PL/SQL stored procedures or functions and back out to .NET code while examining real live data as it is being passed in and out. He uses an array parameter type in the stored procedure to show how powerful visual debugging can be -- no more DBMS_OUTPUT's of every element of a 1000 element array... you can just examine the array in the Visual Studio watch window!

Oracle Riddles: What's The Point?

Eric S. Emrick - Sun, 2006-09-10 09:11
I am frequently asked for directions. Sometimes I am not the best to ask and will just be a waste of your time and energy. Other times I am sought exclusively. I try to lead a balanced life. But, hey, I am not perfect. What exactly am I?

Don't Get Caught With Your GUI Down

Eric S. Emrick - Fri, 2006-09-08 22:47
In a recent wave of interviews I was amazed how little prospective DBA candidates knew about user-managed hot backups. Most could give the BEGIN and END backup stuff and convey that it causes more redo to be generated during this time. But, when asked to give a little more of their insight into the mechanics or performance implications, 9 in 10 had plenty to say - just nothing that was correct. 90% could not explain the significance of putting a tablespace in hot backup mode. That is, why do it? Why not just copy the file while the database is open and cooking? Of course, most understood that Oracle needs us to do this so that the backup is "good", but few knew how Oracle went about doing it. Moreover, few knew why the extra redo was generated. And most amazing, nearly all thought the data files were locked and changes were written to the redo logs and reapplied when the END BACKUP command was given. Where are DBA-types reading this? Irrespective, the DBA population is evolving.

I am not basing my opinion on one simple question concerning user-managed backups, but a host of other questions given as mental exercises. What are some of the Oracle wait events? What do they represent? How would you go about troubleshooting systemic response time degradation in your production database? What is extended SQL tracing and why use it? Time after time candidates struggled to give lucid, well thought out responses. A vast majority of responses could be summarized as, "I would go into OEM and check for A or B." I don't have a problem with using OEM, but usually the A’s and B’s had little relevance to the question.

The herd of available DBAs that are able to navigate the database using native SQL to get at critical performance diagnostic information has thinned dramatically. Sometimes I wonder what would happen to some of these shops being supported by some I interview if OEM, Database Control or Grid Control took the night off. When relegated to digging into the database and troubleshooting armed only with a SQL prompt, many appear to be lost. I certainly appreciate what the Oracle GUI database management tools bring to the table. I even like them. My point is, don't throw away your shovel just because you have a snow blower. The day will come when your GUI will fail you and it will be just you and your SQL prompt.

P.S.> Oracle does not lock the content of its data files during the course of a user-managed hot backup. Actually, Oracle only locks one thing, the master checkpoint SCN inside the file header. Some other constructs in the file header stay mutable. Blocks in data files being backed up can be modified as per normal database operation. The changes to blocks are indeed recorded in the redo, but they are not replayed when the END BACKUP is issued. More redo is possible because Oracle must accommodate the potential presence of fractured blocks.

Some updates

Siva Doe - Thu, 2006-09-07 16:55

It has been a long time. Lot of water has passed under the bridge. I have moved to a new team. Part of the Sun Grid team now.
On the localisation front, if you didnt know yet, Tamil is one of the first new locale that made it to OpenSolaris. This effort jumpstarted some colleagues, Raju Alluri and Kesari Mandyam to add locales for Telugu and Kannada. Very soon, we can see OpenSolaris in Indian languages.
I am running into some translation hurdles though. My home system still does not support Solaris (old network card, Compex). So, I thought I will do the translation in Linux (Ubuntu) using gtranslator. None of the Solaris .po file (untranslated) were opening. A 'msgfmt x.po' gave many syntax errors. So, it looks like I have to get my home system with Solaris, soon. (Siva- move your lazy a$$ and get a modern NIC card). Will post you on my progress later (Not so much later, I assure you).

SIG Meeting

Stephen Booth - Thu, 2006-09-07 08:38
I've just gotten the verbal OK from my line manager to attend the Management and Infrastructure SIG meeting on 4th October. Most of the opresentations look like they will be both interesting and useful. My employer is looking to introduce ITIL so the "Can it help me? What are the pitfalls?" presentation could be useful, similarly the DBA/Database ratio presentation is highly relevant to my Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

My Agenda for UKOUG 2006

Stephen Booth - Wed, 2006-09-06 06:38
These are the sessions I'm planning on attending at this year's UKOUG conference:08:00 - 17:45 Registration Open09:15 - 09:30 Introduction by Ronan Miles, UKOUG Chairman 09:30 - 10:30 Opening Technical Keynote: "What's Coming Next?" by Tom Kyte, Oracle10:30 - 11:00 Coffee Break11:00 - 12:00 Accountability for System Performance (introducing Six Sigma quality in Oracle software Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

SQL Gone Wild!

Eric S. Emrick - Mon, 2006-09-04 14:22
Ever see something so inefficient it evokes images of grape stomping to produce wine? I have, in Oracle 10g no less. A colleague of mine brought me a situation the other day that made me do a double-take, no triple-take, on a 10046 trace. The scenario involved a single row delete from a table using the index associated with the primary key on said table to delete the row, simple right? Well, the delete hung. The 10046 showed "db file sequential reads" spewing at a very rapid clip. The process was reading a child table that contained a column that referenced the primary key of the table being deleted. Okay, this is to be expected. We don't want to break our self-imposed business rules by orphaning child records. So what is my beef with this situation?

The child table had millions of rows that would have been orphaned had the delete succeeded. Keep in mind the constraint was NOT defined with ON DELETE CASCADE. Also, a single column index on the child table was associated with the child key. The stage was set for a swift and proper decline by Oracle to perform our delete. But this did not happen. Oracle was visiting ALL of the child rows then returning ORA-00292 "... - child record found." Yes, each and every child index entry was being visited. My colleague opened as SR with a very elegant little test case that reproduces the problem. Here it is. Try it for yourself and watch the trace with wonder and amazement. We have performed the test in 8i, 9i and 10g with the same results.

DROP TABLE CHILD;
DROP TABLE PARENT;
CREATE TABLE PARENT (COL1 NUMBER);
ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (COL1);
CREATE TABLE CHILD (COL1 NUMBER);
CREATE INDEX CHILD_IX_01 ON CHILD (COL1);
ALTER TABLE CHILD ADD CONSTRAINT CHILD_FK_01 FOREIGN KEY (COL1) REFERENCES PARENT;
INSERT INTO PARENT VALUES (999999999999);
INSERT INTO CHILD VALUES (999999999999);

COMMIT;

-- Insert approximately 1 million records into CHILD
begin
for i in 1..20 loop
insert into child select * from child;
commit;
end loop;
end;
/


alter session set events '10046 trace name context forever, level 12';

DELETE FROM PARENT WHERE COL1 = 999999999999;

Why doesn't Oracle stop once it encounters the first index entry indicating a foreign key violation has just occurred? Isn't a single found entry sufficient to fail my statement? It seems a bit indulgent to check each and every child row irrespective of my barbaric attempt to break my own business rules. Is it a classic case of stupid is as stupid does? Nope. It is a good old fashioned Oracle bug.

By the way, the Oracle support analyst recommended putting the index associated with a child key in a read only tablespace as a workaround. Think about that for a second...

Random training related thought

Stephen Booth - Mon, 2006-08-28 15:50
A thought just crossed my mind. I wonder if there is a market for short (one day) task oriented training for the sort of things that aren't normally covered in general courses.For example I'm aware that there are training courses that mention standby databases and certainly there are courses that cover RMAN, at least for basic backup and recovery. I'm not aware of any that actually take you Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com0

Oracle Develop

Denis Goddard - Wed, 2006-08-23 21:19
I've offically been a PHB for a long time -- long enough to "ossify" (as one of my old friends back at Cummins Electronics used to put it). I know this because I find myself realizing that it's been so long since I really developed anything in detail, I don't even remember what the new features are in Oracle 10g, let alone 10gR1 or R2... (though of course I'm really looking forward to 11gR2, but that discussion will have to wait!)

As it turns out, in the next week or two, I should be able to turn my attention back -- finally -- to ADE 3.2.3, where I'm doing some rather detailed work on PL/SQL and in the Perl/XS layer. The kind of thing where you have to spend a good hour just loading your brain's cache with the details... get into the zone... and stay there for a solid 10-hour block. What Tom Christiansen called Interface Zen
Ah yes, by god, I do so miss being a programmer!

Anyway, I'm seriously considering attending Oracle Develop 2006. First, Oracle keeps coming up with tools and technologies and I have to say, in my opinion we do a good job of being driven by customer demand. Plus, the Moscone Center expos are one of the few things I really miss about living on the SF Bay peninsula.

This could be seriously fun!

OpenWorld "Schedule Builder" - Get First Dibs On Your Favorite Sessions

Christian Shay - Wed, 2006-08-23 20:17
Shay Shmeltzer's recent blog post reminds me to alert you that things are first come first serve at OpenWorld this year! Use the new Schedule Builder tool to get first dibs on your favorite .NET Oracle Develop sessions!

Anyway, Shay said it so well, and has such a great first name, that I will just plagiarize from him shaymelessly:

"But the conference is two months out, why should I do it now?" - you might be asking.

Well the reason is that this is the only way you can guarantee you get to see the sessions you want to see. This year people who register for sessions ahead of time will get into the room before people who just show up. Considering the fact that this is going to be the biggest OOW ever in terms of number of attendees - you want to secure your place. Also, I know how it goes when you don't schedule, you start the conference spending the first hour of each day trying to figure out which session to go to - and by the time you end your daily planning you already missed the morning session. Scheduling your sessions now will also help us better plan rooms allocation for popular sessions.

So do us all a favor and start scheduling now.

Thank you, Shay!

Personalization and the MDS Database Repository

Andries Hanekom - Wed, 2006-08-23 04:04
All declarative User Interface components are stored either in XML files, in a format defined by MDS (Meta Data Services) Schemas, or in the MDS repository tables. When a personalization is created through the OA Personalization Framework it is added on top of the base product meta data. The personalization does not overwrite the existing base product UI and are therefore preserved during upgrades and patches. The MDS repository is supported by the JDR_UTILS PL/SQL package, used to query and maintain the repository:

SQL> set serveroutput on
SQL>
begin
jdr_utils.listcustomizations
('/oracle/apps/icx/por/req/webui/ShoppingCartPG');
end;
/
/oracle/apps/icx/por/req/webui/customizations/site/0/ShoppingCartPG
/oracle/apps/icx/por/req/webui/customizations/org/44/ShoppingCartPG

PL/SQL procedure successfully completed.
SQL>

The MDS database repository consists out of four tables:
  • JDR_PATHS: Stores documents, packages and there parent child relationship.
  • JDR_COMPONENTS – Stores document components.
  • JDR_ATTRIBUTES – Stores attributes of document components.
  • JDR_ATTRIBUTES_TRANS – Stores translated attribute values of document components.

Pages

Subscribe to Oracle FAQ aggregator