Devart Forums

Code: Select all

DEFINE DATA_SPACE                     = "TEST_DATA_SPACE";
DEFINE INDEX_SPACE                    = "TEST_INDEX_SPACE";

CREATE TABLE clob_test ( clob_id NUMBER NOT NULL, C_DATA CLOB )
TABLESPACE   &DATA_SPACE;

CREATE UNIQUE INDEX pk#150_clob_test ON clob_test ( clob_id )
TABLESPACE         &index_space;

ALTER TABLE clob_test ADD CONSTRAINT pk#150_clob_test PRIMARY KEY ( clob_id ) USING INDEX;

CREATE SEQUENCE clob_tst_seq 
START WITH 1 
INCREMENT BY 1 
nomaxvalue; 

CREATE TRIGGER clob_tst_trg           
BEFORE INSERT ON clob_test           
FOR EACH ROW 
BEGIN 
SELECT clob_tst_seq.NEXTVAL           INTO :NEW.clob_id          FROM dual; 
END;
/

Code: Select all

Code: Select all

        private void InsertCLOBToDB(string word)
        {
            if (WordExists(word))
            {
                PrintDebug(String.Format("Word {0} already exists for.", word));
                return;
            }

            PrintDebug(String.Format("Insert word {0}", word));
            CLOB_TEST c = new CLOB_TEST();
            c.CLOB_ID = counter;
            c.C_DATA = word;
            _me.AddToCLOB_TESTs(c);
            _me.AcceptAllChanges();
            counter++;
        }

        private bool WordExists(string word)
        {
            CLOB_TEST d = (from i in _me.CLOB_TESTs
                           where (((i.C_DATA == null) && (word == null)) || (i.C_DATA.ToUpper().StartsWith(word.ToUpper())))
                            select i).FirstOrDefault();
                                 
            return (d != null);
        }

        internal void Run_Main()
        {
            string[] words = { @"Some text with unicode characters to be inserted and checked in the table", 
                               @"cds",
                             @"A Federação é uma organização cultura física, intelectual, moral e cívica.",
                             @"O Tribunal de Justiça Desportiva (TJD) é o órgão judicante do futebol do Estado de São Paulo. Presidido pelo Dr. Ronaldo Botelho Piacente é constituído por nove auditores, aos quais cabe a responsabilidade de apreciar os atos decorrentes dos recursos interpostos das decisões das Comissões Disciplinares. O TJD também analisa outros processos previstos no Código Brasileiro Disciplinar de Justiça.",
                             @"Le mouvement de grève des agents de sûreté aéroportuaire, qui provoque depuis vendredi des annulations de vols à Lyon et des retards à Roissy et Toulouse, devrait se poursuivre au deuxième jour des vacances de Noël. ",
                             @"La chanteuse capverdienne est morte à 70 ans dans son île natale de Sao Vicente, près de trois mois après avoir décidé d’abandonner la scène. ",
                             @"Tombées en disgrâce, l’ex-ministre et l’ex-secrétaire d’Etat donnent du fil à retordre à la droite, en affirmant haut et fort leurs ambitions pour les législatives. ",
                             @"Mais les choses sont beaucoup plus compliquées. Imaginons un dessinateur satirique qui s'amuserait à peindre un citoyen se torchant le derrière avec la Déclaration des droits de l'homme; à peindre des étrangers arrivant sur notre sol en crachant sur nous par principe; à peindre un couple homosexuel livré à la débauche et réclamant ouvertement d'adopter des enfants pour les sodomiser; ou encore, à peindre le camp d'Auschwitz sous des aspects burlesques et ridicules auxquels je n'ose pas penser. Croit-on que tout cela pourrait s'exposer tranquillement au nom de la liberté de la presse? Certainement pas. Tout aussitôt, l'ensemble des médias feraient taire l'importun, comme cela est arrivé il y a quelque vingt ans pour Fun Radio, qui, dans une outre-mesure adolescente, avait osé ricaner sur la Shoah. ",
                             @"Na manhã deste domingo (18), o último comboio com tropas dos EUA deixou o Iraque. Ao todo são mais de 110 veículos blindados e cerca de 500 soldados que atravessaram a fronteira com o Kuwait por volta das 7h30 no horário local (2h30 no de Brasília).

As tropas deixam o país quase nove anos após o invadir para depor o ditador Sadam Husein, foi capturado em dezembro de 2003 e executado na forca três anos depois. Permanecem em Bagdá 157 soldados americanos que ficaram com a missão de treinar as forças iraquianas e proteger a Embaixada dos EUA na capital do Iraque.

Durante os últimos dias, o Exército americano entregou os últimos prisioneiros que tinha em suas mãos às autoridades iraquianas, que na sexta-feira passada assumiram o controle da última base militar que permanecia em poder dos EUA.

No último dia 15, se encenou a retirada americana de maneira simbólica com o recolhimento da bandeira em cerimônia em Bagdá, da qual participou o secretário de Defesa deste país, Leon Panetta. Com o pacto de segurança assinado entre Washington e Bagdá há três anos, ambos os países acertaram a retirada para o final deste ano."
                             
                             };
            foreach (string word in words)
            {
                InsertCLOBToDB(word);
            }
            
        }

Code: Select all

