Suppose you have a table (in Oracle): CREATE TABLE CUSTOMER ( customer_id NUMBER, gender CHAR(1) ); And suppose you have a table type: CREATE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER; Is it possible to write a GROUP BY query such that, for each group, that group's primary key fields are stored in a NUMBER_TABLE_TYPE? For ...
Hi, I have a .Net service that connects to an Oracle database on every request. It works fine at the beginning, but after some number of requests I start getting: Oracle.DataAccess.Client.OracleException ORA-03135: connection lost contact at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection c...
I need to complete some tasks in EM. But I don't have access to web interface and ssh. Is there other way to configure EM? I think EM should have pl/sql API, but I didn't find any information about it. Only idea that came to my head it's to implement java stored procedure that interact with CLI. But maybe less complex solution exists? ...
I want to return rows from a select statement within a declare/begin/end block. I can do this in T-SQL but I would like to know how to do it in PL/SQL. The code looks a bit like the following: declare blah number := 42; begin select * from x where x.value = blah; end; ...
I'm using oracle 11g. I'm inserting date and time using function SYSDATE into column with type TIMESTAMP. But fractional seconds always are 0. How can I insert date and time of database server with fractional seconds? Is there any functions? ...
Hi, I am exporting data from an Oracle database to csv file using the spool command as follows: set heading off set linesize 1000 set long 1000 set pagesize 0 set echo off set verify off spool D:\OVERNIGHT\TEMP_FILES\SE_NEW_PFRA_CRYSTAL_OUTPUT.txt SELECT TRIM(FU_BAN) ||'|'|| TRIM(CASE_ID) ||'|'|| TRIM(case when fu_type is null then ...
I work in a company where we use a lot of Oracle databases, which are used by our in-house software. The database is updated as the software changes and these updates are put in PL/SQL Scripts. We have clients that are still running older versions of our software and so have 'older' versions of our database. Now when we upgrade a cli...
How can I create a composite key on multiple columns, one of which can have some value but not null (or some constant value)? For example: PK Loc_ID Date Time Cancelled 1 1 01/01/2010 10:00AM YES 2 1 01/01/2010 10:00AM YES 3 1 01/...
In DataMapper, I have tables like this: Foo === id Integer other_columns Whatever Fuzz === id Integer other_columns Whatever For associations: class Fuzz has 1, :foo, :child_key => :id end When I call: Fuzz.first.foo DataMapper generates SQL like this: select raw_sql_.* from(SELECT "ID", "OTHER_COL...
I have a table structure similar the following: create table MAIL ( ID int, FROM varchar, SENT_DATE date ); create table MAIL_TO ( ID int, MAIL_ID int, NAME varchar ); and I need to run the following query: select m.ID from MAIL m inner join MAIL_TO t on t.MAIL_ID = m.ID where m.SENT_DATE betwee...
I've asked: http://stackoverflow.com/questions/3366619/how-to-break-connections-tcp-ip-by-keepalive-postgresql-without-changing-anything And now, I want to confirm: My system (C#.NET, NHibernate and Active Record) is running with a database PostgreSQL 8.4 and a Windows Server. I need a way to break idle or lost TCP/IP connections and u...
Environment: Server: Oracle 11.2g server on a 64-bit windows 2008 Client: Oracle 11g client on Windows XP SP3, ASP.Net 4.0, Visual Studio 2010, C# Input size of XML ~ 1,206,500 characters (Calculated based on the maximum data that I would have). Scenario: The web application generates the XML that the oracle stored procedure uses to...
I have two date strings, in the format MM/DD/YYYY and would like to query an Oracle database for any records between those two dates. How do I do this succinctly and simply? ...
While running database configuration assistant(oracle 9i) I get error oversubscribed literal/length tree.Please help me out. ...
Need to rank the below by salary, with highest salary having rank 1. RANK column shown is what I'm after. Empname sal address RANK Ram 3411 45,east road 2 Anirban 2311 34,west wind 4 Sagor 10000 34,south 1 Manisha 3111 12,d.h road 3 ...
Hi, I have two tables: players (has a team name column) and teams (also has a team name column) I want to only allow inserts of new players if the team name exists in the team table. Any help would be appreciated. Please keep it simple because I'm still learning. ...
This is a practice assignment where I have to create a table space with two datafiles. We have to use the exact naming conventions that are given to us. I believe I have it right, but I get this error: "ORA-00972: identifier is too long". I think I'm getting this because the path and filename are so long. The question is, how do I get ar...
Index Organized Tables (IOTs) are tables stored in an index structure. Whereas a table stored in a heap is unorganized, data in an IOT is stored and sorted by primary key (the data is the index). IOTs behave just like “regular” tables, and you use the same SQL to access them. Every table in a proper relational database is supposed to ha...
I created a table named dual2. I've a rows there, and can select from it. When attempting to drop it, it produces this error: ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist However, the table still exists! It returns from dba_tables and user_tables. Any ideas on wha...
Why is long an issue with Oracle? It will be better if some one can tell me what are the issues with using the long data type in oracle? something related to storage? I was asked this in an interview. ...