Some Helpfule DB Commands

Grant and Revoke Commands:
---------------------------------------------
You are granting all privileges like insert, delete, update etc to USER2 on table EMPLOYEE_DTL.

GRANT ALL ON EMPLOYEE_DTL TO USER2;

You are revoking all privileges like insert, delete, update etc from USER2 on table EMPLOYEE_DTL.

REVOKE ALL ON EMPLOYEE_DTL FROM USER2;


Truncate Table:
------------------------

This command deletes all records permanently. You cannot rollback.

TRUNCATE TABLE EMPLOYEE_DTL;

How to rename a table:
------------------------------------

RENAME EMPLOYEE_DTL TO EMPLOYEE_DTL_BKP;
RENAME EMPLOYEE_DTL_BKP TO EMPLOYEE_DTL;

To clear Oracle screen:
-----------------------------------
This is not deleting the records from the database. This command clears the screen on which you are working.

CL SCR;

Spool:
-----------
This command helps to save the query results executed from sql prompt to a file. After executing this command, open the file SPOOL_TABLES.TXT. You will see the results of the query.

SPOOL ON;
SPOOL D:/SPOOL_TABLES.TXT;
SELECT TABLE_NAME FROM DICT;
SPOOL OFF;

How to find out the schema name on which you are working
------------------------------------------------------------------------------------------

SHOW USER;

How to execute (run) a file?

Procedures, Functions, Packages, Triggers etc are created in a file and then created on the database. To create those object, run those files, by using the following command. Here db.txt is the file name that contains the programmatical code and stored in D: drive

STA D:/db.txt;

How to switch from one schema to another schema:
-----------------------------------------------------------------------------------

How to switch from one schema(user1/user1@oracledb) to another schema(user2/user2@oracledb). Assume you have logged in a user1/user1@oracledb where username = user1, password = user1 and oracle instance = oracledb. After typing the following command, you will be switched to USER2 schema.

CONNECT USER2/USER2@ORACLEDB;

If you want to hide the password

connect user2@oracledb;
Oracle will prompt for password and you enter the password "user2.

Select statement - To find out the oracle's views:
--------------------------------------------------------------------------

This select statement retrieves views from Oracle's data dictionary.

SELECT * FROM DICT;

Select statement - To find out the tables under your schema:
---------------------------------------------------------------------------------------------

SELECT * FROM TAB;

There are several categories of dictionary views like "ALL", "GV$", "V$", "USER", "DBA", "SYS" etc. If you have proper privileges, you can get to know about the databases, schemas, tablespace, segments, extents, blocks, partitions and so on. Here are some of the important views, that a data modeler has to know about. The following information has been sourced from www.oracle.com. For details, please go to their official website www.oracle.com.

Prefix the following views with "Select * from " to see the results specified under the description column.

Important Data Dictionary Views:
-----------------------------------------------------------

View Name                                    Description
----------------------                                ------------------------
ALL_USERS               : Information about all users of the database
USER_OBJECTS       : Objects owned by the user
USER_DB_LINKS       : Database links owned by the user
USER_TABLES               :Description of the user's own relational tables
USER_COL_COMMENTS    :Comments on columns of user's tables and views
USER_VIEWS                     :Description of the user's own views
USER_VIEWS                     :Description of the user's own views
USER_MVIEWS              :All materialized views in the database
USER_SYNONYMS              :The user's private synonyms
USER_TRIGGERS              :Triggers owned by the user
USER_PROCEDURES      :Description of the users own procedures
USER_SEQUENCES               :Description of the user's own SEQUENCEs
USER_COL_PRIVS            :Grants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADE    :All grants on columns of objects owned by the user
USER_COL_PRIVS_RECD            :Grants on columns for which the user is the grantee
USER_ROLE_PRIVS                    :Roles granted to current user
USER_CONSTRAINTS            :Constraint definitions on user's own tables
USER_CONS_COLUMNS            :Information about accessible columns in constraint definitions
USER_DEPENDENCIES             :Dependencies to and from a users objects
USER_INDEXES                     :Description of the user's own indexes
USER_IND_COLUMNS              :COLUMNs comprising user's Indexes and Indexes on user's TABLES
USER_SOURCE                     :Source of stored objects accessible to the user

Using BPEL process to Read a file over the HTTP / Web (A Remote Location)

