PL/SQL :: Incorrect Query Result Over Database Link
Jul 2, 2012
My problem is I have 3 tables (TEST_TBL1, TEST_TBL2, TEST_TBL3). TEST_TBL2 and TEST_TBL3 are in remote database and I use database link to join them. The following query returns incorrect result (I seems that it ignore the where clause)
SELECT * FROM TEST_TBL1 JOIN TEST_TBL2@db_remote USING (KEY1) JOIN TEST_TBL3@db_remote USING (KEY2) WHERE KEY1=XXX OR KEY2=YYY;
I am on 11R1 (11.1.0.7)
FOR EXAMPLE:
Local database:
CREATE TABLE TEST_TBL1
(
KEY1 NUMBER(5) NOT NULL,
[Code].....
View 8 Replies
ADVERTISEMENT
Feb 1, 2012
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
(Select distinct c.contact_code
Following is the SQL query
Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......
View 1 Replies
View Related
Mar 3, 2011
When I run a menu (which was working fine yesterday and hasn't been changed in months) I click on an item and it runs the following
"
DECLARE
module_name VARCHAR2(125);
BEGIN
module_name := :GLOBAL.FORMS_PATH || 'covenants';
Open_Form(module_name);
END;
"
The module_name variable should be assigned the value 'covenants', because :GLOBAL.FORMS_PATH is '' (nothing). I stepped thru the code and in the debug system values window there is nothing in :GLOBAL.FORMS_PATH. But instead, it gives me: 'FORM_NAMEcovenants' where 'FORM_NAME' is the form the menu was called from.
View 5 Replies
View Related
Apr 1, 2011
why this query is not raising an error:
select memberid, mi.typeid, emailaddress
from memberdata@prod_db m
join memberitems@prod_db mi on m.memberid = mi.memberid;
These are the tables:
SQL> desc memberdata@prod_db
Name Null? Type
----------------------------------------- -------- ----------------------------
MEMBERID NUMBER
EMAILADDRESS VARCHAR2(50)
ADDRESS1 VARCHAR2(50)
SQL> desc memberitems
Name Null? Type
----------------------------------------- -------- ----------------------------
MEMBERID NUMBER
ITEMID NUMBER
Shouldn't we raise an 'ambiguous column' error on memberid?
View 9 Replies
View Related
Nov 2, 2012
I have the following DDL:
drop table tmp_guid;
CREATE TABLE tmp_guid (
c1 raw(16) not null
,c2 raw(16) not null
);
begin
[code]...
It seems that a combination of a unique index and extended stats are to blame. Removing any one of them causes the query to also produce correct results.Extended stats basically captures the fact that despite being unique, c1 depends on c2.
View 0 Replies
View Related
Aug 7, 2009
I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
View 8 Replies
View Related
Oct 5, 2010
I am trying to insert data from 9i to 11g using db link with the below query but while doing so the select statement is going for a full table scan even though rowid is used. But when we execute the same select statement without this insert command it is using proper index. Similar issues I am facing for updates.
Query:
INSERT INTO /*+ APPEND */ emp@db_link
select *
from emp where rowid ='AAC2SmAIIAAAHQgAAZ'
View 6 Replies
View Related
Feb 12, 2013
I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records
SELECT DISTINCT T_Extract.SerGrp, T_Extract.SerCat, T_Extract.Component, T_Extract.Mod_Item ID,
" ##" AS IM, IM_Mapping.TrfCode, IM_Mapping.CatCode, IM_Mapping.CatDescr, IM_Mapping.SubCatCode, IM_Mapping.SubCatDescr,
LKP_Item.Group, LKP_Item.Modality Source
FROM ((LKP_Product
[code]....
T_Extract.Component
T_Extract.Mod_Item ID
T_Extract.SerCat
T_Extract.SerGrp
Just want to know if there is any possibility of running the query with all the Groups in one go
Even if I run group wise it took 30 min query still running without any results...Is there anyway of restructuring the query for better performance.
View 1 Replies
View Related
Mar 17, 2010
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
View 3 Replies
View Related
Jun 26, 2013
Is it possible to use Database authentication in APEX through database link, and how?Also is it posible to read roles from users through database link?
View 3 Replies
View Related
Aug 30, 2007
My scenario is something like this:
MyTable
Rownum Colval
1 23
2 34
3 45
I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?
View 2 Replies
View Related
Jun 15, 2011
I have a sql query which fetch the data from 4 different tables. I want to write the output of that query into a excel or a CSV file without using TOad and all. Let me know is it possible via creating function or procedure.
View 4 Replies
View Related
Aug 29, 2011
I have a select that return the query like this:
EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br
434Ronaldo 11 21454117compras2@gralha.br
434Thiago 13 25418745thiago.alcarin@br.com.br
so,I need the query result in just one line...
EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br 434Ronaldo 11 21454117compras2@gralha.br
I saw some exemples of "pivot query" but I still looking for a solution.
View 10 Replies
View Related
Jan 13, 2011
when I am executing the below query getting diffrent count every time and not able to guess what is happening.
SELECT
count(1)
FROM table1 where table1.LAST_UPDATE_DATE >= current_date - interval '9' day
View 6 Replies
View Related
Mar 20, 2012
in my table there is a row as below.
CITY_ICITY_NST_PROV_CPSTL_CUPDT_USER_IUPDT_TSUPDT_PGM_I
342DC79a03654822-FEB-12 02.02.13.410000000 PMOMR
when i use below below query, there are no values returned.
select city_i from city_e where (trim(city_n))= 'DC' and st_prov_c=79 but while i use below query, im bale to get the result
select city_i from city_e where city_n='DC' and st_prov_c=79
View 5 Replies
View Related
Jul 11, 2012
I need a a query which will give the result of most expensive query i.e. which is taking most of I/O /memory.
View 3 Replies
View Related
Jul 21, 2012
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
INSERT INTO TEST1 VALUES ('gsdk');
[code]....
I get this:
K1
ŁFa
ła
Śab <- WRONG
Śrrrb <- WRONG
4 rows selected
When i change datatype in column to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT
SQL_ID d3d64aupz4bb5, child number 2
select * from TEST1 where k1 like N'Ł%'
Plan hash value: 4122059633
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 2 (100)
* 1 TABLE ACCESS FULL TEST1 1 82 2 (0) 00:00:01
[code]....
View 7 Replies
View Related
Dec 14, 2011
I have a query, when i run this this will give another sql statement. I want run this dynamically...
How to proceed?
View 10 Replies
View Related
Aug 19, 2010
select 1 from dual
where 1 >= any (select 1 as col from dual
union all
select 1 from dual
union all
select 1 from dual);
Output:
------
1
-
1
[code]....
Why the sub query factoring produces different result.Is sub query factoring can be a problem and leads to different result?
View 27 Replies
View Related
Jul 24, 2012
CREATE TABLE tbl_emp
(
name VARCHAR2(20),
price NUMBER,
[Code]...
NAME PRICE DATAENTRD ROWRANK
aaa 9999 24.07.2012 05:56:00 1
aaa 10000 24.07.2012 05:55:58 2
I want to insert this result into another table, how can I do it??
Quote:TABLE
CREATE TABLE tbl_emp_result_set
(
name VARCHAR2(20),
price NUMBER,
dataEntrd date,
rowrank number
)
View 8 Replies
View Related
Apr 3, 2013
I have a view base on this query returned more rows returned.
select *
From (
Select c_code ,
from_date,
c_range ,
[code].........
View 5 Replies
View Related
May 25, 2013
11.2.0.3 got the send mail code from [URL]
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
[Code].....
/I want to call send_mail to pass a query output to "p_message": as example: select sid, program,module,machine from v$session where program='XXX';
And have been struggling due to lack of experience in PLSQL.
View 18 Replies
View Related
Jun 28, 2012
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- SELECT * FROM NLS_SESSION_PARAMETERS;
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
[code]....
When i change datatype to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d3d64aupz4bb5, child number 2
-------------------------------------
select * from TEST1 where k1 like N'Ł%'
[code]....
Note - dynamic sampling used for this statement (level=2)
View 2 Replies
View Related
Jul 21, 2011
Which background process is responsible for getting result of sql query issued to an oracle engine?
View 1 Replies
View Related
Oct 6, 2012
I want to load query result into .xls file with column names by using plsql.
Like this i need to generate 4 files and zip it then send mail with attachement of that zip file.
Can we automate in PLSQL? If it is possible pls share the script.
Ex: select ename,eno,dept,deptno,sal,hiredate from employee;
ENAME ENO DEPT DEPTNO SAL HIREDATE like this we need to store the query result into .xls file.
View 1 Replies
View Related
Oct 29, 2012
There are 4 rows in table with stat_flag 'Updated Record' and stat_date with todays date.
stat date has date & time both, for that reason just trying to format with yyyy.mm.dd
I am getting zero rows as result.
where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')
View 4 Replies
View Related
Apr 3, 2013
Is there any way to Create Form in which i give a query and in result it return the query result in detail block in form of fields just like we return in toad or pl/sql developer.
View 3 Replies
View Related
Jul 7, 2011
I have this query
SELECT wmsys.wm_concat (gp.prog_acronym)
FROM inf_program gp, ea_audit_program ap
WHERE ap.sys_prog_id = gp.sys_prog_id
AND ap.sys_audit_id =484
is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?
View 13 Replies
View Related
Jul 20, 2011
me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),
[code]...
I want that my query should look like the format as attached in the xls sheet.
View 1 Replies
View Related
Mar 16, 2009
I need to compare data of .csv file with the SELECT query result or with any table data.
Is there any possibilities in ORACLE to fulfill this requirement?
View 3 Replies
View Related
