PL/SQL :: Unable To Pass Comma Separated Values For In Clause

Apr 24, 2013

I have the following query : for :P_LEG_NUM Parameter when i am passing values like 1,2,5 as string type i am getting invalid number error... I have defined in clause for it but still it does not work.. For individual values like 2, etc it works... how can i pass comma separated values for this bind variable

select trip_number as prl_trip_number,
flight_number as prl_f_number,
trip_leg_id as prl_trip_leg_id,
leg_number as prl_leg_num,
dicao as prl_dicao,
[code]........      

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Get Comma Separated Values

Oct 19, 2012

Create table a ( Objectid number, Value varchar2(2000);
/

Insert into a values (12, '2,3,4');
Insert into a values (13, '8,7,4');
Insert into a values (14, '3,8,9');
Insert into a values (15, '6,3,11');

I should get the output as:

ID Value
------ ------
12 2
12 3
12 4
13 8
13 7
13 4
14 3
14 8
14 9
15 6
15 3
15 11

View 6 Replies View Related

Comma Separated Values In Parameter?

Jul 2, 2008

Here is what i have in ms-sql, how to convert this into t-sql ?

@MortgagePurposeID is parameter with comma seperated values ('1,2,3,4')
if(substring(@MortgagePurposeID, LEN(@MortgagePurposeID)-1,1)<>'','')
Set @MortgagePurposeID = @MortgagePurposeID + '',''
Set @pos=0

[Code].....

View 2 Replies View Related

PL/SQL :: Rows Into Comma Separated Values

Oct 30, 2012

I would like get rows into comma separated values expected output

rowvalue1,<space>rowvalue2,<space>rowvalue3,<space>rowvalue4,.....Example:

create table test1 (name1 varchar2(10));

insert into test1 values ('JOHN');
insert into test1 values ('YING');
insert into test1 values ('KAREN');
insert into test1 values ('PEDRO');
commit;

SQL> select * from test1;

NAME1
----------
JOHN
YING
KAREN
PEDROHow can I get this to printed as
JOHN, YING, KAREN, PEDRO

View 3 Replies View Related

SQL & PL/SQL :: Enforce Comma Separated Column Values?

May 21, 2012

I have a requirement wherein I need to enforce certain column to have only comma as a delimiter, where multiple values exists.

Example:

Create table:

create table test_oz ( slot number, server_name varchar2(50), used_by varchar2(50),
constraint test_oz_pk primary key (slot, server_name) );

Insert Test Data:

insert into test_oz values ( 1,'SRV1','SAMMY' );
insert into test_oz values ( 2,'SRV1','SAMMY,TOM' );
commit;

Data:

SQL> select * from test_oz;
SLOT SERVER_NAM USED_BY
---------- ---------- ----------
1 SRV1 SAMMY
2 SRV1 SAMMY,TOM

From above, the USED_BY column data need to be only comma separated. Is there a way to enforce that?

When user tries to insert data using any other delimiter, it should fail.

I was trying to see if a CHECK CONSTRAINT could be of use, but could not find it to work.

View 3 Replies View Related

SQL & PL/SQL :: Function To Select Column Values Separated By Comma?

May 23, 2012

I need to write a function which will take table name as input and should return all the columns separated by coma (,).

For example I have a table product as

PROD_ID PROD_NAME FAMILY_ID
------------------------------------
100006Acetaminophen100005
100013Simvastatin100007
100014Ezetimibe100008
100015Simvastatin+Ezetimibe Oral Family100009
100003Abacavir100003
100007Amlodipine100006
100001Cetirizine HCl Oral Solution100001

My function should return the output as

100006,Acetaminophen,100005
100013,Simvastatin,100007
100014,Ezetimibe,100008
100015,Simvastatin+Ezetimibe Oral Family,100009
100003,Abacavir,100003
100007,Amlodipine,100006
100001,Cetirizine HCl Oral Solution,100001

Is there any inbuilt function available?

View 10 Replies View Related

SQL & PL/SQL :: Amend Hyphen Separated String To Comma Separated

Dec 12, 2010

I have a string like this .

'ABC-XYZ-MNO'

and i want the data in the below format

'ABC','XYZ','MNO'

View 14 Replies View Related

PL/SQL :: How To Pass List Of Values In Where Clause Of Query Parameter

Sep 18, 2012

I need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:

declare
TYPE crMain_record is RECORD (
v_code             dummy.v_code%type,
n_no               dummy.n_no%type,

[Code].....

END;"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL: the totlal number of these mulitipe codes can be more then 50..

And lv_character values are commung from a setup table
lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')';
--And lv_character values are commung from a setup table.where "lv_character" holdes multipe code values...
And lv_character values are commung from a setup table and upper(d.v_code)in lv_characterif the

View 3 Replies View Related

Parsing Comma-separated String?

Jan 22, 2009

I have a string value like -- a,,b,c,d,e,f

Using just sql, I want to put each value of the above string in a different row. So the output should be --

a
b
c
d
e
f

using procedures it would not be that great but I want to do it just using queries.

View 1 Replies View Related

SQL & PL/SQL :: Range And Comma Separated Queries

May 30, 2013

I have a few questions about querying using ranges and comma separated lists. The basic situation is a request comes in with part numbers that can be formatted in a range, comma separated lists or both. For an example, the request contains the following part numbers:

<pnum> 1-10, 14, 17, 11, 21-24 </pnum>

I can muster a basic SQL statement to query for this by hand (more then one way to do this)-

SELECT *
FROM part_table
WHERE pnum BETWEEN '1' AND '10'
OR pnum BETWEEN '21' AND '24'
OR pnum IN (14, 17, 11);

is there a way to create the BETWEEN statement so that the dash doesnt need to be parsed out of the request? (like BETWEEN '1-10') or something that functions to that extent? Is it also possible to nest the BETWEEN statements (or the functionality of the BETWEEN) in the IN statement?

View 1 Replies View Related

SQL & PL/SQL :: Comma Separated View Results

Jun 19, 2010

We have a existing view which pulls list of orders against a deal. Straight forward result of the view is below.

Deal idSALES_ORDER_NUMBER
280760548460578
280760548789518
280760548794798
280760548794799
280760548873291
280760548887725
280760548900581

Each deal may have different number of orders.

We need to modify the view logic to result the list as comma seperated:

48460578,48789518,48794798,48794799,48873291,48887725,48900581

how to proceed.

View 2 Replies View Related

SQL & PL/SQL :: Processing Comma Separated Strings

Mar 3, 2003

Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?

View 4 Replies View Related

SQL & PL/SQL :: To Split The Rows By Comma Separated

Mar 4, 2011

SELECT 'TEST','F1,F2,F3,F4' from dual

I want to split the rows by comma separated as below

TEST F1
TEST F2
TEST F3
TEST F4

View 5 Replies View Related

PL/SQL :: Split Comma Separated String

Mar 11, 2013

I am trying to split comma separated string. My table has more than 5 lacks data. I have tried the following SQL but its taking more than 5 minutes. Any Alternative solution to return data quickly ?

SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY REGEXP_SUBSTR(order_id,'[^,]+',1,LEVEL) IS NOT NULL

SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY LEVEL <= LENGTH(order_id) - LENGTH(REPLACE(order_id, ',')) + 1

View 3 Replies View Related

PL/SQL :: Comma Separated String In Rows

Oct 23, 2013

I have one table   select * from abcd;

No  err-----------------------------1    rishi,rahul2    rishi,ak I want output like: 

No ERR1 rishi1 rahul2 rishi2 ak i am using the below query for this: 

select  no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null  but this query is giving me output: 

1rishi1rahul2ak2rishi1rahul2ak if i am using distinct then only desired output is coming. select distinct  no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but i don't want to use distinct because my table has millions of rows and err contains comma separated varchar(6000);

View 4 Replies View Related

SQL & PL/SQL :: Sort A Comma Separated String?

Aug 4, 2008

I have a requirement to sort a comma seperated string. For example if I pass '1234,432,123,45322,56786' as string, then it should return '123,432,1234,45322,56786', after sorting the numbers inside the string.

I have done it creating Global Temporary table. Is there a way without creating the Temp table. I understand I can write the whole logic to sort and append the string, but if there is any direct way.

CREATE GLOBAL TEMPORARY TABLE TEMP_TAB(COL1 VARCHAR2(100)) ON COMMIT DELETE ROWS;
CREATE OR REPLACE FUNCTION func_sort_string(pi_string IN VARCHAR2, pi_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_str VARCHAR2(2000) DEFAULT pi_string || ',';

[code]...

View 26 Replies View Related

Converting Comma Separated List To Table

Dec 7, 2011

I have a comma separated list 'black','red','white' and I want to get each of words in this list in one rows. Some time ago I done it with:

QUOTE select * from XXX('black','red','white')

where XXX was a function which converted list to table.

View 1 Replies View Related

SQL & PL/SQL :: Multiple Search Terms Will Be Separated By A Comma

Dec 12, 2011

I am building a search for use in one of our major applications. I have written a PL/SQL package that deals with it. I would like to present the requirement list to the group and see what, if anything, you may have done differently than I have.

1.) The search interface must have a single box, like google.

2.) Multiple search terms will be separated by a comma.

3.) The table has the following columns:
-- Name
-- Title
-- addr
-- addr2
-- city
-- state
-- zip
-- phone
-- email

