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

In ORACLE

SELECT REPLACE(REPLACE(YourColumn,CHAR(13),NULL),CHAR(10),NULL)
FROM YourTable


FOR ONE CHAR :
SELECT replace(YourColumn,CHAR(13),'')
FROM YourTable

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

IN .NET 
MyString.Replace("\r\n","")   

or..

MyString.Replace("\r\n",@"\n")

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(c)
else
  sb.append(' '); //replacement char
 
Advertisements

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

After installing the ODT and updating it after to work with VS2012 to ODAC 11.2.0.3.20 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 .

ScreenShot631

ScreenShot629

 

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 ) .

(more…)

What ODP.NET version in ODAC 11.2.0.2.1 ?

station name

odp.net

oracle

os

Terminal1 tk

2.111.7.20

11g

Win 7 64bit

Server 1 tw

2.112.1.0

11g

Win Server 2008 R2  64 bit

Server 2 tt

1.111.7.0

11g

Win Server 2008  32 bit
 

2.111.7.0

   

Terminal 2 aa

1.111.6.20

11g

Win Xp Professional 32 bit
 

2.111.6.20

   

 

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 2.112.2.0 I think.

I’ve found

32-bit ODAC 11.2 Release 3 (11.2.0.2.1) 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 11.2.0.2.1 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 9.2.0.4 and less and DB 10.2.0.1 – 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 11.2.0.3 (maybe end of 2011) – or 12.1 (no target date) –
depending if there is a need for 11.2.0.3 ODP or not.

The ODP.NET nomenclature is
Oracle 9.2.0.X: ODP.NET 9.2.0.7
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 11.1.0.7 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 9.2.0.8 – or upgrade it to 10.2.0.5 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/>
</
return>
exec  GETMYDATA  :P_PARAM1 , :P_PARAM2 , :MY_CURSOR
</sql-query>

</hibernate-mapping>

5. Created the Entity class :

using System;

namespace Infra.Entities
{
[Serializable]
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()
{
Table(“kav_tkufa”);
ReadOnly();
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 =>
{
m.FluentMappings.AddFromAssemblyOf<KavMapping>();
m.HbmMappings.AddFromAssemblyOf<KavMapping>();
m.MergeMappings();
} )

I’ve read Ayende’s post http://ayende.com/blog/1692/using-nhibernate-with-stored-procedures 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 11.1.0.7.20 with Toad 10.6.1.3 on Windows 7

Installed ODTwithODAC1110720

image

 

Installed client with it : Log of it

 

Inventory contents of (Tzvi-PC/169.254.82.172):

Oracle Homes
OraClient11g_home1
Oracle Data Access Components for Oracle Client 11.1.0.7.20
Oracle Data Provider for .NET 2.0 11.1.0.7.20
Oracle Data Provider for .NET Documentation 11.1.0.7.20
Oracle Providers for ASP.NET 11.1.0.7.20
Oracle Data Provider for .NET 2.0 11.1.0.7.20
Oracle Data Provider for .NET Documentation 11.1.0.7.20
Oracle Providers for ASP.NET Documentation 11.1.0.7.20
Oracle Developer Tools for Visual Studio 11.1.0.7.20
Oracle Data Provider for .NET 2.0 11.1.0.7.20
Oracle Data Provider for .NET Documentation 11.1.0.7.20
Oracle Developer Tools for Visual Studio .NET Documentation 11.1.0.7.20
Oracle Provider for OLE DB 11.1.0.7.20
Oracle Objects for OLE 11.1.0.7.20
Oracle Services For Microsoft Transaction Server 11.1.0.7.20
Oracle Universal Installer 11.1.0.7.0
Installer SDK Component 11.1.0.7.0
Oracle One-Off Patch Installer 11.1.0.7.0
Installer SDK Component 11.1.0.7.0
Java Runtime Environment 1.5.0.11.0
Oracle Instant Client 11.1.0.7.0
Oracle ODBC Driverfor Instant Client 11.1.0.7.0
Oracle JDBC/OCI Instant Client 11.1.0.7.0
Oracle Globalization Support 11.1.0.7.0
SSL Required Support Files for InstantClient 11.1.0.7.0
RDBMS Required Support Files for Instant Client 11.1.0.7.0
SQL*Plus Files for Instant Client 11.1.0.7.0
——————————————————————————–
HOME SELECTION
——————————————————————————–
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 :

image

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)
                 .Access
                 .Property()
                 .AsSet()
                 .KeyColumn("CMK_CMU_RECID")
                 .Cascade
                 .All().LazyLoad().Fetch.Join().Inverse();

 

Class Diagram of my entity model was :

The mistake I’ve got in NHibernate Profiler :

it does the select query :

SELECTFROM   CUSTOMER_MUTZAR customermu0_
       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 :

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

WARN:
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()
                .KeyColumn("CMK_CMU_RECID")
                .LazyLoad().Fetch.Join().Inverse();

This has resolved the issue for me.

 

Tag Cloud

%d bloggers like this: