SQL & PL/SQL :: Selecting Clob Value Over DBLink?

Sep 10, 2012

any 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, 2012

I 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 ?

View 2 Replies View Related

SQL & PL/SQL :: Execute Immediate With DBLink

Feb 23, 2011

I 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?

View 4 Replies View Related

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 ?

View 3 Replies View Related

SQL & PL/SQL :: Selecting 6th Top Value

May 9, 2010

how to select the sixth highest earner in my employees table.how to select 6th lowest earner..

View 18 Replies View Related

Getting Data Over SQL Server DBLink

Apr 17, 2012

I 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>

View 1 Replies View Related

Way To Reduce Dblink Timeout

Dec 28, 2011

is 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 Related

Nested Tables By DBLINK

Feb 1, 2011

There'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.

View 2 Replies View Related

SQL & PL/SQL :: Inserting Data Using DBLINK

Feb 21, 2013

I 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.

View 16 Replies View Related

SQL & PL/SQL :: How To Create Procedure By Using Dblink

Jun 26, 2011

iN 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).

View 4 Replies View Related

How To Create A Private DBLINK In 10g

Oct 20, 2012

I 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 Related

Bulk Collect With DBLink?

Feb 23, 2012

create 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]...

View 1 Replies View Related

SQL & PL/SQL :: Materialized View Over DBLink

May 7, 2010

I 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.

View 4 Replies View Related

SQL & PL/SQL :: Selecting 1 Row From A Group?

Jul 3, 2011

I 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]....

View 17 Replies View Related

Selecting The Most Recent Value?

Sep 7, 2007

Have 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.

View 3 Replies View Related

PL/SQL :: Selecting Data For One Day

Jun 18, 2013

I 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');

View 4 Replies View Related

SQL & PL/SQL :: Selecting 1st And 2nd TOP Numbers (from Same Row)

Jul 6, 2012

I 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

View 7 Replies View Related

PL/SQL :: Selecting Into A Varray?

Mar 29, 2013

BANNER
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

View 6 Replies View Related

Control Timeout Before Connecting Via DBLink?

Feb 28, 2012

Is 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.

View 5 Replies View Related

Materialized View Log On DBLink Table?

Aug 6, 2010

DB1: 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?

View 1 Replies View Related

SQL & PL/SQL :: Accessing Data From Server By Using DBLINK

Oct 22, 2010

We 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.

View 15 Replies View Related

Windows :: Move Data From One DB To Other Without DBlink And Using Dot Net

Oct 30, 2012

I 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.

View 3 Replies View Related

SQL & PL/SQL :: Invoke Procedure With DML And COMMIT Through DBLINK?

May 29, 2012

A 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?

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Materialized View And DBLink

Jun 24, 2010

What 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 Related

PL/SQL :: Generate Stats For DBLink Table (SQL)

Sep 7, 2012

I 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.

View 3 Replies View Related

Possible To Specify Login / Password For DBLINK When Calling It?

Apr 3, 2013

I 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'.

View 10 Replies View Related

DBLink Not Querying Remote Table

Apr 16, 2013

I 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)
))

View 14 Replies View Related

SQL & PL/SQL :: Unable To Recreate DBLink In Another DB / Schema?

Mar 25, 2010

I'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].....

View 8 Replies View Related

Wrong Number Format Display Using DBLink

Jan 7, 2013

I 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

View 1 Replies View Related

Server Utilities :: Connect To Databases With DBLINK

Jul 17, 2013

I'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?

View 4 Replies View Related







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