Java.SQL.Exception The Network Adapter could not establish the connection

1. The port number can be 1526 or 1521. Confirm with tnsnames.ora on the Oracle Server.
2. If that is all right, then use the IP address of the Oracle host, rather than the name.

Empty Values Assigned to Variables in webMethods

Only when your are testing a Flow Service from webMethods Developer, you can pass an empty string ( or zero-length string ) to a service.



If you want to pass empty variables (variables that have no value), enable the "Include empty values for String Types" check box. When you enable this option, empty String variables are passed with a zero-length value. If you do not enable this option, the variables will be removed from pipelines.

Map the first name from a document list

1. Add a MAP to your process flow
2. In Properties window, enter "Get the first name" to property Comments.
3. Drag a link line between records.name in Pipeline In to firstName in Pipeline Out.
4. Notice that when you click on the link, it displays the link's Properties.
5. Click on the "Edit..." button of the Indices property.
6. Enter 0 to the records text box, and then click on OK button
7. Notice that the color of the link changed to blue like below:

Insert SYSDATE via JDBC Adapter Service

This is how you can insert a Date + Time to Oracle DATE field in webMethods:

1. Open a new Adapter Serivce
-> Select "JDBC Adapter" type, click Next
-> Select your database "Adapter Connection Alias", click Next
-> Select "InsertSQL" Template, click Next
-> Enter the name of your new Adapter Service, click Finish

2. Select your table under Table tab

3. Add the system date column under INSERT tab.

4. Highlight your date column
-> Double click on the Expression field
-> Enter TO_DATE(TO_CHAR(sysdate, 'DD-MON-YYYY hh:mi:ss'),
'DD-MON-YYYY hh:mi:ss')

Oracle Triggers

CREATE OR REPLACE TRIGGER EBP.INSERT_EBP_TRADING_PARTNERS
BEFORE INSERT
ON EBP.EBP_TRADING_PARTNERS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
v_fax_subject_line VARCHAR2(60);
BEGIN
tmpVar := 0;

SELECT SUBJECT_LINE INTO v_fax_subject_line FROM EBP_BILLERS WHERE BILLER_ID = :NEW.BILLER_ID;

IF :NEW.FAX_SUBJECT IS NULL THEN
:NEW.FAX_SUBJECT := v_fax_subject_line;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Warning: The Biller_Id does not exist in EBP_BILLERS table.');

END ;
/

Select between dates in Oracle SQL

SQL> with t as 
(select to_date('01/01/2007',
'dd/mm/yyyy') as update_date from dual union all
2    select to_date('01/01/2007',
'dd/mm/yyyy') from dual union all
3    select to_date('03/01/2007',
'dd/mm/yyyy') from dual union all
4    select to_date('03/01/2007',
'dd/mm/yyyy') from dual union all
5    select to_date('03/01/2007', 
'dd/mm/yyyy') from dual union all
6    select to_date('04/01/2007', 
'dd/mm/yyyy') from dual union all
7    select to_date('05/01/2007', 
'dd/mm/yyyy') from dual union all
8    select to_date('07/01/2007', 
'dd/mm/yyyy') from dual union all
9    select to_date('07/01/2007', 
'dd/mm/yyyy') from dual)
10  -- end of test data
11  select to_char(x.UPDATE_DATE,  
'YYYY-MM-DD') as update_date, 
DECODE(t.update_date, NULL, 0, count (*)) 
as count
12  from (select to_date('2007-01-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS')+
(rownum-1) as update_date
13        from   dual
14        connect by rownum <= 
(to_date('2007-02-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS')-
15             to_date('2007-01-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS'))) x
16        LEFT OUTER JOIN t 
ON (x.update_date = t.update_date)
17  where x.UPDATE_DATE >= 
to_date('2007-01-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS')
18  and x.UPDATE_DATE < 
color="navy">'2007-02-01 00:00:00', 
'YYYY-MM-DD HH24:MI:SS')
19  group by to_char(x.UPDATE_DATE, 'YYYY-MM-DD'), 
t.update_date
20  order by 1
21  /

UPDATE_DAT      COUNT
---------- ----------
2007-01-01          2
2007-01-02          0
2007-01-03          3
2007-01-04          1
2007-01-05          1

Regular Expression Tips

JavaScript Tester: http://codespec.blogspot.com/2007/10/regular-expression-tips.html

foob.*r : matchs strings like 'foobar', 'foobalkjdflkj9r' and 'foobr'
foob.+r : matchs strings like 'foobar', 'foobalkjdflkj9r' but not 'foobr'
foob.?r : matchs strings like 'foobar', 'foobbr' and 'foobr' but not 'foobalkj9r'
fooba{2}r : matchs the string 'foobaar'
fooba{2,}r : matchs strings like 'foobaar', 'foobaaar', 'foobaaaar' etc.
fooba{2,3}r : matchs strings like 'foobaar', or 'foobaaar' but not 'foobaaaar'

Metacharacters \1 through \9 are interpreted as backreferences. \ matches previously matched subexpression #.

Examples:

(.)\1+ matchs 'aaaa' and 'cc'.
(.+)\1+ also match 'abab' and '123123'
(['"]?)(\d+)\1 matchs '"13" (in double quotes), or '4' (in single quotes) or 77 (without quotes) etc

Metacharacters - line separators

^ start of line
$ end of line
\A start of text
\Z end of text
. any character in line

Examples:

^foobar matchs string 'foobar' only if it's at the beginning of line
foobar$ matchs string 'foobar' only if it's at the end of line
\^foobar$ matchs string 'foobar' only if it's the only string in line
\foob.r matchs strings like 'foobar', 'foobbr', 'foob1r' and so on


Metacharacters - predefined classes

\w an alphanumeric character (including "_")
\W a nonalphanumeric
\d a numeric character
\D a non-numeric
\s any space (same as [ \t\n\r\f])
\S a non space

You may use \w, \d and \s within custom character classes .

Examples:

\foob\dr matchs strings like 'foob1r', ''foob6r' and so on but not 'foobar', 'foobbr' and so on
\foob[\w\s]r matchs strings like 'foobar', 'foob r', 'foobbr' and so on but not 'foob1r', 'foob=r' and so on