Oracle Function To Retrieve Records Randomly?

Jun 19, 2013

I need function to pick the record from DB random manner.

For example
, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5

If user inputted value is 10 means, it should display one record between 1 to 10.

View 1 Replies


ADVERTISEMENT

PL/SQL :: Retrieve The Records Randomly

Jun 19, 2013

I need function to pick the record from DB random manner.For example, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5 

View 12 Replies View Related

PL/SQL :: Oracle 11g Table Function Returns No Records On First Call?

Jul 13, 2012

On a Oracle 11g R2 I've a table function ( PIPELINED ) returning rows selected from a table.The first time the function is selected, in a session ( I've tried to disconnect and log in again ), it returns no rows.I've tried to log the call using DBMS_OUTPUT and from what I see the select on the table function returns no rows and no output is printed. So I presume Oracle is not calling the function.

The same function on a similar environment ( same db versions, patches and database structure ) works fine. The second environment is a production environment so it has more memory and some other settings enabled.

View 6 Replies View Related

Retrieve Records - Using Code?

May 28, 2008

I need to retrieve the records where the involvement is either a person or an organization. Using the code below i receive an error stating that the outer join operator is not allowed in the operand OR:

(( INVOL1.PERSON_IDENTIFIER(+)=ALL_PERSONS.IDENTIFIER )
OR ( INVOL1.OUNIT_IDENTIFIER_INV=INVOL1_ORG.IDENTIFIER(+) ))

If I change it to an and query it will only retrieve the records where there is a person and an organisation as the involvement but I need this option as well as one or the other.

View 7 Replies View Related

SQL & PL/SQL :: Using Rownum To Retrieve Few Records?

Mar 29, 2010

select rownum, CATR_ID, CAT_ID, CATR_REG_COPY, CATR_REG_LABEL, CATR_ACQUIRED_DATE, CATR_REG_DATE, CATR_MEDIA_COMMENTS, CATR_WITH_DIGITAL,
CATR_ORIGINAL, CATR_LINK, CATR_CREATED_BY, CATR_CREATED_DATE, CATR_MODIFIED_BY, CATR_MODIFIED_DATE, CATR_CHECKOUT, Available,
CATR_RETURN_DATE, LOCN_ID, LOCN_SITE, LOCN_LOCATION, MTYPE_GROUP, MTYPE_NAME, ACCESS_LEVEL, DESCRIPTION, CAT_TITLE, CAT_DESCRIPTION,
CATEGORY_ID, CAT_AUTHOR, CAT_PUBLISHED_DATE, CAT_PUBLISHER, CAT_EVAL_RELEVANT_KEYWORDS, CAT_REG_NUMBER, CAT_REG_SUBNUMBER, U_NAME

[Code]..

There are over 1500 records, but this query does not return any row. If i change rownum >= 100 to rownum <= 100 it returns first hundred records though... What is wrong here?

View 12 Replies View Related

SQL & PL/SQL :: Retrieve Records Of 2 Way Transaction

Apr 13, 2011

I want Query on this table. EXAMPLE TABLE:

SENDER RECEIVER AMOUNT

A B 1000
B C 2000
A D 3000
C A 3000
B A 4000
A B 5000
A B 1000
B C 2000
C A 3000
B A 4000
A B 5000
A C 10000

Dynamically I am giving user name A. I want to retrieve the data how much money user B taken from user A and how much money user B given to A lly for C and D.

CONDITION: I want to retrieve the records of 2 way transaction that means user A sent money to user B and user B must sent money to user A. I don't want one way transaction records i.e user A sent money to D but user A didn't get money from user D like this records.

OUTPUT LIKE:

USER MONEY_TAKEN_FROM_A MONEY_GIVEN_TO_A
B 12000 8000
C 10000 6000

View 14 Replies View Related

SQL & PL/SQL :: Retrieve Records From Sys_ref Cursor

Nov 25, 2010

I wrote the function witch returns some information.

function get_cust_info (v_msisdn integer) RETURN sys_refcursor
IS
curs sys_refcursor;
BEGIN
open curs for 'select first_name, last_name, street, town
from the_table where MSISDN = :1' using v_msisdn;
RETURN curs;
end

How to call this function to write result into table? I just want to write function which returns more Varchar.

View 6 Replies View Related

SQL & PL/SQL :: Retrieve 3 Records From Invoice_data_cur - Getting Duplicates

