SQL 1 « oracle « Java Database Q&A

1. Oracle SQL Developer not responsive when trying to view tables (or suggest an Oracle Mac client)    stackoverflow.com

I just get the beach ball all day long (it's been doing nothing for hours). It's not taking CPU, not reading from disk, not using the network. I'm using Java 1.6 on ...

2. How to replace a character programatically in Oracle 8.x series    stackoverflow.com

Due to repetitive errors with one of our Java applications:

Engine engine_0: Error in application action.
org.xml.sax.SAXParseException: An invalid XML character (Unicode: 0x13)
was found in the element content of the document.

3. run oracle sql script from java    stackoverflow.com

i have a bunch of sql scripts that should upgrade the database when the java web application starts up. i tried using the ibatis scriptrunner, but it fails gloriously when defining triggers, ...

4. Database agnostic jdbc table import/export to files?    stackoverflow.com

Is it at all possible to do database-agnostic table dumps/hydrates? I don't have any complicated constraints. I would also settle for db-specific ways, but the more pure jdbc it is the ...

5. Converting between oracle.sql.TIMESTAMPTZ and standard JDBC classes for DbUnit    stackoverflow.com

I'm running Oracle 10g and have columns with Type_Name

TIMESTAMP(6) WITH TIME ZONE

oracle.sql.TIMESTAMPTZ 

6. Generating SQL Schema from XML    stackoverflow.com

We are using an Oracle 11 database and a java development environment (using Eclipse) and would like to migrate several xml schemas to SQL schemas. Have looked ax xsd but really need ...

7. Best way to select out of millions of rows in an Oracle DB    stackoverflow.com

G'day! I have one million different words which I'd like to query for in a table with 15 million rows. The result of synonyms together with the word is getting processed after ...

8. How to limit number of rows returned from oracle at the jdbc data source level?    stackoverflow.com

Is there a way to limit the rows returned at the oracle datasource level in a tomcat application? It seems maxRows is only available if you set it on the datasource in ...

9. Oracle SQL DATE conversion problem using iBATIS via Java JDBC    stackoverflow.com

I'm currently wrestling with an Oracle SQL DATE conversion problem using iBATIS from Java. Am using the Oracle JDBC thin driver ojdbc14 version 10.2.0.4.0. iBATIS version 2.3.2. Java 1.6.0_10-rc2-b32. The problem revolves around ...

10. SQL query statement to retrieve records having CLOB data from the oracle database    stackoverflow.com

I tried this code and the value of comments from final table having a clob datatype is null or sometimes displays few words. What should I add so that I ...

11. Dynamic SQL Java library    stackoverflow.com

We need to generate dynamic SQL in our Java app. Does anyone know a simple library to do this? In our Java app we have a bunch of where clause ...

12. When I call PreparedStatement.cancel() in a JDBC application, does it actually kill it in an Oracle database?    stackoverflow.com

I have Java JDBC application running against an Oracle 10g Database. I set up a PreparedStatement to execute a query, and then call ps.executeQuery() to run it. Occasionally the ...

13. Error starting SQL Developer    stackoverflow.com

HI everyone. I am trying to run sqldeveloper on my Red Hat Linux Enterprise 5 machine but get the following error: [root@machine plugins]# sqldeveloper Oracle SQL Developer Copyright (c) 2006, 2007, Oracle. All rights reserved. ...

14. Execute sql statement via JDBC with CLOB binding    stackoverflow.com

I have the following query (column log is of type CLOB):

UPDATE table SET log=? where id=?

15. Simple Java library - output Oracle query to CSV?    stackoverflow.com

Is there a simple Java library or approach that will take a SQL query and output the result to a CSV file?

16. Joining Information Across DB2 and Oracle Databases Best Practices    stackoverflow.com

We are designing a fairly large brownfield application, and run into a bit of a issue. We have a fairly large amount of information in a DB2 database from a ...

17. oracle jdbc driver version madness    stackoverflow.com

Why the heck does Oracle offer a different(!) version of the JDBC driver, e.g. ojdbc14.jar, for every(!) database version?
The files all have different sizes and thus probably different content. background:
We ...

18. Oracle SYS_CONNECT_BY_PATH hitting 4000 character limit    stackoverflow.com

I'd like to run a query like the one below against an Oracle 9i database from Java (an example table structure and example data are below).

SELECT deptno
,     ...

19. % sign in Java's PreparedStatement    stackoverflow.com

PreparedStatement ps = con.createStatement("select * from table1 where last_name like ?");
ps.setString(1, "'%"+lastName+"'");

Statement s = con.createStatement("select * from table1 where last_name like %"+ lastName);

20. Extracting time zone from Oracle JDBC TIMEZONETZ object    stackoverflow.com

Is there a way to extract timezone information directly from an oracle.sql.TIMESTAMPTZ object (selected from a TIMESTAMP WITH TIME ZONE column)? Ideally, I'd like to be able to pull the time zone ...

21. SQL connection lifetime    stackoverflow.com

I am working on an API to query a database server (Oracle in my case) to retrieve massive amount of data. (This is actually a layer on top of JDBC.) The API ...

22. Testing SQL query on Oracle which includes a remote database    stackoverflow.com

Our development databases (Oracle 9i) use a remote database link to a remote shared database. This decision was made years ago when it wasn't practical to put some of the database schemas ...

23. Is it possible to use GROUP BY with bind variables?    stackoverflow.com

I want to issue a query like the following

select max(col1), f(:1, col2) from t group by f(:1, col2)

connection.prepareStatement
  ("select ...

24. When is sql distinct faster than java programming 'distinct'    stackoverflow.com

If I have a sql query that uses 'distinct' (in oracle), would it be faster than retrieving the non-distinct then getting the unique results via java programming? I heard somewhere that oracle ...

25. Is there an easy way to backup Oracle SQL Developer's User Snippets?    stackoverflow.com

I love Oracle SQL Developer so I find myself using it a lot to complete my daily tasks. The thing is that my collection of custom snippets only keeps growing so ...

26. Oracle lag between commit and select    stackoverflow.com

We have an Java workflow application that uses an Oracle database to track its steps and interactions with other services. During a workflow run several insert/update/selects are performed and occasionally ...

27. Is it possible to use a database link between an oracle database and a postgresql database on different physical servers    stackoverflow.com

I've trying to streamline an intranet application where some of the data is stored locally on the postgresql database of the intranet server and other related data is stored in our ...

28. java.sql.SQLException encountered when connecting to Oracle database    stackoverflow.com

I get the following error when I try to run my project:

java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))

