Home » SQL & PL/SQL » SQL & PL/SQL » parasing csv (comma seperated)
parasing csv (comma seperated) [message #281315] Fri, 16 November 2007 06:57 Go to next message
kidoos
Messages: 11
Registered: June 2007
Junior Member
i will get a string from a legacy application which calls my procedure
the string will be of the format 'AAA,CCC,BBB' etc ,
these are individual values in shrtab.stncod.
i need to fetch all the stncod coressponding to AAA ,CCC and BBB.
i cant use dynamic sql or anything . how to achieve this is a single
query.

the following condition wont work as the IN takes 'AAA,CCC,BBB'
as a single value.


DECLARE
v VARCHAR2(25) :=AAA||','||CCC';
c PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO c FROM
SHRTAB WHERE stncod IN (v);
DBMS_OUTPUT.PUT_LINE ( 'count ' || c );
END;

thanks in advance ...
Re: parasing csv (comma seperated) [message #281318 is a reply to message #281315] Fri, 16 November 2007 07:03 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


DECLARE 
v VARCHAR2(25) :='AAA'||','||'CCC';
c PLS_INTEGER; 
BEGIN 
SELECT COUNT(*) INTO c FROM 
SHRTAB WHERE stncod IN (v);
DBMS_OUTPUT.PUT_LINE ( 'count ' || c );
END; 


Thumbs Up
Rajuvan
Re: parasing csv (comma seperated) [message #281319 is a reply to message #281315] Fri, 16 November 2007 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Go to AskTom site and search for "varying in list"

Regards
Michel
Re: parasing csv (comma seperated) [message #281323 is a reply to message #281318] Fri, 16 November 2007 07:11 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
So you added the accidental ommission of an apostrophe. Is that meant to solve the OP's problem? (It doesn't in case you're interested)
Re: parasing csv (comma seperated) [message #281324 is a reply to message #281323] Fri, 16 November 2007 07:16 Go to previous messageGo to next message
kidoos
Messages: 11
Registered: June 2007
Junior Member
DECLARE
v VARCHAR2(25) :='AAA'||','||'CCC';
c PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO c FROM
SHRTAB WHERE stncod IN (v);
DBMS_OUTPUT.PUT_LINE ( 'count ' || c );
END;

this is not working
as i suppose
the query is treating 'AAA'||','||'CCC'
as a single entity.
Re: parasing csv (comma seperated) [message #281327 is a reply to message #281315] Fri, 16 November 2007 07:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes.. You are right . I didn't notice OP was telling

Quote:

these are individual values in shrtab.stncod


Thumbs Down
Rajuvan
Re: parasing csv (comma seperated) [message #281441 is a reply to message #281324] Fri, 16 November 2007 21:57 Go to previous messageGo to next message
kidoos
Messages: 11
Registered: June 2007
Junior Member
any updates?
Re: parasing csv (comma seperated) [message #281442 is a reply to message #281315] Fri, 16 November 2007 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>i cant use dynamic sql or anything
Why can't dynamic SQL be used?
If the choice is using dynamic SQL or not providing any solution, which alternative get chosen?
Re: parasing csv (comma seperated) [message #281456 is a reply to message #281441] Sat, 17 November 2007 00:45 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 16 November 2007 14:05

Go to AskTom site and search for "varying in list"

Regards
Michel


Previous Topic: Username length limitation in Oracle
Next Topic: LONG to CLOB in Java Stored Function
Goto Forum:
  


Current Time: Wed Dec 07 10:50:52 CST 2016

Total time taken to generate the page: 0.14434 seconds