EIM Table IF_ROW_BATCH_NUM - SQL Server

When loading the EIM tables in Siebel use the logic below to populate the IF_ROW_BATCH_NUM attribute

SQL Server

CASE
WHEN ROW_NUMBER() OVER (ORDER BY ROW_ID)%5000 >= 0 AND
             ROW_NUMBER() OVER (ORDER BY ROW_ID)%5000 <= 5000
THEN
            CEILING(ROW_NUMBER() OVER (ORDER BY ROW_ID)/5000+10000)
ELSE   
            ROW_NUMBER() OVER (ORDER BY ROW_ID)

Oracle
Replace the CASE statement above to DECODE

Siebel Local DB

Siebel Local DB ::
If you forget the username and password to access the Local DB here is the solution

Siebel Version 6.x
User Name : SQL
Password : DBA

Siebel Version > 7.x
User Name : SQL
Password : Enterprise Server Name in Uppercase

SQL Server: Update Records

DECLARE @B_RID NVARCHAR(15);
DECLARE @B_LOC NVARCHAR(50);
DECLARE @B_NAME NVARCHAR(100);
DECLARE @AM_LOC NVARCHAR(50);
DECLARE @AM_NAME NVARCHAR(100);
DECLARE C1 CURSOR FOR
SELECT BASE.ROW_ID, BASE.LOC, BASE.NAME, AUTO.ACCOUNT_ID, AUTO.ACCOUNT_NAME FROM S_ORG_EXT BASE, UCM_ACCT_POST_AUTO_MATCH AUTO
WHERE BASE.LOC = AUTO.ACCOUNT_ID AND BASE.LOC IN (
SELECT DISTINCT ORG_ACCNT_LOC FROM EIM_FN_CIF_SYST WHERE IF_ROW_STAT = 'PARTIALLY_IMPORTED')
ORDER BY BASE.LOC;
OPEN C1;
FETCH NEXT FROM C1 INTO @B_RID, @B_LOC, @B_NAME, @AM_LOC, @AM_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE S_ORG_EXT SET NAME = @AM_NAME WHERE ROW_ID = @B_RID
    FETCH NEXT FROM C1 INTO @B_RID, @B_LOC, @B_NAME, @AM_LOC, @AM_NAME
END
CLOSE C1
DEALLOCATE C1

Arithmetic overflow error converting expression to data type int

The "Arithmetic overflow error converting expression to data type int." error occurs when the computing field's result is bigger than the size of the data type defined.

The error can also occur using COUNT(*). Try using the COUNT_BIG(*)

Generate Address Name for EIM

Many times we have to replicate the ADDR_NAME configuration/calculation for Initial load.

Below is the query to generate the exact string as it gets generated from UI. This is written in SQL Server and the inbuilt functions can be changed to replicate the same in Oracle

ADDR NAME
------------------------
SELECT
LEFT
(ADDR,
CASE
WHEN (100-CASE
WHEN STATE IS NULL
THEN 0
ELSE LEN(STATE)
END
-
CASE
WHEN CITY IS NULL
THEN 0
ELSE LEN(CITY)
END
- 4) < (LEN(ADDR))
THEN 100-CASE
WHEN STATE IS NULL
THEN 0
ELSE LEN(STATE)
END
-
CASE
WHEN CITY IS NULL
THEN 0
ELSE LEN(CITY)
END - 4
ELSE LEN(ADDR)
END)
+
CASE WHEN ADDR_LINE_2 IS NOT NULL THEN ', ' ELSE '' END
+
ISNULL(ADDR_LINE_2,'')
+
CASE
WHEN ADDR IS NOT NULL AND CITY IS NOT NULL OR STATE IS NOT NULL
THEN ', '
ELSE ''
END
+
CITY
+
CASE WHEN (STATE IS NOT NULL AND ADDR IS NOT NULL OR CITY IS NOT NULL)
THEN (CASE WHEN CITY IS NOT NULL THEN ', ' ELSE '' END)
ELSE ''
END
+
STATE
FROM dbo.S_ADDR_PER

For Oracle Replace
+ with (Double Pipe)
ISNULL with NVL
LEN with LENGTH
LEFT with SUBSTR
CASE WHEN THEN ELSE END with DECODE

Java: Passing arrays to a stored procedure with PLSQL Table Type

Not all PL/SQL arrays can be direcly accessed using JDBC. Procedures or Functions that take arrays and are declared inside packages instead of with “CREATE TYPE” can not be called using JDBC. This is a limitation of Oracle.
Approach to solve this issue:
1. Write an additonal wrapper procedure that converts an array created with CREATE TYPE into the format required by your procedure
If you can not change the procedure itself or it is already used by a large base of existing code you may have to go with option 2 of writing an additional wrapper procedure that takes parameters usable by JDBC:

Original Code:
Create or Replace Package tms_user_autocode IS
Type DeriveValuesREC IS RECORD (
DefLevelId number(10),
ColumnName VARCHAR2(30),
ValueText tms_dict_contents.term%TYPE,
ClassificationCode VARCHAR2(1));

Type DeriveValuesTAB is TABLE of DeriveValuesREC INDEX BY BINARY_INTEGER;

FUNCTION ClassifyTerm(
pDefDictionaryId IN NUMBER
pDefDomainId IN NUMBER
pTerm IN VARCHAR2
pSourceTermId IN NUMBER
pOccurrenceId IN NUMBER
pSourceTermAltKey IN VARCHAR2
pNoOmissionFlag IN VARCHAR2
pDefIntegrationKey IN VARCHAR2
pDefInstanceName IN VARCHAR2
pXArea IN NUMBER
pExtValue1 IN VARCHAR2
pExtValue2 IN VARCHAR2
pExtValue3 IN VARCHAR2
pExtValue4 IN VARCHAR2
pExtValue5 IN VARCHAR2
pExtValue6 IN VARCHAR2
pExtValue7 IN VARCHAR2
pExtValue8 IN VARCHAR2
pOmissionStatus IN OUT VARCHAR2
pOmissionOwner IN OUT VARCHAR2
pActionText IN OUT VARCHAR2
pVTAid OUT NUMBER
pSearchID OUT NUMBER
pDeriveValues IN OUT tms_user_autocode.DeriveValesTAB) RETURN PLS_INTEGER

END tms_user_autocode;
Wrapper Procedure:

CREATE OR REPLACE
PROCEDURE CTRM_tms_user_autocode
(jDefDictionaryId IN NUMBER,
jDefDomainId IN NUMBER,
jTerm IN VARCHAR2,
jSourceTermId IN NUMBER,
jOccurrenceId IN NUMBER,
jSourceTermAltKey IN VARCHAR2,
jNoOmissionFlag IN VARCHAR2,
jDefIntegrationKey IN VARCHAR2,
jDefInstanceName IN VARCHAR2,
jXArea IN NUMBER,
jExtValue1 IN VARCHAR2,
jExtValue2 IN VARCHAR2,
jExtValue3 IN VARCHAR2,
jExtValue4 IN VARCHAR2,
jExtValue5 IN VARCHAR2,
jExtValue6 IN VARCHAR2,
jExtValue7 IN VARCHAR2,
jExtValue8 IN VARCHAR2,
jOmissionStatus IN OUT VARCHAR2,
jOmissionOwner IN OUT VARCHAR2,
jActionText IN OUT VARCHAR2,
jSearchID OUT NUMBER,
jVTAid OUT NUMBER,
jDeriveValues IN OUT CTRM_DeriveValuesTAB
returnValue OUT Number) AS

l_array tms_user_autocode.DeriveValuesTAB;
l_record tms_user_autocode.DeriveValuesREC;

BEGIN
– Load our JDBC table into the PL/SQL one…
l_array.delete;
FOR i IN jDeriveValues.FIRST
.. jDeriveValues.LAST LOOP
l_record := NULL;

