Feed aggregator

Time Series Revisited

Marcos Campos - Sat, 2006-10-28 17:55
I have been asked a couple of times for a script that would reproduce the results in the time series forecasting series. I finally managed to do it. In the process I found out that a couple of the queries needed to be tuned:In the airline example described in Part 2, the normalization shift and scale parameters were computed using the whole data. A better methodology would be to use only the Marcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com2
Categories: BI & Warehousing

Domain index not used after migrating from 8.1.7.4 to 10.2.0.2

Mihajlo Tekic - Sat, 2006-10-28 11:16
If you have application running on Oracle 8.1.7.4 and you are massively using CONTEXT indexes, you better analyse the queries where context indexes are involved, before you decide to migrate to 10.2.0.x.
This is because execution paths might be way different in 10.2.0.x that may lead to performance degradation.
Here is one example where you should consider query modification in order to avoid bad performances.
Let assume that you have a query that has OR-condition where OR-predicates use CONTEXT operators.

Example:

select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)>0
or
CONTAINS(b.text,:b,1)>0
or
CONTAINS(c.text,:c,2)>0
)

This query in 8.1.7.4 most likely will have execution plan similar to this one:

Plan Table
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 42 | 8K| 93 |
| CONCATENATION | | | | |
| NESTED LOOPS | | 14 | 1K| |
| NESTED LOOPS | | 14 | 2K| 17 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 135K| 1 |
| DOMAIN INDEX |ALL_OBJEC | 2K| | |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_B | 2K| | |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 1K| 2 |
| INDEX UNIQUE SCAN |ALL_OBJ_A | | | 2 |
| NESTED LOOPS | | 14 | 1K| |
| NESTED LOOPS | | 14 | 2K| 31 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |
| DOMAIN INDEX |ALL_OBJEC | 2K| | |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 135K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 2K| | |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 1K| 2 |
| INDEX UNIQUE SCAN |ALL_OBJ_A | | | 2 |
| NESTED LOOPS | | 14 | 2K| 31 |
| NESTED LOOPS | | 14 | 2K| 17 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_B | 2K| | |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 2K| 135K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 2K| | |
----------------------------------------------------------------

where domain indexes are used to support CONTAINS function.
Well, in 10.2.0.2, you better be prepared to have at least twice slower response time, since for this type of query no domain indexes can be used:
The execution plan for my 10.2.0.2 database looks like:

Plan Table
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 42 | 8K| 1650 |
| HASH JOIN | | 42 | 8K| 1650 |
| TABLE ACCESS FULL |ALL_OBJEC | 51K| 4M| 234 |
| HASH JOIN | | 51K| 6M| 835 |
| TABLE ACCESS FULL |ALL_OBJEC | 51K| 2M| 234 |
| TABLE ACCESS FULL |ALL_OBJEC | 51K| 3M| 234 |
----------------------------------------------------------------

All tables were identical in both databases.

I have opened a SR with Oracle Support on this one, and the answers that I got were very interesting and make sense.
The key reason for such a behaviuor is how Oracle optimizer handles OR-expansion condition where OR-predicates use CONTEXT operators.
Oracle optimizer usually probes OR-expansion to see if a cheaper index based plan can be produced. But, Or-expansion is not legitimate if OR-predicate has a CONTEXT operator, because some of the new "concatenated" query blocks will loose context of this operator.
So, generally, the plan generated in 8.1.7.4 is not legitimate, even though it is very good one, performance wise.
Anyway, if you want to use plan similar to the one you used to have, then you better transform your queries.
I modified the query shown above, to this one:


select * from
(
select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)>0
)
)
UNION ALL
(
select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)<=0 and CONTAINS(b.text,:b,1)>0
)
)
UNION ALL
(select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)<=0 and CONTAINS(b.text,:b,1)<=0 and CONTAINS(c.text,:c,2)>0
)
)


Execution plan has been changed to this one:

