I'm trying to configure Oracle Database Gateway for Sybase. I've followed oracle manuals but link to sybase is not working. I receive following error while trying to select from sybase database: SELECT * FROM DUAL@billd * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: ORA-02063:...
Oracle 10g. We have a large table partitioned by a varchar2 column (if it were up to me, it wouldn't be this column, but it is) with each partition having a single value. Ex. PARTITION "PARTION1" VALUES ('C'). We also have NLS_COMP = LINGUISTIC. Partition pruning, when indicating a value in that column, doesn't work. SELECT * from ...
I need to insert a date format from an outside source which includes the three letter code for time zone, but the TZD formatting mask does not seem to work... insert into blah values (to_date('Thu, 18 Feb 2010 08:37:00 EST','Dy, DD Mon YYYY HH24:MI:SS TZD')); ORA-01821: date format not recognized If I remove the "TZD"... insert ...
Hi all, I'm new to Oracle analytic functions and I'm trying to find the best way to write my query. The following is a simplified version of a table I'm working with... CREATE TABLE my_table ( pid NUMBER NOT NULL, my_value NUMBER, value_date DATE NOT NULL, CONSTRAINT pk_my_table PRIMARY KEY (pid, v...
Here's my cursor: CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE; I immediately open the cursor in order to lock these records for the duration of my procedure. I want to raise an application error in the event that there are < 2 records in my cursor. Using the C1%ROWCOUNT property fails because it only counts t...
Let's say I've got a large script and want to cut it into pieces and then load the pieces from a main script file. The question is how to load and execute an external script using plain SQL in Oracle DBMS or PL/SQL from another script file? ...
Hey all, I have a need to check a live table against a transactional archive table and I'm unsure of the fastest way to do this... For instance, let's say my live table is made up of these columns: Term CRN Fee Level Code My archive table would have the same columns, but also have an archive date so I can see what values the live t...
Is there an API that will read a TNS file and present it in some easy-to-use data structure? I would like to get a list of all my TNS entries. This works but is not particularly elegant! grep '^[a-zA-Z].*=' /etc/tnsnames.ora | sed 's/[ =].*//' ...
Hi I need help to understand the decode part of a query that goes something like the following. SELECT ax.animal_code FROM raw_animal_xref ax, animal_xref_type axt WHERE ax.animal_mnemonic = l_animal_mnemonic -- Example 'COUGAR' AND ax.animal_code_type = axt.animal_code_type ORDER BY DECODE (animal_type, l_...
In SQL we will be having a sequence. But it should be appended to a variable like this M1,M2,M3,M4.... Any way of doing this ? ...
Hi i tried to connect oracle 10g in c++ using occi, the environment was created successfully, but when trying to create a connection ora 24960 came and it said that the attribute OCI_ATTR_USERNAME is greater than the maximum allowable length of 255 I am very new to c++ and occi so please help me in that issue Thanks in advance ...
Hi I have this problem. Given a users table that consists of users' username in a social network and friends table that contain a user's name and a user's friendname like below... username friendname John Thomas Chris James ... I'm trying to write an SQL statement that will if a user is in my network. In other words i...
Hi, i m using Oracle 9i. I m fetching data from a cursor into an array : FETCH contract_cur BULK COLLECT INTO l_contract ; But now i want to "convert" this *l_contract* into a CLOB variable *l_clob* Is there an easy way to do that? Or otherwise, how do i convertthe rows from a SELECT statement into one single CLOB Variab...
Hello I'm having issues with the Java code below. It is supposed to update certain records in a table where the ID is given, and where the STATUS column is 'good' (this is only one row at any given time). However, when I run the code below, it seems to be ignoring the AND STATUS = 'good' part, and updating all NUMRECS wherever the ID ma...
Hi all, I just stumbled upon something in ORACLE SQL (not sure if it's in others), that I am curious about. I am asking here as a wiki, since it's hard to try to search symbols in google... I just found that when checking a value against a set of values you can do WHERE x = ANY (a, b, c) As opposed to the usual WHERE x IN (a, b, ...
How do I add an attribute to xml contained within a CLOB in an Oracle database? I can use the UpdateXML function to update an existing attribute but it will not add one. ...
Hello, I want to run 200+ select queries and append the results to a file. All queries are the same the only difference in the date-time variable. I don't have privileges to create a routine that's why I had to create all the queries. I don't have privileges to create a view or another table to store the results in. I don't have access...
The problem is: I'm setting a preparedStatement for a query in a table with this fields (among others): TABLE1_RSPN NUMBER(8,0) TABLE1_AFDV NUMBER(8,0) TABLE1_VALUE NUMBER(17,2) TABLE1_NOTE VARCHAR2(255 BYTE) TABLE1_USR VARCHAR2(20 BYTE) ... Trying to get some info into my Java app, I set a preparedStatement which raises the Orac...
Does anyone know how to avoid a system hang on Windows XP when creating a database? I installed Oracle 11g Personal Edition on Windows XP. This is on a Windows XP system with DCHP, so I followed Oracle's instructions to install the Microsoft Loopback Adapter and updated my hosts file. During install I told it to create a Starter dat...
Say a table has several subclass types. How can I see all columns from different types in sqldeveloper? In table view, only can see common columns. Thanks. ...