Environment: Rails 2.2.2, Oracle 10g Most of the columns declared "date" in my ActiveRecord models are exactly that: dates: they don't care about time at all. So with a model declared thus:# class MyDateOnlyModel < ActiveRecord::Migration def self.up create_table :my_date_only_model do |t| t.date :effective_date t.ti...
So I have an Oracle instance, and I know it's running on this system, I've su'd to the oracle user, and I'm trying to connect using "/ as sysdba". However, when I do connect, it says the instance is idle. I know the database is up and opened, because my application's talking to it. My paths (ORACLE_HOME, etc.) might be incorrect: any ...
I have a table where I'm recording if a user has viewed an object at least once, hence: HasViewed ObjectID number (FK to Object table) UserId number (FK to Users table) Both fields are NOT NULL and together form the Primary Key. My question is, since I don't care how many times someone has viewed an object (after the f...
I ported a Delphi 6 application to Delphi 2007 and it uses BDE to connect to an Oracle 9i database. I am getting an "ORA-01426: numeric overflow exception" when I execute a stored procedure. This happens randomly and if I re-run the stored procedure through the application with the same parameters the exception does not occur. The old D...
So we've been told that one source of TM Enq contention can be unindexed FK's. My question is which one. I have an INSERT INTO Table_B that is recording TM Enq Wait. It contains a PK that is the parent to other tables and it has columns that are FK constrained to other PKs. So which FKs need indexed. That table's columns or its childr...
How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT? For example how do a SELECT like: SELECT foo, bar FROM foobar; Hint : BEGIN SELECT foo, bar FROM foobar; END; doesn't work. ...
By default sqlplus truncates column names to the length of the underlying data type. Many of the column names in our database are prefixed by the table name, and therefore look identical when truncated. I need to specify select * queries to remote DBAs in a locked down production environment, and drag back spooled results for diagnosis....
I have a legacy Oracle database that has multiple 1:n lookup tables that are based on CHAR(6) primary keys in the child tables. Unfortunately, I'm not the only one who has problems with how NHibernate deals with char fields (especially with those as primary keys). See: http://forum.hibernate.org/viewtopic.php?t=984281 http://forum.hibe...
I have a query that runs super fast when executed in the sql editor (oracle): 1ms. The same query (as stored procedure) when executed by a DataSet-TableAdapter takes 2 seconds. I'm just retrieving 20rows. Since I'm using a TableAdapter, the return values are stored in a ref cursor. If I was fetching 2'000 rows I could understand that ...
For complicated reasons (aren't they always) I need to query an Oracle table from a Sybase view. Any thoughts of how I can do this? example oracle con details: database: [email protected] schema: dta pwd: 123 ...
I want to be able to run my SqlDataProvider against an oracle stored procedure. I can use Microsoft's Oracle Provider but that wouldn't allow me to call a stored procedure. has anyone been able to get this to work? I particularly want to be able to use declarative data binding. I have been able to programatically create a DataTable but I...
Hello All. Is there a command in oracle 9i that displays the foreign keys of a table and also the table that those foreign keys reference? I was searching, did not find anything but i found an equivalent command that works with MySql which is SHOW CREATE TABLE Is there an equivalent command for this within oracle's SQL? I appreciate ...
Hello is there any Oracle database ( around version 10 ) front end that we can use in Linux. free or open source? I am currently using Ubuntu 8.10 and if possible I just want it to need the thin JDBC to connect to oracle and not the whole ( huge ) client of oracle installed in it. Thanks ! ...
The client has asked for a number of tables to be extracted into csv's, all done no problem. They've just asked we make sure the files are always in UTF 8 format. How do I check this is actually the case. Or even better force it to be so, is it something i can set in a procedure before running a query perhaps? The data is extracted fr...
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".): SELECT A.identifier , A.name , TO_NUMBER(DECODE( A.month_no , 1, 200803 , 2, 200804 , 3, 200805 , 4, 20...
I have two tables, we'll call them Foo and Bar, with a one to many relationship where Foo is the parent of Bar. Foo's primary key is an integer automatically generated with a sequence. Since Bar is fully dependent on Foo how would I setup the primary key of Bar given the following constraints: Records for Bar are programatically gene...
I have four tables (A,B,C,D) where A is the parent of one to many relationships with B and C. C and D are parents to a one to many relationship with table D. Conceptually, the primary keys of these tables could be: A: Aid B: Aid, bnum (with foreign key to A) C: Aid, cnum (with foreign key to A) D: Aid, bnum, cnum (with foreign keys ...
I'm porting a servlet from Tomcat 5.5 to OC4J 10.1.3.1. In Tomcat we set up a JDBCRealm for authentication and authorization. This was configured via the $TOMCAT_HOME/conf/server.xml. Is there a similar mechanism in OC4J? Where do I start looking for it? How do I define it? ...
I haven't been able to find this explicitly stated anywhere yet, but a bunch of examples I've found online follow what I've been doing. I have a C# class which uses ODP.net to connect to an Oracle DB and run a procedure that's in a package. My package has stored procedures which take a ref cursor output parameter. All the procedure ...
Hi all, I recently learnt that oracle has a feature which was pretty useful to me - as the designer/implementator didn't care much about data history - I can query the historical state of a record if it's available yet in the oracle cache, like this: select * from ( select * from sometable where some_condition ) as of t...