oracle

Flashback Table in Oracle 10g

How to use the Flashback Table feature in Oracle 10g. I want to know all the steps involved in detail. ...

How to get record count using reference cursor in oracle10g

I want to know how we can get record count using reference cursor in oracle10g. ...

Batch multiple select statements when calling Oracle from ADO.NET

I want to batch multiple select statements to reduce round trips to the database. The code looks something like the pseudo code below. It works perfectly on SQL Server, but does not work on Oracle - Oracle complains about the sql syntax. I have had a look around and the only examples I can find of returning multiple result sets from Orac...

Queue Oracle transactions using PHP oci_pconnect function in a webservice

I have written a webservice using the PHP SOAP classes. It has functions to return XML data from an Oracle database, or to perform insert/update/delete on the database. However, at the moment it is using Autocommit, so any operation is instantly commited. I'm looking at how to queue up the transactions, and then commit the whole lot on...

Accessing Oracle Database from C#

I have a C# program that uses System.Data.OracleClient to access an oracle database. The code uses OracleCommand, OracleDataReader objects. And it uses the TNS names to refer to specific oracle servers (as defined in the tnsnames.ora file). It runs fine on my computer. And then I copied the binary to another computer, and upon running ...

Compare strings by their written representation - is it possible?

For example I have a column that contains string (in English - "A", "B" ) "AB1234" and I'd like to compare it to the string "AB1234" (in Russian "A", "B" ), for Example. Is there any built-in function to achieve this? The best way I found is to use Translate func where i enumerate all needed symbols. ...

Concurrent queries on a given JDBC connection?

I'm seeing OraclePreparedStatement executeQuery() exhibit serialization. That is, I have two queries that I want to run concurrently against an Oracle database, using the same connection. However, the OraclePreparedStatement seems to explicitly prohibit concurrent queries. My question is: Is this serialization a necessary artifact of...

Resultset logic when selecting tables without a join?

Dumb question.. what logic does the database use to determine the result set if you do a select without a join such as: select * from table1, table2 ...

Select data from a record collection type.

I have a stored proc in oracle 11g server that has an out variable of record. I cannot edit this procedure. I am creating a function that will call the procedure and return the information in the record set. I looked at the following question asked here: past question My Question is can I create a type of table for a record and query i...

How do I Open a Stored Procedure and Edit it in SQL*Plus

I need to make some changes to an old Oracle stored procedure on an obsolete box that is being kept around to run an old legacy process. The only tool I can connect to the db with is SQL*Plus. How do I load a stored proc into memory for editing in SQL*Plus? The tutorials I've found on-line don't exlain how that's done. :-( ...

Continuing Inserts in Oracle when exception is raised

Hi, I'm working on migration of data from a legacy system into our new app(running on Oracle Database, 10gR2). As part of the migration, I'm working on a script which inserts the data into tables that are used by the app. The number of rows of data that are imported runs into thousands, and the source data is not clean (unexpected nul...

Possible to create Oracle associative array type outside of a package/procedure?

In Oracle Database 10g, is it possible to create an associative array type outside of a package or procedure? I would like to be able to do this so that I can reference this associative array type in another type. For example: create type my_type_map is table of varchar2(10) index by varchar2(10); create type my_other_type as object ...

SQL GROUP BY week (Monday 07:00:00 to Monday 06:59:59)

Hi folks, I've created a query that groups production data on ISO week by using this query SELECT to_char(wid_date,'IYYY'), to_char(wid_date,'IW'), tonnes FROM production WHERE to_char(wid_date,'IYYY') = '2009' GROUP BY to_char(wid_date,'IYYY'), to_char(wid_date,'IW') The problem is that our "production weeks" don't follow the ISO ...

Suggestions to connect Oracle db with SAP CRM via Web Service

Which Web Service platform to choose and why? On the Oracle db host we have also access to Informatica ... The amount of data to transfer is minimal - e.g. low cost solution will be appreciated The first combination which came to mind was GlassFish + Hibernate ...

Oracle - Is there any effects of not having a primary key on a table ?

We use sequence numbers for primary keys on the tables. There are some tables where we dont really use the primary key for any querying purpose. But, we have Indexes on other columns. These are non-unique indexes. The queries use these non-primary key columns in the WHERE conditions. So, I dont really see any benefit of having a primary...

Getting the return value of a PL/SQL function via Hibernate

I have a PL/SQL function in an Oracle database that I can't change. This function takes a parameter which identifies an entity, creates a copy of that entity and then returns the ID of the copy. This looks something like FUNCTION copy_entity(id IN NUMBER) RETURN NUMBER I need to call this function from Hibernate. I tried creating a nam...

Possible to create Oracle Database object types inside of PL/SQL?

Is it possible to create an object type inside of a package in Oracle Database 10g? Something like: create or replace package my_package as type my_type as object ( id number(15) ); end; Gives: Error(3,9): PLS-00540: object not supported in this context. What I'm ultimately looking to be able to do is use po...

In Oracle, is starting the SQL Query's WHERE clause with 1=1 useful?

I'm working with a client that starts almost all of their WHERE clauses in Oracle with 1=1. Forgive my ignorance, but isn't this a no-op? Are there any negative consequences of this usage? Here's a scrubbed example: SELECT gpz.zname ,gpp.pname FROM table1 gpp INNER JOIN table2 gpz ON gpz.p_id = gpp.p_id WHERE 1=1 ...

Unable to set up ODBC after installing ODAC (Xcopy)

We are trying to use ODAC Xcopy to minimize the footprint of installing Oracle 11g Client. Currently, we use the Oracle 11g Admin install (~700mb). I've tried using the ODAC Xcopy, and that works. However, the only issue I now have is that I cannot set up an ODBC on the target system by just installing the ODAC Xcopy. After installing ...

Drawback of large LIMIT of VAR-types

Is there any drawback (except allowing the value or array to grow too large) to setting the max integer size of a VARCHAR or VARRAY to a value significantly larger than actually necessary (or setting it to the max size allowed)? ...