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
Jun 13, 2013
how to migrate sql server database to oracle database?
View 2 Replies
View Related
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
Jan 13, 2011
can we use oracle client version to create a database in that server.
View 3 Replies
View Related
Jul 20, 2012
Can i install Oracle 10g Client 32 bit on our Windows Server 2008 R2 64 bit?
View 1 Replies
View Related
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
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
Jan 18, 2011
want to transfer data from sql to oracle having same structure in both database.
View 3 Replies
View Related
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
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
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
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
Jan 31, 2013
best method (easy and efficient) to load tables from sql server database to oracle database.
View 2 Replies
View Related
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
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
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
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
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
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
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
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
Jan 4, 2011
How can i insert 50 million records at a time
View 1 Replies
View Related
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
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
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
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
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
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
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
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