Tips and Tricks in a world of Mix

Posts tagged ‘Oracle’

Remove Special Charachters \n\r CR LF from data PL / SQL Oracle and .Net


FROM YourTable

SELECT replace(YourColumn,CHAR(13),'')
FROM YourTable

update YourTable
set YourColumn =replace(YourColumn,CHAR(13),'')




Potential Blacklist
Special control chars 0-31 by Unicode must be handled
White list 
[^a-zA-Zא-ת0-9._] -and punctuation chars by your choice
- this is a start of a regex pattern - [^] -everything that not in the pattern is not allowed.
also can do 
[^\w\s\p{IsHebrew}\p{P}] - not taking accesnt chars like ~^ , not finished , but a start

better handled by StringBuilder with loop through chars than Regex 
(more than 10 times better).just Whitelist of allowed chars and if in range 
like this 

if(c>=a && c<=z)...
  sb.append(' '); //replacement char

Synonym of Oracle is missing– Is the Oracle False messiah ?!

After installing the ODT and updating it after to work with VS2012 to ODAC with Oracle Developer Tools for Visual Studio on the same Oracle Client Home

I was able to create the EDMX model , but the synonyms stayed hidden to me on the wizard of the VS (by the way at VS 2010 also) .

The connection works perfect and gets me the regular tables , views and stored procedures .

The trouble is with the rest .




From my approach to Oracle I’ve got a couple of links and a statement that it’s Microsoft’s product that doesn’t support the import at the initialization wizard.

The links provided –

1) Make Entity Framework Designer support Synonyms  – we are not Code First – the schema exists and running

2)A problem to view synonym of Oracle in the Entity Data Model Designer – can’t see the synonyms , not with dba not with any user ..


Any ideas will be appreciated!

Working with Oracle from SqlServer 20008 with Services–the Architecture part 1

At the present project I’ve encountered a problem of working with Oracle through the portal of SqlServer .

q: What is the need for it ?

a: Reduction of development time ..


So , I have at my project a few tasks :

  1. Create a DB schema and fill it out with temporary data.
  2. Create quickly a code corresponding with database
  3.  Add sources of Oracle to SQL SEVRER .
  4. Include handle of UDT objects .
  5. use it – The download link for SSMA for Oracle.  
  6. Be able to work with web services , maybe RIA services ,  especially with Oracle UDT objects .  This is a sample of Grid Silverlight through RIA Services .


If I’d start doing all this things through the procedures of Oracle I would need at least one DB programmer working on it for about a month and a half I think , and then I’d need a .Net programmer that would work on his end for the same amount of time , probably not in parallel , so it would prolong the development in 2 , maybe even more.


The goal is to create a DB  in SqlServer and create corresponding entities automatically .

Then create RIA services per entity . (Well RIA Services is really a luxury , so we’ll see how it goes ) .


What ODP.NET version in ODAC ?

station name



Terminal1 tk


Win 7 64bit

Server 1 tw


Win Server 2008 R2  64 bit

Server 2 tt


Win Server 2008  32 bit


Terminal 2 aa


Win Xp Professional 32 bit



We are starting using TFS build templates  , so that we need to install the ODP.NET on the tfs server . So the most updated version for now to install is I think.

I’ve found

32-bit ODAC 11.2 Release 3 ( Installation Instructions, Setup, and Notes

December 2010

at your site .

What version of ODP.NET does it contain ?
What versions of the OS does it support?

What previous versions of ODP.NET does it support ?


Answer of Oracle team :

Best choice if OS Range is from XP to W2008R2 / W7 and both 32bit and 64bit software is used,ODP.NET 2.112.2 or higher should be used – since ODAC exists for 32bit and 64bit Platformsand it supports .NET 2.x and 4.X this underlines the recommendation.

The only limitation is the used database – 11.2.0.X cannot access DB and less and DB – but since 9.2 is desupported since years and 10.2 will be in extends support in July 2011 this should not play a role.
Future Version of ODAC maybe (maybe end of 2011) – or 12.1 (no target date) –
depending if there is a need for ODP or not.

The ODP.NET nomenclature is
Oracle 9.2.0.X: ODP.NET
Oracle 10.1 / .NET 1.X : ODP.NET 1.101.X.Y
Oracle 10.1 / .NET 2.X : ODP.NET 2.101.X.Y
Oracle 10.2 / .NET 1.X : ODP.NET 1.102.X.Y
Oracle 10.2 / .NET 2.X : ODP.NET 2.102.X.Y
Oracle 11.1 / .NET 1.X : ODP.NET 1.111.X.Y
Oracle 11.1 / .NET 2.X : ODP.NET 2.111.X.Y
Oracle 11.2 / .NET 2.X : ODP.NET 2.112.X.Y
Oracle 11.2 / .NET 4.X : ODP.NET 4.112.X.Y

The X describe the patch set level – for example for X=Y
And the Y describes a internal revision – ODAC and Full Client sometimes differed – for example Full Client Y=0 and ODAC = 20 etc..
Next, if there are 9.2 DB on the system I would suggest you lift the DB to – or upgrade it to since 9.2 is desupported since years
Installation of two ODP.NET can be done – but in that case you have to ensure that ORACLE_HOME and PATH is set correctly
before a specific app is started using a special ODP.NET

Problem Using Stored Procedure with Fluent Nhibernate

    1. Gave the mapping file the extension .hbm.xml
    2. Set the build action to ‘Embedded Resource’
    3. Set the copy-to-output to ‘Copy Always’
    4. Created the Hbm for SP:

<?xml version=1.0encoding=utf-8?>
hibernate-mapping xmlns=urn:nhibernate-mapping-2.2assembly=Infranamespace=Infra.Entities>
<sql-query name=GETMYDATA>
return class=Kav>
return-property column=Kav.MAKAT8name=MAKAT8/>
return-property column=Kav.DESCRIPTIONname=DESCRIPTION/>


5. Created the Entity class :

using System;

namespace Infra.Entities
public class Kav : Entity
public virtual string Makat8 { get; set; }
public virtual string Description { get; set; }

6. Created the Mapping class for the entity:

namespace Infra.Entities.Mapping
public class KavMapping : EntityClassMap<Kav>
public KavMapping()
Id(x => x.Makat8).Column(“MAKAT8”).Length(10);
Map(x => x.Description).Column(“Description”).Length(50);


7. Added the mapping to the FNH configuration (the entities and hbm in the same assembly) :

.Mappings(m =>
} )

