Client Tools :: How To Insert Data From SQL To Oracle Server

Dec 13, 2012

I have problem: I have 1 sql server already setup SQL Server 2012 Express and 1 Oracle Database server 10g. Now i want to insert data from SQL server to Oracle database through link server.

Some step i already make:
1. Setup oracle database 10g and configure listener (Finished)
2. Setup Sql server 2012 express on Windows 7 (Finished)
3. Setup ODTwithODAC1020221 on PC already setup SQL server (Finished)
4. Make Linkserver from SQL server to Oracle database (Finished), and can select data from Oracle Database on SQL server through Linkserver.

However when i insert data from SQl server to Oracle Server not success.

select * from OPENQUERY (QVHKTEST, 'SELECT * FROM QVSYSTEM')

After i run above script, result is OK
With: "QVHKTEST" is alias of Link server from SQL to Oracle server
: "QVSYSTEM" is a table on Oracle database, that table we want to get through Linkserver on SQL server

Both Server Database contain same name table is "QVSYSTEM"
-----------
INSERT OPENQUERY (QVHKTEST, 'SELECT BODY_NO,
MERCHANDISE,
MODEL_NAME,
LINE_NAME,
DATE_ENTRY
FROM QVSYSTEM')
values('VNF4619829','3227B002CA','L1068','01','2012/09/26 03:18:11');

If i run script above directly in SQL Window query can insert OK. This is code in trigger at table on SQL server:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author Name: Phuong Do Minh >
-- Create date: <Create Date: 10/12/2012>
-- Description:<Description: After data insert into table qvsystem on SQL server
-- This trigger will fire and insert that data into table qvsystem
[code].......

But when i make trigger after insert on table in SQL Server to insert data From SQL server to Oracle server, however not success and SQL server raise error below:

OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure Insert_data, Line 16

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" was unable to begin a distributed transaction.

I don't know how to configure them.

View 8 Replies


ADVERTISEMENT

Client Tools :: How To Migrate Data From SQL Server To Oracle Database

Jun 13, 2013

how to migrate sql server database to oracle database?

View 2 Replies View Related

Client Tools :: Generate Insert Script For Existing Table Data?

Aug 21, 2008

Is theree any way to generate the insert script (with the data) for an existing table.

In Toad, we can generate the DDL for a particular table. Just curious if it can be done using any Tool.

View 19 Replies View Related

Client Tools :: Can Use Oracle Client Version To Create A Database In That Server

Jan 13, 2011

can we use oracle client version to create a database in that server.

View 3 Replies View Related

Client Tools :: Oracle Client 32 Bit On Windows Server 2008 R2 64 Bit

Jul 20, 2012

Can i install Oracle 10g Client 32 bit on our Windows Server 2008 R2 64 bit?

View 1 Replies View Related

Client Tools :: INSERT INTO With GUI - Insert Record Greyed Out?

Oct 28, 2013

I wish to make this simple statement with Toad GUI

INSERT INTO EXCLUDE_xxx
VALUES ('xxx',
'xxx',
'xxx',
'xxx',
SYSDATE);

Insert record is greyed out. How to insert new rows with Toad (click click)?

View 7 Replies View Related

Server Utilities :: Which Client Need To Use Data Pump Tools

Feb 13, 2012

I'm installing a new application-testing server, i have installed 11g r2 instant clients & SQL* Plus client.

when i'm trying to run an expdp command, i get this:

'expdp' is not recognized as an internal or external command

Now, i understand this is because i don't have the Bin directory of a client installation in my Path of the OS. My question is, which one exactly i need for using data-pump utility, and where to download it?

I've found lots of posts of people that had issues with defining the ORA_HOME$in in the $PATH, or having a client incompatibility issue throughout the web, but no answer to my specific question.

View 4 Replies View Related

Client Tools :: How To Transfer Data From Sql To Oracle

Jan 18, 2011

want to transfer data from sql to oracle having same structure in both database.

View 3 Replies View Related

Server Utilities :: Insert Data Without Writing Insert Statement In Oracle?

May 15, 2010

how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.

View 2 Replies View Related

Client Tools :: Return Data From Oracle For Crystal?

Aug 9, 2010

I have problems in Oracle returning data from a stored procedure. In MS SQL it's quite simple.

i.e.

CREATE OR REPLACE PACKAGE globalpkg
AS
TYPE RCT1 IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END;
/

next the stored procedure:

CREATE OR REPLACE PROCEDURE mytest
(
RCT1 OUTGLOBALPKG.RCT1
)
AS
BEGIN
OPEN RCT1 FOR
SELECT *
FROM EMPLOYEE;
END;

Next trying to run the Stored procedure:

VARIABLE resultSet REFCURSOR
EXEC mytest (:resultSet);

Result always returns this error:

REFCURSOR - Unrecognized type.

I have tried using sample on the internet, but all returns the same error, what am I missing

View 32 Replies View Related

Client Tools :: Oracle Connection To SQL Server

Mar 2, 2011

I am using dg4msql to connect to SQL server from Oracle(10.2.0.4, with AIX 5.3).

#from my laptop sqlplus (10.2.0.2)

SQL>select count(*) from table1@mssql; #works
SQL>select * from table1@mssql; #ERROR

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver]String data, right truncation
{01004}[Oracle][ODBC SQL Server Driver]String data, right truncation {01004}

#from my laptop, TOAD
Both queries works fine.

i have compared

"select * from nls_session_parameters;" values , TOAD and sqlpus gives same result.

View 3 Replies View Related

Client Tools :: Oracle Data Pump Utility Executable Must Be Specified

Feb 4, 2011

I faced the following problem while exporting tables by using data pump in TOAD.

"Oracle Data Pump Utility executable must be specified."

View 4 Replies View Related

Client Tools :: Load Tables From SQL Server To Oracle

Jan 31, 2013

best method (easy and efficient) to load tables from sql server database to oracle database.

View 2 Replies View Related

Client Tools :: Connection From Oracle SQL Developer To MS SQL Server

Jun 8, 2010

I'd like to make a connection from Oracle SQL Developer to a MS SQL Server DB. How can I do that?

View 2 Replies View Related

Client Tools :: No Server Option In Oracle SQL Developer?

Jan 31, 2013

Using SQL Developer I am able to login using oracle credentials but I dont see any option for SQL Server. I see both Oracle and Access. Why I am not able to see the SQL Server option in SQL Developer tool? I need the SQL Server option because I want to transfer some tables from SQL Server database to Oracle database.

View 1 Replies View Related

Client Tools :: Insert XML Into XMLType With Value

Mar 29, 2012

I'm trying to insert an XML into an XMLType with the following value:

insert into Table1(
code, xmlColumn) VALUES
(1, '<script> > </script>');

the question is I need to have > in the text, but Oracle insists in putting it like <script> > </script>.

What do I have to do to solve this?

View 15 Replies View Related

Client Tools :: Export Data From Oracle Database To Excel File?

Aug 21, 2012

I am using SQLTools 1.5 for writing Oracle SQL scripts.

I have to import data from excel file to oracle database. How can I do it?

Also how can I export data from Oracle database to Excel file?

View 2 Replies View Related

Client Tools :: How To Sync Oracle 10g Database With SQL Server 2005

Jun 27, 2013

i have my oracle 10g database on my windows 7 and i install sql server 2005 on my windows server 2003 .now i want to sync my oracle 10g database with my sql 2005 means whenever i make any changes in my oracle table its automatically effect in my sql server also.

View 1 Replies View Related

Client Tools :: Oracle Services For Microsoft Transaction Server?

Apr 2, 2012

I want to use SSIS,when i use oracle 10g as a data source to SQL Server 2008 as Destination, it gives error ora-01017 invalid username/password logon denied, i did normal oracle installation and give full privileges(sysdba or sysoper) to the user(sys,system...),when i check connection it shows test connection succeeded but when click on next it gives above error.

View 3 Replies View Related

Client Tools :: Insert Into Multiple Table

Jun 22, 2012

I want to insert data into three table

my first table is

firstemp

having columns
1.EMPID
2.NAME
3.STATUS

second table is

secimp

have same columns as of first table

third table is

thirdimp

columns are same as of first andsecond

I want to insert in Firsttable if EMPid=EMP_1h and insert in second table if empid=EMP_2u and insert into third table if empid=emp_3p

View 1 Replies View Related

Client Tools :: Oracle 10gR2 Database Server - Unable To Use Isqlplus?

Apr 11, 2007

I have installed Oracle 10gR2 database server recently in the Windows box. The database works fine. But I am unable to connect through the Internet Explorer. I found that the service for the isqlplus is not available. Do I need to run any script for creating the service?

View 12 Replies View Related

Client Tools :: Insert 50 Million Records At A Time?

Jan 4, 2011

How can i insert 50 million records at a time

View 1 Replies View Related

Client Tools :: Ora-01017 Import / Export SQL Server 2008r Wizard From Oracle 10g

Jun 18, 2013

I would like to Import data from Oracle 10g using Microsoft Sql Server (2008 R2) Import/Export Wizard, but i get this error when i try too establish the connection :

ora-01017

this the step i followed;

1- i filled the server name
2- username & password
3 - test connection ---> success

but when i try to follow pressing next(Suivant) i got the ora-01017 Error

View 12 Replies View Related

Client Tools :: Providing Bind Variables As Values In Insert Statement?

Aug 23, 2011

I executed the following PL/SQL block in SqlDeveloper :

VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/

And it gave the error : Quote:Error report:

ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"

The same code when executed in iSqlPlus gave no error.

View 13 Replies View Related

Client Tools :: Unable To Insert Euro Symbol In Database With Character Set WE8MSWIN1252?

Mar 29, 2011

I am using oracle 9.2.0.6.0 on HP-UX.

I am unable to insert and even display euro symbol from server as well as windows client.

Following are the details of my database server

SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252

[code]....

1) When I try to insert € from Db server (using putty) using Alt+0128 it does not print anything (nothing gets typed on the screen). Not even junk characters Also following query does not print anything

SQL> select chr(128) from dual;
C
-

2) while I set NLS_LANG on client and try to insert €, Alt+0128 produces a question mark symbol And following query displays junk character

SQL> select chr(128) from dual;

C
-
Ç

3) Regardless when I inserted couple of rows and tried UNISTR following was the result

SQL> select unistr(v) from t;
Error 45 initializing SQL*Plus
Internal error
$

View 19 Replies View Related

Client Tools :: Tools For Load Testing On Oracle - J2EE Application?

Jan 5, 2012

which are recommended Tool for load testing (for performance) on Oracle-J2EE, 3 Tier applications?

Is 'Oracle Application Test Suite' the best for such test where we can simulate numbers of users and their various actions?

Does it come with Oracle Database license or we have to buy it separately?

View 1 Replies View Related

Client Tools :: Basic Tools For Oracle Version Control

Aug 26, 2011

I'm looking for 3 simple things.

1) A reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.

2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).

3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.

View 9 Replies View Related

Client Tools :: Cannot Connect Using JDBC Oracle Thin Client With (New Method)?

Feb 21, 2013

The problem is that this connection errors when I try to connect using my SQuirreL client. My developers want to connecting using the SID or the SERVICE_NAME and by using the "New Method" syntax, not the "Old Method" syntax. According to the documentation the "New Method" syntax works with the SERVICE_NAME or the SID. The "Old Method" only works with the SID.

# Host name is vmwwin7634.na.SAS.com
# SID is sting2
# SERVICE_NAME is sting2.na.sas.com

PROBLEM using SID errors ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2
THIS WORKS using SERVICE_NAME ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com

I am using SQuirreL Client version 3.4.0

# Here is the tnsnames.ora entry on the server.
STING2 =
(DESCRIPTION =

[code]...

# In this documentation it says, "On new syntax SERVICE may be a oracle service name or a SID."You can find this on the orafax wiki under JDBC#Thin_driver.

# I was able to connect with the "old method" using the SID only, SERVICE_NAME errors jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2.na.sas.com
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2 (this works)

# "New Method"
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com (this works)
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 (this errors)
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")

View 10 Replies View Related

Client Tools :: How To Connect From Client To Oracle Database In Unix

Dec 11, 2012

I have new virtual UNIX machine and I installed oracle client on /usr/lib/oracle. Also I have a oracle database and I am able to connect to this database from my desktop sql developer.

So now I am trying to connect from new UNIX machine. Where I created tnsnames.ora file under /usr/lib/oracle/network/admin and before connecting did export the following

export TNS_ADMIN=/usr/lib/oracle/network/admin
export ORACLE_HOME=/usr/lib/oracle/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:/sbin:$ORACLE_HOME/bin
export ORACLE_SID=VFIODSD1

when I try

# sqlplus
username :xxxxxxx
password : xxxxxxx

ORA-12545: Connect failed because target host or object does not exist. Not sure what I missed here. using same tns file I am able to connect from sql developer on windows.

View 1 Replies View Related

Client Tools :: Install Oracle To Get Latest SQL Plus Working On Client PC

Jul 11, 2012

I need to install Oracle client to get latest sql plus working on client pc as the problem is, i am not able to find out where i can download this , i just need the sql plus as i am not able use many commands because when i installed oracle forms 6i client tool , sql plus 8.0 got installed.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved