oracle

Oracle Date Subtraction

How would i write an expression that gives me the difference between two dates in days, hours, minutes, seconds, etc? By default subtracting two dates in oracle returns days as a decmial. ...

What would be the SQL Select statement for mixed dates in one column?

I have a customer who has legacy data stored in Oracle database. Such data contains mixed DATE values in one column in database (that field is VARCHAR(32)). For example they store '30-Sep-2009' and sometime '1254431689' (that is a timestamp in epoch time). I have no option to split the data into multiple columns (so I have to deal with...

Problem importing LOB into Oracle using SQL Loader

I'm trying to export from SQL Server to Oracle a table that consists of an ID and a really long XML string stored as nvarchar(max). SQL Table -- CREATE TABLE MyStuff (MyID int, MyText nvarchar(max)) Oracle Table -- CREATE TABLE MyStuffImported (MyID int, MyText NCLOB) I'm using the SQL Server bcp utility to export to a text file (MyStu...

atomic compare and swap in a database

Hello: I am working on a work queueing solution. I want to query a given row in the database, where a status column has a specific value, modify that value and return the row, and I want to do it atomically, so that no other query will see it: begin transaction select * from table where pk = x and status = y update table set status ...

Oracle on Mac OS X Snow Leopard

Hi, I am trying to install oracle on snow leopard using these guidelines. http://blog.rayapps.com/2009/09/14/how-to-install-oracle-database-10g-on-mac-os-x-snow-leopard/ There is a step in the installation, where I have to change netca and dbca such that it runs in 32 bit mode. Can someone please tell me how to do it. I have pasted the...

Oracle Partitioning

I have 1 table which contain 8 subtables. Each subtable contains another 3 to 5 subtables. Now my question is that is it possible to partition tables by hierarchy? That means i like to keep all the tables info in one table and then create partitioning on them. ...

Closed Cursors - SQL Best Practice

Hi all, I work in a large, separated development team. The project I'm currently working on has an Oracle DB team that develops the stored procedures and other related components that our J2EE web-tier talks to. One thing that's arisen from development is the concept of a closed cursor when results aren't found. From my experience, c...

Reading views in Oracle using SYSobjects

Hi all, I'm using the code below to read tables and views from a SQL database, this works for Oracle tables but not Oracle views, does anyone know why it wouldn't work and how I can fix it? cmdList = new OdbcCommand("select name, user_name(uid) from sysobjects where type='U' or type='V'", cn); Thanks ...

Oracle Stored Procedure/Function Load Ordering

I have many stored procedures and functions in a folder. I have a batch file that loops over the .sql files in the folder and writes the file names to a single SQL file for execution. After this, the batch file executes the single file in SQLPlus. The problem that I am running into is the ordering of the creation of the stored procedures...

Call a package function from C#: PLS-00306

Hi, I'm trying to call an Oracle package function from C#, but I keep getting the same error: PLS-00306: wrong number or types of arguments in call to 'F_CTCPREPAREP151HEADER'. This is the function declaration in the package: function f_CtcPrepareP151Header ( as_codjal in varchar2/*, ad_datdoc in varchar2, as_codfnr in varchar2, ad_dt...

Oracle table partition

Can any one help me referring a book for Oracle table partitioning. Oracle 10g complete reference contains the basics but i need elaborate with example. Thank you ...

how do I "Order by min(subquery)"?

I am trying to return a list of dates. Each row has multiple dates concatenated as a single string. I want to order the rows by the minimum date in each row. Here is my query: SELECT br.bm_tracking_number, (SELECT TOLIST(APPT.fact_date) FROM bm_fact APPT WHERE APPT.bm_review_sk = br.bm_review_sk ...

ORA-00980 error when attempting export using the EXP command

I'm trying to export a schema in an oracle 10 database using the EXP command. Let's call the schema "myschema" and the tns name "mydb" to protect the names of the innocent. Anyway, here's the command line that I'm using exp myschema/mypassword@mydb file=myschema.dmp grants=y This works when I try to run an export on other instan...

SQL Server parallels to Oracle DBMS_METADATA.GET_DDL ?

I'm looking for command line or scripted solutions to pull the DDL out of SQL Server 2005+ for all database objects: tables, stored procs, views, indices/indexes, constraints, etc. GUI tools are not of interest. Preference is for built-in tools, since that would be the most comparable to Oracle's DBMS_METADATA stuff. Also, preference f...

Can I substitute savepoints for starting new transactions in Oracle?

Right now the process that we're using for inserting sets of records is something like this: (and note that "set of records" means something like a person's record along with their addresses, phone numbers, or any other joined tables). Start a transaction. Insert a set of records that are related. Commit if everything was successful, ...

Getting Connection timeout in OCCI

Hi All, I need to test the connectivity if the Oracle::OCCI::Connection, and how can i get and set the connection timeout value? i read the documentation of Oracle OCCI but i can't find the required functions. Thanks in Advance. ...

Need to incorporate Timezone Selection (UTC) within Web App

Hi, I need to incorporate a Timezone dropdown selection within my web app, which I need to use within an Oracle database. I basically require the user to select their timezone and I then need to use this against time stamp info stored within the Oracle tables. Unsure where/how to build this Timezone selection list within my page - exa...

Oracle: Find previous record for a ranked list of forecasts

Hi I am faced with a difficult problem: I have a table (oracle 9i) of weather forecasts (many 100's of millions of records in size.) whose makeup looks like this: stationid forecastdate forecastinterval forecastcreated forecastvalue --------------------------------------------------------------------------------- varchar (...

Database Connection Always disconnect when Developing on same PC as DB

I am developing a JSP application and I used Oracle Express 10g as database. I notice when I try to develop in the same PC, I would only be able to make several page request and transition and I immediately get IO exceptions. But I download the same code in a different PC and connect to the said database PC. It does not kick me out immed...

Oracle legacy table without good PK: How to Hibernate?

I have legacy tables in an Oracle database which i'd like to access from a Java application with Hibernate. The problem is: the tables don't have good primary keys. For instance, a table would look like this: create table employee ( first_name varchar(64) not null, last_name varchar(64) not null, hired_at date, depar...