I have been porting oracle selects, and I have been running across a lot of queries like so: SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id; ...and: SELECT last_name, d.department_id FROM employees e, departments d WHERE e.department_i...
Hi Everyone, I work as a developer on a small development team, and something has annoyed me to the point where I have decided to act... Oracle does not support a Bit datatype - or indeed anything else that makes obvious sense in true/false scenarios. Undeterred however, before I joined the team my forebears decided to use char(1) fiel...
Hi, I doing a Proof Of Concept to figure out how efficient to call a store procedure using EclipseLink. I was able to call oracle store procedure using EclispeLink with Scalar/primitive data types (link Integer, varchar etc). I wanted to understand how can I handle Oracle Store procedure from EclipseLink with collection(Oracle Types/Use...
Is there an easy way to convert an XMLType to a User Defined Type? I can convert the UDT to XMLType using the below. select SYS_XMLGEN(pUDT) into param2 from dual; I can't though, is find a function that takes that and turns it back into that UDT using the same mappings the SYS_XMLGEN used. ...
How do you detect the current SCAN state and revert it after changing it? An exerpt from the script in question: SET SCAN OFF SET ECHO ON SET SQLBLANKLINES ON SET SCAN ON UPDATE TABLE_NAME SET CREATED_BY = &&created_by; SET SCAN OFF The problem is that if the script doesn't have the first line (SET SCAN OFF), then the code to prompt...
Could anyone tell me how to bulk insert data from a ref cursor to a temporary table in PL/SQL? I have a procedure that one of its parameters stores a result set, this result set will be inserted to a temporary table in another stored procedure. This is my sample code. CREATE OR REPLACE PROCEDURE get_account_list ( type_id in account_t...
Hi, Using an Oracle temporary table does not generate much redo log as a normal table. However, the undo log is still generated. Thus, how can I write insert, update, or delete statement on a temporary table but Oracle will not generate undo log or generate as little as possible? Moreover, using /+append/ in the insert statement will g...
We are using a JEE application and we are right now using Informix DB.Our code hits the DB with queries like "select first 10 * from test" Now as far as I know Oracle does not support 'first 10 *' kind of statements.We have more than 1000 queries like this.Should we manually change this or can have some manual customization? ...
The following query works on Oracle 10.2.0.1.0 on windows,but doesn't work on Oracle 10.2.0.2.0 on Linux. Error report: SQL Error: ORA-00904: "T"."AUDIT_USECS": invalid identifier 00904. 00000 - "%s: invalid identifier" It works after i remove the sub-query. I found that if use fields of T in sub-query,then error occurs. Is it saying...
Have a lot of unnecessary results using contains() method in my query. Don't tell me to use like or something else. It is hardcoded and couldn't be changed. ...
How can I get the scripts of Stored procedures, Views, Functions, Triggers in toad for oracle? ...
Continuation to my previous question Migrating to oracle Is there any tool or script to convert SQL procedures in Informix into Oracle? ...
Suppose I have a user defined type: CREATE OR REPLACE TYPE TEST_TYPE AS OBJECT ( f1 varchar2(10), f2 number(5) ); Now, I want to create a table to hold these types. I can do the following: create table test_type_table ( test_type_field test_type ); This gives me a table with one column, test_type_field. Is there an...
I am using a pretty complex query to retrieve some data out of one of our billing databases. I'm running in to an issue where the query seems to complete fairly quickly when executed with SQL Developer, but does not seem to ever finish when using the OracleDataAdapter.Fill() method. I'm only trying to read about 1000 rows, and the quer...
I am trying to diagnose this exception : System.Runtime.InteropServices.COMException (0x80070008): Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008) at System.Runtime.Remoting.RemotingServices.AllocateUninitializedObject(RuntimeType objectType) at System.Runtime.Remoting.RemotingServices.Alloc...
Can anyone spot the error in this attempted data load? The '\\N' is because this is an import of an OUTFILE dump from mysql, which puts \N for NULL fields. The decode is to catch cases where the field might be an empty string, or might have \N. Using Oracle 10g on Linux. load data infile objects.txt discardfile objects.dsc truncate in...
Hi, I'm finding some problems with a query that returns the sum of a field from a table for all the records that meet certain conditions. I expected to receive a "No records found' when there were no records, but instead I'm receiving a null result. SQL> SELECT * FROM DUAL WHERE 1=2; no rows selected SQL> SELECT SUM(dummy) FROM DUAL W...
Dears, I have a report server project in VS2005, and I added an oracle data source to my project and I did anything like my colleage! both of us did the same settings but I can't connect to the server. I connected to DB in TOAD but in VS2005 I couldn't. What should I check? I get this error during test connection : ORA-12154: TNS:could n...
I want to create a menu in oracle report 10g. Tell me how i can do it. thanks ...
Hi guys I want to know how can we know the other users of Oracle 10g on the same system? Is there any query for that? Thank you!!! ...