Plan Table
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 42 | 6K| 31 |
| UNION-ALL | | | | |
| NESTED LOOPS | | 14 | 1K| 19 |
| NESTED LOOPS | | 14 | 1K| 19 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 5 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 1 | 21 | 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_B | 1 | | 0 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 1 | 4 | 0 |
| NESTED LOOPS | | 14 | 2K| 5 |
| HASH JOIN | | 14 | 2K| 5 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 994 | 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 1 | 4 | 0 |
| HASH JOIN | | 14 | 2K| 7 |
| HASH JOIN | | 14 | 2K| 5 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 994 | 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 756 | 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
----------------------------------------------------------------

end the response time was much better compared to both previous plans.

Now, the questions are:
How this can be implemented in the application?
How many queries similar to this one are being used in the application?
Is it worth the effort?
Can your application live with the execution paths based on full table scan?

You better answer to these questions before you decide what your next step would be.

On the road again

Mihajlo Tekic - Fri, 2006-10-27 22:51
It’s been a long time since my last post on this blog.
Ok, one of the reasons for this “absenteeism” was my new job.
Honestly, I didn’t have too much time to write anything except reports, research papers, proof of concepts, etc.
Although I commute more than I used to, I don’t mind since I use every chance to read something interesting while riding on CTA trains.
In three months, only in train cars I’ve read three books, two for the first time and one for the second time (just because it is perfect).

1. Oracle High-performance SQL tuning - Don Burleson believe or not, I found it for $9.99 in Borders Outlet Store.

Oracle Replication: Snapshot, Multi-master & Materialized Views Scripts - John Garmany & Robert Freeman

3. Effective Oracle by Design by Tom Kyte I’ve read this book once, and I read it again, just because it is perfect.

Who said that riding in CTA trains is boring? Actually it is sometimes when train cars are so crowded.

Anyways, I am on the road again, hopefully I’ll be more active in the future writing on this blog.

An FAQ for 'Pro EJB 3: Java Persistence API'

Mike Keith - Fri, 2006-10-20 21:12

I use this blog for two things. The first is as an outlet to vent about things that I feel passionate about and that I want to yell out to the world (or at least to the two or three people that are unlucky enough to accidentally stumble across this blog and can?t find the back button). The second is out of laziness. When I have to repeat something numerous times to numerous people it eventually occurs to me that I could just write it down in a public place and point people to it. This entry falls into the latter group.

A lot of people ask Merrick and I about what our book Pro EJB 3: Java Persistence API covers, and whether it would help them in their particular situation. This is kind of a FAQ about the book and answers the most common questions that we get asked.

I have never used EJB. Is this book okay for beginners?

One of our goals was to make this book suitable for people that had no EJB experience at all. We start from the beginning and bring the reader all the way to the intermediate and advanced concepts that they may encounter when developing JPA applications.

What if I don't know EJB 2.0? Will I be able to understand the book?

In general we think people are probably better off starting with no knowledge of previous versions than if they are holding preconceptions based on EJB 2.x. We intentionally left out EJB 2.x from the book because we thought that including it would actually just add confusion to an API that we think is elegantly simple. We did add a few sidebars that differentiate between the old and the new versions, though, just to help those who are accustomed to the old model.

How much of the book is devoted to EJB 3.0 stuff that is not persistence-related?

It is first and foremost a book about the Java Persistence API, and that is what most of the content is about. We did think it worthwhile, though, to cover some of the Java EE 5 enterprise components that access persistence, since that is most likely where JPA will be used. A healthy chapter on session beans, message-driven beans describes some of the basics of the component API. It does not include any of the advanced topics like interceptors, though.

Where do I get the sample code and what do I need to run the examples?

The examples can be downloaded from the Apress web site. To run them the Glassfish application server (the Java EE 5 Reference Implementation), which includes Toplink Essentials (the JPA Reference Implementation) can be downloaded. The Derby database is also included in the Glassfish distribution.

