Home » SQL & PL/SQL » SQL & PL/SQL » How to pass multiple values through a single variable?
How to pass multiple values through a single variable? [message #376975] Fri, 19 December 2008 05:21 Go to next message
vasantha17
Messages: 10
Registered: April 2008
Location: Hyderabad
Junior Member
Hi,

I am trying to pass two values to a select statement like below.

declare
pv_geo varchar2
v_count varchar2(7);
begin
pv_geo:='''AMER''.''LA''';
dbms_output.put_line(pv_geo);
v_count:=0;
select count(1) into v_count FROM   oper_units
             WHERE  oputcode IN ('FE','CUSTOMER')
             AND    geo in (pv_geo) AND    delete_flag IS NULL ;
dbms_output.put_line(v_count);
end;
/


Its returning 0 values. But when i tried to run the select statement directly on the database its returning 16000 records.

please help me to fix this issue.

Thanks,
Vasantha

[MERGED by LF, after moving topic from the Forms forum into the SQL & PL/SQL one]

[Updated on: Sat, 20 December 2008 02:09] by Moderator

Report message to a moderator

Re: how to pass multiple values to a variable [message #376992 is a reply to message #376975] Fri, 19 December 2008 06:16 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Search for "varying elements in IN list" might help.
Re: how to pass multiple values through a single varialbe [message #376996 is a reply to message #376975] Fri, 19 December 2008 06:30 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
EXECUTE IMMEDIATE
(the select statement must be as string)

Rajy
Re: how to pass multiple values to a variable [message #377001 is a reply to message #376975] Fri, 19 December 2008 06:58 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Why did you put the same post in the "FORMS" forum?
Kindly do not multipost your questions. I replied there.

Try to use the execute immediate statement.

Rajy
Re: how to pass multiple values through a single varialbe [message #377002 is a reply to message #376996] Fri, 19 December 2008 07:03 Go to previous messageGo to next message
vasantha17
Messages: 10
Registered: April 2008
Location: Hyderabad
Junior Member
Thanks a lot...
Re: how to pass multiple values through a single varialbe [message #377009 is a reply to message #377002] Fri, 19 December 2008 07:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
EXECUTE IMMEDIATE doesn't work in Forms Smile
Re: how to pass multiple values through a single varialbe [message #377083 is a reply to message #376975] Sat, 20 December 2008 00:59 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Nothing is mentioned about FORMS although this post is in the FORMS FORUM. The title mentions "Oracle 9i" only.
Besides,
Quote:
dbms_output.put_line(v_count);

the OP uses "DBMS_OUTPUT.PUT_LINE" in his query, which ,I believe, is not supported in FORMS too. So, I think this post may be moved to the appropriate forum, and then the use of EXECUTE IMMEDIATE is correct.

To use dynamic SQL in forms, try the DBMS_SQL package.
Have a look at THIS THREAD

Rajy


Re: how to pass multiple values through a single varialbe [message #377094 is a reply to message #377083] Sat, 20 December 2008 02:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It makes sense, Rajy. @vasantha17 has opened the same thread in the SQL & PLSQL Forum, which has been closed after your remark. However, regarding the fact that code DOES include the DBMS_OUTPUT package, perhaps this question really belongs to SQL & PL/SQL forum. Therefore, I'll move it over there and merge with another one. If it turns out that it should be moved back, no problem.
Re: How to pass multiple values through a single variable? [message #377109 is a reply to message #376975] Sat, 20 December 2008 04:57 Go to previous message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Thank you for doing so!!
I would have done it myself, but I am not yet a moderator Wink
Hehehe!!

Rajy
Previous Topic: Behaviur of UNION
Next Topic: Package Doubt
Goto Forum:
  


Current Time: Sat Dec 10 10:35:11 CST 2016

Total time taken to generate the page: 0.10584 seconds