29. Is possible to get a constant value declared in a database package? (SQL or Java)    stackoverflow.com

I have some Packages in a Oracle database. They contain stored procedures, functions and constants. I can call functions, in Java, using a Java CallableStatement. Also, I could execute a SQL ...

30. large sql resultsets in java    stackoverflow.com

How can I fetch large resultset in java? I have about 140,000 rows with 3 columns.

31. MySQL 'create schema' and 'create database' - Is there any difference    stackoverflow.com

Taking a peak into the 'information_schema' database and peaking at the metadata for one of my pet projects, I'm having a hard time understanding what (if any) differences there are between ...

32. ORA-0 [BEA][Oracle JDBC Driver] Unhandled sql type    stackoverflow.com

I have a very simple sql query and when i try to execute, i get following error

java.sql.SQLException: [BEA][Oracle JDBCDriver]Unhandled sql type  at
weblogic.jdbc.base.BaseExceptions.createException(Unknown Source)  at
weblogic.jdbc.base.BaseExceptions.getException(Unknown Source)  at
   ...

33. build oracle sql query dynamically from java application    stackoverflow.com

How do I build oracle pl/sql query dynamically from a java application? The user will be presented with a bunch of columns that are present in different tables in the database. ...

34. how to get oracle port no from sql query    stackoverflow.com

Is it possible to get the oracle server port no from a sql query? Thanks,
Fell

35. Oracle JDBC select with WHERE return 0    stackoverflow.com

