Home » SQL & PL/SQL » SQL & PL/SQL » do i need to pass values in single quotes in IN clause while passing to procedure
do i need to pass values in single quotes in IN clause while passing to procedure [message #417984] Wed, 12 August 2009 06:09 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
p_acct_id is a string varaiable ;values may go either single or multiple values like 'a' or 'a','b','c','d'.


how to pass these values in ex procedure and how to execute select statement.do i need to pass values with single quotes or without quotes.in this case do i need to use execute immediate statement or not?

procedure ex(p_acct_id varchar2) IS

select * emp
from accitd in (p_acct_id);

end ex;
Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #417986 is a reply to message #417984] Wed, 12 August 2009 06:22 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
try with dynamic sql's
Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #417987 is a reply to message #417984] Wed, 12 August 2009 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "varying inlist".

Regards
Michel
Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #417988 is a reply to message #417984] Wed, 12 August 2009 06:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
how to pass these values in ex procedure and how to execute select statement.do i need to pass values with single quotes or without quotes.in this case do i need to use execute immediate statement or not?

No, it is possible to use static SQL. The string parameter has to be converted to SQL table type before. Details are described e.g. in this article: http://tkyte.blogspot.com/2006/06/varying-in-lists.html (@Michel: I hope, this article is not outdated)
Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #418095 is a reply to message #417984] Wed, 12 August 2009 22:57 Go to previous messageGo to next message
basu2706
Messages: 10
Registered: August 2009
Junior Member
Hi,

You can try with this query.

Ex:

P_ACCT_ID := 'a ,b ,c ,d ';

procedure ex(p_acct_id varchar2) IS
select *
from emp
where INSTR(p_acct_id,accitd||' ') > 0 ;

end ex;

Regards,
Basu
Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #418118 is a reply to message #418095] Thu, 13 August 2009 01:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
P_ACCT_ID := 'a ,b ,c ,d ';
In this case, I prefer a comma rather than space. Have a look at this.

By
Vamsi
icon7.gif  Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #418123 is a reply to message #418118] Thu, 13 August 2009 01:41 Go to previous messageGo to next message
basu2706
Messages: 10
Registered: August 2009
Junior Member
That will also do.
Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #418125 is a reply to message #418123] Thu, 13 August 2009 01:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> select ename
  2  from   emp
  3  where  instr('BLAKES ,KINGSFORD ,JONES , DORKING ', ename||' ') > 0;

ENAME
----------
JONES
KING
FORD

This is a very buggy method.
Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #418126 is a reply to message #418118] Thu, 13 August 2009 01:54 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
vamsi kasina wrote on Thu, 13 August 2009 12:03
Have a look at this.
Yes, exactly the same, we have discussed in OTN thread.

By
Vamsi
Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #418128 is a reply to message #418125] Thu, 13 August 2009 01:59 Go to previous messageGo to next message
basu2706
Messages: 10
Registered: August 2009
Junior Member
Hi,

Please try to modify the query to your need..

select ename from emp where instr(' BLAKES , KINGSFORD , JONES , DORKING ', ' '||ename||' ') > 0;

Try wit this.
Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #418129 is a reply to message #418128] Thu, 13 August 2009 02:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I just showed you an example how your code can be flawed. Since you have no idea of what the inidividual elements in the list consist of (might be complete sentences, you don't know if spaces are allowed in a single entry, etc) you can never be sure it will work.
Om top of that, it requires me to format my list of elements according to your code. You don't know if that is possible or not.

[Updated on: Thu, 13 August 2009 02:02]

Report message to a moderator

Re: do i need to pass values in single quotes in IN clause while passing to procedure [message #418133 is a reply to message #418129] Thu, 13 August 2009 02:07 Go to previous message
basu2706
Messages: 10
Registered: August 2009
Junior Member
Yes, i agree with you. This will fail when used for complete sentances. This can be used perticular simple cases. This is just one of the many ways.
Previous Topic: Explain plan differents for same query, resulting in longer query execution time
Next Topic: Store and Display website Link using plsql
Goto Forum:
  


Current Time: Sun Dec 04 02:13:32 CST 2016

Total time taken to generate the page: 0.07707 seconds