0
голосов
2ответов
2910 просмотров

Problem with dbUnit: java.sql.SQLException: Closed Statement

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

0
голосов
2ответов
1652 просмотров

UNION ALL versus CONNECT BY LEVEL for generating rows

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

1
голосов
4ответов
2963 просмотров

Avoiding full table scan

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

3
голосов
4ответов
2824 просмотров

Making sure String does not exceeds 2000 bytes in Oracle database table column

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

4
голосов
2ответов
382 просмотров

Handling circular data in Oracle's SQL

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

12
голосов
4ответов
33538 просмотров

Oracle SQLDeveloper Autocomplete in Lowercase (How about Uppercase)

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

0
голосов
5ответов
3896 просмотров

Bulk Copy from SQL Server to Oracle

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

3
голосов
3ответов
4872 просмотров

Oracle 10g express home page is not coming up

My Oracle 10g Express Edition , I can login in the SQL plus but I cannot login into oracle via SQL developer and cannot view the Home page at link http://127.0.0.1:8080/apex. This was working fine until yesterday. I have checked via (WINDOWS)netstat -ab and no other app is using the 8080 port. ...

12
голосов
4ответов
64621 просмотров

What is wrong with this PL/SQL? Bind Variable * is NOT DECLARED

Here is: declare v_str1 varchar2(80); begin v_str1 := 'test'; print :v_str1; end When I run it using SQLDeveloper just in a sql worksheet I get this: Bind Variable "v_str1" is NOT DECLARED anonymous block completed

2
голосов
1ответов
3047 просмотров

Is using a SELECT inside a pipelined PL/SQL table function allowed?

The docs for pipelined functions say that DML is not allowed when they are used in a SQL statement (typically a SELECT), and in most examples the pipelined functions are used for data generation or transformation (accepting a custor as parameter), but not issuing any DML statements. Now, technic...

2
голосов
4ответов
4059 просмотров

Oracle external tables. Optimising select queries

I have to perform many selects from an Oracle external table. I have 10 cursors that look a lot like this (ext_temp is the external table) CURSOR F_CURSOR (day IN varchar,code Number,orig Number) IS select NVL(sum(table_4.f),0) from ext_temp table_4 where --couple of condi...

2
голосов
1ответов
123 просмотров

Selecting transactions as a single row

I have a requirement where I need to generate a report about current software projects. Two of the columns are the date of the latest milestone and the date of the milestone previous to that. Obviously, milestones are stored in a transaction table so we can have many milestone per project. I'v...

1
голосов
2ответов
55 просмотров

PL SQL - Resembling (!a?0:a) functionality

I have a simple query in a cursor Cursor some_cursor IS select sum(some_field) from some_table table_1 where table_1.TYPE =1 AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905' AND table_1.f2 = 962 AND table_1.f3 = 418...

0
голосов
1ответов
500 просмотров

Blob into Oracle: about 15% are filled with \00

Under Weblogic 10, I am using Hibernate to store data into several tables with BLOBs. It's always worked fine but the customer found specific circumstances where 15% of the BLOBs have the correct size but only contain null characters. I can't figure out what makes it good or full of emptiness. T...

2
голосов
2ответов
7308 просмотров

Mapping Calculated Fields in Hibernate

I'm not sure if this is possible in Hibernate but it would be really great if it was :) I've not actually got past the conceptual stage with this but I'll explain as best as I can. I want to make use of Oracle Spatial features to do proximity based searching. Imagine I've got a Location entity w...

2
голосов
5ответов
1772 просмотров

number format in oracle

Hai, i have a problem with number format.i'm using oracle. I have a number field in database.But when i retreive it i need to be seen as floating point number For example: while retreiveing,now i got the result as 200 DR (DR for Debit,it is given manually). Now i need to get the result as 200.00...

1
голосов
1ответов
137 просмотров

Hibernate mapping - compile error

I get a compile error from eclipse with this hibernate query. Below is the code and the picture of eclipse compile error <sql-query name="endDateChecker"> <return-scalar column="PId" type="java.lang.Long"/> select pid as PId from info ...