Similar question to: http://stackoverflow.com/questions/903488/strange-problem-with-jdbc-select-returns-null but people didn't ask for this. My code:

public int myMethod(String day) throws SQLException{
  String sql = "Select count(*) from MyTable WHERE someColumn = " + day;
  Connection ...

36. Closed Cursors - SQL Best Practice    stackoverflow.com

I work in a large, separated development team. The project I'm currently working on has an Oracle DB team that develops the stored procedures and other related components that our J2EE ...

37. Prepared statement - using a function as part of the where clause    stackoverflow.com

I am working with a Java prepared statement that gets data from an Oracle database. Due to some performance problems, the query uses a "virtual column" as an index. The query looks ...

38. Does PL/SQL have an equivalent StringTokenizer to Java's?    stackoverflow.com

I use java.util.StringTokenizer for simple parsing of delimited strings in java. I have a need for the same type of mechanism in pl/sql. I could write it, but if ...

39. Problem with dbUnit: java.sql.SQLException: Closed Statement    stackoverflow.com

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

40. What's the easiest way to parse multi-line SQL statements in Java?    stackoverflow.com

I'm pretty new to Java, but I'm looking for Java code that can take multi-line SQL statements, say something like this from a flat file:

CREATE OR REPLACE TRIGGER REQ.TR_INPT_STAY_DETAI_SQ_BI
  BEFORE ...

41. Find a Database table's unique constraint    stackoverflow.com

I'm trying to find the unique constraints of a table using Java (on an Oracle Database, but that should make no difference). I found a way to discover the Primary Keys of ...

42. Supporting both Oracle and MySQL: how similar is their SQL syntax?    stackoverflow.com

We use Oracle on a project and would like to also support MySQL. How close are their SQL dialects? Is it perhaps even possible to use the same SQL ...

43. OJB / Oracle XE sql debug-display problem    stackoverflow.com

I have a Java application, and use OJB as my ORM technology. I have an Oracle XE installation locally to develop against. The problem is when I need to ...

44. Maintaining / Auto-generating IBatis SQL Maps?    stackoverflow.com

I just started a new job and inherited the project from hell. Hell = {2 years over schedule, overly complex, uses both oracle and sql server} There are 100+ stored procedures in the ...

45. Converting Oracle date arithmetic to work in HSQLDB    stackoverflow.com

I'm trying to spot-test an Oracle backed database with hsqldb and dbunit, but I've run into a snag. The problem is with the following EJB-QL (simplified a bit):

SELECT o 
FROM Offer o ...

46. Most of the scenarios in Relational Database Management System Primary Key used will be of Integer Type why?    stackoverflow.com

Its been habitual in most of the scenarios while developing a database design we set primary key as integer type for a unique identifier in the Table , Why not ...

47. Hi getting java.sql.SQLException: ORA-04031:    stackoverflow.com

Hi I am getting Following when query executing on through web application

java.sql.SQLException: ORA-04031:
java.sql.SQLException: ORA-04031: unable to allocate 48784 bytes of shared memory ("shared pool","SELECT   emplid levempid, '2...","Typecheck","qry_text : qcpisqt")

48. Export oracle table data as sql Insert-String    stackoverflow.com

this is me Francisco that wrote the first question click-here , but now I've an account. I'll try to explain it. For a Job I need a .txt file ...

49. What would cause this query to stop working?    stackoverflow.com

I'm working with a legacy Java app that's pulling data from Oracle. One of the queries appears to no longer work:

select {hier.*} from ecadmin.dept_hier_cache hier
connect by prior parent_deptid = deptid
start with ...

50. How to print on Oracle SQL Developer console using SQLJ    stackoverflow.com

I created and launch the SQLJ Java procedure (using SQL Developer). I have some bugs and I would like to debug Java source code. What is the best way to do ...

51. Insert Java variable using Java in SQL    stackoverflow.com

I was trying to do:

 String sql = "INSERT INTO CURRENT_WEATHER_US VALUES("+city_code+",   
"+object.city+","+object.region+","+object.country+","+object.wind_chill+",  
"+object.wind_direction+", "+object.wind_speed+","+object.humidity+","+object.visibility+", 
"+object.pressure+","+object.rising+",  
"+object.sunrise+","+object.sunset+","+object.textual_description+",  
"+object.condition_code+","+object.temp+","+object.for_temp_high+",  
"+object.for_temp_low+","+object.for_description+","+object.forecast_code+")";   

  stmt.execute(sql);  ...

