Home » SQL & PL/SQL » SQL & PL/SQL » String input (Oracle, 10g, Windows Server 2003)
String input [message #445771] Thu, 04 March 2010 01:09 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
I have a procedure, which is getting called from ado.net with a input parameter named p_inp_oids which is a character string like '(122,323,434,444)',


My procedure body contains a query whose WHERE clause contains

AND OID IN p_inp_oids


I cannot post the whole procedure due to some problem.
Also please note that OID is number.



How to collect that input parameter in my procedure?


Regards,
Ritesh
Re: String input [message #445772 is a reply to message #445771] Thu, 04 March 2010 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Already answered many times, search for varying-inlist.

Principle:
SQL> with 
  2    input as (select '(122,323,434,444)' input from dual),
  3    data as (select ','||substr(input,2,length(input)-2)||',' input from input)
  4  select substr(input,
  5                instr(input,',',1,level)+1,
  6                instr(input,',',1,level+1)-instr(input,',',1,level)-1)
  7          val
  8  from data
  9  connect by level < length(input)-length(replace(input,',',''))
 10  /
VAL
-----------------
122
323
434
444

4 rows selected.

Regards
Michel
Re: String input [message #445780 is a reply to message #445772] Thu, 04 March 2010 02:01 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
thanks for your reply

But i have use that input parameter in WHERE clause of my query, so please suggest some idea

My filter condition is


AND OID IN p_inp_oids


Also please note OID is a number


Regards,
Ritesh
Re: String input [message #445781 is a reply to message #445780] Thu, 04 March 2010 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just put: "IN (my query)"

Regards
Michel
Re: String input [message #446220 is a reply to message #445781] Sat, 06 March 2010 08:52 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
Thank you

Regards,
Ritesh
Re: String input [message #477795 is a reply to message #446220] Mon, 04 October 2010 09:35 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

What change to be done in following code my procedure is getting an input of
'('SCOTT','MIKE')' instead of '(122,323,434,444)'
SQL> with 
  2    input as (select '(122,323,434,444)' input from dual),
  3    data as (select ','||substr(input,2,length(input)-2)||',' input from input)
  4  select substr(input,
  5                instr(input,',',1,level)+1,
  6                instr(input,',',1,level+1)-instr(input,',',1,level)-1)
  7          val
  8  from data
  9  connect by level < length(input)-length(replace(input,',',''))
 10  /


Regards,
Ritesh

[Updated on: Mon, 04 October 2010 09:36]

Report message to a moderator

Re: String input [message #477799 is a reply to message #477795] Mon, 04 October 2010 09:45 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
What output do you get with that input?

EDIT: typo

[Updated on: Mon, 04 October 2010 09:45]

Report message to a moderator

Re: String input [message #477805 is a reply to message #477799] Mon, 04 October 2010 09:52 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

I get no rows returned with that input, even though rows are present in database.


Also please note that now OID is of varchar2 type.

Re: String input [message #477806 is a reply to message #477799] Mon, 04 October 2010 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what output do want?

Regards
Michel
Re: String input [message #477810 is a reply to message #477806] Mon, 04 October 2010 09:57 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
What ouput do you get from this:
SQL> with 
  2    input as (select '(122,323,434,444)' input from dual),
  3    data as (select ','||substr(input,2,length(input)-2)||',' input from input)
  4  select substr(input,
  5                instr(input,',',1,level)+1,
  6                instr(input,',',1,level+1)-instr(input,',',1,level)-1)
  7          val
  8  from data
  9  connect by level < length(input)-length(replace(input,',',''))
 10  /

With that input?
Cause it won't be no rows.
Re: String input [message #477811 is a reply to message #477806] Mon, 04 October 2010 09:59 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Principle:

SQL> with 
  2    input as (select '(122,323,434,444)' input from dual),
  3    data as (select ','||substr(input,2,length(input)-2)||',' input from input)
  4  select substr(input,
  5                instr(input,',',1,level)+1,
  6                instr(input,',',1,level+1)-instr(input,',',1,level)-1)
  7          val
  8  from data
  9  connect by level < length(input)-length(replace(input,',',''))
 10  /
VAL
-----------------
122
323
434
444

4 rows selected.


Earler code given was correct for series of comma separated numbers inputed as string, but now i am getting a series of comma separated strings.Thats the only difference

i.e. '(122,323,434,444)' is replaced by '('SCOTT','MIKE')'

[Updated on: Mon, 04 October 2010 10:01]

Report message to a moderator

Re: String input [message #477815 is a reply to message #477811] Mon, 04 October 2010 10:03 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I know about varying in-list.

What I'm trying to suggest - In a way that is obviously far too subtle for you - is that you should compare the difference in the output of that query that you get from each of the two strings. Having analysed the differences it should be very easy to change the SQL to do what you need.

Re: String input [message #477816 is a reply to message #477811] Mon, 04 October 2010 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
'(122,323,434,444)' is replaced by '('SCOTT','MIKE')'

'('SCOTT','MIKE')' is NOT a valid string, so your question is meaningless.

There is no difference in the method between numbers and strings as in this one numbers are treated as strings.

Regards
Michel

[Updated on: Mon, 04 October 2010 10:05]

Report message to a moderator

Re: String input [message #477817 is a reply to message #477815] Mon, 04 October 2010 10:08 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

SELECT EMPNO,ENAME 
FROM EMP E
WHERE ENAME IN NVL(( with 
      input as (select '(MIKE,SCOTT)' input from dual),
      data as (select ','||substr(input,2,length(input)-2)||',' input from input)
    select substr(input,
                  instr(input,',',1,level)+1,
                  instr(input,',',1,level+1)-instr(input,',',1,level)-1)
            val
    from data
    connect by level < length(input)-length(replace(input,',',''''))),E.ENAME);


Output
EMPNO  ENAME
1234   MIKE



It is not giving outpput for SCOTT even though data is present for it.

Here if i change the input data sequence i get

SELECT EMPNO,ENAME 
FROM EMP E
WHERE ENAME IN NVL(( with 
      input as (select '(SCOTT,MIKE)' input from dual),
      data as (select ','||substr(input,2,length(input)-2)||',' input from input)
    select substr(input,
                  instr(input,',',1,level)+1,
                  instr(input,',',1,level+1)-instr(input,',',1,level)-1)
            val
    from data
    connect by level < length(input)-length(replace(input,',',''''))),E.ENAME);

EMPNO  ENAME
7788   SCOTT

[Updated on: Mon, 04 October 2010 10:13]

Report message to a moderator

Re: String input [message #477818 is a reply to message #477817] Mon, 04 October 2010 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Post what does the inner query returns
2/ Prove there is a row for SCOTT
3/ Why did you change the original working inner query for this: replace(input,',','''')?

Regards
Michel

[Updated on: Mon, 04 October 2010 10:13]

Report message to a moderator

Re: String input [message #477819 is a reply to message #477818] Mon, 04 October 2010 10:23 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

1/ Post what does the inner query returns
2/ Prove there is a row for SCOTT
3/ Why did you change the original working inner query for this: replace(input,',','''')?

Answer to 1 question

VAL
------
SCOTT
MIKE

this is with reference to code given in this forum
Answer to 2 question

Already shown in my post above

Answer to 3 question

with original code i'm getting

ORA-01427-single row subquery returns more than one row

Regards,
Ritesh

[Updated on: Mon, 04 October 2010 10:38]

Report message to a moderator

Re: String input [message #477824 is a reply to message #477819] Mon, 04 October 2010 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Answer to 2 question

Already shown in my post above

Don't see anywhere it REPOST WHEN WE ASK YOU.
I see nothing that proves there is a row for SCOTT in EMP.

Regards
Michel

[Updated on: Mon, 04 October 2010 11:57]

Report message to a moderator

Re: String input [message #477829 is a reply to message #477817] Mon, 04 October 2010 12:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Apologies.

Incorrect post.

Regards

Raj

[Updated on: Mon, 04 October 2010 12:49]

Report message to a moderator

Re: String input [message #477854 is a reply to message #477824] Mon, 04 October 2010 22:03 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Proof that there is SCOTT in EMP table


SQL> select *
  2  from emp
  3  where empno = 7788;

EMPNO ENAME JOB     MGR   HIREDATE   SAL COMM DEPTNO
----- ----- ---     ---   --------   --- ---- ------
7788  SCOTT ANALYST 7566  19-APR-87 3000          20


Regards,
Ritesh

[Updated on: Mon, 04 October 2010 22:26]

Report message to a moderator

Re: String input [message #477865 is a reply to message #477854] Tue, 05 October 2010 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your output is faked, this is NOT a proof.

Regards
Michel
Re: String input [message #477866 is a reply to message #477865] Tue, 05 October 2010 00:22 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
  1* select * from emp where empno = 7788
SQL> /

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20

Re: String input [message #477897 is a reply to message #477866] Tue, 05 October 2010 02:18 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

SELECT EMPNO,ENAME 
FROM EMP E
WHERE ENAME IN NVL(( with 
      input as (select '(SCOTT,MIKE)' input from dual),
      data as (select ','||substr(input,2,length(input)-2)||',' input from input)
    select substr(input,
                  instr(input,',',1,level)+1,
                  instr(input,',',1,level+1)-instr(input,',',1,level)-1)
            val
    from data
    connect by level < length(input)-length(replace(input,',',''))),E.ENAME);

This query when executed gives

ORA-01427-single row subquery returns more than one row


In same query if i remove NVL() I get

SELECT EMPNO,ENAME 
FROM EMP E
WHERE ENAME IN ( with 
      input as (select '(SCOTT,MIKE)' input from dual),
      data as (select ','||substr(input,2,length(input)-2)||',' input from input)
    select substr(input,
                  instr(input,',',1,level)+1,
                  instr(input,',',1,level+1)-instr(input,',',1,level)-1)
            val
    from data
    connect by level < length(input)-length(replace(input,',','')));

EMPNO  ENAME
-----  -----
1234   MIKE
7788   SCOTT


Following are the table creation scripts

CREATE TABLE "SCOTT"."EMP" 
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10 BYTE), 
	"JOB" VARCHAR2(9 BYTE), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0), 
	 CONSTRAINT "PK_EMP1" PRIMARY KEY ("EMPNO")
   );

Insertion scripts


Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (1234,'MIKE','MANAGER',7788,to_date('12-APR-10','DD-MON-RR'),20000,null,10);  

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,10); 

Insert into "EMP" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10); 


Here i have to use NVL, is there some to write it

Regards,
Ritesh
Re: String input [message #477901 is a reply to message #477897] Tue, 05 October 2010 02:46 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
getritesh wrote on Tue, 05 October 2010 09:18
Here i have to use NVL, is there some to write it

Then you have to pass it an expression as the first parameter (one value, not a collection row). Documentation is also quite clear about it - e.g. here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions105.htm#i91798

If you stop insisting on using NVL, you might try the approach described in this post: http://www.orafaq.com/forum/mv/msg/161980/477280/96705/#msg_477280
Re: String input [message #477937 is a reply to message #477901] Tue, 05 October 2010 05:14 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Please someone tell a solution for this.
Here in this query i've to use NVL(), it is must.
Re: String input [message #477957 is a reply to message #477937] Tue, 05 October 2010 06:32 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
getritesh wrote on Tue, 05 October 2010 12:14
Please someone tell a solution for this.
Here in this query i've to use NVL(), it is must.

It sounds strange you have to use certain implementation; as the reasonable requirements should be only related to the functionality.

Once again, if you insist on using NVL, you have to pass expression (one value). Full stop.

If you want to achieve, that the query returns all rows when P_INP_OIDS is empty, just add appropriate conditions, e.g.
WHERE p_inp_oids IS NULL  -- empty string
   OR length(p_inp_oids) <= 2   -- only closing parenthesis
   OR <the condition without NVL>

Of course, it would be much easier, if P_INP_OIDS had collection (TABLE OF) datatype.
Previous Topic: Windowing clause in analytical function
Next Topic: UTL_FILE package (merged)
Goto Forum:
  


Current Time: Mon Sep 26 14:32:05 CDT 2016

Total time taken to generate the page: 0.23609 seconds