You work for Oracle. Why did you use Glassfish for the application server and not OC4J?

The Java Persistence API is a new standard that provides an abstraction across all persistence vendors. While it is true that we both work for Oracle, we wrote the book to apply to all vendors of the specification. The best way to show this was to use the reference implementations of the standard, i.e. the Glassfish container with the TopLink Essentials persistence provider. In addition, they were also the only fully spec-compliant implementations available at the time, and were very available and the most accessible. Having said that we still believe that OC4J is the best EJB 3.0 container on the market, and fully encourage people to download and use OC4J (which also ships with TopLink Essentials) to try out the examples as well. In fact, the examples were actually initially developed using OC4J because it was available to us internally. If you would like to use OC4J to run the examples it can be downloaded from OTN.

How come you didn't cover topic X or topic Y in the book?

When we were planning the book out we were really trying to come in between 300 and 350 pages. We wanted it to be small enough to be comfortably carried around, fit nicely and easily in a carry-on for reading on a plane, or a bus, and not appear to be a daunting read. As it turned out, despite our best efforts, we ended up going a little over 400 pages (plus some extra appendix stuff). If we didn't cover topic X it wasn't because we were ignoring it or didn't think of it (although I can't disprove the possibility that we didn't think of it) we really just couldn't cram it in.

Why didn't you put more TopLink examples in the book?

The book is about the JPA standard, so we didn't want to include all of the weird and wonderful things that you could do with the TopLink implementation as it might just confuse people with proprietary APIs. We did include a couple of proprietary sections, but it was either covering the topic of spec extensions, or discussing features that we got asked a lot about and the specification did not cover. We also clearly point out where the spec ends and when something we discuss is non-compliant.

Oracle Open World 2006 - and the last preparations - this time JDev team

Clemens Utschig - Fri, 2006-10-20 15:41
Susan, member of our JDeveloper PM team, created a cool viewlet (and I think she will demo this in real during her session).

It's about BPEL and ESB development in Oracle's JDeveloper - you can be found here.

Enjoy - and even more if you come to see us live at this year's Oracle Open World.

Oracle releases SOA Suite 10.1.3.1

Clemens Utschig - Fri, 2006-10-20 12:28
Download it from here - and experience our next generation SOA plattform
Oracle SOA SUITE 10.1.3.1

Oracle on Windows vs. Oracle on Linux

Edward Whalen - Wed, 2006-10-18 12:18
There has been much discussion as to whether Oracle on Windows or Oracle on Linux is a better platform. My opinion has always been that if you are choosing between the two, the platform that better fits into your environment is the better choice. If you are a windows shop and have extensive expertise on Windows and no Linux experience it doesn't make much sense to put a foreign Operating System into your data center. On the other hand, if you are a Unix only shop, and have no in-house Windows expertise, Linux might be a better choice.

In addition, I feel that the way Oracle had developed the Oracle Database Server for Windows using the threading model would turn into an asset once 64-bit Windows is adopted. Remember that 32-bit Oracle on Windows suffers from virtual memory issues that are solved with 64-bit Windows. Thus the liability of using the thread model has turned into an asset (see previous blog).

In order to dispel any rumors or conjecture on whether Windows or Linux works better on the same hardware we recently ran a comparison. This comparison was done using the SwingBench tool. The result of this comparison is provided in a white paper which we have just published on our website. In order to get to this whitepaper follow this link.

Choosing the best OS for your environment involves more than just the performance of the database server. The Oracle Database Server on Windows provides compatibility with Active Directory and your entire integrated environment.

SOA 10.1.3.1 Hints - Episode 3

Clemens Utschig - Tue, 2006-10-17 19:24
In today's note we will discover the steps needed to increase the inactivity timeout of the SingleSignOn - enabled consoles shipped with SOA Suite 10.1.3.1.