Oct 8, 2010

I have to write a file using the UTL_FILE that needs to look like the one below. I am getting the righ results but I am getting duplicates, If I have more than one records retrieve in the Invoice_data_cur cursor(for the same pidm) I got the right results, but I got duplicate data, in another words if I retrieve 3 records from the Invoice_ data_cur, I got the same record write to the file 3 times, but the right results. If I only retrieve 1 record in Invoice_ data_ cur , I only get the results of the in invoice_detail_charges_cur I don't get the results of the invoice_ detail_ payments_ cur:

H xxxxxxxxxxxx
I 00399999 10/02/201000000000005000000000000500Jane Smith
D 00366899 Current charges Fall 201010/02/2010Study Abroad Fee - Semester 0000000001200
D 00366899Current charges Fall 201010/02/2010France School Abroad 0000000011200
[code]....

I am getting data in all the cursors..

View 25 Replies View Related

SQL & PL/SQL :: Query To Retrieve Records In Frequency Of Days?

Dec 18, 2012

I have a following requirement in SQL -

Requirement -

I need to list the purchase Ordrers which are not closed.

my sql query should pull if the PO is not closed in 30,90 and 150 days.

It should be shown only on 3oth,90th and 150th day only even the Purchase order is not closed on 33rd day.

View 1 Replies View Related

Client Tools :: Several Short Fetch To Retrieve Records

Dec 28, 2010

I have a big database, I want to retrieve its records in several tries; first I use "select * from dbname where rownum <1000" but for 2'th try how can I get next 1000 records but not previous records?

View 1 Replies View Related

SQL & PL/SQL :: Build Query To Retrieve Records Between Dates With Same Field?

Mar 17, 2010

i have a doubt in building a query.

I have a table with fields

job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010

If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.

View 12 Replies View Related

SQL & PL/SQL :: Query To Retrieve List Of Order_id Which Has No Records In Database

Jul 20, 2010

Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data. But my query is to retrieve the list of order_id which has no records in database.

View 12 Replies View Related

Forms :: How To Retrieve Records From Table Without Using Data Block Wizard

Jun 17, 2012

i want to retrieve records of empno from SCOTT.EMP talbe in form but without data block wizard using ..how .. and what logic will be in coding

DECLARE
TYPE EROC IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
E1 EROC;
NO_EMPNOVARCHAR2(2000);
[code]....

just caling here EMPNO and :v_empno is fields on form...

View 1 Replies View Related

PL/SQL :: Function Retrieve Output Very Slowly - Alternative Code To Replace With Join?

Aug 6, 2012

In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join

CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS

[code]...

View 2 Replies View Related

Execution Plan Randomly Changes After Statistics Collection

Oct 10, 2012

I don't know, if this is the intent behavior of oracle or not. But i noticed, my queries Execution plan randomly changes after statistics collection. Several tables are truncated after the daily run at 8AM and statistics gathered for all the tables in that schema.

However execution plans for 2-3 sql statements always changes after this and performance is brought back to normal by executing the procedure by explicitly calling it from the command line with arguments instead of bind variables.

View 3 Replies View Related

PL/SQL :: Updating A Column Randomly With Selected Values?

Sep 26, 2013

 We've an accounts table that basically represents hotel chains & brands. an example shown below. 

account_id chain_id brand_id  service1              
NULL     NULL          111              
NULL     NULL          122              
NULL     NULL           11

 Here I want to update the chain_id & brand_id which are currently NULL in order to make every row eligible for further processing.

There is another table(say chain_brand) which maintains the relationship between chain_id and brand_id. one chain_id can have multiple brand_ids eg.,

chain_id  brand_id         
101     2011          101    
2012          102     2020 

Now I need a script that could randomly pick values from chain_brand table and update the accounts table. condition is those values should be unique for an account_id eg., 

account_id chain_id brand_id service1              
101          2011     111               101         
2011     122               102          2020     11 

so each account can be attached to only one chain_id and one brand_id.

View 2 Replies View Related

Truncate Statement Failing Randomly With ORA-03291 Error

Mar 31, 2009

My question is on TRUNCATE statement.

