i have created the DB LINK from Oralce 11gR2 to MS Sql Server 2005,am able to access the view of Sql Server from Oralce,but the Columns which have Datatype nvarchar(max) in Sql Server am not able to access through DB Link.
View 1 Repliesi have created the DB LINK from Oralce 11gR2 to MS Sql Server 2005,able to access the view of Sql Server from Oralce,but the Columns which have Datatype nvarchar(max) in Sql Server am not able to access through DB Link.
View 1 Replies View RelatedWe got a big problem that we have a db link to connect to sql server(big 5).
In original setting, there is no Chinese characters in the data. We simply grab the data from sql server and do not have any issue. But now we have data with Big5. When we import the data into our Oracle DB, which uses UTF8, all the text becomes "?
Changing character set in DB is not possible. We need to convert the text into unicode format, or allow oracle/sql server to convert it automatically.
How to migrate Data from oracle to MS SQL Server or Vice Versa.
I came to know about 2 methods:
1) Using SQL Developer
2) USing ODBC.
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 RelatedUsers are reporting that are unable to use the NVARCHAR(4000) due to the character set which is set in the database.
Currently NLS_CHARACTERSET is set to AL32UTF8
and NLS_NCHAR_CHARACTERSET from AL16UTF16
They want me to change both the character set to UTF8, so that they can use the NVARCHAR2(4000) datatype in their tables.
As UTF8 is the subset characterset and I did the cs scan and this found characterset can't be modified to UTF8 and there may be the data dictionary corruption.
Is there any other way so that users can use the NVARCHAR(4000) as datatype. And the reason why NVARCHAR2 is required is, users are trying to import the data from other database which is using NVARCHAR(4000) as one of the field.
i'm newbie in oracle and visual studio 2005..i running a sql-query in visual studio 2005 with the oracle dataset..i want the data that displayed is in the date range and the parameter is set by user..
EXEC ('
SELECT DISTINCT
XNP_TIMER_VIOLATION.VIOLATION_TIME,
FROM
XNP_TIMER_VIOLATION, ...
WHERE
(DATEADD(hh, 8,XNP_TIMER_VIOLATION.VIOLATION_TIME) BETWEEN ? AND ?)',@FROM_DATE, @TO_DATE ) AT npcrpt ;
I found a link to a topic concerning this issue (click here), but the question mentions that the poster was looking for a "band-aid" fix...if this method will work for a more long-term solution
View 1 Replies View RelatedI have been trying to link/connect Oracle XE 10g to Sql Server 2008 Express using Oracle heterogeneous services without any luck. I have MS ODBC drivers loaded on my PC which I know work as I can create and test the connectivity of a DSN which is successful, however when I try to link Oracle to that ODBC driver I get the following error message.
TNS-12545: Connect failed because target host or object does not exist.
Here is listener.ora file.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
[code]........
# DEFAULT_SERVICE_LISTENER = (XE)
Here is how the tnsnames entry is defined.
SqlServer =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYPC-LT)(PORT = 1521))
)
(CONNECT_DATA =
(SID=SqlServer)
)
(HS=OK)
)
Here is a copy of the initSqlServer.ora file.
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
[code]......
Just to be clear the ODBC DSN is named "SqlServer". I followed through all of the steps outlined in the following URL.
[URL].......
There seems to be a disconnect between the ODBC driver and SQL*Net. Possibly due to Oracle HS?
the procedure to link oracle 9i database server withi 10i.
View 1 Replies View RelatedConnecting Oracle to SQL Server via database link.
I am trying to connect from Oracle 10G to a SQL Server database. I have looked at the manual and i will admit that i am finding the documentation quite difficult to follow. There seems to be several options to use but none of the documentation describe how each option works.
As an example, i have been given the following information on the database i need to connect to (i.e. the SQL Server database)
- Username
- Password
- Database Name [lets assume the database name is data_extract]
To connect the above i made the following changes
$ORACLE_HOME/hs/admin/inithsodbc.ora
---------------------------------------
HS_FDS_CONNECT_INFO = data_extract
HS_FDS_TRACE_LEVEL = 0
$ORACLE_HOME/network/admin/tnsnames.ora
---------------------------------------
sqlserver.db =
(DESCRIPTION =
[code]....
Note: In listener.ora, i only added the last SID_DESC entry. I then went on and created the database link as shown below
create database link sqlservdb using 'sqlserver.db';
When i try to access a table i get the following error
sqlplus> select * from TESTTABLE@sqlservdb;
select * from TESTTABLE@sqlservdb
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from ORASQLSERVER
A couple of things i am not sure of:
- Where do i specify the username and password to access the sql server from the oracle db to the sql server db
- Having read around on the internet, i can see some people refering to a DSN datasource. I am told that the information that i have is all i need. Is this correct or do i need something else?
- The sid i specified in inithsodbc.ora and tnsnames.ora is actually the sql server database. Is this correct?
i have developed an oracle form application in oracle developer 10g, now i want to make it accessible based on a browser to the clients(make it a web aaplication form)
View 3 Replies View RelatedDB : Oracle 9i
Query : select to_date(sysdate,'dd-mm-yyyy') from dual
Result : 26-09-0005
Doubt : Why yyyy is represented as 0005 instead of 2005 and what should I do to make it 2005?
I need to create one to many user DB link in oracle 10g. Meaning I have a user A in database 1 and I want to access the objects from user B,C,D in database 2, how to create a public database link so that i can have this one to many user access?
View 8 Replies View RelatedI am having database A and database B and I am inserting into database b by selecting some records from database a using db link.Is there any way to find whether database link is being used?
View 6 Replies View RelatedI have login through the sysdba and created dblink and modified the tnsnames.ora for necessary changes. And the it started working correctly and I can able to get the data by selecting it.But when I login with different user that is showing the following error.
ORA-12154: TNS:could not resolve the connect identifier specified
How to resolve this,is there any privileges or permission need to give to that user.
One of colleague (who has left) created a database link using the statement:
CREATE PUBLIC DATABASE LINK CIVCLMANLINK USING ''CIVCLMAN''
When I try to use this link like:
SELECT * FROM CLIENTMANAGEMENT.ADDRESS@CIVCLMANLINK;
I get the following error:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
I can connect to the CIVCLMAN using the Oracle Enterprise Manager / SQL Plus, I can tnsping as well
Is this possible to run a direct impdp with database link for transport tablespace instead of first creating export dump and then using impdp.
I tried the below command and it shows up error.I have gone through the entire Oracle docs but could not find the exact command with network link for transport tablespace.
$impdp system/passwd DIRECTORY=data_pump_dir transport_tablespaces=TBS1,TBS2,TBS3 LOGFILE=tbs.log network_link=dblink
UDI-00015: invalid context or job state for parameter, 'transport_tablespaces'
I tried to find how to import whole schema over db link , can below command use for whole schema import
CREATE DATABASE LINK EXP_SAT CONNECT TO SYS IDENTIFIED BY SYS USING 'PRTRD01'
IMPDP SYS/SYS@PRTRD01 directory =EXP_SCHEMA NETWORK_LINK = EXP_SAT remap_schema = STG_CUPID_RP2:STG_CUPID_RP2;
How can i import whole schema from other database. i have a database WHO2 WHERE I HAVE A schema called stg_cupid_rp2 i want to import into data WH0T in stg_cupid_rp2 using DBlink EXP_SAT.
I would like to create a view on the Oracle which points to SQLserver tables.
oracle db version : 10g
sql server version :sql server 2008 R2
on my Windows 7 machine with Oracle Client 12.0.1.0 installed I'm trying to create a database link to a database server, that runs under Oracle Standard Edition1 11.2.0.2. When i login to sqlplus on the client with "sqlplus /nolog" and type the command : SQL> create database link SID connect to user identified by passwd using 'tnsnames.ora connection'; I'm getting an SP2-0640: not logged in.
Ok, thats clear, because i use sqlplus with /nolog option. But on client side normally I have no database, where I can login. So I think, there will be no user, or? The database to which I want to connect is set in tnsnames.ora. Generally: Is it possible to use the Oracle Client to create a database link?
I'm trying to connect Oracle 11g database from Oracle 9i database, by creating dblink on Oracle 9i database. But my session got hang while i perform this. Here i'm giving the steps i followed on my both the database. Here Oracle 9i database is my source db and Oracle 11g is my target db.
Following steps/setting on Oralce 11g Database
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL> alter user SSA identified by SSA;
User altered.
Password File recreted on Oracle 11g Database Server
[oracle1@dev11g dbs]$ orapwd file=/backup/ora11gsoft/odb/OH1/dbs/orapwwbdata
ignorecase=n password=dev entries=5
Following steps performed on Oralce 9i Database
DROP DATABASE LINK "SSA11G.SSA_DB1.WBTEA.COM";
CREATE DATABASE LINK "SSA11G.SSA_DB1.WBTEA.COM"
CONNECT TO SSA
IDENTIFIED BY SSA
USING 'DEVDATA.DEV11G';
Database link created.
SELECT * FROM SSA.SSMASTER@SSA11G;
And the hangs for ever.
I am trying to upgrade my database approx 20GB size from 8.1.7.2.1 to 8.1.7.4 runningon windows NT 32bit server.I found that we can download from metalink.oracle.comIs that any cost involved in downloading and installing or is it provided freely by Oracle.
View 7 Replies View RelatedI'm a webdeveloper working in .net with sqlserver.Now in my office,we're going to do a proj with oracle DB.I'm idiot about oracle.Then i browsed in internet for oracle and i found out something like orcale8i,9i,10i,oracle developement and all. i dont know where to start to refer.any link to download oracle complete reference ebook?
View 2 Replies View RelatedI am using below command to import a schema using network link. Command is :
impdp system directory = IMP_DIR schemas = XYZ network_link = PQR remap_schema = XYZ:XYZ exclude=view: "= 'XYZ.VW_ACCEPTDETAILS'"
This command is giving below error
LRM-00116: syntax error at 'view:' following '='
When I have tried Like in place of '=' sign i.e. EXCLUDE = VIEW:"LIKE '%VW_ACCEPTDETAILS%'" , it gives me below error:
UDI-00014: invalid value for parameter, 'exclude'
how to create link file dynamically in oracle reports 6i
View 3 Replies View RelatedI have installed unixodbc 2.3.1, postgres odbc driver (psqlodbc-07.03) and dg4odbc 11.2..On querying : select sysdate from dual@dblink_postgresql, the following error occurs:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found {01000}
ORA-02063: preceding 2 lines from DBLINK_POSTGRES
Similar error appears in the trace file as well.
[unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a'
$ isql -v postgresql postgres postgres@2012
[01000][unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found
[ISQL]ERROR: Could not SQLConnect
However the file is present in the location and has no permission related problems.
$ pwd
/oracle/psqlodbc/lib
$ ls -lrt
total 2952
-rwxr-xr-x 1 oracle oinstall 663 Mar 25 15:28 psqlodbc.la
-rw-r--r-- 1 oracle oinstall 731419 Mar 25 15:28 libpsqlodbc.a
-rw-r--r-- 1 oracle oinstall 12215 Mar 25 22:11 win_md5.o
-rw-r--r-- 1 oracle oinstall 18100 Mar 25 22:11 options.o
[code]....
i would like to import a table from another database by using database link.
impdp muba/muba tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
i created the public database link as system user
it gave error like
Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:07:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Starting "MUBA"."SYS_IMPORT_TABLE_01": muba/******** tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
[code]....
after that i try to import as system user like
impdp system/passwd tables=FUNCTION_NO schemas=muba directory=testdump NETWORK_LINK=DBLINK1
but error like
Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:27:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
UDI-00010: multiple job modes requested, schema and tables.
when i use IMP utility
imp system/passwd file=dump.dmp fromuser=userA touser=userB log=dmplog.log but when i use IMPDP what is the parameter equal to fromuser & touser
My user would like to have a way to attach files to database records. For simplicity, we'll say that each employee in the database could have 0-many file attachments associated with them. I know how to make this part work using Oracle Forms.
So that's the easy part for me. The tough part is that they want to be able to run an Oracle Report on our application server (displays as a PDF, also downloadable as a PDF) and have links on this report to the attachments that they uploaded using the forms process above.
For the idea of storing the files on a folder somewhere, I simply created a text object on the report that had it's hyperlink property pointing to the file location. Done.. opens fine.
However, I want to be able to have the option of storing these files in the database instead, just in case we can't go with the shared folder idea. I'm not sure how to make this work. I can store a blob in the database.. but how do I link to that blob on the report for them to be able to download it? Is this even possible?
how to create a download link on the Oracle Report that let's them download a file out of the database.
I am trying to create a database link from the 11g database to the 10g database using:
create database link ORCL10R2 connect to <username10g> identified by <password10g> using <db10g>;
It Returns
Database link created.
select sysdate from dual@ORCL10R2 returns error:
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
what changes I need to make to tnsnames and listener at both servers.