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.

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