Convert Credit Card Exp date to YYYY-MM-DD

Had a recent requirement to convert a credit card expiration date from MMYY to YYYY-MM-DD so that it could be sorted upon in Salesforce.  Fortunately, I only had to modify the view in Oracle to the following:

select
    DECODE(
      NVL(myColumn,'N/A'),
      'N/A',
      'N/A',
      to_char(last_day(to_date(myColumn,'MMYY')),'YYYY-MM-DD')
    )
  from myTable

ORA-12519

I created a polling adapter in my BPEL process.  Once I threw it on the server, it caused the Oracle XE (10.1.3?) database to crash.  After the database crashed, I could not view the BPEL console nor execute any commands on the database through SQL Developer.  The error I received was 'ORA-12519'.

What happens is that the XE database by default only allows a small number of connections.  BPEL consumes those connections rather quickly. If you encounter this problem, restart the XE database:

1.   Perform the following SQL as SYS user: ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE
2. Restart the database
3. Restart BPEL / SOA

BPEL - Importing schemas for Polling orchestration

By default, BPEL orchestrations are started with a client partner link with two activities, one for input and one for output. When creating a polling orchestration, or an orchestration that polls from a database rather then waits for an http request, the first task is to remove the partner link and the two activities. Unfortunately, this renders the client WSDL useless.

Without the client WSDL, there's no other way to import additional schemas into the BPEL orchestration. Or is there? When you create the polling activity in BPEL, it generates an XSD and a WSDL file for the Polling partner link. When you need to add additional schemas to your BPEL process, then add the following into your Polling WSDL. (If the types element already exists, then you can add your schema import there).



After saving the WSDL, be sure to add the declaration of the schema and namespace to your bpel source. Then you should be able to reference the schema in your orchestration.

Displaying compile-time warnings for triggers in Oracle

I'm keeping this hear mostly for my future reference.

The following code snippet is for displaying compile time warnings when a trigger compiles, but shows warnings.  For instance, if I compile a trigger in SQL Developer, sometimes it will compile, but then the trigger is invalidated when I try to run it.  I go to check for any errors, but SQL Developer only shows 'Compiled with warnings'.

select line, position, text from dba_errors where owner='SCHEMANAME' and
name='TRIGGERNAME' and type='TRIGGER'
order by sequence, line, position;

This query can also be used for package and package body compilation errors/warnings. Instead of type='TRIGGER' use (type='PACKAGE' or type='PACKAGE BODY')

Note, the user you're running the query as, needs to have select priveleges to dba_errors, such as the SYS user.