Problem Statement:- I am inserting a record into Oracle if that record is already present(duplicate==>primary key is present) in database i want to update it with new one. Currently to solve this while inserting the record if i get OCI_ERROR then i call OCIErrorGet( (dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,errbuf, (ub4) size...
We have a mature Oracle database application (in production for over 10 years), and during that time, we have been using scripts of our own devising to remove old data that is no longer needed. They work by issuing delete statements against the appropriate tables, in a loop with frequent commits, in order to avoid overloading the system...
Given a table A of people, their native language, and other columns C3 .. C10 represented by ... Table A PERSON LANGUAGE ... bob english john english vlad russian olga russian jose spanish How do I construct a query which selects all columns of one row for each distinct language? Desired Result PERSON L...
Hello, We have an Java workflow application that uses an Oracle database to track its steps and interactions with other services. During a workflow run several insert/update/selects are performed and occasionally the select will not return the updated data, even though the insert/update commit that ran before it completed successfully....
I am trying to export an excel file directory into an Oracle table as opposed to looping through the range and executing a lot of insert statements. I would think that there are better ways to accomplish this in .NET but I can't seem to find any other answer besides convert excel to csv & load it using Sql Loader or External Table. Does ...
Hi, I have a XMLTYPE with the following content: <?xml version="1.0"?> <users> <user> <name>user1</name> </user> <user> <name>user2</name> </user> <user> <name>user3</name> </user> </users> How can I loop in PL/SQL through all the elements "use...
Which would be a better option for bulk insert into an Oracle database ? A FOR Cursor loop like DECLARE CURSOR C1 IS SELECT * FROM FOO; BEGIN FOR C1_REC IN C1 LOOP INSERT INTO BAR(A, B, C) VALUES(C1.A, C1.B, C1.C); END LOOP; END or a simple select...
Is it possible to return an Oracle Ref Cursor to a caller that is in SqlServer T-SQL? When dealing with a normal .Net program there is this Knowledge Base article: http://support.microsoft.com/kb/322160 But is this same type of thing possible from T-SQL? ...
I need to write a report that generates summary totals against a table with date ranges for each record. table data: option start_date end_date opt1 6/12/2009 6/19/2009 opt1 6/3/2009 6/13/2009 opt2 6/5/2009 6/6/2009 What I want out is basically this: date option count 6/1/2009 opt1 0 6/1/200...
I've written a java applet that connects to Oracle on a hosted server. I have been testing it by connecting by VPN to my hosting service and connecting to the Oracle database. I've done this so I don't have to expose the Oracle db to outside connections, but now I need to be able to connect to Oracle without being on the VPN. Is there...
The typical syntax for creating a db link is as follows: create database link remote_db_link connect to remote_user identified by remote_password using 'remote_db' But I'd like my DB link owned by another account after it's created. Is there a way to do this? The following does NOT work: create database link anotheruser.remote_db...
I would like to have a block in an Oracle 10g form that would show all of the PDF files in a particular folder on the user's C drive. The user should at least be able to double-click on the file to open it, and ideally be able to delete and rename the files, too. The list of files should show the filename (obviously) as well as the siz...
I suppose I don't necessarily need an actual reference book per se, but I tend to learn as I go. Thus, it would be nice to have some kind of terse but readable book that I can read in chunks either if I need help remembering the syntax of a particular query or if I want to learn about particular database features one at a time. It seem...
Hi All, I want to connect user sys in sqlplus of oracle but after I connect, I type like this: SQL>sqlplus sys as sysdba password:123456 Error: ORA-01030:insufficient privilege warning:You are no longer to connect oracle. Does anyone help me to solve my problem? Thanks. ...
I have a common database joining situation involving three tables. One table, A, is the main table with a primary key named id. Tables B and C contain auxiliary data for entries and A, and each also has a column named id which is a foreign key pointing to A.id. Now, if I want all data from A, B and C in one query, I would write: SELECT ...
I need some pointers on how to diagnose and fix this problem. I don't know if this is a simple server setup problem or an application design problem (or both). Once or twice every few months this Oracle XE database reports ORA-4031 errors. It doesn't point to any particular part of the sga consistently. A recent example is: ORA-04031: ...
I'm Learning Oracle and wanted to try creating a trigger. I tried this example form a book in sqlplus. SQL> CREATE OR REPLACE TRIGGER policy_bull BEFORE insert or update 2 ON emp 3 FOR EACH ROW 4 BEGIN 5 :new.salary := 200; 6 END 7 / ERROR at line 1: ORA-04089: cannot create triggers on objects owned by SYS even t...
Learning about Explicit Cursors and trying to create my frst one.: SET SERVEROUTPUT ON DECLARE v_ename EMP.FIRST_NAME%TYPE; v_salary EMP.SALARY%TYPE; CURSOR c_emp IS SELECT first_name, salary FROM emp; BEGIN OPEN c_emp; FETCH c_emp INTO v_ename, v_salary; DBMS_OUTPUT.PUT_LINE('Employee Details ' || v_ename || ' ' || v_salary) ...
We have a few customers with large data sets and during our upgrade procedure we need to modify the schema of various tables (adding some columns, renaming others, occasionally changing data types, but that's rare). Previously we've been going via a temporary table with the new schema, and then dropping the original and renaming the tem...
I have the probably unenviable task of writing a data migration query to populate existing records with a value for a new column being added to a table on a production database. The table has somewhere in the order of 200,000 rows. The source of the value is in some XML that is stored in the database. I have some XPath that will pull ou...