l_record.DefLevelId := jDeriveValues(i).DefLevelId;
l_record.ColumnName := jDeriveValues(i).ColumnName;
l_record.ValueText := jDeriveValues(i).ValueText;
l_record.ClassificationCode := jDeriveValues(i).ClassificationCode;
l_array(i) := l_record;

END LOOP;
returnValue := tms.TMS_user_AUTOCODE.ClassifyTerm(
jDefDictionaryId
, jDefDomainId
, jTerm
, jSourceTermId
, jOccurrenceId
, jSourceTermAltKey
, jNoOmissionFlag
, jDefIntegrationKey
, jDefInstanceName
, jXArea
, jExtValue1
, jExtValue2
, jExtValue3
, jExtValue4
, jExtValue5
, jExtValue6
, jExtValue7
, jExtValue8
, jOmissionStatus
, jOmissionOwner
, jActionText
, jSearchId
, jVTAid
,l_array
);

FOR i IN l_array.FIRST .. l_array.LAST LOOP
jDeriveValues(i).DefLevelId := l_array(i).DefLevelId;
jDeriveValues(i).ColumnName := l_array(i).ColumnName;
jDeriveValues(i).ValueText := l_array(i).ValueText;
jDeriveValues(i).ClassificationCode := l_array(i).ClassificationCode;

END LOOP;
–
END;

Java program :