I’ve read Ayende’s post on connecting to SP , but I’m failing to connect.

How should I map the Kav so that I could get the list of entities from SP ?

Oracle installation–tutorial , problems & solutions 11g ODT , ODAC with Toad on Windows 7

Installed ODTwithODAC1110720



Installed client with it : Log of it


Inventory contents of (Tzvi-PC/

Oracle Homes
Oracle Data Access Components for Oracle Client
Oracle Data Provider for .NET 2.0
Oracle Data Provider for .NET Documentation
Oracle Providers for ASP.NET
Oracle Data Provider for .NET 2.0
Oracle Data Provider for .NET Documentation
Oracle Providers for ASP.NET Documentation
Oracle Developer Tools for Visual Studio
Oracle Data Provider for .NET 2.0
Oracle Data Provider for .NET Documentation
Oracle Developer Tools for Visual Studio .NET Documentation
Oracle Provider for OLE DB
Oracle Objects for OLE
Oracle Services For Microsoft Transaction Server
Oracle Universal Installer
Installer SDK Component
Oracle One-Off Patch Installer
Installer SDK Component
Java Runtime Environment
Oracle Instant Client
Oracle ODBC Driverfor Instant Client
Oracle JDBC/OCI Instant Client
Oracle Globalization Support
SSL Required Support Files for InstantClient
RDBMS Required Support Files for Instant Client
SQL*Plus Files for Instant Client
Central Inventory Location: “C:\Program Files (x86)\Oracle\Inventory”
Oracle Home           Status
OraClient11g_home1    Selected

PATH = C:\app\Tzvi\product\11.1.0\db_1\bin;C:\Program Files\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live;C:\app\Tzvi\product\11.1.0\client_1;C:\app\Tzvi\product\11.1.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\;C:\Program Files (x86)\Windows Live\Shared


Installed DB – WindowsServer_x64_11g :


Problem :

“Oracle Database 11g is not certified on the current operating system.”

Solution : (more…)

Fluent Nhibernate – fail-safe cleanup (collections) warning


My mapping was in CustomerMutzar :

  HasMany<CustomerMutzarKishur>(x => x.Col_CustomerMutzarKishur)


Class Diagram of my entity model was :

The mistake I’ve got in NHibernate Profiler :

it does the select query :

       left outer join CUSTOMER_MUTZAR_KISHUR col_custom1_
         on customermu0_.CMU_RECID = col_custom1_.CMK_CMU_RECID
       left outer join CUSTOMER_MUTZAR_PRODUCT customermu2_
         on col_custom1_.CMK_CMP_RECID = customermu2_.CMP_RECID
WHERE  customermu0_.CMU_RECID = 450 /* :p0 */


and then I get few problematic statements in profiler :

fail-safe cleanup (collections) : NHibernate.Engine.Loading.CollectionLoadContext<rs=System.Data.OracleClient.OracleDataReader>

On CollectionLoadContext#cleanup, localLoadingCollectionKeys contained [1] entries

and a system crash from log  :

StackTrace: NHibernate.PropertyAccessException: Invalid Cast (check your mapping for property type mismatches); setter of Ness.DoarKamuti.Infrastructure.Entities.CustomerMutzar —> System.InvalidCastException: Unable to cast object of type ‘NHibernate.Collection.Generic.PersistentGenericSet`1[Ness.DoarKamuti.Infrastructure.Entities.CustomerMutzarKishur]’ to type ‘System.Collections.Generic.IList`1[Ness.DoarKamuti.Infrastructure.Entities.CustomerMutzarKishur]’.
   at (Object , Object[] , SetterCallback )

The Solution :

I went again to the mapping of the entity and adjusted the relationships :


 HasMany<CustomerMutzarKishur>(x => x.Col_CustomerMutzarKishur).AsBag()

This has resolved the issue for me.


Tag Cloud

%d bloggers like this: