I'm getting an error while updating the table with a subquery which should not have more than one value, but does. The query: UPDATE @Table1 SET D = t2.D + t3.D FROM @Table1 t1 INNER JOIN @Table2 t2 ON t1.P = t2.P INNER JOIN @Table3 t3 ON t1.A = t3.A ...
Hi All, I am executing the below query with Oracle ANSI left outer join syntax,It's returning 107 records: SELECT bs_accts.acct_num, bs_accts.acct_name, br_data.record_id , as_users.username, br_fx.fx_rate , FROM bs_accts CROSS JOIN as_users CROSS JOIN br_fx left outer join br_data ON ...
i have this procedure and i have to create an anonymous block to print all the orders at same time. here is the procedure create or replace procedure Item_bill is sSQLstr VARCHAR2(1000); type refcur is ref cursor; rcur refcur; stmt1 VARCHAR2(300); BEGIN sSQLstr := 'select t.n_portions||'' portions of ''||p.dish_name||'' at '...
I am reading the string of data from the oracle database that may or may not contain the Unicode characters into a c++ program.Is there any way for checking the string extracted from the database contains an Unicode characters(UTF-8).if any Unicode characters are present they should be converted into hexadecimal format and need to displ...
We're trying to figure out the best way to handle BULK INSERTs using Oracle (10gR2), and I'm finding that it can be a pretty complicated subject. One method that I've found involves using the Append optimizer hint: INSERT /*+ Append*/ INTO some_table (a, b) VALUES (1, 2) My understanding is that this will tell Oracle to ignore indexe...
Hi All, I have set the below parameters in sqlplus to display my view out put on unix box,However it is displaying one line gap between two records.I don't want that one line gap between two records Ex- set feedback off SET NEWPAGE NONE set HEADING Off set pagesize 0 set linesize 125 SET TRIMSPOOL ON set termout off spool /export/home/4...
Is it possible to create a table (in my dev db) using a SELECT from a different database? I want something like: create tmp_table as select * from prod_db.prod_schema.table Is there syntax to do this, or do I need to create a database link first? ...
Is it bad practice to use triggers or scripts to maintain data integrity that Oracle is not designed to enforce, or is this a sign I'm modeling my data in a poor way? From responses to a previous post (Implementing User Defined Fields), I have decided that I want to move forward designing with a mix of Class and Concrete Inheritance. I...
What are some Oracle gotchas for someone new to the platform, but not new to relational databases (MySQL, MS SQL Server, Postgres, etc.) in general. Two examples of the kind of things I'm looking for Many relational database products handle creating an auto_increment key for you. Oracle does not, you must manually create the sequence...
I need to get NHibernate 2.1 to talk to an oracle database. I am being required to use the Orace 10g client. I get the following error when trying to build my session factory: Unable to cast object of type 'Oracle.DataAccess.Client.OracleConnection' to type 'System.Data.Common.DbConnection'. I don't recall getting this error ...
Hi, I'm using ODP.NET (migrating from Microsoft's provider), and I have got stuck on a stored procedure that returs a refcursor. I have the following PL/SQL procedure (I have changed it a little bit to make it more general): PROCEDURE MyProc(parameter_no1 IN NUMBER, parameter_no2 IN NUMBER, RETCURSOR OUT ret_type ) AS BEGIN OPEN RET...
First, here are some scripts to setup the tables and background. CREATE TABLE TEST_P ( ID NUMBER(3) NOT NULL PRIMARY KEY, SRC VARCHAR2(2) NOT NULL, DEST VARCHAR2(2) NOT NULL, AMT NUMBER(4) NOT NULL, B_ID_SRC NUMBER(3), B_ID_DEST NUMBER(3) ); A row in this table indicates that AMT is being moved from SRC to DEST. ...
I'm using Oracle's ODP.Net to manipulate records. When using the XML methods to insert and update records, I've run into a few problems. When I am inserting a new record, I'd like the value of one of the columns to come from a sequence (MySeq.nextval). I'd also like an UPDATE_DATE column to reflect sysdate. I've only seen examples of...
Hi, I'd like to know how to modify the server.xml file so all my webservices built on axis2 can talk to the DB using Connection Pooling. Each webservice has a different data source, one points to one instance of the DB and the other to another DB server. How do I specify the context that should be used by each service? Thanks in advanc...
I been working the whole week to troubleshot a production error. I have eventually got the the point where I can find the culprit record which is causing all the mess. I've got the following error message: java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-01438: value larger than specified precision allows for this column ...
Here is my table MYTABLE(ID NUMBER(10), DATATYPE VARCHAR2(2 BYTE), XMLDATA LONG ) Note1: I cannot alter this table Note2: I'm using Oracle 10g Here is a sample of XMLDATA <myxml version="1"> <node1> <child1>value to get</child1> </node1> </myxml> I tried xmltype() but it's not working with a LONG type (ORA-0...
Hi all I have a program that needs to query a database in a given interval and with the records it gets call perform some action, then update the table again. I am using the ExecutorService to run the threads, but am wondering, should each thread get its own connection (because it needs to update database) or can I use the same connect...
there is a table== create table newstudent(enroll int, name varchar2(20), DoJoin date); in c# when i type oraclecommand(1,'amit purohit', DateTime.Now()); //Error found 'nvalid month' oraclecommand(1,'amit purohit', String.Format("{0:dd-MMM-yyyy}")); //problme that save only date into database but i need time also. like '11-AUG-2009 11:...
I have a .NET Winform Click-Once-deployed fat client that talks to three databases, 2 SQL Server databases and a Oracle one. The first SQL Server database, I will call the Master. I wanted add more tables and columns to the Master database but I was told I would not be given rights to do so, that it was non negotiable. So, I decided to ...
Taking a peak into the 'information_schema' database and peaking at the metadata for one of my pet projects, I'm having a hard time understanding what (if any) differences there are between the 'create schema' command and the 'create database' command for MySQL. Are there any differences? If not, is this a rather typical pattern of beha...