I have a requirement for a project to move data from SQL Server to Oracle in bulk mode. There is OracleBulkCopy from DataDirect and ODP .net but to use that I have to first convert the data reader from SQL server into a CSV file and then can export that using bulk copy.This is a very inefficient process and I was wondering if there is an...
I am currently using the latest Oracle sql developer. I just have one nuisance here. When I do auto-complete of table names or columns, they show up in lower case. As most people I do have coding guidelines in SQL statements and I usually want table names, column names, and any other identifiers in all capitals. I tried to check the se...
Hi everyone, There is a problem that don't know how to resolve only with SQL (I could do it with PL/SQL, but it should be doable only with SQL). I have a table (in fact it's not a table but the result of a with query) with contains pair of values like this: column1 column2 --------- --------- value1 value2 value1 ...
Want to truncate error string so it for sure fits into Oracle table column VARCHAR2(2000 BYTE) Design forces: The main goal is to fit to the table column. 90-95% of string text is exception message and stacktraces. But it could contain some customer name with french, turkish characters which I am willing to disregard and see as ? or ...
Hello, I have the following query which obtains transactions from the transactions table and transaction detail. Both tables have a big amount of entries, so this query takes a while to return results. SELECT * FROM transactions t LEFT JOIN transac_detail tidts ON (tidts.id_transac = t.id); However, I'm more worried because of the f...
I was wondering which is a better/faster/more efficient way of turning arbitrary strings into columns: UNION ALL SELECT my_field, CASE WHEN my_field = 'str1' THEN ... ... END, ... FROM ( SELECT 'str1' AS my_field FROM DUAL UNION ALL SELECT 'str2' AS my_field FROM DUAL ...
Hi, I have a strange problem with dbUnit. I use dbUnit 2.4.4, java 1.6, Spring (as db connection pool), Oracle 9 for my project with about 50 unit tests. For some of them (when I run whole set of tests) I get such exception: Closed Statement [junit] junit.framework.AssertionFailedError: Closed Statement [junit] at com.myproj.DataAcc...
Hi I run this query to read the count of the Records with an output parameter using OracleCommand: var query = "declare MyCount number; begin SELECT COUNT(*) INTO :MyCount FROM T_ISSUE; end;"; this one works fine. But if I split the query into two lines like this: var query = @"declare MyCount number; begin SELECT CO...
I'm introducing a DAO layer in our application currently working on SQL Server because I need to port it to Oracle. I'd like to use Hibernate and write a factory (or use dependency injection) to pick the correct DAOs according to the deployment configuration. What are the best practices in this case? Should I have two packages with dif...
Hello folks, i'm working in an app that was originally built to connect to SQL Server by using ADO.NET SqlClientProvider. Recently, i had to generalize the data provider code to work with either SQL Server or Oracle (by using DbClasses instead of SqlClasses and so on...). My problem is: Oracle SPs need to use ref cursors to retrieve ta...
Hello all, Here is an example of some TSQL that I would like to rewrite in PL/SQL. DECLARE @xml XML SET @xml = '<theRange> <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow> <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow> <theRow><First>John</First><Last>Bates</Last><Age>40</Age></theRo...
Hi, I have created an index on my table like this: CREATE INDEX index_typ_poplatky ON Auta (typ DESC, poplatok_denny DESC, poplatok_km DESC); How to I check that the index file exists? ...
I was looking for a way to do a BITOR() with an Oracle database and came across a suggestion to just use BITAND() instead, replacing BITOR(a,b) with a + b - BITAND(a,b). I tested it by hand a few times and verified it seems to work for all binary numbers I could think of, but I can't think out quick mathematical proof of why this is cor...
I'm trying to figure out why my application is unable to unlock a user's Oracle account successfully. Here's a snippet from my code: OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource(); Properties props = new Properties(); props.put("user", "sys"); props.put("password", "sys"); props.put("internal_logon", "sysdba"); ods.set...
I am wondering what the performance of a query would be like using the LIKE keyword and the wildcard as the value compared to having no where clause at all. Consider a where clause such as "WHERE a LIKE '%'". This will match all possible values of the column 'a'. How does this compare to not having the where clause at all. The reason I...
I've declared the following types in my PL/SQL package: TYPE t_simple_object IS RECORD ( wert NUMBER, gs NUMBER, vl NUMBER); TYPE t_obj_table IS TABLE OF t_simple_object INDEX BY BINARY_INTEGER; Then I declare a variable: obj t_obj_table; However, when I want to use the variable, I cannot initialize or extend ...
Hello, I am using the Patterns and Practices Data Access Application Block and I want to be able to perform a SELECT using multiple criteria like you can do in SQL using the IN keyword. Such as: SELECT * FROM SomeTable WHERE PrimaryKey IN (@keys) How can I pass in the @keys values? I do not want to have to dynamically build my SQL. ...
I want to have oracle in windows and linux both in one pc. but I dont know , some how , is it possible to install oracle in one os and use it in both os on my pc or not? if it is possible , how? thanks. ...
Hi, Im trying to get to run a very simple example in liquibase 1.9.5 (see at the end). When I run liquibase --changeLogFile=test_sample.xml update I get this error message. Migration Failed: ORA-03115: unsupported network datatype or representation Im using oracle 10g XE 10.2.0.1. As I understand (and googled), this is an...
Hello Oracle Experts I have a Stored Procedure like this procedure P_IssueUpdate ( Id in integer, ModifiedDate in date, Solution in varchar2 ) AS BEGIN update T_Issue Set ModifiedDate = ModifiedDate, Solution = Solution where id = id; END P_IssueUpdate; my problem is that the parameter name is the same name as the Tab...