System.Data.EntityCommandExecutionException was unhandled
  Message=An error occurred while executing the command definition. See the inner exception for details.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator()
       at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.b__1[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
       at TestCLOB.CLOBTester.WordExists(String word) in c:\users\awaitzbe\documents\visual studio 2010\Projects\TestCLOB\TestCLOB\CLOBTester.cs:line 118
       at TestCLOB.CLOBTester.InsertCLOBToDB(String word) in c:\users\awaitzbe\documents\visual studio 2010\Projects\TestCLOB\TestCLOB\CLOBTester.cs:line 101
       at TestCLOB.CLOBTester.Run() in c:\users\awaitzbe\documents\visual studio 2010\Projects\TestCLOB\TestCLOB\CLOBTester.cs:line 146
       at TestCLOB.Program.Main(String[] args) in c:\users\awaitzbe\documents\visual studio 2010\Projects\TestCLOB\TestCLOB\Program.cs:line 13
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: Devart.Data.Oracle.OracleException
       Message=ORA-01460: unimplemented or unreasonable conversion requested
       Source=Devart.Data.Oracle
       ErrorCode=-2147467259
       Code=1460
       Offset=0
       StackTrace:
            at Devart.Data.Oracle.ap.d()
            at Devart.Data.Oracle.x.m()
            at Devart.Data.Oracle.x.c()
            at Devart.Data.Oracle.u.a(Int32 A_0, bf A_1)
            at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
            at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
            at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at Devart.Data.Oracle.Entity.f.a(CommandBehavior A_0)
            at Devart.Common.Entity.o.b(CommandBehavior A_0)
            at Devart.Data.Oracle.Entity.f.b(CommandBehavior A_0)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       InnerException: 

Code: Select all

Oracle 11.1.0.7.0

Code: Select all

SQL> select * from nls_database_parameters;
NLS_LANGUAGE           = AMERICAN
NLS_TERRITORY          = AMERICA
NLS_ISO_CURRENCY       = AMERICA
NLS_CHARACTERSET       = UTF8
NLS_NCHAR_CHARACTERSET = UTF8
NLS_SORT               = BINARY
NLS_COMP               = BINARY
NLS_LENGTH_SEMANTICS   = BYTE
NLS_NCHAR_CONV_EXCP    = FALSE
NLS_RDBMS_VERSION      = 11.1.0.7.0

Code: Select all

var d = (from i in _me.CLOB_TESTs where i.C_DATA.ToUpper().StartsWith(word.ToUpper()) select i);
return d.Count() > 0;

Code: Select all

SELECT 
"GroupBy1".A1 AS C1
FROM ( SELECT Count(1) AS A1
	FROM SEC.CLOB_TEST "Extent1"
	WHERE (INSTR(UPPER("Extent1".C_DATA), UPPER(:p__linq__0))) = 1
)  "GroupBy1"

Code: Select all

SELECT
"GroupBy1".A1 AS C1
FROM ( SELECT Count(1) AS A1
   FROM SEC.CLOB_TEST "Extent1"
   WHERE (INSTR(UPPER("Extent1".C_DATA), UPPER('çã'))) = 1
)  "GroupBy1"

Code: Select all

OracleConnection conn = new OracleConnection(connectionString);
conn.Open();

OracleCommand cmd = new OracleCommand(
  @"SELECT ""GroupBy1"".A1 AS C1
    FROM ( SELECT Count(1) AS A1
    FROM UTF_CLOB_TEST ""Extent1""
      WHERE (INSTR(UPPER(""Extent1"".C_DATA), UPPER(:p__linq__0))) = 1
    )  ""GroupBy1""",
  conn);
cmd.Parameters.Add("p__linq__0", OracleDbType.VarChar, "çã", ParameterDirection.Input);

var reader = cmd.ExecuteReader();
if (reader.Read())
  Console.WriteLine(reader[0]);

Code: Select all

OracleDataReader reader = cmd.ExecuteReader();

Code: Select all

private string ADOConnectionStringBuilder()
{
    OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
    oraCSB.Direct = true;
    oraCSB.Port = 1529;
    oraCSB.Sid = "OPSECDB";
    oraCSB.UserId = username;    // const
    oraCSB.Password = password; // const
    oraCSB.Unicode = true;
    oraCSB.PersistSecurityInfo = true;
    oraCSB.Server = "localhost";
    oraCSB.ConnectionTimeout = 0;
    oraCSB.DefaultCommandTimeout = 0;
    return oraCSB.ConnectionString;
}

Code: Select all

private string ADOConnectionStringBuilder()
{
    System.Data.SqlClient.SqlConnectionStringBuilder oraCSB = new System.Data.SqlClient.SqlConnectionStringBuilder();
    oraCSB.UserID = username;
    oraCSB.Password = password;
    oraCSB.PersistSecurityInfo = true;
    oraCSB.Add("Server", "(DESCRIPTION =(ADDRESS= (PROTOCOL = TCP)(HOST=localhost)(PORT = 1529))(CONNECT_DATA=(SID = OPSECDB)))");
    return oraCSB.ConnectionString;
}

Code: Select all

SELECT 
"GroupBy1".A1 AS C1
FROM ( SELECT Count(1) AS A1
	FROM SEC.CLOB_TEST "Extent1"
	WHERE (UPPER("Extent1".C_DATA)) = (UPPER(:p__linq__0))
)  "GroupBy1"

Code: Select all

WHERE dbms_lob.compare(UPPER(""Extent1"".C_DATA), UPPER(:p__linq__0)) = 0