Home » Developer & Programmer » Forms » Limitation of NVARCHR2 in Cursor (D2k 10g Rel 2)
Limitation of NVARCHR2 in Cursor [message #385414] Mon, 09 February 2009 08:35 Go to next message
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 #385426 is a reply to message #385414] Mon, 09 February 2009 09:48 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
what are you fetching the cursor into?
Re: Limitation of NVARCHR2 in Cursor [message #385489 is a reply to message #385426] Mon, 09 February 2009 22:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #385512 is a reply to message #385414] Mon, 09 February 2009 23:45 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Why are you running your own cursor? What is wrong with the default behaviour of Oracle Forms?

David
Re: Limitation of NVARCHR2 in Cursor [message #385515 is a reply to message #385503] Tue, 10 February 2009 00:01 Go to previous messageGo to next message
jaymala_k
Messages: 19
Registered: May 2008
Location: India
Junior Member
Hi Nikhil,

In actual code, loop is used, but still it giving problem.


Regards,

Jaymala
Re: Limitation of NVARCHR2 in Cursor [message #385517 is a reply to message #385515] Tue, 10 February 2009 00:07 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please answer the two questions in my previous post.

David
Re: Limitation of NVARCHR2 in Cursor [message #385519 is a reply to message #385512] Tue, 10 February 2009 00:11 Go to previous messageGo to next message
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 #385521 is a reply to message #385519] Tue, 10 February 2009 00:19 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What is 'pr_alprop'?

David
Re: Limitation of NVARCHR2 in Cursor [message #385522 is a reply to message #385521] Tue, 10 February 2009 00:26 Go to previous messageGo to next message
jaymala_k
Messages: 19
Registered: May 2008
Location: India
Junior Member


It is a internal procedure for using Alerts. This i used just to display field's values.

Regards
Re: Limitation of NVARCHR2 in Cursor [message #385524 is a reply to message #385522] Tue, 10 February 2009 00:33 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please confirm that if there are two records in the cursor that three 'pr_alprop' calls display the first record both times.

David
Re: Limitation of NVARCHR2 in Cursor [message #385525 is a reply to message #385524] Tue, 10 February 2009 00:43 Go to previous messageGo to next message
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 #385526 is a reply to message #385525] Tue, 10 February 2009 00:46 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What happens if you use a 'message;pause;' pair to display the information?

David
Re: Limitation of NVARCHR2 in Cursor [message #385541 is a reply to message #385526] Tue, 10 February 2009 01:27 Go to previous messageGo to next message
jaymala_k
Messages: 19
Registered: May 2008
Location: India
Junior Member
Same result with message also.
Re: Limitation of NVARCHR2 in Cursor [message #385544 is a reply to message #385541] Tue, 10 February 2009 01:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
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 #385651 is a reply to message #385414] Tue, 10 February 2009 07:15 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what is the code you are using to display the values?
Re: Limitation of NVARCHR2 in Cursor [message #385654 is a reply to message #385651] Tue, 10 February 2009 07:24 Go to previous messageGo to next message
jaymala_k
Messages: 19
Registered: May 2008
Location: India
Junior Member
I tried with message and also assigned the values to block items on the form.
Re: Limitation of NVARCHR2 in Cursor [message #385666 is a reply to message #385414] Tue, 10 February 2009 08:12 Go to previous message
cookiemonster
Messages: 12403
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)
Previous Topic: how to load the data from Excel to the Oracle database
Next Topic: enter_query when clicking on image (and not a button)
Goto Forum:
  


Current Time: Sat Dec 03 17:55:11 CST 2016

Total time taken to generate the page: 0.11669 seconds