ORACLE BLOB, CLOB Handling

Some time it is necessary to convert blob, clob text data to varchar, want to datalenght of blob/clob field. That situation DBMS_LOB packgae can be utilize.

Some Example

1. SELECT DBMS_LOB.SUBSTR(CLOB_Field) AS VARCHAR_FIELD FROM AnyTable

2. SELECT DBMS_LOB.LENGTH(CLOB_Field) AS VARCHAR_LENGTH FROm AnyTable

I hope It helps.

Oracle Statement

Working with BLOB type UTL_RAW package is important.

===============================================
CREATE TABLE Data(Data BLOB);
INSERT INTO Data VALUES (rawtohex('hello world'));
SELECT UTL_RAW.CAST_TO_VARCHAR2(Data) FROM Data;
===============================================
Important DateCalculation Function

CREATE OR REPLACE FUNCTION DateDiff( p_Interval IN CHAR, p_startDateIn IN TIMESTAMP, p_disconDateIn IN TIMESTAMP ) RETURN NUMBERAS
v_duration INTERVAL DAY TO SECOND; v_myDay NUMBER; v_myHour NUMBER; v_myMinute NUMBER; v_mySecond NUMBER; v_totalSeconds NUMBER; v_Value NUMBER;BEGIN
v_duration := (p_disconDateIn - p_startDateIn);
v_myDay := TRUNC(to_number(extract(DAY FROM v_duration))*86400); v_myHour := to_number(extract(HOUR FROM v_duration))*3600; v_myMinute := to_number(extract(MINUTE FROM v_duration))*60; v_mySecond := to_number(extract(SECOND FROM v_duration));
v_totalSeconds := v_myDay + v_myHour + v_myMinute + v_mySecond ;
IF UPPER(p_Interval) = 'MS' THEN v_Value := v_totalSeconds * 1000;
ELSIF UPPER(p_Interval) = 'S' THEN v_Value := v_totalSeconds;
ELSIF UPPER(p_Interval) = 'M' THEN
v_Value := ROUND(v_totalSeconds / 60);
ELSIF UPPER(p_Interval) = 'H' THEN v_Value := ROUND(v_totalSeconds / 3600);
ELSIF UPPER(p_Interval) = 'D' THEN
v_Value := TRUNC(v_totalSeconds / 86400);
ELSE RAISE_APPLICATION_ERROR(-20001, 'Invalid parameter value p_Interval');
END IF;
RETURN v_Value;
END;

CREATE OR REPLACE FUNCTION ca_fn_GetUTCDateRETURN TIMESTAMPAS v_UTCDateTime TIMESTAMP;BEGIN SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) INTO v_UTCDateTime from dual;
RETURN v_UTCDateTime;END;/
CREATE OR REPLACE FUNCTION ca_fn_NEWIDRETURN VARCHAR2AS v_GUID VARCHAR2(50) := ''; v_GUID1 VARCHAR2(50) := ''; BEGIN SELECT SYS_GUID INTO v_GUID1 FROM dual; v_GUID := SUBSTR(v_GUID1, 0, 8) '-' SUBSTR(v_GUID1, 8, 4) '-' SUBSTR(v_GUID1, 12, 4) '-' SUBSTR(v_GUID1, 16, 4) SUBSTR(v_GUID1, 20, 4) SUBSTR(v_GUID1, 24);
RETURN v_GUID;
END;

=================================================================
Various Syntax
-----------------
CREATE DATABASE LINK EventLink CONNECT TO cic IDENTIFIED BY cic using 'caEvents'