Using BPEL process to Read a file over the HTTP / Web (A Remote Location)  .... ex : http://ww1.microchip.com/downloads/en/DeviceDoc/30292c.pdf



To do this you would need to use Java Embedding in your BPEL process. (To read the URL and get the data in base64 format).  We will later right the read file into a file and aswell as into a table (with BLOB column for the read file) using DB adapter



Pre-Setup:
==========

(1) Set Transaction time out parameter in 3 places

A.1.1 Setting Properties for BPEL Processes to Successfully Complete and Catch Exception Errors

The values to which you set the transaction-timeout and syncMaxWaitTime properties can impact whether a transaction scope successfully completes or times out and catches exception errors. For example, assume you have two processes:

    * TimeoutSubprocess (A synchronous detail process that includes a wait activity set to three minutes)
    * TimeoutMainProcess (An asynchronous main process that calls the TimeoutSubprocess)

If syncMaxWaitTime is set to 45 seconds (the default value) and transaction-timeout is set to 30 seconds, after 45 seconds the main process continues running and does not successfully complete and catch the following exception error as expected:

com.oracle.bpel.client.delivery.ReceiveTimeOutException


In the domain.log file, the following exception error displays:

An exception occurred during transaction completion:; nested exception is:
javax.transaction.RollbackException: Timed out
javax.transaction.RollbackException: Timed out


Perform the following procedures for the main process to successfully complete and catch the exception error.

   A. Property: transaction-timeout
      File Location: SOA_Oracle_Home\j2ee\home\config\transaction-manager.xml
      Value to be set to: Larger than the transaction-timeout value in orion-ejb-jar.xml and the syncMaxWaitTime value
      Example: 7200

   B. Property: transaction-timeout  (every transaction-timeout parameter in this file)
      File Location: SOA_Oracle_Home\j2ee\home\application-deployments\orabpel\ejb_ob_engine\orion-ejb-jar.xml
      Value to be set to: Less than the transaction-timeout value in transaction-manager.xml.
      Example: 3600  

   C. Property: syncMaxWaitTime
      File Location: SOA_Oracle_Home\bpel\domains\domain_name\config\domain.xml  , where domain_name is the name of the domain to which you are deploying.
      Value to be set to: Less than the transaction-timeout value in orion-ejb-jar.xml.
      Example: 300

      This causes the main process to successfully complete and catch the exception error.


(2) Set Transaction type as not 'Global' in while creating the datasources (JNDI Entry)

(3) add SetBigStringTryBlob = true  property to connection pool
eg:
<connection-pool name="MY_POOL" connection-retry-interval="5" max-connect-attempts="30" min-connections="10" SetBigStringTryBlob="true">
<connection-factory factory-class="oracle.jdbc.pool.OracleDataSource" user="user" password="password" url="jdbc:oracle:thin:@//myserver:1521/myservice" commit-record-table-name=""/>
</connection-pool>

(4) Has to be async process

(5) while writing the data into BLOB , the data has to be in base64 binary format.



===================================
Java Embedding :

import these in the bpel code:


    <bpelx:exec import="java.util.*"/>
    <bpelx:exec import="java.io.*"/>
    <bpelx:exec import="java.lang.*"/>
    <bpelx:exec import="java.math.*"/>
    <bpelx:exec import="java.net.*"/>
    <bpelx:exec import="com.collaxa.common.util.Base64Decoder"/>
    <bpelx:exec import="com.collaxa.common.util.Base64Encoder"/>



write this java code in the Java Embedding block:

try{            
        URL u = new URL("http://ww1.microchip.com/downloads/en/DeviceDoc/30292c.pdf");            
        URLConnection uc = u.openConnection();            
        String contentType = uc.getContentType();            
        int contentLength = uc.getContentLength();            
        InputStream raw = uc.getInputStream();            
        InputStream in = new BufferedInputStream(raw);            
        byte[] data = new byte[contentLength];            
        int bytesRead = 0;            
        int offset = 0;            
        while (offset < contentLength) {            
          bytesRead = in.read(data, offset, data.length - offset);            
          if (bytesRead == -1)            
            break;            
          offset += bytesRead;            
        }            
        in.close();            
            
        if (offset != contentLength) {            
          throw new IOException("Only read " + offset + " bytes; Expected " + contentLength + " bytes");            
        }            
            
 //       String filename = u.getFile().substring(filename.lastIndexOf('/') + 1);            
          String filename = "C:\\Projects\\Tests\\TestFile.pdf";            
        FileOutputStream out = new FileOutputStream(filename);            
        out.write(data);         
                
        out.flush();            
        out.close();          
        Base64Encoder Encoder = new Base64Encoder();  
        String encodedString = Base64Encoder.encode(data);  
setVariableData("BLOBPayload",encodedString);           
    }            
        catch (MalformedURLException e) {            
                        e.printStackTrace();            
                } catch (IOException e) {            
                        e.printStackTrace();            
                }




