oracle

What is the way to get the timespan between two dates in Oracle 9i

Hi, Which is the best way in Oracle 9i to get the difference between two dates including the times ? Something like the Timespan function in C#. Thanks, Chak ...

Oracle database table insertion

I have two tables: create table Number( num number(5)); create table Entry(id number(3), name varchar(50)); How can I increment the num field of Number table in Oracle whenever I insert something in the Entry table? ...

Using Java ApplicationModule to connect to Oracle Database

I'm working in JDeveloper and I need to connect to Oracle Database using the ApplicationModule interface. So I set up the connection, imported the table and created a view. Then I created a new ApplicationModuleImpl that connects to that view. Now, how do I get to the data through the ApplicationModule object? I read the documentation ...

How do I use a version of ODP.NET different to the one I built with?

I've got an app that is built with ODP.NET 2.111.6.20 - all the references in VS are set Specific Version to false, but when I try to run the app on a machine that only has 2.111.6.0, it throws an error saying it can't find the 2.111.6.20 assembly. How can I get my app to run with any version of ODP.NET 2.111? ...

Issues Connecting to SQLExpress using Oracle SQL Developer

Hey Guys, I'm trying to create a connection inside Oracle SQL Developer to a SQLExpress database I have Everything I have resides on the same machine so there isn't any network issues I should have to deal with but everytime I follow the instructions and I try to connect I get the following message "Failure - Unable to get informatio...

Hibernate translation capabilities

Hi Our project must be able to run both in Oracle and SQL Server. The problem is we have a number of HQL + native queries with non-standard operators (i.e. bitand and || ) and functions ( i.e. SUBSTR ) that work fine in Oracle but not in SQL Server. I wonder if Hibernate is capable of translating them dynamically. I suppose that with ...

Invalid Number Error! Can't seem to get around it.

Oracle 10g DB. I have a table called s_contact. This table has a field called person_uid. This person_uid field is a varchar2 but contains valid numbers for some rows and in-valid numbers for other rows. For instance, one row might have a person_uid of '2-lkjsdf' and another might be 1234567890. I want to return just the rows with valid...

get the first row of a join

Hi guys! I've a query like this: select t1.id, t1.sample, t2.id from table t1 join table t2 on t1.sample = t2.sample and t2.id > t1.id I need to get the first row that satisfy the second condition. Any idea? ...

How to Install DBD::Oracle in Strawberry Perl

I am trying to install DBD::Oracle using the CPAN shell in Strawberry Perl. I initially experienced an error because the Makefile could not locate an OCI library, so I installed the instant client from Oracle. I thought this would fix the problem, but now I get a large mixture of errors and warnings from Oracle.h, dbdimp.h, Oracle.c, O...

Will Oracle 9i OCI driver work with an Oracle 10g server?

I'm in charge of a Perl application that uses DBD::Oracle compiled against a 9.2.0.8 OCI client. This is on a sun4u box, albeit with 32-bit binaries. DBD::Oracle is linked against 32-bit OCI libs. We're currently running against a 9i server. Our DBA team is planning to upgrade the server to 10g, namely 10.2.0.4. I'm faced with two optio...

How do I rename primary key values in Oracle?

Our application uses an Oracle 10g database where several primary keys are exposed to the end user. Productcodes and such. Unfortunately it's to late to do anything with this, as there are tons of reports and custom scripts out there that we do not have control over. We can't redefine the primary keys or mess up the database structure. ...

T-SQL's equivalent of Oracle's %TYPE operator?

When writing a procedure in PL/SQL, I can declare a parameter's type as myTable.myColumn%TYPE so that when I alter myColumn's type from say varchar2(20) to varchar2(50) I don't have to change the procedure's parameter type. Is there something similar in T-SQL? ...

How to create a menu in SQLPlus or PL/SQL

I have several scripts that I would like to start from a menu presented to the SQLPlus user. Something like: Please make a selection: 1: Do script a 2: Do script b 3: Do script c I just need a point in the right direction, not a quick answer. ...

SQLPlus - Count function across several tables

hello, i am trying to count the number of riders in my data. and i am having trouble figuring this out. sample of my output is noted below. The data comes from many different tables and I had to join the tables which is not the problem I am having. I am trying to get the count number of RIDERS by EVENT by DESCRIPTION. And still disp...

How do I select part of a BLOB field in Oracle?

I have a table in Oracle that stores files as byte arrays in a BLOB field. I want my client to be able to download the stored file in small chunks (as opposed to downloading the whole thing all at once) so I can display a progress bar. How can I write an Oracle query that retrieves only part of the contents of a BLOB field? I imagine ...

How bad is it to simulate IDENTITY/AUTOINCREMENT columns using triggers in Oracle?

I maintain an application that was originally written to be SQL Server-specific (using IDENTITY fields). Thus, we've had to define a lot of triggers to auto increment tables' primary keys. I'm told that this is considered to be a hacky workaround in the Oracle world, but that was told to me in a "friend of a friend" kind of way. How b...

Get value from oracle stored procedure

Hi all I have a oracle procedure which should return a concatenated string of all the parameters create or replace procedure tin_builder (type in varchar2, tin_serial in number, rand_digit in varchar2, tin out varchar2 ) is BEGIN tin := type || TO_CHAR(tin_serial) || rand_digit ; END Now i want to call the proc...

how to increase oracle query when 22,000,000 records in single table?

I have oracle 10g installed on windows server 2003. I have 22,000,000 records in single table and this is a transactional table, increasing of records in same table approx. 50,000 per month. My question is that whenever I run query on it always my query too slow. Is there any method by which I can improve the performance of the query, l...

Oracle type compare

Hi all Oracle & objects: I have a table that contain more than one type of object (by using inheritance),but i want to know the actual type of each (using loop and ?). Is there a function like isInstanceOf() here? plz provide an example Thanks in Advance ...

oracle stored procedure problem

Hi I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. Procedure: create or replace procedure temp_proc is begin DBMS_OUTPUT.PUT_LINE('Test'); end it is created successfully. But when i execute: execute temp_proc; it shows ORA-00900: invalid SQL st...