CREATE OR REPLACE TRIGGER tr_Delete_DvrConfigBEFORE DELETE ON DvrConfigFOR EACH ROW
DECLARE
v_Old xmltype; v_TableName nvarchar2(50) := 'DvrConfig'; v_OperatorName nVarChar2(50); v_StationName nVarChar2(50); v_Description nvarchar2(100) := 'DvrConfig ID ' TO_CHAR(:OLD.DvrID);
BEGIN SELECT XMLELEMENT("DvrConfig", XMLATTRIBUTES(:OLD.DvrID AS DvrID, :OLD.ServerName AS ServerName, :OLD.ServerIP AS ServerIP, :OLD.UserName AS UserName, :OLD.Password AS Password, :OLD.UserType AS UserType, :OLD.Active AS Active, :OLD.EnableNotification AS EnableNotification, :OLD.DVRVendor AS DVRVendor, :OLD.caObjectID AS caObjectID, :OLD.LastUpdated AS LastUpdated, :OLD.LastOperator AS LastOperator, :OLD.LastWorkStation AS LastWorkStation)) INTO v_Old FROM DUAL;
ca_sp_OperatorWorkStation(:OLD.LastWorkStation, :OLD.LastOperator, v_OperatorName, v_StationName);
INSERT INTO DBAudit(Tablename, Description, Actions, RevisionStamp, OldData, OperatorName,StationName) VALUES(v_TableName, v_Description, 'D', ca_fn_GETUTCDATE, v_Old, v_OperatorName, v_StationName);
END;
==================================================================

CREATE ROLE UMR_Role_Admin;
GRANT CONNECT, RESOURCE, CREATE USER, DROP USER, ALTER USER, GRANT ANY ROLE TO UMR_Role_Admin;
GRANT EXECUTE ANY PROCEDURE, SELECT ANY TABLE, SELECT ANY DICTIONARY TO UMR_Role_Admin;



CREATE USER proxyuser IDENTIFIED BY proxyuser;
GRANT CONNECT TO proxyuser;

grant select,insert,update,delete on system.umr_patient to public


exp userid=BockDBA/Password file='USER100.dat' owner=USER100 grants=N indexes=Y compress=Y rows=Y
imp userid=BockDBA/password file='USER100.dat' fromuser=USER100 touser=USER101 rows=y indexes=y

Based on the table of keywords, interpret the example import command given below:
imp userid=SCOTT/TIGER ignore=Y tables=(expenses, advances) full=N


CREATE OR REPLACE PUBLIC SYNONYM sp_PatientGet FOR system.sp_PatientGet;


CREATE OR REPLACE PROCEDURE sp_UserAdd( p_UserID IN nchar, p_FirstName IN nvarchar2, p_LastName IN nvarchar2, p_Visibility IN number, p_UserGroup IN number, p_Email IN nvarchar2, p_Phone IN nvarchar2, p_Password IN varchar2 )AS v_sql varchar2(2000) :=''; v_Count int :=0; v_ApplicationUserException EXCEPTION; v_DatabaseUserException EXCEPTION; v_PermissionException EXCEPTION;
BEGIN
SELECT COUNT(*) INTO v_Count FROM sys.DBA_Role_Privs R WHERE R.Grantee = (SELECT UPPER(USER) FROM DUAL WHERE UPPER(R.Granted_Role) = 'UMR_ROLE_ADMIN');
IF (v_Count <=0) THEN RAISE v_PermissionException; END IF;
SELECT COUNT(*) INTO v_Count FROM UMR_Users U WHERE UPPER(U.User_ID) = UPPER(p_UserID);
IF (v_Count > 0) THEN RAISE v_ApplicationUserException; END IF;
SELECT COUNT(*) INTO v_Count FROM sys.DBA_Users U WHERE UPPER(U.USERNAME) = UPPER(p_UserID);
IF (v_Count > 0) THEN RAISE v_DatabaseUserException; END IF;
v_sql := 'CREATE USER ' p_UserID ' IDENTIFIED BY ' p_Password;
EXECUTE IMMEDIATE v_sql;
v_Sql := 'ALTER USER ' p_UserID ' GRANT CONNECT THROUGH proxyuser' ; EXECUTE IMMEDIATE v_Sql;
IF (p_UserGroup >= 10) THEN v_Sql := 'GRANT UMR_Role_Admin TO ' p_UserID; ELSE v_Sql := 'GRANT UMR_Role_Operator TO ' p_UserID; END IF;
EXECUTE IMMEDIATE v_Sql;
INSERT INTO UMR_Users ( User_ID, First_Name, Last_Name, Visibility, User_Group, Created_On, Email, Phone ) VALUES ( p_UserID, p_FirstName, p_LastName, p_Visibility, p_UserGroup, CURRENT_TIMESTAMP, p_Email, p_Phone );
EXCEPTION WHEN v_PermissionException THEN RAISE_APPLICATION_ERROR(-20010, '');
WHEN v_ApplicationUserException THEN RAISE_APPLICATION_ERROR(-20001, '');
WHEN v_DatabaseUserException THEN RAISE_APPLICATION_ERROR(-20002, '');
END

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