Some of you might have already noticed, not using the session for some minutes results in a rerouting to the login page.. and if it happens the first time some ohh, arghhh or other words may follow :D

The SOA related web consoles are ..
  • BPEL Console (http://host:port/BPELConsole)

  • ESB Console (http://host:port/esb/esb/EsbConsole.html)

  • Rule Author (http://host:port/ruleauthor)

  • and the famous Human Worklist(http://host:port/integration/worklistapp)

As all are deployed in the same way, and are JSSO enabled - here are the steps you need to follow, to increase the timeouts of them.
  • 1) Stop your SOA Suite (opmnctl stopall)

  • 2) Depending on your installation your OC4J container holding the apps is either located in $ORACLE_HOME/j2ee/home (basic install) or $ORACLE_HOME/j2ee/oc4j_soa (advanced install), which I refer to $OC4J_HOME from now

  • 3) Go to $OC4J_HOME/config and backup jazn.xml

  • 4) Add a new property called custom.sso.session.timeout inside the <jazn> section, the value is in seconds and should be a multiply of it

  • 5) Step by step now - you are going to reconfigure the consoles

    • BPEL Console

      • Go to $OC4J_HOME/applications/orabpel/console/WEB-INF and backup web.xml

      • Add the following fragment
          <session-config>
        <session-timeout>-1</session-timeout>
        </session-config>


        inside the <web-app> tags

      • Go to $OC4J_HOME/applications-deployments/orabpel/console and delete orion-web.xml

    • ESB Console

      • Go to $OC4J_HOME/applications/esb-rt/provider-war/WEB-INF and backup web.xml

      • Add the following fragment
          <session-config>
        <session-timeout>-1</session-timeout>
        </session-config>


        inside the <web-app> tags

      • Go to $OC4J_HOME/application-deployments/esb-rt/provider-war and delete orion-web.xml

    • Rule Author

      • Go to $OC4J_HOME/applications/ruleauthor/ruleauthor/WEB-INF and backup web.xml

      • Add the following fragment
          <session-config>
        <session-timeout>-1</session-timeout>
        </session-config>


        inside the <web-app> tags

      • Go to $OC4J_HOME/application-deployments/ruleauthor/ ruleauthor/ and delete orion-web.xml

    • Human Worklist

      • Go to $OC4J_HOME/applications/hw_services/worklistapp/ WEB-INF and backup web.xml

      • Add the following fragment
          <session-config>
        <session-timeout>-1</session-timeout>
        </session-config>


        inside the <web-app> tags

      • Go to $OC4J_HOME/application-deployments/hw_services/ worklistapp and delete orion-web.xml

  • 6) Start your SOA Suite, by opmnctl startall

Voila, the inactivity timeout in YOUR control

Oracle on Windows and NAS Storage

Edward Whalen - Sat, 2006-10-14 07:36

I have been asked the question "Does Oracle support NAS on Windows". Officially Oracle does not support native NAS devices per-se on Windows. I am speaking specifically of the CIFS protocol. This is not true for all platforms; Oracle does support NAS on Unix with NFS, but not on Windows. However, with the introduction of iSCSI Oracle does now support NAS on Windows.

The iSCSI protocol uses embedded SCSI commands within IP packets. This allows for storage to be accessed over the network, but at the same time take advantage of a proven storage protocol. The main benefits of iSCSI is that it is cheaper and potentially easier to use than the fibre channel equivalent.

In June of 2003 Microsoft released the iSCSI initiator and driver for Windows 2003. In addition, Microsoft has qualified a number of storage vendor’s products for use with Windows 2003. At the same time, Microsoft has introduced Windows Storage Server 2003, which allows a Microsoft Server to become the iSCSI NAS.

With iSCSI Oracle on Windows is completely supported as both a stand-alone Oracle database server or an Oracle RAC server. Yes, Oracle does support NAS on Windows.

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?

Pages

Subscribe to Oracle FAQ aggregator