So if the truncate syntax goes like
TRUNCATE { TABLE [ schema. ]table
[ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ]
| CLUSTER [ schema. ]cluster
}
[ { DROP | REUSE } STORAGE

Where it is really not mandatory to add (DROP STORAGE) to the truncate statement knowing fully well the Watermark is dropped. Lately some of my Truncate statements that are part of Plsql package have been failing randomly with a ORA-03291 error. I iteratively walk through a list to truncate some tables and 1 or 2 out of 10 randomly fail. But work fine when I rerun. Its been a night mare for couple of weeks now. This code was working alright for last several years now in 10g and previously 9i. Do you believe some changes to settings on the Oracle database or to blame for ?

ORA-03291: Invalid truncate option - missing STORAGE keyword
ORA-06512: at "xxxx.xxxxxxxxx", line 35
ORA-06512: at line 2

View 7 Replies View Related

PL/SQL :: How To Get All Records Using CASE Function

May 20, 2013

first_tbl

First_dt second_dt Item
01-JAN-09     01-JUL-09     item1
01-APR-09     01-DEC-09     item1
01-JUL-09     31-DEC-09     item1
15-DEC-09     10-MAY-10     item1
20-MAR-12     10-AUG-12     item2
31-JUL-12     15-DEC-12     item2
01-APR-09     31-DEC-09     item2

second_tbl

Start_dt end_dt item flag
01-AUG-09     01-NOV-09 item1 null     
02-NOV-09     01-DEC-09     item1     null
01-DEC-09     15-DEC-09     item1     null
15-DEC-09     31-DEC-09 item1 null
01-JAN-10     10-MAY-10 item2 null
10-MAY-10     31-DEC-10     item2 null     
01-JAN-12     15-MAR-12     item2 null
20-MAR-12     31-JUL-12     item2 null

I need o/p in such a way that,if first_dt or second_dt in between start_dt and end_dt, i need to make the flag as y(item is the common join) other wise 'N'.

I was able to make the flag as'Y' but not 'N'

Select A.first_Dt, A.second_Dt, B.Start_Dt, B.End_Dt, A.item, B.item,

Case When B.falg is null
Then 'N'
Else 'Y'
End
From firsttbl A,second_tbl B
Where
A.item=B.item

and

((A.first_Dt>=B.Start_Dt And A.first_Dt<=B.End_Dt)          
Or     
(A.second_Dt>=B.Start_Dt And A.second_Dt<= B.End_Dt));

View 13 Replies View Related

SQL & PL/SQL :: Ranking Function And Duplicate Records

Aug 30, 2012

I have the following table :

CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));

INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(7,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(9,2,3,4);
[code]....

I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);I would like to use the rank function.

Desired update:

A B C D FLAG
1 2 3 4
2 4 5 8
1 2 3 4 D
2 4 5 8 D
7 2 3 4
9 2 3 4
7 2 3 4 D
1 2 3 4 D
5 4 5 8
2 2 3 9
2 4 5 8
6 2 3 4
1 3 3 4
8 2 8 4

View 3 Replies View Related

PL/SQL :: Rank Function And Duplicate Records

Aug 30, 2012

I have the following table :

CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));

INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
[code].......

I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);

I would like to use the rank function.

Desired update:
A     B     C     D     FLAG
1     2     3     4     
2     4     5     8     
1     2     3     4     D
2     4     5     8     D
7     2     3     4     
9     2     3     4     
7     2     3     4     D
1     2     3     4     D
5     4     5     8     
2     2     3     9     
2     4     5     8     
6     2     3     4     
1     3     3     4     
8     2     8     4     

View 5 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records Using Analytical Function

Dec 22, 2012

how to delete duplicated records from a table without using row_id. I found the duplicated rows from a table using Analytical Function. But i could not use the Analytical function in the where condition.

My table(tab2) Structure is

DEPTNODEPT_NAMEEMPIDSEXID1
107jadf 1F1
40asdf 55
10purchase 2M2
10sales 3M3
30HR 4F4

I found the Duplicate Record by using the query

with a as
(select deptno,dept_name,empid,sex,id1,row_number()over(partition by deptno order by deptno) rnum from tab2)
select * from a where rnum >1

how to delete duplicate record .

View 19 Replies View Related

SQL & PL/SQL :: Split String Function For Large Number Of Records?

Aug 6, 2010

I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function

Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)

CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED

[code]....

But here I am facing 2 problems.

1. The function is not accepting a large string & I am getting the error

ORA-01704: string literal too long

2. The function is going for an infinite loop.