52. Execute dynamic sql and pl/sql in Java via web interface    stackoverflow.com

Currently I'm making sort of SQL command line interface for web-based application. It should act roughly like sqlPlus. I have encountered a problem how to execute sql's. They can be both as ...

53. SQL Update hangs Java program    stackoverflow.com

I'm writing a combination Java/Perl program that parses XML files into an Oracle SQL database. There are two tables in the database - one that holds the data from the XML ...

54. java.sql.SQLException: Io exception: Got minus one from a read call during JDBC connection with oracle    stackoverflow.com

Hi I am new to java when I tried to connect oracle with my java sample code I got the above exception My Code is

import java.sql.*;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class ...

55. Getting error while executing dao    stackoverflow.com

Hello friends i am running code given below which contains the setLogTimeEntery function and when this function is executed i am getting "Error : java.sql.SQLException: ORA-00917: missing comma" error and my ...

56. oracle sql converter CharacterConverter12Byte problem in jdbc    stackoverflow.com

I was working on a simple GUI script yesterday using jdbc in eclipse. When I went to run the program, I got a pop up screen asking me about character conversion in ...

57. Oracle JDBC connection with Weblogic 10 datasource mapping, giving problem java.sql.SQLException: Closed Connection    stackoverflow.com

Oracle JDBC connection with Weblogic 10 datasource mapping, giving problem java.sql.SQLException: Closed Connection I am using weblogic 10 JNDI datasource to create JDBC connections, below is my config

<?xml version="1.0" encoding="UTF-8"?>
<jdbc-data-source xmlns="http://www.bea.com/ns/weblogic/90" xmlns:sec="http://www.bea.com/ns/weblogic/90/security" ...

58. ibatis/Oracle - SELECT query inside INSERT is failing    stackoverflow.com

I'm trying to do an Insert operation using iBatis.

    INSERT INTO SCHEMA.TABLE
       (FIELD1, 
        FIELD2, ...

59. LPX-00607 for ora:contains in java but not sqlplus    stackoverflow.com

I am trying to doing some SQL queries out of Oracle 11g and am having issues using ora:contains. I am using Spring's JDBC implementation and my code generates the sql ...

60. Weblogic 10.3, JDBC, Oracle, SQL - Table or View does not exist    stackoverflow.com

I've got a really odd issue that I've not had any success googling for. It started happening with no changes to the DB, connection settings, code etc. Problem is, when accessing a servlet, ...

61. Dynamically assembling SQL queries in Java    stackoverflow.com

What is the best way to assemble an SQL query with join conditions dynamically? I don't want to hard code the query for each different condition on a webpage or a ...

62. bulk insert from Java into Oracle    stackoverflow.com

I need to insert many small rows rapidly into Oracle. (5 fields). With MySQL, I break the inserts into groups of 100, then use one insert statement for every group of 100 ...

63. Ordering by a max or a min from another table    stackoverflow.com

I have a table that consists of a unique id, and a few other attributes. It holds "schedules". Then I have another table that holds a list of ...

64. Printing SQL Query In PreparedStatement in oracle.jdbc.driver.OraclePreparedStatement    stackoverflow.com

I need to see the query being sent to Oracle from a Java program. In the PostgreSQL JDBC driver, toString() does the job, but the same does not apply to prepared ...

65. java.sql.Exception ClosedConnection    stackoverflow.com

I am getting the following error:

java.sql.SQLException: Closed Connection at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) ...

66. using ansi sql syntax for formatting Numeric    stackoverflow.com

I am using two different databases for my project, Oracle and Apache Derby, and am trying as much as possible to use the ANSI SQL syntax supported by both of ...

