PL/SQL :: Query A Remote DUAL Causes ORA-01729 Or ORA-02019

Jul 6, 2012

I have ORACLE XE 11g installed on two machines and have been trying my luck to get my local db to query/insert/update a table from the remote db.

I have created the public database link and querying the dba_db_links shows that the links is successfully created (see the bottom of the post). I have also created synonyms for my remote table and queries like select name from jforum_forums succeed, with jforum_forums being a public synonym referring to user.jforum_forums@corona.magic.ubc.ca.

However when I try to insert a row into the remote table, there is a query to the remote DUAL to fetch the last generated ID for a given table similar to the following: SELECT jforum_forums_seq.currval FROM DUAL. Statements of such fail and they either throw an ORA-02019: connection description for remote database not found or ORA-01729: database link name expected. I tried including the database link with the name of the table making it like SELECT jforum_forums_seq.currval FROM DUAL@corona.magic.ubc.ca but that didn't work either.

PS, I should mention that global_names are set to true in both databases and that my database link has the same name as the global_name of the remote database.

OWNER
------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME
------------------------------
HOST
--------------------------------------------------------------------------------
CREATED

[code]...

View 18 Replies


ADVERTISEMENT

SQL & PL/SQL :: ORA-02019 / Connection Description For Remote Database Not Found

Mar 12, 2009

I have created ".sql" script in Unix and I am trying to execute the script at SQL prompt. But I am getting the following error message after getting the output.

ORA-02019: connection description for remote database not found

Disconnected from Oracle Database 10g Enterprise Edition

Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options

View 10 Replies View Related

Networking And Gateways :: ORA-02019 / Connection Description For Remote Database Not Found

Jun 11, 2011

I created database link from bala1 database of bala user with the following command.

CREATE DATABASE LINK maddy CONNECT TO P10 IDENTIFIED BY secret554
USING 'MADDY.WORLD';

my aim is to create a dblink to access 'maddy' database objects from bala1 database.

I gave select * from v$database@MADDY.WORLD in bala user and it throwed error.

ORA-02019: connection description for remote database not found

View 5 Replies View Related

Remote DB Query With Joins?

Feb 23, 2012

We have access to a remote Oracle database in Germany and need to insert selected data to our local Oracle database. Our problem is that we have to do several joins (7 tables) on the remote database as well as using one where clause (always the same: P.T_LIEF_LFNT_1='12803193').

Unfortunately we do not have rights to create a view on the remote database. Is there another way to send the entire query to the remote database for processing? Also, the query returns approximately 34,000 rows.

Here is our current query:

INSERT INTO PRIMUS(PARTNO,
SORT_FORMAT,
TP_WORKSPACE,

[Code].....

View 3 Replies View Related

Getting Query Rewrite Using MV's Built From Remote Tables?

Nov 19, 2012

Is it possible to have query rewrite using MV's built from remote tables?

11.20.2

View 1 Replies View Related

SQL & PL/SQL :: Remote Database - Cannot Perform DML Operation Inside A Query

Jun 16, 2010

I have a function declared as PRAGMA AUTONOMOUS_TRANSACTION.

If i execute this function everything is fine.

If I call this function from a remote database, I have this error message:

"ORA-14551: cannot perform a DML operation inside a query".

select function('parameter') from dual;

Result: "OK"

select function@dblink1('parameter') from dual;

Result: "ORA-14551: cannot perform a DML operation inside a query"

View 14 Replies View Related

Add A Column To Dual Table

May 17, 2012

i'v a problem regarding dual table,i logged to oracle as sys as sysdba and i add a column to dual table and then want to delete that added column but oracle gives "ORA-12988: cannot drop column from table owned by SYS"

i'm not able to drop any user oracle gives "ORA-00604: error occurred at recursive SQL level 1" "ORA-01031: insufficient privileges"

another error:: SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level oracle gives error when i want to switch user all problems starts when i add that column to dual table

View 1 Replies View Related

Dual Table Has Only One Column?

Oct 29, 2010

why the dual table has only column?

View 3 Replies View Related

SQL & PL/SQL :: All DML Operation Can Be Done In DUAL Table?

Apr 29, 2010

What are all the DML operation can be done in DUAL table.?

View 5 Replies View Related

SQL & PL/SQL :: Matching Dates Using Dual?

Jul 7, 2010

What I'm trying to do is make it so that it returns 'MATCH' when I pass a date that matches a date every two weeks starting Jan 01. Like Jan 01, Jan 15, Jan 29, Feb 12, etc. would return as MATCH Jan 02, Jan 03, etc. would return as NO_MATCH

The part in bold is what I'm having trouble figuring out.

select nvl(
(select 'MATCH'
from dual
where 'date' = '2 week intervals starting Jan 01'
), 'NO_MATCH') from dual

View 7 Replies View Related

SQL & PL/SQL :: Select Sysdate From Dual

Aug 30, 2012

when i run this nls qusery i got this error

E:oracleproduct10.2.0db_1BIN>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 11:45:59 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select value from v$nls_parameters where parameter= 'NLS_DATE_FORMAT';

VALUE
----------------------------------------------------------------
DD-MON-RR

SQL> select sysdate from dual;

SYSDATE
----------------
30-????? -12

View 11 Replies View Related

SQL & PL/SQL :: UNION While Selecting From Dual?

Sep 19, 2013

I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way

SQL> set heading off;
1 select '#ENCODING WINDOWS-1252' from dual
2 union
3 select ' ' from dual
4 union
5 select 'Language Section EN-US' from dual
6 union
7 select ' ' from dual
8 union
9* select 'Catalog Section Title Date Source' from dual
SQL> /

#ENCODING WINDOWS-1252
Catalog Section Title Date Source
Language Section EN-US

- - - - - - - - - - - - - - - - -

Desired Output:
#ENCODING WINDOWS-1252
Language Section EN-US
Catalog Section Title Date Source

View 12 Replies View Related

PL/SQL :: DDL And DML Commands On Dual Table

Feb 8, 2013

Can we perform DDL & DML Commands on dual table????

If yes then how??

View 11 Replies View Related

PL/SQL :: DUAL Table In Subquery Of UPDATE

Apr 26, 2013

Should the following code work? I feel like I have done this before, but I'm getting a "must be a subquery" error now.

update TABLE1
set (FIELD1, FIELD2) =
values(select 'blah','blah' from DUAL)
where PK_FIELD=12345;

If this is invalid, is there another way to hardcode a series of values like this?

View 8 Replies View Related

SQL & PL/SQL :: Creating List Of Month From Dual Table?

Nov 3, 2011

creating sql query for producing result as per below from dual table.

SELECT to_char( sysdate,'MON-RRRR')
FROM dual
where sysdate between '01-APR-2011' and '31-MAR-2012'
order by 1 desc

from above query the result is

NOV-2011

but i need the result as per below

APR-2011
MAY-2011
JUN-2011
JUL-2011
AUG-2011
SEP-2011
OCT-2011
NOV-2011 CURRENT MONTH

View 4 Replies View Related

SQL & PL/SQL :: Select From Dual Versus Equals Operator?

Mar 14, 2011

I have a package function which is wrapped and I cannot see the code.The package function raises an user-defined exception when :

SELECT ABC.*
FROM ABC
WHERE ABC.A = PACK.FUNC(ABC.B,ABC.C)

But it does not raise any exception and the query works absolutely fine generating desired results when :

SELECT ABC.*
FROM ABC
WHERE ABC.A = (SELECT PACK.FUNC(ABC.B,ABC.C) FROM DUAL)

View 6 Replies View Related

SQL & PL/SQL :: Using Sequence.NEXTVAL From DUAL Versus In INSERT Statement?

Apr 8, 2013

I am trying to understand the difference between using sequence.NEXTVAL from DUAL as against using it direclty in an INSERT statment.

--Sequence Creation
CREATE SEQUENCE SEQ_ID START WITH 1 MINVALUE 1 NOCYCLE CACHE 500 NOORDER;
--Table1 Creation
Create table TABLEA (COL1 number, COL2 varchar2(10),
constraint COL1_PL primary key (COL1));
--Table2 Creation
Create table TABLEB(COL3 number);
alter table TABLEB add constraint COL1_FK foreign key(COL3) references TABLEA(COL1);

-- Option1 - Using sequence.NEXTVAL from DUAL

DECLARE
v_seq_num NUMBER;
BEGIN
SELECT SEQ_ID.NEXTVAL INTO v_seq_num FROM DUAL;
INSERT INTO TABLEA (COL1, COL2) VALUES (v_seq_num, 'test');
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;

-- Option2 - Using sequence.NEXTVAL in INSERT USING RETURNING INTO clause

DECLARE
v_seq_num NUMBER;
BEGIN
INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test') RETURNING COL1 INTO v_seq_num;
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;

View 9 Replies View Related

TimesTen In-Memory :: Execute Statement SELECT CURRENT_DATE FROM DUAL On 11.2.2?

Sep 24, 2013

We are trying to execute a statement SELECT CURRENT_DATE FROM DUAL on Timesten 11.2.2  . It throws error unknown referenced column error. Command> select current_date from dual; 2211:

Referenced column CURRENT_DATE not foundThe command failed. But the following doc shows the support. 

TimesTen PL/SQL Support: Reference Summary CURRENT_DATE function

Returns the current date in the session time zone. YIn TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.

View 4 Replies View Related

Forms :: Dual Monitor Forms With Alerts / Messages

Apr 18, 2013

We have forms 11gr2 on win xp 32bit , weblogic linux server 64bit. We are converting 6i to 11g. Our system has many forms that span on 2 monitors. 1280x768 monitors, so some forms are 2560x768.

When alerts/messages pop up on the screen , they are positioned in the middle of both screens (guessing Oracle by default calculates width =2560/2, height =768/2).

Possible options:

1- is there a way to tell oracle to open alerts/messages a X Y coordinates? Maybe a config file on the server? Already file a Sr with Oracle tech support.
2- tried creating a form to do our messages. but this is flawed since when you call the new form to display the message, then the focus on current form is lost. when the message form is closed (then trigger when_window_activate fires)...this would be hell to try and control all our messages (we may have 20-30 on each big form. We have a total of 118 forms. Lots of messages with Y/N/Cancel options.
3- Maybe create a place holder for messages on all forms (bottom of screen)...thats the best idea we have so far.
4- Is there a way to do something with a java beans? We already have some knowledge with beans since we use image beans and pdf beans.

View 1 Replies View Related

SQL & PL/SQL :: Changes In Remote Database

Nov 20, 2012

I want to update my table base on changes in a column of a table from remote database automatically. I just want to pick some updated column then update to my own. i have made a trigger but failed.

CREATE OR REPLACE
TRIGGER TG_UPD_ID
after update OF STUD_ID on STUDENT@SCHOOLDB
for each row
begin

update MARKS set ID_ST = :new.STUD_ID where STUDID_STUDENT = :old.IDS;

end;

Error report:
ORA-02021: DDL operations are not allowed on a remote database
02021. 00000 - "DDL operations are not allowed on a remote database"
*Cause: An attempt was made to use a DDL operation on a remote database.

For example, "CREATE TABLE tablename@remotedbname ...".

*Action: To alter the remote database structure, you must connect to the remote database with the appropriate privileges.

View 11 Replies View Related

Datapump Use In Remote Servers

Nov 19, 2008

We have eight servers. One of them is for centeral management. The other seven is for development and test databases. Every night, we start a process which exports database schemas (without data) into the centeral server. An example of the process we run is just like below:

CODEcd /data1/backup
mknod exp_pipe_oracle1 p
compress<exp_pipe_oracle1>dev.dmp.Z&
exp user/****@dev file=exp_pipe_oracle1 owner=schema1,schema2,schema3 statistics=none rows=n log=dev.log

As you can see, we use @dev to connect remote database. But dump file is created in local (centeral) server. We are planning to use datapump instead of classic export in future. But I couldn't find a solution, that could take a database export in a remote server and create dump file in local. I have looked at NETWORK_LINK parameter; but it doesn't seem to work for our case.

Is it possible to backup a remote database with datapump and make it create dumpfile in local? (Of course we can use some solutions such as NFS, but we really not prefer this; if datapump has ability to remotly backup.)

View 5 Replies View Related

Security :: Remote Connection Using SYS

Jan 8, 2013

while testing SYS remote connection in our staging server. Executing the command below is OK:

sqlplus sys/<password>@sales as sysdba

While this is mind-boggling for me...

sqlplus sys@sales as sysdba
Enter password:
ERROR:
ORA-01017: Message 1017 not found; No message file for product=RDBMS,
facility=ORA

Enter user-name:
ERROR:
Error while trying to retrieve text for error ORA-01017

I entered the correct password.I've been simulating this for a couple of times, even tried the copy-and-paste method.

View 8 Replies View Related

SQL & PL/SQL :: How To Check Remote Connection

Mar 15, 2011

I working on data transferring from one database server to another database server. but at starting i want to check weather remote database is connected or not?

for e.g.

a := get_remote_connection; --- Calling function for check remote connection return boolean
if a then
<call my data_transfer_proc>
else
exit;
end if;
reply.

View 4 Replies View Related

SQL & PL/SQL :: Calling A Remote Table In SP?

Feb 21, 2011

I have created a remote link to a sql server db in oracle 11.0.7 works fine in sql plus. I can select tables by select * from table@linkname...the link is a public link But when I put it in the procedure it says invalid table name. then I created a public synonym for the table - i can select from the synonym in sql plus - but in the procedure it says synonym translation invalid...Also I cant grant any privileges to the synonym as it says ddl operations are not allowed on remote objects

View 1 Replies View Related

SQL & PL/SQL :: Ping Remote Database

Nov 21, 2011

I have 2 database . One is production and other is Integration and I have one table "emp" which is on integration server and a synonym of that table with the same name"emp" on production. On production we have a proc in which we are using "emp", but some time our integration server goes down, so is there any way to ping the integration server inside the proc and if it returns success then we will execute the rest of code of proc , otherwise it will send a mail to us saying that integration server is down.

View 1 Replies View Related

Oracle 11.x Log To Remote Syslog

Apr 3, 2013

We are running Oracle 11.2.x DB on Solaris platform. Looking for a way to see if I can send alert log and listener log to a remote syslog server? I know there are some other methods like Epilog and some other scripts that could be used, but wondering if there is a "built-in" method to accomplish this?

View 4 Replies View Related

Add Datafile On Remote Computer

Sep 19, 2012

i am working on oracle 10g windows & all my disks are full and i want to add a datafile on other computer . how can i do it ?

View 11 Replies View Related

Remote Migration Of Data

Oct 30, 2012

Is there any oracle utility to do remote migration of data or it is possible only using some third party software.

View 1 Replies View Related

Rman Remote Access

Mar 16, 2011

I am unable to access remote (hosted on remote server) database using rman.

When I try to connect using "rman TARGET sys/syspwd@db" I am getting:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12520: TNS:listener could not find available handler for requested type of server

I the same time I am able to login using sqlplus (sqlplus sys/syspwd@db as sysdba) without any issue.

What am I doing wrong?

View 1 Replies View Related

RMAN Backup To Remote Machine

Nov 2, 2011

am trying to backup my oracle database using RMAN to a remote machine. both systems are using redhat linux. i have successfully configured NFS on my remote machine and mounted it from my oracle database machine.

i then configure rman channel to disk on mounted linux directory..when i take full backup of database i receive this error

ORA-19504: failed to create file "/mnt/ora_df766174940_s104_s1"
ORA-27054: NFS file system where the file is created or resides is not mounted w
ith correct options
Additional information: 3

How do i mount this with correct options the error msg says OR what do i do to backup to the said directory?

View 5 Replies View Related







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