4.) Number of Search Terms per query will be unlimited. (for now, as practicality dictates)

5.) Each search term will be checked against various columns.

6.) Search terms must not have a preference in order. Name, Address = Address, Name

7.) Records will be returned only for the rows where all search terms are found.

View 3 Replies View Related

SQL & PL/SQL :: Count Number Of Words In A String Separated By Comma

Aug 31, 2011

I need writing sql which can return the Count of Comma's in a string. Here is my table and data

CREATE TABLE TEST1(SNO NUMBER,STR1 VARCHAR2(30));

INSERT INTO TEST1 VALUES(1234,'ABCD,LL LT,MP');
INSERT INTO TEST1 VALUES(1456,'PP MR');
INSERT INTO TEST1 VALUES(1589,NULL);
INSERT INTO TEST1 VALUES(1897,'PP MR,FTR CLR ON');

Here is the output I am expecting

SNO STR1 STR1_COUNT
1234 ABCD,LL LT,MP 3
1456 PP MR 1
1589 0
1897 PP MR,FTR CLR ON 2

Basically I need to the count of Words separated by comma

View 9 Replies View Related

Performance Tuning :: Split Data Separated By Comma / Then Create Collection With Data Mapped From Other Columns

Sep 25, 2013

DB Used : Oracle 10g.