Application support SQL Server & Oracle

Sometimes it is requied to support sqlserver & oracle databaes for application. Both database came from different vendors. when you want to compatable both database then some issue you have to solve.

1. First Issue Datatype


SQL Server Oracle
------------------------------------------------------------------------------------------
1. TINYINT NUMBER(3)
2. SMALLINT NUMBER(6)
3. INT NUMBER(10)
4. BIGINT NUMBER(19)
5. BIT NUMBER(1)
6. VARCHAR VARCHAR2
7. SAMLLDATETIME/DATETIME DATE/TIMESTAMP
8. TIMESTAMP TIMESTAMP
9. MONEY NUMBER(19,4)
10. TEXT CLOB
11. BINARY/IMAGE BLOB
12. SQL_VARIANT SYS_ANYDATA
13. TABLE VARRAYS/NESTEDTABLE
14. UNIQUEIDENTIFIED VARCHAR2(50)

Data Load in List View Control

Listview control is very nice control. But load data is little confusing.
Many times I forget, how to Load data in ListView Control. I think many person fell like me.
So I need to publish the usage with code sample


void LoadErrors()
{
lvwErrors.Columns.Clear();
lvwErrors.Columns.Add("ErrorCode");
lvwErrors.Columns.Add("ErrorSource");
lvwErrors.Columns.Add("ErrorDescription");
lvwErrors.View = View.Details;
lvwErrors.Items.Clear();
List errors = ErrorManager.GetErrors();
ListViewItem item= null;
foreach (ErrorObject error in errors)
{
item = new ListViewItem(error.ErrorCode.ToString());
item.SubItems.Add(error.ErrorSource);
item.SubItems.Add(error.ErrorDescription);
lvwErrors.Items.Add(item);
}
}

Oracle Connection String

Most of the time I use tns.ora file for Oracle connection.
But if i install oracle client or odac then tns.ora file is created and accessed.

but some time your client may not have that component. so i need direct connection string.
The syntax is:

string connectionString = "Data Source =(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.dcl.net)(PORT = 1521)) )(CONNECT_DATA = (SERVICE_NAME = umrdb) )); User ID=rraquib; Password=rraquib";

cur_Out OUTPUT variable IN Oracle

I spent allmost all day spent to search how i return data as output parameter from oracle with the help of Data Access Application Block.
Ultimatly I got the answer.
Its only solution is the variable name should be cur_Out.

The sample code is given bellow.
-----------------------------------
CREATE OR REPLACE PROCEDURE sp_EmployeeGetAll
( cur_Out OUT SYS_REFCURSOR )
AS
BEGIN
OPEN p_Result FOR
SELECT EmpID, Name, JoinDate FROM Employee;
END;
/

The Dotnet code is
--------------------

Database db = new OracleDatabase(_connectionStr);
DbCommand command = db.GetStoredProcCommand(spName);


DataSet ds = new DataSet();
try
{
ds = db.ExecuteDataSet(command);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}