Accessing remote Oracle DB from Siebel server script and executing insert with bind variables

 

It is very common to be asked to develop an interface between Siebel and other systems. It could be that other system has some kind of API or maybe it doesn’t . In my case , remote system doesn’t has any API, but you can access its database. So how can you do it from Siebel server side script?

First of all lets create  some dummy table that will be used as insert target

create table test_insert (data varchar2)

Now define in some Business Service new server script.

Explanation of the code can be found in the code comments

function Service_PreInvokeMethod (MethodName, Inputs, Outputs) {
   if (MethodName=="Connect")
   {
       //obtain connection object
       var oConnection = COMCreateObject("ADODB.Connection");
      //connection objects
        var cmd;
       var myparam;
       var res;
       var adVarChar =200;
       var adParamInput =1;
       var adCmdText = 1;

       try
       {
       //here I use TNS style connection string to connect to remote Oracle database 
       oConnection.ConnectionString = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=130.39.120.58)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));
uid=system;pwd=manager;";
       oConnection.Open();
      //create command object
       cmd = COMCreateObject("ADODB.Command");
      //assign connection to command
       cmd.ActiveConnection = oConnection;
      //this is SQL statement that I am about to execute
       var insertStmt = "INSERT INTO TEST_INSERT (DATA) VALUES (?)";
  
       cmd.CommandText = insertStmt;
       // command type is - text
       cmd.CommandType = adCmdText;
       //create and append new parameter
       // 1 = first parameter
       //  adVarChar = database column type
      //   adParamInput = parameter direction (input)
      //   5 - the lenght of the parameter value
      //   TEST2 - parameter value
       cmd.Parameters.Append(cmd.CreateParameter(1, adVarChar, adParamInput,5,"TEST2"));

       // Now, inserting into the data source...
       cmd.Prepared = true;
       //execute update - no commit is needed
       res = cmd.Execute();

        Outputs.SetProperty("DONE","DONE");


       }
       catch(e)
       {
          Outputs.SetProperty("DONE","NOT OK "+e.toString());
       }
       finally
       {
        //release variables
         adCmdText =null;
         adParamInput = null;
         adVarChar = null;
         myparam = null;
         cmd = null;
         oConnection = null;
       }
          return (CancelOperation);
   }
     return (ContinueOperation);
}

Dynamically Execute SQL statement stored in CLOB field with bind variables

 

  I saw many examples in the past when SQL statement was actually stored in CLOB field and programmer had to execute the statement dynamically.

All Oracle PL/SQL developers are familiar with “execute immediate” statement, but unfortunately  it cannot use CLOB field as an input.

But it is not the only way to execute the code dynamically.  Oracle also provides DBMS_SQL package that can execute the code dynamically and it can get the statement as an array data type. We will use it

   Here how you can do it.

Let assume that your SQL will be stored in the following variable

l_stmt         CLOB;

Now we need to split the CLOB to chunks that will became a member of array I mentioned previously

v_upperbound NUMBER;

This is array definition

v_sql        DBMS_SQL.VARCHAR2S;

Calculate array length

v_upperbound := CEIL(DBMS_LOB.GETLENGTH(l_stmt)/256);

Fill array members:

FOR i IN 1..v_upperbound
LOOP
         v_sql(i) := DBMS_LOB.SUBSTR(l_stmt
                                  ,256 -- amount
                                  ,((i-1)*256)+1 -- offset
                                  );
END LOOP;

 

Create new cursor:

v_cur        INTEGER;

v_cur := DBMS_SQL.OPEN_CURSOR;

 

Prepare your statement for execution , but parsing array members

DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);

You can also use bind variables at this point

dbms_sql.bind_variable( v_cur, ':USER_NAME', ‘SYSADMIN’);

All what is left is to execute the statement

v_ret        NUMBER;

v_ret := DBMS_SQL.EXECUTE(v_cur);

 

Bellow if full example that uses approach described above

declare

  l_stmt       CLOB;
  v_upperbound NUMBER;
  v_cur        INTEGER;
  v_sql        DBMS_SQL.VARCHAR2S;
  v_ret        NUMBER;

begin
   -- get sql statement into CLOB
  select statement
    into l_stmt
    from XX_NEW_STATEMENTS_V --some view that holds CLOB 
                                         --with SQL
   where statement_type = 'SETUP'
     and seq_num = 10;

  v_upperbound := CEIL(DBMS_LOB.GETLENGTH(l_stmt) / 256);

  FOR i IN 1 .. v_upperbound LOOP
    v_sql(i) := DBMS_LOB.SUBSTR(l_stmt, -- clob statement
                                256,     -- amount
                                ((i - 1) * 256) + 1 
                                );
  END LOOP;
        
  v_cur := DBMS_SQL.OPEN_CURSOR;
        -- parse sql statement
  DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
        -- use bind variable if you need
  dbms_sql.bind_variable(v_cur, ':USER_ID', 235341);
  -- execute
  v_ret := DBMS_SQL.EXECUTE(v_cur);

end;