SQL & PL/SQL :: Selecting Clob Value Over DBLink?
Sep 10, 2012any way to select the clob value from dblink.?
View 7 Replies
ADVERTISEMENT
How To Compare Data Of Clob And Long Datatypes Using DBLink
Aug 13, 2012I would like to run below query on all tables, however it doesnt work on clob and long datatypes
select * from owner.table_name
minus
select * from owner.table_name@remote_db;
from dba_tables
where owner in ( '....');
ORA-00932: inconsistent datatypes: expected - got CLOB
How can I compare the data of clob and long datatypes using dblink ?
SQL & PL/SQL :: Execute Immediate With DBLink
Feb 23, 2011I need to write several variations of this statement below to query a remote database, but am having trouble with the syntax.
execute immediate 'select count(*) from ' || v_tablename || '@dblink ' || into v_rowcount;
PLS-00103: Encountered the symbol "INTO"....
What's the correct way to format this kind of dynamic query?
PL/SQL :: ORA 02070 And DBLink?
Jul 20, 2012 i am using 11g and pl sqlk developper, here is the script snippet
MERGE INTO GDIEXP.OEM_SCHEMAS@OX10CC30_GDI.LOTO_QUEBEC.COM O
USING (
SELECT T.GRANTEE, I.INSTANCE_NAME
FROM DBA_ROLE_PRIVS T, V$INSTANCE I
WHERE T.GRANTED_ROLE = 'PRIVPROPRIETAIRES') DRP
[code]...
An oracle 02070 is popping up, i don't what's the real reason behind it, i only know it works when i don't use the db link. What can i do ?
SQL & PL/SQL :: Selecting 6th Top Value
May 9, 2010how to select the sixth highest earner in my employees table.how to select 6th lowest earner..
View 18 Replies View RelatedGetting Data Over SQL Server DBLink
Apr 17, 2012I just created a dblink from our Oracle 9i database to a SQL Server 2008 R2 Server using oracle heterogeneous services. The dblink seems to be OK and I can query the remote SQL Server database but I only get results for numeric columns.
Say the remote SQL Server table was created like:
CREATE TABLE [MITLOC] (
[MLCONO] [numeric](3, 0) NOT NULL,
[MLWHLO] [nchar](3) NOT NULL,
[MLITNO] [nchar](15) NOT NULL,
[MLWHSL] [nchar](10) NOT NULL,
[MLBANO] [nvarchar](20) NOT NULL,
[MLCAMU] [nvarchar](20) NOT NULL,
[MLFACI] [nchar](3) NOT NULL,
[MLWHLT] [nchar](2) NOT NULL,
[MLSTQT] [numeric](15, 6) NOT NULL)
The remote dblink is named after M3PREP.
Then if I run:
CODEselect MLCONO, MLSTQT from MITLOC@M3PREP => this works OK and I get the two columns.
If I run:
CODEselect * from MITLOC@M3PREP => I get the same results as in the previous query and all nchar, nvarchar type columns are missing, I only get the same two columns.
If I run:
CODEselect MLCONO, MLWHLO from MITLOC@M3PREP=> I get ORA-00904 - the column name entered is either missing or invalid.
I set up my .ora init file like this, I suspect there is something missing here, is it related to the NLS_LANG or so?
CODE#
# HS init parameters
#
HS_FDS_CONNECT_INFO = M3PREP
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = M3PREP.LOG
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
Way To Reduce Dblink Timeout
Dec 28, 2011is there any way to reduce the dblink timeout, i need to test if the remote server is up by testing the dblink throw 'select 1 from dual@link_name' but it take too long time 5-6 min., i need to make it 5 sec after that the exception tns timeout appear. I try with some sqlnet.ora parameters like INBOUND_ TIMEOUT but not work for me, also i try using tnsping but it also take long time when the remote server is down.
View 2 Replies View RelatedNested Tables By DBLINK
Feb 1, 2011There's a table T with a nested table within it on the master database. I need to mantain a materialized view of the table T on a remote database.
I get this error: QUOTE ORA-22804: remote operations not permitted on object tables or user-defined type columnsIs there any recommended workaround of this problem? The remote data must have the same structure as the master one.
The data should be refreshed every day, the data changes moderately, there are more or less 500 records.
SQL & PL/SQL :: Inserting Data Using DBLINK
Feb 21, 2013I am inserting XMLTYPE data using DBLINK I am getting the following error.
INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY
ORA-22804: remote operations not permitted on object tables or user-defined type columns
Source table structure
Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE
Target table structure(another DB)
Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE
how to insert XMLTYPE data using DBLINK.
SQL & PL/SQL :: How To Create Procedure By Using Dblink
Jun 26, 2011iN MY DB SERVER (CALLED A),THERE IS A DBLINK TO ANOTHER DB SERVER (CALLED B). DBLINK'S IS LINKB.
----------
BY USING LINKB,I CAN CREATE,DROP TABLE;DELETE,UPDATE,INSERT DATAS IN SERVER B.
Now,i want to create procedure on server b(but i can only deal with b using dblink linkb).
How To Create A Private DBLINK In 10g
Oct 20, 2012I want to create a private DBLINK in 10g and grant access to use this to limited (another 2/3) users. Is that possible ?I don't want to create PUBLIC DBLINK because the users to use it are limited.
View 4 Replies View RelatedBulk Collect With DBLink?
Feb 23, 2012create or replace PROCEDURE CDR_PROC_ARCHIVE_ORDER_EXTRACT
IS
/*
Criteria to be followed to Order Archival
* Order Status should be 'Cancelled' or 'Complete'
* Order Closed date should be 6 months before
*
-- main Cursor to spool the Orders to be archived based on criteria
[code]...
SQL & PL/SQL :: Materialized View Over DBLink
May 7, 2010I have got a materialized view which is created over a dblink as below:
CREATE MATERIALIZED VIEW CRIMEREPORTODSV
TABLESPACE ODS_DATA
BUILD IMMEDIATE REFRESH FAST AS
(SELECT * FROM CRIME_INT@CRISP);
This is all fine and works apart from any changes to the source table (CRIME_INT) isn't reflected unless I perform a refresh. Whereas I want any changes to be reflected straight away. I have had to use a Materialized view in this case as one of the columns in the source table is a CLOB and Oracle won't allow creation of a view with a CLOB field over DBLINK.
SQL & PL/SQL :: Selecting 1 Row From A Group?
Jul 3, 2011I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.
CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)
[code]....
Selecting The Most Recent Value?
Sep 7, 2007Have a table like this:
ID1ID2DATEID2Value
1121/1/20066
1241/1/2006400
1246/1/2006410
1366/1/2006100
2121/1/20077
2246/1/2007350
2247/1/2007360
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1246/1/2006410
2247/1/2007360
I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.
PL/SQL :: Selecting Data For One Day
Jun 18, 2013I want to select data inserted in the table for that day only.
Table name -->ADJCOLUMNS
i want to select areAccount_no-->number datatype TRANSACT_DATE-- NOT NULL DATE I have written the query below .Is the below query correct.
select account_no,to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') T_date from adj
where to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') between
to_char(TRUNC(sysdate),'DD-MON-YY hh24:mi:ss') AND
to_char(TRUNC(sysdate+1) - 1/86400,'DD-MON-YY hh24:mi:ss');
SQL & PL/SQL :: Selecting 1st And 2nd TOP Numbers (from Same Row)
Jul 6, 2012I need to find the top two values value for each ID Number:
CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));
INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');
Desired Result:
ID Number 1st 2nd
12345679 30 30
99999999 30 25
PL/SQL :: Selecting Into A Varray?
Mar 29, 2013BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
[code]...
SELECT job_request_id,
CAST (COLLECT (USER_ID) AS SYS.OdcinumberList) user_ids
FROM mytable
GROUP BY job_request_id;
ORA-22814: attribute or element value is larger than specified in type
Control Timeout Before Connecting Via DBLink?
Feb 28, 2012Is there any way to control the timeout before connecting via dblink. the idea is to test the dblink before calling functions over it, the problem it when i try 'select 1 from dual@dblink' in case if the remote server is down it takes too long time 5-7 min. which causing hang up the session,
So is there any parameter to reduce the waiting time say after 3 sec stop and return the timeout exception.I try the SQLNET.INBOUND_CONNECT_TIMEOUT parameter but not working, also try the tnsping but it alse take long time when the server down.
the database version is 10gr2.
Materialized View Log On DBLink Table?
Aug 6, 2010DB1: Sql Server
DB2: Oracle 11g
Is it possible to create a MV log against table@DB1? If not, then am I limited to refresh complete if I need to create MVs against @DB1?
SQL & PL/SQL :: Accessing Data From Server By Using DBLINK
Oct 22, 2010We are accessing data from the server ADM.WORLD by using DBLINK.We got the following error.
PL/SQL: ORA-04052: error occurred when looking up remote object
sysadm.PS_HP_INC_ELIG_VW@ADM.WORLD
ORA-00604: error occurred at recursive SQL level 1
ORA-28000: the account is locked
ORA-02063: preceding line from ADM
For that we checked in the server ADM.WORLD for the account the account is showing locked .After that we successfully accessed the object sysadm.PS_HP_INC_ELIG_VW@ADM.WORLD.
For next day also the account is locked.Why the account is frequently locking.
Windows :: Move Data From One DB To Other Without DBlink And Using Dot Net
Oct 30, 2012I have database A and B , we are strictly restricted to not to create any DB links between these database. In database A based on complex logic we in a stored procedure we populate the data to table type..now dot net will take this table type data and insert the same in database B.
Question here :
1.When we try to bulk collect all the data into table type , i am getting error operand exceeds limit, but it is mandate to put all data in one short to table type as dot net will take that and insert into B database.
2.Is there any good logical method to achieve this in a simple way.
SQL & PL/SQL :: Invoke Procedure With DML And COMMIT Through DBLINK?
May 29, 2012A simple procedure is created as follows:
Create procedure p1(a varhar2, b varchar2, c_err out varchar2) is
begin
insert into t1 values(a,b);
commit;
exception
when others then
c_err:= substr(sqlerrm,1,250);
end;
It is executed fine.But if it is executed from a dblink as
declare
c varchar2(200);
begin
t1@remote_link('testa','testb',c);
dbms_output.put_line(c);
end;
It is giving error "ORA-02064: distributed operation not supported". How can I pass DML through this procedure and commit from a dblink?
SQL & PL/SQL :: Difference Between Materialized View And DBLink
Jun 24, 2010What is the difference between Materialized view and dblink. When we use materialized view and when we use dblink. In my knowledge mviwes and dblinks are used to retrieve data from remote database.But I am unable to understand when should we materialized views and when should we use dblinks.
View 1 Replies View RelatedPL/SQL :: Generate Stats For DBLink Table (SQL)
Sep 7, 2012I am trying to generate some statistics on tables connected by a dblink. I know with oracle you have table_columns which you can reference and pull some stats from.
Trying to get the column count and record counts for each table connected by a dblink. I have tried these queries below to see if I could see any db properties: (some just to try something different)
select * from "table_owner".table_column@dblink ;
select * from "status"@dblink;
select /*DRIVING_SITE(a) */ count(*) from @dblink a;
What is the best method to finding this out without spending a lot of time? I have over 30 tables which are with large record sets and would love to learn a faster approach then pulling a sample table and doing a manual count and query for each table to count the rows.
Possible To Specify Login / Password For DBLINK When Calling It?
Apr 3, 2013I have a Oracle 91 database running on Windows 2003 with a DBLINK setup to connect to an external client. I'm looking at how i can setup a quick job that, say every 5 mins, runs a query over the DBLINK and if it returns data, then success. If it returns an error, then the DBLINK is down, moreso the external server, and then fire off an email to someone.
However, this DBLINK i think was created to be used by a specific username/password. When i try to run a query against it using SYS, i get invalid username/password error.
So is there a way i can use a DBLINK and specify the username/pwd to be used at runtime? I'm new to Oracle database..Something like 'select * from global_name@DBLINK1 as username/pwd'.
DBLink Not Querying Remote Table
Apr 16, 2013I successfully created the following DBLink in my DEV environment:
CREATE PUBLIC DATABASE LINK PROD
CONNECT TO USER1 IDENTIFIED BY pwd98
USING 'PRD';
I have a table in both schemas. In PROD it has 0 recs, in DEV it has 134 recs.
select count(*) from hold@PROD.
The above query gives me 134 recs instead of 0!
Here is my TNSNAMES entry for PRD:
PRD=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1529)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=PRD_USR)
))
SQL & PL/SQL :: Unable To Recreate DBLink In Another DB / Schema?
Mar 25, 2010I'm unable to recreate the db link (private) in another schema/database. Even its a schema object, its come without schema name while extracting from database using metadata api.
code is below (just for extracting)
CREATE OR REPLACE FUNCTION DDI.DBLINK_DDL
RETURN CLOB
AUTHID CURRENT_USER
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
[code].....
Wrong Number Format Display Using DBLink
Jan 7, 2013I have an issue trying to execute some queries using a dblink. When i run any query with numeric fields only display 4 digit and int the source database the fields have 5 digit. The dblink work between ans MSSQL database to an Oracle Database
Example:
MSSQL
select cardnumber from card
cardnumber
19121
19122
Oracle (with dblink)
select cardnumber from card@dblink1
cardnumber
1912
1912
Server Utilities :: Connect To Databases With DBLINK
Jul 17, 2013I'm try to connect to databases with DBLINK. It works, but I drop the dblink to do it again in the final server. The problem that I now is that there is not any DBLINK in my database but I can see the data of the other database (with select 1 from DATABASE.TABLE)
Is my database saving the data of the other database without de DBLINK?