Db Link Error Run Pl Sql
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About pl sql database link Us Learn more about Stack Overflow the company Business Learn more about hiring pl sql create database link developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the pl sql dblink Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Using DB Links on a PL/SQL declare pl sql script throws “Table not found” error up vote 2 down vote favorite 2 While trying to create a report using PL/SQL I'm finding myself stuck with the following problem, I created a SQL query that access 2 tables on a remote DB using a DB Link, running the query alone itself returns the expected results but when I run the very same query and put the
Pl Sql Create Synonym
results into a cursor I get a PL/SQL: ORA-00942: table or view does not exist error. I'm not sure if it has something to do with the alias I'm using for each table or maybe the select statement is trying to select local tables, I don't know, do you have any suggestions? PL/SQL: DECLARE CURSOR t_bug_details IS (SELECT h.* FROM table1@REMOTEDB h, table2@REMOTEDB rml WHERE h.product_id IN (123) AND h.category IN ('category') AND h.status < 4 AND h.status NOT IN (1,2,3) AND h.release_status IN (upper('P')) --AND h.programmer IN (upper('MRFOO')) AND h.some_id = rml.some_id and rownum <=400); REPORT_DAY VARCHAR2(40); mail_html clob; mail_bod clob; BEGIN FOR v_some_details in t_bug_details LOOP REPORT_DAY := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'); DBMS_OUTPUT.PUT_LINE(REPORT_DAY || '|' || v_some_details.reptnom || '|' || v_some_details.subject || '|' || v_some_details.field || '|' || v_some_details.release_status || '|' || v_some_details.status || '|' || v_some_details.category || '|' || v_some_details.sub_field || '|' || v_some_details.datef1 || '|' || v_some_details.field_by || '|' || v_some_details.programmer || '|' || TRUNC(sysdate - v_some_details.datef1) || '|' || TRUNC(sysdate - v_some_details.upd_date)|| '|' || v_some_details.fix_avail_date|| '|' || v_some_details.bug_type || '|' || v_some_details.base_reptnom); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('NO RECORDS FOUND'); END; oracle plsql oracle-apex ora-00942 share|improve this question edited Aug 3
8:21 pm UTC Category: Database – Version: oracle 8.0.5 Latest Followup You Asked how can i create database links to access remote databases. please tell me the procedure of creating database
Run T Sql
links. and we said... You need a tnsnames entry in your tnsnames.ora on run sql plus the server. If you can: sqlplus scott/tiger@some_other_database from the machine the server you want to create the database link run transact sql ON works -- you've gotten the first step done. For example, I can: $ sqlplus scott/tiger@ora8idev SQL*Plus: Release 8.1.5.0.0 - Production on Thu Jul 20 09:16:25 2000 (c) Copyright 1999 Oracle Corporation. All http://stackoverflow.com/questions/6915907/using-db-links-on-a-pl-sql-script-throws-table-not-found-error rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options PL/SQL Release 8.1.5.0.0 - Production scott@DEV8I.WORLD> Once you have that setup, you log into the database (the local database -- the one you want to create the database link in to connect to the OTHER database) and issue the create database link command (see the sql reference manual https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:456820211101 for complete syntax). For example I can: scott@8i> create database link ora8idev 2 connect to scott 3 identified by tiger 4 using 'ora8idev' 5 / Database link created. scott@8i> select * from dual@ora8idev; D - X scott@8i> I do not have to use the connect to and identified by clauses, if I do not, it will use the login and password of the currently connected user to connect to the remote database. Reviews Write a Review tnsnames March 10, 2002 - 4:24 am UTC Reviewer: mohammad toaha from Dubai, UAE My problem description is detailed below: SQL> CONNECT DEWADBA/DEWADBA -- Connected to local database Connected. SQL> CREATE TABLE T12 AS SELECT * FROM W_NODE@DISPDC; CREATE TABLE T12 AS SELECT * FROM W_NODE@DISPDC * ERROR at line 1: ORA-02019: connection description for remote database not found SQL> CREATE TABLE T12 AS SELECT * FROM W_NODE@DISPDC; CREATE TABLE T12 AS SELECT * FROM W_NODE@DISPDC * ERROR at line 1: ORA-02019: connection description for remote database not found SQL> CONNECT DEWADBA/DEWADBA@DISPDC -- connected to remote database Connected. Then,as an alternative, I tried to cretae a database link as below: SQL> create database link dispdc1 2 conne
refuses to compile [message #387803] Fri, 20 February 2009 12:17 martin41339 Messages: 6Registered: April 2008 Location: London, UK Junior Member we have a requirement to refresh any one of http://www.orafaq.com/forum/t/140985/ a dozen or more databases from a production server. Permanent dblinks are not tolerated, but application-created ones are OK (ie created for the session and then dropped). I am writing a transportable tablespace http://www.akadia.com/services/ora_database_links.html module which takes the target instance sid as a parameter and I am using that to name a database link created on the fly. In the code, the link is referred to as pl sql a variable: create or replace procedure transport_tablespace_set(p_source_sid varchar2, p_target_sid varchar2, p_tablespace_list varchar2, p_password varchar2) as v_source_sid varchar2(12) := upper(p_source_sid); v_target_sid varchar2(12) := upper(p_target_sid); v_tablespace_list varchar2(4000) := upper(p_tablespace_list); ....... -- need to create a database link to connect to target instance execute immediate('create or replace database link '||v_target_sid||' connect to system identified by '||p_password||' using '''||v_target_sid||''''); -- need to check for connection select 1 into v_checknum pl sql create from dual@v_target_sid; commit; When I try to compile, the error returned is "ORA-00942 table or view does not exist". I am aware that authid current_user does not need to verify objects until runtime, but even with this option set, this will still not compile. Any ideas out there? Report message to a moderator Re: dblink variable refuses to compile [message #387804 is a reply to message #387803] Fri, 20 February 2009 12:32 ThomasG Messages: 3186Registered: April 2005 Location: Heilbronn, Germany Senior Member You use the database link @v_target_sid in the code. The database link @v_target_sid does not exist at compile time. Quote: Permanent dblinks are not tolerated, but application-created ones are OK (ie created for the session and then dropped) Go to the person who came up with that idea, and tell them there are catastrophes about to happen. Rule One of Oracle development: Don't create objects at runtime by the application. Report message to a moderator Re: dblink variable refuses to compile [message #387805 is a reply to message #387803] Fri, 20 February 2009 12:33 BlackSwan Messages: 24922Registered: January 2009 Senior Member You need to help us
another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement. Prerequisites You must have the CREATE DATABASE LINK system privilege. You must have the CREATE SESSION system privilege on the remote Oracle database. You must be able to connect to the remote database. Check GLOBAL_NAMES - to setup the name of the database link. DB_NAME The DB_NAME parameter is the SID of the database and is the name used when creating the database. It is specified within the INIT.ORA parameter file or in the CREATE DATABASE command. select name, value from v$parameter where name = 'db_name'; DB_DOMAN The DB_DOMAIN parameter is the value of the domain to which the database belongs. It can be the same as defined in the DNS - and is usually the company name. However you can set a DB_DOMAIN value which is not part of the DNS. select name, value from v$parameter where name = 'db_domain'; GLOBAL_NAMES GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are connecting. Usually GLOBAL_NAMES is set to TRUE. select name, value from v$parameter where name = 'global_names'; Setting the Name of the Database Link You can query GLOBAL_NAME on the remote database to find out how the name of the database name has to be on the local database. CELLAR> sqlplus system/...@GENTIC SQL> select global_name from global_name; GENTIC.COMPANY.COM So you have to set the database link name to GENTIC.COMPANY.COM if GLOBAL_NAMES is TRUE. SQLNET Setup your TNSNAMES.ORA so you can connect to the remote database. GENTIC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = gentic.company.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GENTIC.COMPANY.COM) ) ) CELLAR = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = cellar.company.com)(PORT = 1521)) (CONNECT_DATA = (S