===============================

Crate a table where you want to insert the data with a BLOB column type and insert into this table using the DB adapter.

===================

Oracle ESB Debugging in SOA

Debugging ESB Instances: (ref: http://soafusion.blogspot.com/2008/02/how-to-enable-debugging-in-oracle-esb.html)

Quite some times it is little frustrating to understand what happens inside Oracle ESB. It is because of Error reporting which is very limited and not to the point.

This can be resolved by enabling the ESB log level FINE in the Oracle application server control and below are the steps to do that.


1. Login into the Application Server Control at http://localhost:8888/em
2. Click on the oc4j_soa instance and Administration tab.
3. Click on Logger Configuration.
4. Now expand Root Logger / oracle to display the logger classes and the Log Level settings.
5. In the Log Level list, select the level FINE for the following classes
. oracle.tip.esb.server.common
. oracle.tip.esb.common.control
. oracle.tip.esb.common.system
. oracle.tip.esb.server.service.impl.inadapter

You DONT need to Bounce the server if this change is done from the EM console.

Look for the log file in : $ORACLE_HOME\j2ee\oc4j_soa\log\oc4j_soa_default_group_1\oc4j\log.xml

For more detailed information regarding the  esb.classes refer to the following blog:

http://www.gwr.no/?p=217

How to Post HTML / XMl code in your Blog

Go to the following site and type in  your code which you want to publish. Encode it, copy and paste the encoded code in your Blog. As simple as that !

http://centricle.com/tools/html-entities/

Jave Base64 Decoder for decoding Opaque data

Use the following java code to decode opaque data. Very useful if you are capturing the rejected messages as opaque data and want to translate the data:

String input = (String)getVariableData("OpaqueString");          
Base64Decoder Decoder = new Base64Decoder();         
         
         
try                  
{                  
String decoded = Base64Decoder.decode(input);                    
 setVariableData("ErrorPayload",decoded);                   
}                  
catch(UnsupportedEncodingException uee)                  
{                  
uee.printStackTrace();                   
}

How to avoid multiple instances of BPEL trying to access the same message in a High Available Environemnt .....(Singleton BPEL processes) You have to configure the bpel.xml file in your BPEL process to include the following 'clusterGroupId' property name: .....

SomeServicename.wsdl MyClusterName PollingLogicalDeleteService_ptt

This feature uses JGroups underneath for the implementation, hence the clusterGroupId property. These features require cluster and JGroups configuration in

$ORACLE_HOME/bpel/system/config/collaxa-config.xml and $ORACLE_HOME/system/config/jgroups-protocol.xml. All BPEL instances

participating in the cluster must share the same cluster name in collaxa-config.xml,and the same multicast address and port in jgroups-protocol.xml.

Rejection Message Handling

The "Rejected Message handlers" come in four different flavors. They must be defined as
part of the Activation Agent properties.
·  File system based Rejection Handler
<property name="rejectedMessageHandlers">
file://<directory-path>
</property>
for example
<property name="rejectedMessageHandlers">
file://C:/orabpel/domains/default/rejectedMessages
</property>
This rejection handler is straight forward and the bad messages are written to the
configured directory using the file name pattern
INVALID_MSG_ + <process-name> + <operation-name> + <current-time>

·  RAW Oracle Advanced Queue based Rejection Handler
<property name="rejectedMessageHandlers">
queue://jdbc:oracle:thin:@<db-host>:<tns-port>:<sid>|<user>/<password>|<queue-name>
</property>
The <password> can be encrypted. For example
<property name="rejectedMessageHandlers">
queue://jdbc:oracle:thin:@acme-sun:1521:ORCL|scott/tiger|JCA_BAD_MESSAGES
</property>
This rejection handler allows the user to designate a Oracle Rdbms RAW AQ queue
as the rejection storage. Note that the ":" and "|" must appear in the places shown.
Also note that the password can be encrypted using the encrypt.bat utility in
orabpel/bin.
·  BPEL Process Rejection Handler
<property name="rejectedMessageHandlers">
bpel://<bpel-domain[:<password>]>|<process-name>|<operation-name>|<input-message-partname>
</property>
The <password> for the BPEL domain can be encrypted - "[]" means that the
property is optional. For example
<property name="rejectedMessageHandlers">
bpel://default|JCA-RejectionHandler|handleRejection|message
</property>
This rejection handler will send the bad message to another (designated error
handling) BPEL process. The user can thus define a process with a Receive operation
of his own choosing (WSDL and BPEL src) - the only constraint is on the Message
Type of the message that will be sent to this rejection handler. It must be declared to
have the type "RejectedMessage". This can conveniently be achieved by importing
the "xmllib" resident WSDL "RejectionMessage.wsdl" which defines such a message:
<message name="RejectionMessage">
<part name="message" element="err:RejectedMessage"/>
</message>
An xmllib WSDL import (from another WSDL) is achieved using the "well-known"
URL
<import namespace="http://xmlns.oracle.com/pcbpel/rejectionHandler"
location="http://localhost:9700/orabpel/xmllib/jca/RejectionMessage.wsdl"/>
i.e. the Receive operation WSDL which the user defines for his Rejection Handler BPEL Process would simply contain this import and then the port type would
reference this:
<definitions ...
xmlns:rej="http://xmlns.oracle.com/pcbpel/rejectionHandler"
<portType name="MyRejectionHandlerPortType">
<operation name="myHandleRejectionOperation">
<input message="rej:RejectionMessage"/>
</operation>
</portType>
·  WSIF Based Rejection Handler
<property name="rejectedMessageHandlers">
wsif://<wsif-wsdl-location>|<operation-name>|<input-message-part-name>
</property>
For example
<property name="rejectedMessageHandlers">
wsif://file:/C:/orabpel/samples/test/ErrorTest/FileAdapterWrite.wsdl|write|message
</property>
This last rejection handler lets the user configure any type of WSIF WSDL (JCA, EJB,
JMS, HTTP, Java etc), i.e. any kind of Service which can be reached via WSIF - as
the bad message handler. The exact same constraint vis-a-vis the Message Type as
described above for the BPEL Process Rejection Handler also applies here.

Notes: (1) BPEL supports all four types of rejection handlers

(2) ESB supports only 3 types of rejection handlers except the BPEL based rejection handler ( Until Soa Suite 10.1.3.5) .

The following examples demostrate how rejectionmessageHandler can be defined in ESB

(a) ESB AQ rejected Message Handler:

queue://jdbc:oracle:thin:@localhost:1521:XE|jmsuser/jmsuser|ESB_REJ_MSG_TOPIC


(b) ESB BPEL rejected Message Handler:

bpel://default|CaptureRejectedMessage|process|message


(c) ESB WSIF based rejection handler:
wsif://http://localhost:8888/orabpel/default/CaptureRejectedMessage/1.0/CaptureRejectedMessage?wsdl|handleRejection|message

SOA Fault Handling

A BPEL fault has a fault name called a Qname (name qualified with a namespace) and a possible messageType. There are two categories of BPEL faults:

• Business faults

• Run-time faults

(1)Business faults: Business faults are application-specific faults that are generated when there is a problem with the information being processed

(2)Run-time faults: Run-time faults are the result of problems within the running of the BPEL process or Web service. Binding fault and remote Fault are the main types of run time faults

 targetNamespace="http://schemas.oracle.com/bpel/extension"
 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 xmlns="http://schemas.xmlsoap.org/wsdl/">

 
  
  
  
 

1. bindingFault : A bindingFault is thrown inside an activity if the preparation of the invocation fails. For example, the WSDL of the process fails to load. A bindingFault is not retryable. This type of fault usually must be fixed by human intervention

2. remoteFault : A remoteFault is also thrown inside an activity. It is thrown because the invocation fails. For example, a SOAP fault is returned by the remote service. A remoteFault can be configured to be retried

For More Information refer to: http://download.oracle.com/docs/cd/E11036_01/integrate.1013/b28981/faults.htm