67. SQL running slow when i executes thru java    stackoverflow.com

I am trying to execute a query in oracle db. When i try to run thru SQLTools the query is executing in 2 seconds and when i run the same query thru JAVA ...

68. ava.sql.SQLSyntaxErrorException: ORA-00904:    stackoverflow.com

I'm creating a web project under wicket, Spring, Hibernate, Oracle 11. I have an error, please help to resolve it. This is my config file:

    <?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  ...

69. How can I use a treetable to show all rows in an Oracle SQL table?    stackoverflow.com

I have this table: CREATE TABLE perarea ( id_area INT primary key, nombre ...

70. iBatis error when applying parameter map - Caused by SQLException: Cursor is closed    stackoverflow.com

I am debugging an issue with a null ResultsMap returned from calling a stored procedure via iBatis for java. Here is a truncated copy of the exception I'm receiving.

DataAccessException: Exception calling ...

71. java.sql.SQLData - Oracle object mapping problem    stackoverflow.com

I am using java.sql.SQLData interface to map my java objects to Oracle database types. For example, I have an object type Person in Oracle DB defined as:

CREATE OR REPLACE TYPE PERSON AS ...

72. Parameterized Oracle SQL query in Java?    stackoverflow.com

I've been trying to figure out why the following code is not generating any data in my ResultSet:

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL = ? ";
PreparedStatement prepStmt = ...

73. java.sql.SQLException: Exhausted Resultset    stackoverflow.com

I get the error java.sql.SQLException: Exhausted ResultSet to run a query against an Oracle database. The connection is via a connection pool defined in Websphere. The code executed is as follows:

 ...

74. What Java data type corresponds to the Oracle SQL data type NUMERIC?    stackoverflow.com

What Java data type does the Oracle JDBC driver assign to the Oracle SQL data type NUMERIC? Does this vary with the size of the NUMERIC type?

75. Can I have a mutex table in my database (Oracle) with just plain SQL?    stackoverflow.com

I would like to have a table in my Oracle database whose rows act as locks. The table would have one column, a varchar, and my clients (Java processes over ...

76. Empty ResultSet after executing PreparedStatement    stackoverflow.com

When I execute sql query SELECT * FROM TABLE (TEST.getDevices()) in SqlDeveloper I've got about 200 rows, but when I try to execute it in java:

//cut here
String query = "SELECT * ...

77. Optimize Oracle SQL with large 'IN' clause    stackoverflow.com

Here I have a query like below:

SELECT field
FROM table
WHERE value IN ('val1', 'val2', 'val3', ... 'valn')

78. nested loop for retrieving specific row doesn't work    stackoverflow.com

The below code doesn't work. The while loop doesn't display any values. If I change it to 0 and 150 it works fine. Please help me out here. Anything other than ...

79. SQL/JDBC : select query on variable tablenames    stackoverflow.com

I'm using Oracle DB and I would like to write a SQL query that I could then call with JDBC. I'm not very familiar with SQL so if someone can help ...

80. Oracle & java dynamic 'Order by' clause    stackoverflow.com

I am trying to build a dynamic sql query in java (shown below)

    sqlStr = "Select * " +
        "from " ...

81. BigDecimal to SQL NUMBER: check for value larger than precision    stackoverflow.com

In my app, I handle numbers as BigDecimal and store them as NUMBER(15,5). Now I'd need to properly check on Java if the BigDecimal values would fit the column, so that ...

82.  PreparedStatement error     stackoverflow.com

I have two select statements and make 'union all' for these two statements. Then, I use the PreparedStatement and when I setString to this preparedStatement, it shows "java.sql.SQLException: Missing IN or ...

83. Parse SQL via Oracle's JDBC driver    stackoverflow.com

I'd like to test whether given SQL statement is syntactically and semantically valid (ie. no syntax errors and no field misspellings). For most databases Connection.prepareStatement and PreparedStatement.getMetaData would do the trick (no ...

84. how can i pass information from ant to sql file or procedure    stackoverflow.com

i am creating a user by using ant target as below

<target name="create_user">
     <exec executable="sqlplus" dir="${basedir}/dbsetup">
          <arg value="system/oracle@orcl"/>
 ...

85. Mysterious SQL blocking my stored procedure from executing on ORACLE    stackoverflow.com

I am trying to run a procedure on ORACLE with the thin jdbc client and c3p0. here's the code that's supposed to run:

        Map<String, Object> ...

86. How to view Execution Plans for Oracle Database in Java    stackoverflow.com

With SQL Plus for Oracle Database, I can call SET autotrace on and then see Execution Plan, statistics, etc. The problem is that I want access to information about the Execution Plan and statistics ...

87. Help with SQL syntax    stackoverflow.com

I am not very good with sqls and I am trying to understand an sql statement which I haven't seen before. We're using Oracle and the IBatis framework so this might ...

88. Create a oracle db trigger using thin jdbc driver    stackoverflow.com

currently I setting up a test environment for an application. I'm using jUnit and Spring in my test environment. Before a test execution I want to set up a database test ...

89. How do I modify an oracle.sql.ARRAY argument in a Java stored procedure?    stackoverflow.com

When writing Java stored procedures, I often receive values via oracle.sql.ARRAY parameters. However, how do I export values via oracle.sql.ARRAY parameters? For example:

public static void fetchFavoriteFoods(oracle.sql.ARRAY favoriteFoods) {
    ...

90. Using Types.NVARCHAR with oracle JDBC driver to work with Cyrillic chars    stackoverflow.com

I am trying to use the "New Methods for National Character Set Type Data in JDK 1.6", to get a standard JDBC solution to handle cyrillic chars, but when ...

91. How can I get the current date/time from a DB server via JDBC, regardless of DB vendor?    stackoverflow.com

I'd like to be able to get the current date/time (timestamp) from the DB sever I have a Connection to, regardless of the DB. (Ideally, I'd like something that works for ...

92. How can I prevent this exception? java.sql.SQLException: Fail to convert to internal representation:    stackoverflow.com

My code is throwing the above exception on the following line (line 2 of this) :

final ArrayDescriptor tParamArrayDescriptor = ArrayDescriptor.createDescriptor("MY_SYSTEM.T_PARAM_ARRAY", databaseHandler.getConnection());
final ARRAY oracleArray = new ARRAY(tParamArrayDescriptor, databaseHandler.getConnection(), myObjects.toArray());

93. insert BLOB file from Local to DB    stackoverflow.com

I am trying to allow users to choose a file from local hard disk and to insert it with java in a Oracle Data base as BLOB value. The setBinaryStream did not ...

94. creating a random blob in jdbc and writing it to oracle    stackoverflow.com

I want to create many blobs in java [in memory] and write it to Oracle table. What I want is the blob[bits inside it] to be random or sudo random so ...

95. JDBC returning empty result set    stackoverflow.com

I'm using JDBC for very simple database connectivity. I have created my connection/statement and executed a query. I check the query object of the statement in the debugger to confirm that it is ...

96. JDBC and SQL+ return different results on Oracle DB    stackoverflow.com

Query in question:

select count(*) from test m where m.IND_1 is not null AND m.IND_2 is null

97. Oracle 11g and Database Connection    stackoverflow.com

I need to have access to a SQL worksheet in 11g for my coursework. In university I have access to a Oracle server but I wish to do it on my ...

98. How to include multiple commands in a JDBC SQL query to Oracle 10g?    stackoverflow.com

I'm completely new to Oracle (I come from MySQL and MSSQL) and am novice at JDBC. One of my table creation queries looks like this:

CREATE TABLE  "LISTS" 
   ...

99. How to see the actual optimized SQL query in a database    stackoverflow.com

Databases usually optimize queries before executing them. For a query issued using JDBC or a command line client is it possible to see the actual optimized query that was executed by ...

100. .nextval JDBC insert problem    stackoverflow.com

I try to insert into table with sequence .nextval as primary key, the sql in Java is

sql = "INSERT INTO USER 
         ...