View 10 Replies View Related

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

Client Tools :: Debug Function Which Returns Table Of Records?

Apr 24, 2012

I want to debug a function which returns the table of records. When I try to add the parameters and run the debug it gives the error as

PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

if there is any way to debug the function which returns table of records.

View 3 Replies View Related

Performance Tuning :: Function - Insert Around 900000 Records In Name_compress Table?

Apr 12, 2013

I have created below function to remove specific words/special characters from string. This function is producing expected result. Using this function i need to insert around 900000 records in name_compress table. Insert is taking around 7 mins, how we can tune this function so that insert will be executed within 1-2 mins.

Function -

CREATE OR REPLACE FUNCTION NAME_FN(IN_STRING1 VARCHAR2)
RETURN VARCHAR2 IS
V_OUTPUT VARCHAR2(300);
V_OUTPUT1 VARCHAR2(300);
V_OUTPUT2 VARCHAR2(300);
V_OUTPUT3 VARCHAR2(300);

[code]...

View 11 Replies View Related

How To Connect To Oracle To Retrieve Data Using SQL Language

Jan 9, 2012

how to connect to Oracle to retrieve data using SQL language. I've started with a new company which is pretty much a sink or swim type of environment meaning you're an idiot if you don't figure it out on your own. I was told that we use Toad for Oracle 10g. I was given a server name; location and an .ora file named tnsnames that was given to me but I have no clue what to do with. I've come from companies where this was already setup. You just go into access to retrieve the data.

II have totally hit a brick wall with all the other roadblocks I encounter and all researched out. Where I can get step by step instructions on how to connect to start sql in Toad/Oracle?

View 5 Replies View Related

SQL & PL/SQL :: Assign Doc_num From Doc_id Table To 4 Customers In Rent Table Randomly

Jan 6, 2012

I've 2 table with below colums

Create table rent (customer_id number(10),
doc_num varchar2(20)
);
Create table doc_id (doc_num varchar2(20));

Insert into rent(customer_id) values (1);
Insert into rent(customer_id) values (2);
Insert into rent(customer_id) values (3);
Insert into rent(customer_id) values (4);
[code]...

Now my requirement is i need to assign doc_num from doc_id table to 4 customers in rent table randomly. I mean update doc_num in rent table from doc_id table randomly. how to write update statement.

View 8 Replies View Related

Application Express :: Oracle 11g XE - Retrieve Or Reset Password

Dec 12, 2012

I cannot remember my password to access an existing database in Application Express. I tried entering my password 3 times, but the system does not provide a status other than 'Invalid Login Credentials'.

I am going to assume that 3 times is limit before you have to reset the password or from what I see reinstall. When selecting reset, the system asks for an email address that was never established in the system. How can I reset my password without uninstalling and reinstalling Oracle 11g XE?

View 10 Replies View Related

Windows / .NET :: Microsoft Exchange To Retrieve Data From Oracle DB

Aug 8, 2012

My company already work with Oracle 10g, I developed an application using this database and asp.NET. It allows the management of BD and exploitation of these data by clients via ASP pages.

Now I must use Microsoft Exchange (especially the calendar) to retrieve data from the Oracle database and embed links to setup my ASP pages to the calendar.

Being new to Exchange, my questions are:

- It can be done?
- Should I use a particular driver?
- Some idea ?

View 0 Replies View Related

SQL & PL/SQL :: Retrieve HTML Using Oracle And Setting Correct User Agent

Jan 24, 2013

i need to retrieve html using oracle and setting the correct user agent.I used the ut_http package and his fuction utl_http.request_pages.

I worked perfectly for me, execpt that i could be able to setup the correct user-agent.I read the documentation and i found that this instruction utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0'); should fix my problem, but when i used it, nothing changed and the webserver didn't recognize the correct user agent.

I post downhere a piece of the pl-sql procedure i made (it seems that i could find the correct "link" between utl_http.set_header instruction and utl_http.request_pieces instruction)

while(ng < nmax)
loop
ch_in:=p_mese_elab||'-'||ng;
url:=url to retrieve;
req := UTL_HTTP.BEGIN_REQUEST(url);
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
RISULTATO:=utl_http.request_pieces(url,99999);
delete temp_esamina
where data_ril = trunc(to_date(ch_in,'yyyy-mm-dd'));
[code]...

View 11 Replies View Related







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