Home » SQL & PL/SQL » SQL & PL/SQL » Variable value is not being recongnize (Oracle)
Variable value is not being recongnize [message #580739] Wed, 27 March 2013 12:35 Go to next message
punu77
Messages: 20
Registered: July 2005
Junior Member
Hello,

Thank you in advance!

I need a help in reading the value from csv file and store into a variable and then use that variable in select statement in

filter. I have tried the following ways but it is now working.

File content is
first, second, third, forth
Y, N, 001|002, abc
N, Y, 003|004|005|006, xyz
n, Y, 007|008|009, mno



set serveroutput on size 1000000
DECLARE
v_inFile utl_file.file_type;
v_line varchar2(2000);
v_first varchar2(10);
v_second varchar2(10);
v_third varchar2(50);
v_forth varchar2(50);
cntr number :=0;

BEGIN
v_inFile := utl_file.fopen('/wc2000/interfaces/gtr/inbound', 'file.csv','r');

LOOP
v_first := null;
v_second := null;
v_third := null;
v_forth := null;
cntr := cntr+1;
begin
utl_file.get_line(v_inFile,v_line);
exception
when no_data_found then
exit;
end;
/*End Read File*/

if cntr > 1 then
v_first := substr(v_line,1,instr(v_line,',',1,1)-1);
v_line := substr(v_line,instr(v_line,',',1,1)+1);
v_second := substr(v_line,1,instr(v_line,',',1,1)-1);
v_line := substr(v_line,instr(v_line,',',1,1)+1);
v_third := substr(v_line,1,instr(v_line,',',1,1)-1);
v_line := substr(v_line,instr(v_line,',',1,1)+1);
v_forth := substr(v_line,1,instr(v_line,',',1,1)-1);
v_line := v_line;

dbms_output.put_line(v_first);
dbms_output.put_line(v_second);
dbms_output.put_line(v_third);
dbms_output.put_line(v_forth);

for i in ( select col1, col2 from new_table where groups in (v_third))

loop

begin
dbms_output.put_line(i.col);
end;

end loop;

dbms_output.put_line('Hi3');
end if;
END LOOP;

Thank you,
Punu
Re: Variable value is not being recongnize [message #580741 is a reply to message #580739] Wed, 27 March 2013 12:38 Go to previous messageGo to next message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
"It is not working" is not a valid Oracle error message.
Re: Variable value is not being recongnize [message #580742 is a reply to message #580739] Wed, 27 March 2013 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

BlackSwan wrote on Mon, 22 October 2012 17:16
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Michel Cadot wrote on Tue, 23 October 2012 07:58
punu77 wrote on Mon, 22 October 2012 17:21
Did I posted the the question in wrong form?


With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


Michel Cadot wrote on Wed, 29 June 2011 20:59
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


Will you still ask what you did wrong?

Regards
Michel
Re: Variable value is not being recongnize [message #580751 is a reply to message #580739] Wed, 27 March 2013 13:18 Go to previous messageGo to next message
punu77
Messages: 20
Registered: July 2005
Junior Member
Hi,

There is not any oracle error. nothing is printing in DBMS OUTPUT.


Thanks,
Punu
Re: Variable value is not being recongnize [message #580756 is a reply to message #580751] Wed, 27 March 2013 14:15 Go to previous messageGo to next message
cookiemonster
Messages: 10860
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you got no error and no output and serveroutput is on then this line of code must have run:
exception
when no_data_found then
exit;


However your design is fundamentally flawed. Read this asktom thread
Re: Variable value is not being recongnize [message #580759 is a reply to message #580756] Wed, 27 March 2013 14:21 Go to previous messageGo to next message
BlackSwan
Messages: 22556
Registered: January 2009
Senior Member
post code does not successfully compile for me.
One way to debug code like above is to eliminate all logic within LOOP & simply COUNT the records in the file.
If the COUNT is not correct, the additional code lines add nothing but confusion to completing the over all task.
Re: Variable value is not being recongnize [message #580765 is a reply to message #580759] Wed, 27 March 2013 16:41 Go to previous messageGo to next message
punu77
Messages: 20
Registered: July 2005
Junior Member
Hi,
Thank you for replying. I have corrected the code little bit and pasted down below.
new_table strcture is like this:
create table new_table (col1 varchar2(10), col2 varchar2(10), groups varchar2(10));
select * from new_table
col1 col2 groups
aaaa zzzz 001
bbbb yyyy 002
cccc xxxx 003
dddd wwww 004
eeee vvvv 004

v_third variable holding the 001,002 value
I think since v_third variable holding the string and I am using that variable in where clause, it's not recognizing the string.
If I break it down this value in two seperate variable it will work, but I don't in the data some time it's 001|002 some time 002|003|004.
Please help me out.

set serveroutput on size 1000000
DECLARE
v_inFile utl_file.file_type;
v_line varchar2(2000);
v_first varchar2(10);
v_second varchar2(10);
v_third varchar2(50);
v_forth varchar2(50);
cntr number :=0;

BEGIN
v_inFile := utl_file.fopen('/wc2000/interfaces/gtr/inbound', 'file.csv','r');

LOOP
v_first := null;
v_second := null;
v_third := null;
v_forth := null;
cntr := cntr+1;

begin
utl_file.get_line(v_inFile,v_line);
exception
when no_data_found then
exit;
end;
/*End Read File*/

if cntr > 1 then
v_first := substr(v_line,1,instr(v_line,',',1,1)-1);
v_line := substr(v_line,instr(v_line,',',1,1)+1);
v_second := substr(v_line,1,instr(v_line,',',1,1)-1);
v_line := substr(v_line,instr(v_line,',',1,1)+1);
v_third := translate(substr(v_line,1,instr(v_line,',',1,1)-1),'|', ',');
v_line := substr(v_line,instr(v_line,',',1,1)+1);
v_forth := substr(v_line,1,instr(v_line,',',1,1)-1);
v_line := v_line;

dbms_output.put_line(v_first);
dbms_output.put_line(v_second);
dbms_output.put_line(v_third);
dbms_output.put_line(v_forth);

for i in ( select col1, col2 from new_table where groups in (v_third))

loop

begin
dbms_output.put_line(i.col1);
exception
when no_data_found then
dbms_output.put_line('hi2');

end;

end loop;

dbms_output.put_line('Hi3');
end if;

END LOOP;

END;
Re: Variable value is not being recongnize [message #580766 is a reply to message #580765] Wed, 27 March 2013 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 22556
Registered: January 2009
Senior Member
SQL statements must be known & fixed at compile time.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Variable value is not being recongnize [message #580767 is a reply to message #580756] Wed, 27 March 2013 17:13 Go to previous message
cookiemonster
Messages: 10860
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Wed, 27 March 2013 19:15

However your design is fundamentally flawed. Read this asktom thread


You can't make this work without reading that.
Previous Topic: Use index to minimize the lock on parent table
Next Topic: generating a key for grouping
Goto Forum:
  


Current Time: Fri Aug 01 12:30:07 CDT 2014

Total time taken to generate the page: 0.13283 seconds