PL/SQL :: Only Get Users That Match All Links
Jul 17, 2013
Seemingly a very simple problem, but I can't seem to figure it out. Table below is a table that holds UserId' s and LinkId's.
CREATE TABLE UserLink (UserId NUMBER, LinkId NUMBER );
ALTER TABLE UserLink ADD PRIMARY KEY (UserId, LinkId);
INSERT INTO UserLink VALUES (1, 1);
INSERT INTO UserLink VALUES (1, 2);
INSERT INTO UserLink VALUES (3, 1);
INSERT INTO UserLink VALUES (4, 1);
I'm looking for a query in which I can plug a List of LinkId's that outputs only users that have a relationship with ALL LinkId's provided. In this example you may assume that the List is equal to (1, 2), but you can't make any assumptions about the size of the list, other than size != 0. The query to find users that match ANY of the provided LinkId's is quite simple:
SELECT UserId FROM UserLink
WHERE LinkId IN (1,2)
The correct query should output:
USERID
------
1
And it should work for any size > 0 list of values.
View 6 Replies
ADVERTISEMENT
Jul 17, 2013
i want to use more than four db links in my stored procedure to retrieve data from different databases. but it says ORA-02020: too many database links are opened. i also wrote execute immediate to close some links but its not working.
View 16 Replies
View Related
May 24, 2010
We have two databases A and B, both are 10g Enterprise edition and we have to replicate only one table from A to B. A is the source and B needs a read only copy. The source of A is modified by User through Oracle Forms.
A latency of 1day can be accepted though the lesser the better. The problem is that this company does not approve of DB links in any form or any tool which in the end has to create a DB link.
View 2 Replies
View Related
Apr 1, 2013
Basically, we have 2 procedures in 2 different databases. One of them makes a call to the other over a database link passing an associative array as a parameters.
This has woked fine for many years 9.2.0.8.0 but we are in the process of migrating to 11.2.0.2.0 and have started getting compilation errors.
1 > Create TYPE in Database_1
=====================
CREATE OR REPLACE PACKAGE DB1_Pkg
IS
type x_tab is table of number index by binary_integer;
v_tab x_tab;
END DB1_Pkg;
[code]....
View 3 Replies
View Related
Sep 7, 2012
How can I determine what views have a dblink hard coded in them? I am talking about a large number of views so bringing up each view's DDL in a GUI data dictionary tool to look is not an option. I tried running the query below unfortunately "text" is a LONG and doesn't allow the use of the "like" statement.
select * from dba_views where text like '%@%' ;
View 4 Replies
View Related
Nov 29, 2010
the thread title was a bit confusing, couldn't come up with anything short to describe the question. What I am looking for is a query which will put records into groups based on matching values in one of two columns. So if two records have a matching value in column 1 or column 2 they are in the same group. See the example bellow and expected output for a "better" explanation:
--setup
CREATE TABLE foo
(foo_id NUMBER NOT NULL PRIMARY KEY,
record_number NUMBER,
record_value VARCHAR2(1));
[Code]...
--expected output
group# foo_id record_number record_value
1 1 1 A
1 2 1 B
1 3 2 B
1 4 2 C
2 5 3 D
3 6 4 E
3 7 5 E
My initial thought is that is feels a little bit like the sequential seat problem but not quite close enough. I know it could be done iteratively with PL/SQL but I am thinking there must be a way to do it in SQL I am not seeing yet.
View 6 Replies
View Related
Mar 28, 2012
I've begun experiencing a very odd issue with one instance and all of their their db links. When I'm connected via telnet to the unix server, and I connect:
connect myuser/password via sql plus, I connect fine, and the link works.
but when I connect this way:
connect myuser/password@db.tnsname.entry.com using the TNS name specified, I again connect fine but the link calls fails with a TNS cannot resolve service name issue.
This is an existing instance with existing db links, but I repeat the issue on this machine alone with new links. the fully qualified name is the same name entry I'm using to create the link, both when it succeeds and when it fails. This is an 11g instance and the TNSnames file is in the ASM home.Also, i f I connect
connect sys as sysdba I get success as well. This behavior happens with both private and public database links. I'm not aware of any setting changes within this instance, and I've modified (after backing up) the TNSNames file with values from another that works perfectly well. Connecting via remote sqlplus or other JDBC connections gives the same error. Success connecting locally but failure connecting to DB Links when I'm remote. Connecting to the instance shows no issue.
Another instance on the network? An IP config issue in DNS? We have 100's of servers so it's needle in a haystack time. Can I run some sort of trace to see what it's calling to when it works and when it doesn't? (It selects another db's single table and I've confirmed the target is correct and the data is correct).
View 4 Replies
View Related
May 16, 2013
I have a view called V_MEDGEN_LISTRADIO but I can't run SELECT * FROM V_MEDGEN_LISTRADIO
ORA-02020: too many database links in useThe cause is that we call in the view 5 tables over dblink and the open_links parameter is set to 4.
What is strange is that when I run this select by adding a where clause then it works!
SELECT * FROM V_MEDGEN_LISTRADIO WHERE SPRR = 3645;
(use of index so all tables are not called ?)
View 4 Replies
View Related
Oct 2, 2011
I am new to sql..
I need to join 2 tables based on first match.. I cldnot use distinct on the result, as distinct work with entire row..
I cant use group by as well, since for group by hv to select all the columns which we need to display
View 1 Replies
View Related
Jan 20, 2012
Suppose I have a table in which I have first_name, last_name, dob. Now I have to fetch on the basis of first_name=some_value, last_name=some_value and dob=some_date. I want to sort it on the basis of exactly fetched values. Let me take an example-
test table contains-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Sharma 01-Jul-1987
Avinash Pandey 03-Feb-1988
Ankit Gupta 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Sikha Batham 17-Apr-1988
Now if I give parameters-
first_name='Manu'
last_name='Batham'
dob='02-Feb-1988'
then my result should be like below-
result-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Manu Sharma 01-Jul-1987
Ankit Gupta 02-Feb-1988
Sikha Batham 17-Apr-1988
My result is based on the approach-
if matched first_name, last_name, dob --> 1st prefrence in order
if matched first_name, dob --> 2nd prefrence in order
if matched first_name, last_name --> 3rd prefrence in order
if matched last_name, dob --> 4th prefrence in order
if matched first_name --> 5th prefrence in order
if matched last_name --> 6th prefrence in order
if matched dob --> 7th prefrence in order
I designed the following query for the same-
Select first_name,last_name,dob,1 "Order" from test Where
first_name='Manu' and
last_name='Batham' and
dob=to_date('02/02/1988','dd/mm/yyyy')
union
Select a,b,c,2 from test Where
[code]......
I know that this is not the best possible solution as the table is very big and doing so many hits on that table will certainly decrease the performance.
View 19 Replies
View Related
Jan 25, 2012
I'm currently assessing the design/performance of a Distributed System in which hundreds of Field reps have local Oracle DBs (10.2.0.4) on laptops & have to update a remote database (11.2.0.1) via a PUBLIC database link. Field data (millions of records) collected daily is synched from the local to the remote DB & vise versa through this database link. I have 2 concerns here:
1. Is the database link the best option for such a configuration? (recently field reps have been complaining about the slowness in synchronizing data between local & remote DBs). If not, what other options are available for such processing?
2. I've read a lot about security concerns with using PUBLIC database links, but haven't seen any documents to proof they're a majority security issue. why PUBLIC database links are considered not to be very secure?
View 39 Replies
View Related
Nov 14, 2011
I am using SQL Developer.I am self-teaching myself PL/SQL. What I am trying accomplish is to run a select query across many database links at runtime and to insert that query onto a local table. So far, I have only gotten as far as querying the database link names. I am stuck at where my variable calls the database link name. It does not recognize the database link name, and I can't quite grasp the reason why. Below is the first part of my script which does retrieves the column values no problem:
001 SET SERVEROUTPUT ON
002 DECLARE
003 db_link_varVARCHAR2(30);
004 source_cursorINTEGER;
005 destination_cursorINTEGER;
006 src_csrINTEGER;
007 dst_csrINTEGER;
008 rundate_varDATE;
009 instance_name_var VARCHAR2(30);
[code]....
If I break the script down to the bare select query, it will query absolutely fine.If I run the script in its entirety, it will not pick up the variable dbl as a dynamic database link. 02019. 00000 - "connection description for remote database not found"
If I comment out line 031 and prevent the looping of querying of database links (which will only fetch the first value of db_link from dba_db_links), the script will complete and I will have a row inserted into my local table.
I am suspecting it's the looping that is incorrect but I understand it is not going to do anything beyond line 059.
View 8 Replies
View Related
Aug 28, 2012
I have some records as below would like to match and merge them
ID1 ID2
101 103
101 104
205 103
206 103
205 106
151 222
157 222
151 223
156 222
134 223
134 435
156 438
Output should be. All matching records should be grouped together and generate result by new seq
ID1 ID2 Result
101 103 1
101 104 1
205 103 1
206 103 1
205 106 1
151 222 2
157 222 2
151 223 2
156 222 2
134 223 2
134 435 2
156 438 2
View 15 Replies
View Related
Jul 21, 2011
how to find out what are all the objects referring to particular database links in database?
View 6 Replies
View Related
Jul 2, 2011
There is a user account called 'BALA' in my database.I want to find out the database links that uses this 'BALA' account.
View 2 Replies
View Related
Oct 1, 2012
Is it possible to use the Oracle HAIP feature to bond multiple network links between Active and Standby database ?
Does this require a seperate license ?
Basically, I was looking for a solution for bonding the muliple WAN links that we have between the active and Standby database.
View 0 Replies
View Related
Jul 23, 2013
is there any way to refer a table which is in different schema other than using DB links.
View 5 Replies
View Related
Dec 28, 2011
I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.
Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'
Basically the code I'm trying only turns up complete matches and not partials. what I'm doing wrong or how I can improve it?
CODEselect *
from TEST
where NAME1 like ('%'||NAME2||'%');
View 4 Replies
View Related
Aug 2, 2012
Below is a column 'ADDR' with the data (single column)
ADDR
--------
/shared/Folder_1 :^BIAdministrator:^BIAuthor:^BIConsumer:BISystemUser:OracleSystemUser:System:weblogic: :F
[Code]....
View 2 Replies
View Related
Aug 19, 2013
find two words matching from two different tables.
Example:-
table1 || table2
john Dev || Kab Leva
Zaheer khan || mark dev
Cina maater || jhon dev wood
kab leva Sumo || Tony levis
output:-
john dev || john dev
kab leva || kab leva
View 9 Replies
View Related
Apr 23, 2010
I have table as follows:
create table sample1 (
i number ,
j date,
k number)
insert into sample1 values (1,'23-Apr-2010',11)
insert into sample1 values (2,'22-Apr-2010',12)
insert into sample1 values (3,'21-Apr-2010',13)
insert into sample1 values (4,'19-Apr-2010',14)
insert into sample1 values (5,'18-Apr-2010',15)
insert into sample1 values (6,'17-Apr-2010',16)
I would like to get nulls , if there is no data for a date. As we can see , here i am missing the data for '20-Apr-2010'.
I did it through "UNIX" , but it's not efficient.
The data might be missing for the complete week also. I need to test in this way only for the last 7 days. I tried something like this:
select i, j , sum(k)
from sample1
where j in (select to_date(sysdate - rownum)
from dual
connect by rownum < = 7)
group by i, j
View 3 Replies
View Related
Oct 14, 2013
A website requires to display consolidated data from databases located in different geographical regions (India, London and New York). The application server for the website is hosted only in one location India. What are the techniques that can be used for faster retrieval of data from all 3 databases?
Note: There is no need of real time data retrieval from different regions; however the user should able to view the updated data at predefined intervals.
View 9 Replies
View Related
Jun 15, 2013
I know how to use database links in various forms, but I've been trying to think through how the authentication works for a connected user link in 11g. If I create the link like this,
create public database link using 'orcl';
then any user can use the link, provided they have an identical username/password in the two databases. With pre-11g passwords, it was understandable: the password was salted with the username, so the hash of the password would be the same in both databases, and I assumed that the logon through the link used some sort of IDENTIFIED BY VALUES mechanism. But in 11g, the salt will different in the two databases. So the hash will be different. And of course Oracle never stores the actual password. So I don't see how the authentication works.
View 1 Replies
View Related
Dec 19, 2011
I would like to know if we can insert 300 million records into an oracle table using a database link. The target table is inproduction and the source table is in development on different servers.The target table will be empty and have its indexes disabled before the insert. if this can be accomplished in less than 1 hour.
View 26 Replies
View Related
Jun 16, 2013
I know how to use database links in various forms, but I've been trying to think through how the authentication works for a connected user link in 11g. If I create the link like this,create public database link using 'orcl';then any user can use the link, provided they have an identical username/password in the two databases. With pre-11g passwords, it was understandable: the password was salted with the username, so the hash of the password would be the same in both databases, and I assumed that the logon through the link used some sort of IDENTIFIED BY VALUES mechanism. But in 11g, the salt will different in the two databases. So the hash will be different. And of course Oracle never stores the actual password. So I don't see how the authentication works.
View 4 Replies
View Related
Mar 25, 2013
Application which I`m creating would work only inside domain. In the report I would like to have a link to files that stored outside from oracle server ( so apex cannot see the file but knows the absolute path to the file ).
And when I click in the report on the link to the file I would like to have this file opened in the application controlled from my OS ( naturally if I have rights to the path specified given from domain level).
I have been trying with column link ( a href, file:// ) and none of them works. I want to avoid APEX from copying files to local path or opening using java or creating directory.
View 2 Replies
View Related
Feb 28, 2011
In query I have WHERE clause like this:
WHERE TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTD,'DD-MON-YYYY:HH24MI') >
TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTA,'DD-MON-YYYY:HH24MI')
I have data like this:
PEROFOPFROMAIRCRAFTSTD
29/03/20102150
NULL NULL
NULL NULL
30/03/20102150
When I execute the query it always gives me the error "literal does not match format string".
View 7 Replies
View Related
Dec 6, 2012
I'm trying to do data mining on a web log which recorded one day web access information from a busy web server. I imported the data into Oracle Data miner, and created a table (WEBLOG). The idea is to create a new field, i.e. session, for the users so that each session could be thought as a representative of a user-intent (aka topic). Now based on this, data mining models would be used to cluster(group) the users based on their similarity. The first step is to prepare the data which involves using SQL queries. So first, all I did was to create a function for date and time. This is the following code I used,
create or replace function ssndate(p_date in varchar2 default '03-01-18',
p_time in varchar2)
return number
$if dbms_db_version.ver_le_10 $then
deterministic
$elsif dbms_db_version.ver_le_11 $then
result_cache
$end
as
begin
return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss')
- to_date('01-01-90','dd-mm-yy')) * (86400/2400));
end ssndate;
/
The function ssndate compiled successfully.The next step I took was to create a view through the following query,
create or replace view WEBLOG_VIEWS
as
select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT",
C_IP,
CS_USER_AGENT,
(CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK
from WEBLOG;
This was successful as well. The problem is in the next step where I try to do data grouping.
create table FINAL_WEBLOG as
select SESSION_DT, C_IP, CS_USER_AGENT,
listagg(WEB_LINK, ' ')
within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS"
from WEBLOG_VIEWS
group by C_IP, CS_USER_AGENT, SESSION_DT
order by SESSION_DT
[code]....
the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively.
View 6 Replies
View Related
Sep 3, 2012
I've created a table, i have to insert data into it. Herwith Create table statement:
CREATE TABLE IOSTAT_MAC (
IS_DATE DATE,
IS_RS NUMBER(4,2),
IS_WS NUMBER(2,2),
IS_KRS NUMBER(5,2),
IS_KWS NUMBER(5,2),
[code]....
herewith data I have to insert
insert into iostat_mac values(to_date('2012/03/28 08:00:00 AM'),'653.6','20.7','15392.0','451.8','0.0','5.5','0.0','8.2','0','64','/vol/sun_dc_u2');
Receive ora-01861 error when trying to insert.Suspected it had to do with the date format, I changed the nls parameter for my session to include the time format, but to no avail.
View 4 Replies
View Related
Jul 27, 2012
Here is the code :
CREATE OR REPLACE PROCEDURE SEND_MESSAGE_PRC (P_MESSAGE_CONTROL_id varchar2)
IS
BEGIN
DBMS_SCHEDULER.DROP_job(job_name => 'ftp_job');
DBMS_SCHEDULER.drop_program (program_name => 'ftp_prg');
DBMS_SCHEDULER.create_program(program_name => 'ftp_prg',
[code]...
When I execute this : I get an error saying that "too many declarations of set_job_argument_value match this call"..It worked before.
View 2 Replies
View Related