import java.sql.*;

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class Test {
public static void main(String[] args) throws SQLException {
CallableStatement proc = null;
try {
Connection conn=null;
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
//Establish a connection
conn = DriverManager.getConnection
(”jdbc:oracle:oci8:@123.WORLD”,
“scott”, “tiger);
System.out.println(”connection success0″);

int DictId = 8;
int DomainId = 1;
String Term = “BLOOD”;
int SourceId=0;
String pOccurrenceId=”0″;
String pSourceTermAltKey =”";
String pDefIntegrationKey=”CTRM”;
String pDefInstanceName=”OCP451.TEST.COM”;
int pXArea=1;
String Flag=”N”;
String pExtValue1=”";
String pExtValue2=”";
String pExtValue3=”";
String pExtValue4=”";
String pExtValue5=”";
String pExtValue6=”";
String pExtValue7=”";
String pExtValue8=”";
String ActionText=”";
int returnValue=0;
int sid=0;
String oStat=”";
String oOwner=”";
//String xsystem=”";
int vtaid=0;


// create the ARRAY by calling the constructor


System.out.println(”connection success1.1″);
//First, declare the Object arrays that will store the data.
Object [] p1recobj = {new Integer(811),”TERM”,”",”"};
Object [] p2recobj = {new Integer(811),”DICT_CONTENT_CODE”,”",”"};
Object [] p3recobj = {new Integer(812),”TERM”,”",”"};
Object [] p4recobj = {new Integer(812),”DICT_CONTENT_CODE”,”",”"};
System.out.println(”connection success1.2″);
//Declare the Object Arrays to hold the STRUCTS.
Object [] p1arrobj;
//Object [] p2arrobj;
System.out.println(”connection success1.3″);
// Declare two descriptors, one for the ARRAY TYPE
// and one for the OBJECT TYPE.
StructDescriptor desc1 = StructDescriptor.createDescriptor(”CTRM_DERIVEVALUESREC”, conn);
ArrayDescriptor desc2 = ArrayDescriptor.createDescriptor(”CTRM_DERIVEVALUESTAB”, conn);
System.out.println(”connection success1.4″);
// Create the STRUCT objects to associate the host objects
// with the database records.
STRUCT p1struct = new STRUCT(desc1,conn,p1recobj);
STRUCT p2struct = new STRUCT(desc1,conn,p2recobj);
STRUCT p3struct = new STRUCT(desc1,conn,p3recobj);
STRUCT p4struct = new STRUCT(desc1,conn,p4recobj);
System.out.println(”connection success1.5″);
// Initialize the Input array object - to an array of STRUCT Objects.
p1arrobj = new Object []{p1struct,p2struct,p3struct,p4struct};
System.out.println(”connection success1.6″);
// Set up the ARRAY object.
ARRAY p1arr = new ARRAY(desc2,conn,p1arrobj);
// ARRAY p2arr;
System.out.println(”connection success1.7″);
proc = conn.prepareCall(”{ call CTRM_tms_user_autocode(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}”);
System.out.println(”connection success1.8″);
proc.setInt(1, DictId);
proc.setInt(2, DomainId);
proc.setString(3, Term);
proc.setInt(4, SourceId);
proc.setString(5, pOccurrenceId);
proc.setString(6, pSourceTermAltKey);
proc.setString(7, Flag);
proc.setString(8, pDefIntegrationKey);
proc.setString(9, pDefInstanceName);
proc.setInt(10, pXArea);
proc.setString(11, pExtValue1);
proc.setString(12, pExtValue2);
proc.setString(13, pExtValue3);
proc.setString(14, pExtValue4);
proc.setString(15, pExtValue5);
proc.setString(16, pExtValue6);
proc.setString(17, pExtValue7);
proc.setString(18, pExtValue8);
proc.setString(19, oStat);
proc.setString(20, oOwner);
proc.setString(21, ActionText);
proc.setInt(22,sid);
proc.setInt(23, vtaid);
proc.setArray(24,p1arr);
proc.setInt(25,returnValue);
//Registering OUT parameter
proc.registerOutParameter (19, Types.VARCHAR);
proc.registerOutParameter (20, Types.VARCHAR);
proc.registerOutParameter (21, Types.VARCHAR);
proc.registerOutParameter (22, Types.INTEGER);
proc.registerOutParameter (23, Types.INTEGER);
proc.registerOutParameter(24,OracleTypes.ARRAY,”CTRM_DERIVEVALUESTAB”);
proc.registerOutParameter (25, Types.INTEGER);
System.out.println(”connection success1.9″);
proc.execute();
System.out.println(”connection success2″);
// Associate the returned arrays with the ARRAY objects.
oStat=proc.getString(19);
oOwner=proc.getString(20);
ActionText=proc.getString(21);
sid = proc.getInt(22);
vtaid = proc.getInt(23);
p1arr = (ARRAY) proc.getArray(24);
returnValue=proc.getInt(25);
System.out.println(”connection success2.1″);
//Get the data back into the data arrays.
p1arrobj = (Object [])p1arr.getArray();
System.out.println(”connection success2.2″);
// Get the data records from each array element (which is of type STRUCT).
p1recobj = ((STRUCT)p1arrobj[0]).getAttributes();
p2recobj = ((STRUCT)p1arrobj[1]).getAttributes();
p3recobj = ((STRUCT)p1arrobj[2]).getAttributes();
p4recobj = ((STRUCT)p1arrobj[3]).getAttributes();
System.out.println(”connection success2.3″);
//Show the results:
System.out.println(”returnValue “+returnValue);
System.out.println(”ActionText “+ActionText);
System.out.println(”OmissStat “+oStat);
System.out.println(”OmissOwner “+oOwner);
System.out.println(”SearchID “+sid);
System.out.println(”VTAID “+vtaid);
System.out.println(”First Object is now “+p1recobj[0]+” and “+p1recobj[1]+” and “+p1recobj[2]+” and “+p1recobj[3]);
System.out.println(” “+p2recobj[0]+” and “+p2recobj[1]+” and “+p2recobj[2]+” and “+p2recobj[3]);
System.out.println(”Second Object is now “+p3recobj[0]+” and “+p3recobj[1]+” and “+p3recobj[2]+” and “+p3recobj[3]);
System.out.println(” “+p4recobj[0]+” and “+p4recobj[1]+” and “+p4recobj[2]+” and “+p4recobj[3]);



conn.commit();

} catch (Exception e) {

System.out.println(”e>”+e);

} finally {
if (proc != null)
proc.close();
}
}
}

2008 June Visa Bulletin Update

EB1 China, India, Mexico, Philippines, ROW-C
EB2 India- 01 APR 04, China, Mexico, Philippines, ROW-C
EB3
China- 01 MAR 06,
India- 01 NOV 01