11
голосов
6ответов
17161 просмотров

Opposite of INTERSECT in Oracle

I have two selects and I want to combine them in such a way, that only rows unique in both selects are returned. Is there any built-in way in Oracle 10g to achieve this? I know I can do something like this: (select1 UNION select2) MINUS (select1 INTERSECT select2) but I would like to avoid i...

7
голосов
6ответов
2544 просмотров

Is `Delete From Join` Standard SQL?

Among other questions, this one asked how to delete from a join. My question: How much of this is standard SQL? On which databases would this actually work (most notably for me would be Oracle, MySQL and SQLServer)?

4
голосов
8ответов
12141 просмотров

How to calculate distances between coordinates EFFICIENTLY in Oracle

I have a large Oracle database ( 720,000 records aprox) where each record has its own geographic coordinates (lat & lng) and i need to select just the records that are in a specific distance from a point ( inside a specific radius). Currently i've implemented a distance function (based on ...

3
голосов
3ответов
29071 просмотров

Oracle - difference or changes between two rows from two tables

I have two tables both have same schema, one will have previous day records other will have current. I want to compare both and find only changes or only rows that have different value in atleast one column. How is this possible in pl/sql, oracle? (I did code something similar using checksum i...

0
голосов
2ответов
2298 просмотров

Sql Developer 2.1 query results window: turn off auto resizing?

I recently upgraded to Oracle Sql Developer 2.1 It's far better, but the query results window automatically makes itself really big each time I run a query, covering most of my worksheet. Does anyone know a way to turn this off, so that it stays the same size when a query is run?

0
голосов
4ответов
577 просмотров

A query: select * returns data, but select Column does not return results

I have a query like this: select pln.* from plan pln where pln.id = '0003' and pln.seq = (select max(pln_es.seq) from plan pln_es where pln_es.id = pln.emplid and pln_es.career =pln.career and pln_es.nbr =...

1
голосов
1ответов
964 просмотров

best time to SELECT value in Oracle nested query

From an optimization standpoint, I want to know when it's best to SELECT some arbitrary data as columns. Specifically, I have this query: Where do I put this part? SELECT 'String 1' AS segment FROM DUAL UNION ALL SELECT 'String 2' AS segment FROM DUAL UNION ALL SELECT 'String 3' AS segment FRO...

8
голосов
2ответов
16053 просмотров

Using an object type in a select statement in Oracle

I have a function that returns an object that has 3 values. Is there a way to call that function from a select statement and have each value be a different column? I could break it into 3 functions, but the values are related so I wanted to keep it as one for performance reasons. (So oracle doesn...

12
голосов
5ответов
87555 просмотров

How to replace single-quote with double-quote in sql query - oracle 10g?

How can I replace single-quote (') with double-quote (") in sql query - oracle 10g?

0
голосов
4ответов
1513 просмотров

Oracle SQL query question

I want to extract data from database where field "end_ date" is less than today's date and where end_date is not equal to null; how would I do that? I figured the second part out .. I have trouble with first part select * from table where to_char(end_date) IS NOT null

1
голосов
5ответов
7793 просмотров

How do I prevent the loading of duplicate rows in to an Oracle table?

I have some large tables (millions of rows). I constantly receive files containing new rows to add in to those tables - up to 50 million rows per day. Around 0.1% of the rows I receive are duplicates of rows I have already loaded (or are duplicates within the files). I would like to prevent th...

2
голосов
2ответов
65675 просмотров

Oracle - Number to varchar

I have a table containing a column of type Number create table tmp ( /*other fields*/ some_field Number ) and in a PL SQL script, I want to convert that field to a varchar. However, i don't know its length, so I get an exception Exception message is ORA-06502: PL/SQL: numeric or ...

2
голосов
3ответов
1109 просмотров

Insert into oracle database

Hi I have a database with loads of columns and I want to insert couple of records for testing, now in order to insert something into that database I'd have to write large query .. is it possible to do something like this INSERT INTO table (SELECT FROM table WHERE id='5') .. I try to insert the ...