I have a table "defects" in the following format: id status stat_date line div area 1 Open 09/21/09 F A cube 1 closed 01/01/10 F A cube 2 Open 10/23/09 B C Back 3 Open 11/08/09 S B Front 3 closed 12/12/09 S B Front My problem is that I want to ...
Just looking for some comfirmation here. We are attempting to use the merge statement for two tables called from C# thru stored procs. the calls are made from seperate threads and/or processes. We are getting unique key violations on the two columns that also make up the on statement. Our theory for this is that the statements are ha...
hi In access I can make a query (1) that returns something and make another query (2) that uses the first query (1). How I can do it in Oracle 11g ? thanks in advance ...
Just wondering if anyone has ever thought of/implemented something like this. I'm considering implementing a DB versioning scheme. Once per day, a job would run, which outputs all DDL and source code into a single file. A hash would then be run against that file. The hash would be stored in the DB with a version number. Anytime the h...
Hi, I need to drop a table and make a new one. If I drop the table and the table doesn't exist, I get an error How can I check if the table exists? I'm working on Oracle 11g Thanks in advance. ...
Hi , I have an Oracle stored procedure and it has three parameters. I am in confusion to enable this porcedure to Microsoft Excel? Once data are available in Excel, user will make a chart(graph). Can you please give me some samples of code: Stored procedure with parameters, how to connect stored procedure to Excel, Parameter pop in ...
I am validating an XML document against a schema. So far, all is going well, but there are some significant differences between the generated document and the schema (hence the validation). I've noticed that Oracle will stop validating after the first error is encountered. I'd like to be able to compile a list of errors and take them to ...
What is the magical incantation to create an identity column in Oracle? ...
Hello, I have installed Oracle 10G XE. I want to connect to it using JDBC . Which driver should i use for it and from where can i download it ? Thank You. ...
when i do query SUBSTR(e.updatetime - s.updatetime, 1,30) i will get result below +000000001 05:06:47.388000 can this save in java.util.Date? or which java class should i use other than String to ease me retrieve day,minutes,hours... p/s: e.updatetime is timestamp type ...
I just learned that by default, Oracle does not grant the 'create session' privilege to new users. No problem, I can do that myself. Anyway, I'm curious: why would I want an Oracle user without 'create session' privilege? What can it be used for? There must be some non-obvious use. My first thought was running database jobs, but AFAIK t...
i have following two queries, one is create procedure query and other is the call to the same procedure. they both run fine when running indiviually, but when i try to run them together in one go i get errors: like executed with warnings and the results are not proper with that. can you please tell me how can i achieve this? may be it ...
Hi, I have a Pl/Sql procedure signature that look like this foo(param1 IN type1, param2 IN type1, c OUT REF CURSOR). This stored procedure is being called in C#. In the C# code, I fill a DataTable with this cursor. I would like to know when the cursor will be closed. Should I close it in the SP? In the code? Or is the object OracleRef...
We're using a PL/SQL table (named pTable) to collect a number of ids to be updated. However, the statement UPDATE aTable SET aColumn = 1 WHERE id IN (SELECT COLUMN_VALUE FROM TABLE (pTable)); takes a long time to execute. It seems that the optimizer comes up with a very bad execution plan, instead of using the...
Recently I wanted to play a bit with Eclipse and JPA. Thus I wanted to create a JPA project. I got stuck when specifying the database connection, as editing the "Connection URL" (shown on the image) is not possible and I want to enter a (good, working) connection string to an Oracle RAC server which looks more or less like that: jdbc:...
We have two tables like so: Event id type ... a bunch of other columns ProcessedEvent event_id process There are indexes defined for Event(id) (PK) ProcessedEvent (event_id, process) The first represents events in an application. The second represents the fact that a certain event got processes by a certain...
I've noticed that whenever I have package-level constants (or any variables for that matter), whenever I recompile the package, persistent connections to the database get an error that "existing state of package body has been invalidated". Is there a way to avoid this? Perhaps through synonyms? What are best-practices in this case? ...
I tried to register a schema in Oracle the other day to validate an XML document. The code I used was fairly simple: dbms_xmlschema.registerSchema(schemaURL => 'http://www.myCompany.com/schema', schemaDoc => :schemaCLOB); At first, everything seemed to work well, as far as validation goes. I noticed thoug...
Hi all, I've seen discussions about this in the past, such as here. But I'm wondering if somewhere along the line, maybe 10g or 11g (we are using 11g), ORACLE has introduced any better support for "parameterized views", without needing to litter the database with all sorts of user-defined types and/or cursor definitions or sys_context va...
I have a view that is built on multiple tables from different users schema. By virtue of the currently logged in user, he is able to see the table from different schema. When the view is created the table name becomes ambiguous as the user have access to the same tables from the different schema. Is there any way to specify to use the ta...