A table X : NUM, INST are column names

NUM ----- INST

1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1

-- Based upon requirement :

1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,

Eg:

For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298

3) Save these values into

A table Y : INST, NUM are column names.

INST NUM
23 1234,1298

1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.

2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..

View 4 Replies View Related

SQL & PL/SQL :: Use Model Clause To Get Comma Separate Single Row For Multiple Rows?

Feb 19, 2010

I am trying to use model clause to get comma separate single row for multiple rows. My scenario is like this:

SQL> desc test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
VALUE CHAR(6)

SQL> select * from test1 order by id;

ID VALUE
---------- ------
1 Value1
2 Value2
3 Value3
4 Value4
5 Value4
6
7 value5
8

The query that I have is:
SQL> with t as
2 ( select distinct substr(value,2) value
3 from test1
4 model
5 ignore nav
6 dimension by (id)
7 measures (cast(value as varchar2(100)) value)
8 rules
9 ( value[any] order by id = value[cv()-1] || ',' || value[cv()]
10 )
11 )
12 select max(value) oneline
13 from t;

ONELINE
---------------------------------------------------------------------------------------------------
Value1,Value2,Value3,Value4,Value4,,value5,

what I want is : null value should not come and duplicate value should not come (Value4 in output above)

View 11 Replies View Related

SQL & PL/SQL :: How To Get Concatenated Values Separated In Rows

Mar 1, 2012

I have input like below

ID | Name
--------------
1 | ABC, BCA, AAA, BBB (all in one column)
2 | ABC,DBA

and I want to get concatenated values separated

ID | Name
--------------
1 | ABC
1 | BCA
1 | AAA
1 | BBB
2 | ABC
2 | DBA

View 17 Replies View Related

SQL & PL/SQL :: Separated Result With Multiple Values

Aug 24, 2010

