Limitation of NVARCHR2 in Cursor [message #385414] |
Mon, 09 February 2009 08:35 |
jaymala_k
Messages: 19 Registered: May 2008 Location: India
|
Junior Member |
|
|
Hi,
I am using one table with multiple NVARCHAR2 fields. If, I try to select the fields in D2K with cursor, then it showing only first value and rest value returning as NULL.
Example : Table is Employee
(first_name NVARCHAR2(200),
Middle_name NVARCHAR2(100),
Last_name NVARCHAR2(100),
age number)
cursor c1 is select * from employee;
I changed the cursor with
cursor c1 select first_name,middle_name,last_name,age from employee;
then also same problem, it showing only first_name.
I have tried with TO_CHAR
cursor c1 is
select to_char(first_name),to_char(middle_name),to_char(last_name,age from employee;
then it is working. Is it necessary to use To_char for NVARCHAR2 fields?
Regards,
Jaymala
|
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385489 is a reply to message #385426] |
Mon, 09 February 2009 22:26 |
jaymala_k
Messages: 19 Registered: May 2008 Location: India
|
Junior Member |
|
|
Hi,
Code is as follows,
Declare
Cursor c1 is select * from employee;
ro_appl c1%rowtype;
Begin
open c1;
fetch c1 into ro_appl;
ro_appl.first_name := c1.first_name;
ro_appl.middle_name := c1.middle_name;
ro_appl.last_name := c1.last_name;
pr_alprop('al_msg','Error',First Name'||ro_appl.first_name,v_prval);
pr_alprop('al_msg','Error',Middle Name'||ro_appl.middle_name,v_prval);
pr_alprop('al_msg','Error',Last Name'||ro_appl.last_name,v_prval);
close c1;
end;
Regards,
Jaymala
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385503 is a reply to message #385414] |
Mon, 09 February 2009 23:17 |
urnikhil
Messages: 42 Registered: March 2008
|
Member |
|
|
I think your code should be something like this with a loop to get all records:
Declare
Cursor c1 is select * from employee;
ro_appl c1%rowtype;
Begin
open c1;
loop
fetch c1 into ro_appl;
exit when c1%notfound;
ro_appl.first_name := c1.first_name;
ro_appl.middle_name := c1.middle_name;
ro_appl.last_name := c1.last_name;
pr_alprop('al_msg','Error',First Name'||ro_appl.first_name,v_prval);
pr_alprop('al_msg','Error',Middle Name'||ro_appl.middle_name,v_prval);
pr_alprop('al_msg','Error',Last Name'||ro_appl.last_name,v_prval);
end loop;
close c1;
end;
- Nikhil.
|
|
|
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385519 is a reply to message #385512] |
Tue, 10 February 2009 00:11 |
jaymala_k
Messages: 19 Registered: May 2008 Location: India
|
Junior Member |
|
|
Hi David,
Depending on one condition I have to fetch data from this table. Same code is working, if I, alter table from Nvarchar2 to varchar2 or I use To_CHAR for each field.
If any problem with cursor or query then it should not work with other alternatives also.
Any restriction to use NVARCHAR2 fileds?
Regards,
Jaymala
|
|
|
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385525 is a reply to message #385524] |
Tue, 10 February 2009 00:43 |
jaymala_k
Messages: 19 Registered: May 2008 Location: India
|
Junior Member |
|
|
pr_alprop, is just for checking.
For first record also, it showing value of first field and returning rest of the field values as NULL eventhough in the table there are values.
Example :
Table has following data
Emp_id First_name Middle Name Last_name Age
1 Vijay Ashok Karmarkar
2 Dany Sam pinto
For this data, cursor is showing only value of Vijay and returning middle and last_name NULL.
This is only for NVARCHAR2.
Jaymala
|
|
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385544 is a reply to message #385541] |
Tue, 10 February 2009 01:47 |
jaymala_k
Messages: 19 Registered: May 2008 Location: India
|
Junior Member |
|
|
I also tried to get the values with Select...into, but same result.
Is there any restriction on NVARCHAR2 fields in one table?any restriction on Fiels size?
Any specific setting to use NVARCHAR2 in D2k?
Regards,
Jaymala
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385613 is a reply to message #385414] |
Tue, 10 February 2009 04:40 |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Posting your real code would probably be helpfull, seeing as how this:
Declare
Cursor c1 is select * from employee;
ro_appl c1%rowtype;
Begin
open c1;
fetch c1 into ro_appl;
ro_appl.first_name := c1.first_name;
ro_appl.middle_name := c1.middle_name;
ro_appl.last_name := c1.last_name;
pr_alprop('al_msg','Error',First Name'||ro_appl.first_name,v_prval);
pr_alprop('al_msg','Error',Middle Name'||ro_appl.middle_name,v_prval);
pr_alprop('al_msg','Error',Last Name'||ro_appl.last_name,v_prval);
close c1;
end;
Will never ever compile.
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385629 is a reply to message #385613] |
Tue, 10 February 2009 05:40 |
jaymala_k
Messages: 19 Registered: May 2008 Location: India
|
Junior Member |
|
|
Declare
Cursor c1 is select * from employee;
ro_appl c1%rowtype;
Begin
open c1;
lopp
fetch c1 into ro_appl;
exit when c1%notfound;
ro_appl.first_name := c1.first_name;
ro_appl.middle_name := c1.middle_name;
ro_appl.last_name := c1.last_name;
end loop;
close c1;
end;
No compilation error but not returning values for 2nd and 3rd fields.
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385636 is a reply to message #385414] |
Tue, 10 February 2009 05:50 |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
REALLY?
'record.item := cursor.item'
is not valid syntax.
In fact cursor.item is not valid syntax.
You should get something like this:
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
SQL> Declare
2 Cursor c1 is select * from user_tables;
3 ro_appl c1%rowtype;
4 Begin
5 open c1;
6 LOOP
7 fetch c1 into ro_appl;
8 exit when c1%notfound;
9 ro_appl.table_name := c1.table_name;
10 ro_appl.tablespace_name := c1.tablespace_name;
11 ro_appl.status := c1.status;
12 END LOOP;
13 CLOSE c1;
14 END;
15 /
ro_appl.table_name := c1.table_name;
*
ERROR at line 9:
ORA-06550: line 9, column 26:
PLS-00225: subprogram or cursor 'C1' reference is out of scope
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored
ORA-06550: line 10, column 31:
PLS-00225: subprogram or cursor 'C1' reference is out of scope
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
ORA-06550: line 11, column 22:
PLS-00225: subprogram or cursor 'C1' reference is out of scope
ORA-06550: line 11, column 1:
PL/SQL: Statement ignored
SQL>
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385647 is a reply to message #385636] |
Tue, 10 February 2009 06:35 |
jaymala_k
Messages: 19 Registered: May 2008 Location: India
|
Junior Member |
|
|
Declare
Cursor c1 is select * from employee;
ro_appl c1%rowtype;
Begin
open c1;
fetch c1 into ro_appl;
<<Displaying the values fetch from cursor>>
close c1;
end;
but not returning values for 2nd and 3rd fields, only for NVARCHAR2 fields.
|
|
|
|
|
Re: Limitation of NVARCHR2 in Cursor [message #385666 is a reply to message #385414] |
Tue, 10 February 2009 08:12 |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I'm not aware of any restrictions with NVARCHARs but I don't really use them. The fact that one of them works but not the others suggests to me that something else is going on but since you keep supplying incomplete and/or erroneous code it's impossible to tell what's going on.
Suggest you try the following.
1) base a datablock on your table, query it and see if it displays the data correctly.
2) Change your block of code to insert the data you've got from the cursor back into the table (changing the emp_id of course) - if that works the problem is not the cursor.
Basically if one NVARCHAR displays without needing to_char then they all should. If that's not the case then either you've got some obscure oracle bug - in which case metalink is your best bet, or there's some other code/item/property/trigger that you haven't shown us that's interferring with what you are trying to do. (or you're not querying the data you think you are querying)
|
|
|