with t as
( select 1 id, 101 book_id, 'MICROBIOLOGY' book_type, 1 category, 'sCIENCE AND TECH' category_name
from dual
union all
select 1 , 101 , 'MICROBIOLOGY', 2 , 'HEALTHCARE' from dual
union all

[Code]....

id book_id BOOK_TYPE category category_name

1 101 MICROBIOLOGY 1 SCIENCE AND TECH
1 101 MICROBIOLOGY 2 HEALTHCARE
1 102 CHEMISTRY 5 CHEMICAL ENGINEERING
2 105 COMP SC 1 SCIENCE AND TECH

The above is the output for a query after joining multiple tables. I have just put here the output I am getting after joining the tables.Now I want to achieve the below result.

Expected output: it should be | delimited

1|101|MICROBIOLOGY|102|CHEMISTRY|1|sCIENCE AND TECH|2|HEALTHCARE
2|105|COMP SC|1|SCIENCE AND TECH

Is there any alternative way other than SYS_CONNECT_BY_PATH? I also tried to use CONCAT_ALL but its not working.

View 13 Replies View Related

SQL & PL/SQL :: Display Values Separated By Commas

Jun 22, 2010

I am posting the table and data, i just want to display the columns values separated by ','.

SQL> create table t(c1 varchar2(10),
2 c2 varchar2(10),
3 c3 varchar2(10),
4 c4 varchar2(10));

Table created.

SQL> insert into t values ('A','B','C',null);

1 row created.

SQL> insert into t values ('A','B',null,'D');
1 row created.
SQL> insert into t values ('A',null,'C','D');
1 row created.
[code]....

But it giving extra comma in 1,5,8 rows.

View 3 Replies View Related

Performance Tuning :: Pass 4000 - 5000 Parameter In IN Clause?

Mar 2, 2011

In my below query example , i have to pass more than 4000-5000 paramter in "a1.num" in below query. what is the best way to handle this, also if I pass more than 2000 paramter , the query takes a long time to execute. How can we solve the performance issue as well how I can pass more parameter.

SELECT c1, c2,
TO_CHAR (c3, 'HH24:MI'),
c4,
c5,

[code]...

View 12 Replies View Related

Application Express :: Comma When Passing Values In URL

Jun 20, 2012

I have two page , from first page to second page I am passing some values using url parameter passing but some values contains comma ex :- P1_ NATION  text field contains INDIA,USA,UK

but apex treated that as these are separate values and assign it to separate items

suppose my intention is like P2_NATION,P2_EMPLOYEE,P2_EMPID :INDIA,USA,UK,SAGAR ,123

but apex treating it as P2_NATION = INDIA :P2_EMPLOYEE=USA :P2_EMPID = UK

I am using apex4.1 , db 11g , ie , chrome , ff.

View 2 Replies View Related

Values In User Pass To Be In Encrypted Form

Apr 1, 2009

I have a table in my oracle database named user_details in which i have two fields

a) username varchar(30),
b) userpass varchar(30)

i have inserted some data in it

i want my values in user pass to be in encrypted form .

View 1 Replies View Related

SQL & PL/SQL :: How To Pass Shell Script Values To Procedure

May 30, 2012

I have one procedure , which calls the DBMS_scheduler.run_job , which calls the Shell Script. now based on the shell execution i need to return the message lines to procedure.

View 1 Replies View Related

Data Guard :: Unable To Transfer Archivelog Wrong Pass File

Aug 2, 2010

I am creating physical standby database through Rman duplicate command from 2 node rac cluster. rman do all its work. now am try to start the mrp process on physical standby database. I am getting following errors

------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
ORA-16191: Primary log shipping client not logged on standby
------------------------------------------------------------

I copied the same pass file from primary to standby and many times verify the same but i got the same error.

View 4 Replies View Related

How To Pass Values From Bash Shell Script To Sqlplus

May 3, 2012

I want to run parallel queries of 16 sqlplus jobs through shell script.i don't know to pass values dynamically from shell script to sqlplus.

here i am hard coded the values but i want to pass the values dynamically. and how i will know each job is running successfully.if any job is fail the it will exit from script.

my shell script is :

sqlplus -s username/password @script_file.sql 1001 1004 &
sqlplus -s username/password @script_file.sql 1006 1010 &
sqlplus -s username/password @script_file.sql 1011 1015 &
sqlplus -s username/password @script_file.sql 1016 1025 &

my sql file is:

CLEAR BUFFER
SET SQLBLANKLINES ON
SET ECHO OFF
SET FEEDBACK OFF
SET FLUSH OFF
SET HEADING OFF
[code]........

View 1 Replies View Related







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