fetch whole records [message #274777] |
Wed, 17 October 2007 02:10 |
kuwait
Messages: 55 Registered: October 2007
|
Member |
|
|
hello,
I have a problem I want to get the whole results from cursor c1 into (tnc.contname)
Here is my code:
-----------------------------------------------------------
declare
CURSOR c1
is
select a.contname
from ads.tnc a
where upper(a.contname) like upper(:cb.srchf)||'%';
begin
OPEN c1;
LOOP
FETCH c1 INTO tnc.contname;
EXIT WHEN c1%NOTFOUND;
end loop;
close c1;
end;
-----------------------------------------------------------------
I'm trying to create database info.sys. using oracle form builder
tnc.contname is a text item in the data block tnc it's referring to a database field contname in tnc table defined in ads db
I'm outputting the tnc.contname on the form data records into block tnc, the problem is i have the output only of the last fetched records because it keeps writing on the previous results how to get all the output?
the first portion (declaration) of the code is working right I tried it on sql+
-----------------------------------------------------------------
declare
CURSOR c1
is
select a.contname
from ads.tnc a
where upper(a.contname) like upper(:cb.srchf)||'%';
----------------------------------------------------
I think the change should be in the 2nd part of the code
Please, any idea?thx
[Updated on: Wed, 17 October 2007 02:45] Report message to a moderator
|
|
|
|
Re: fetch whole records [message #274783 is a reply to message #274777] |
Wed, 17 October 2007 02:29 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm trying to create database info.sys. using oracle form builder
Hmmm.... Then you should probably be looking at the Create Database command. But I don't think that you really mean what you said there.
TO get all of the values concatenated into a string from a cursor, you could:DECLARE
v_temp varchar2/(32767);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_temp;
EXIT WHEN c1%NOTFOUND;
tnc.contname :=tnc.contname||','||v_temp;
END LOOP;
CLOSE c1; END;
|
|
|
Re: fetch whole records [message #274798 is a reply to message #274783] |
Wed, 17 October 2007 03:06 |
kuwait
Messages: 55 Registered: October 2007
|
Member |
|
|
Thanks JRowbottom,
but it doesn't work
tnc.contname is referring to a database field and I want to retrieve all the records containing the result of c1
I just get the last records in c1
how to output the whole records in c1 to tnc.contname at once?
|
|
|
|
Re: fetch whole records [message #274803 is a reply to message #274798] |
Wed, 17 October 2007 03:18 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
No, I'm afraid that if your code worked, then mine would too. (Well, you'd have to add in the cursor definition into the DECLARE section, but other than that it should be fine)
They both populate TNC.CONTNAME.
Now, if this is a Forms problem, and TNC was the name of your Block then it might be that you need to refer to :TNC.CONTNAME
If you are actually trying to update the column CONTNAME in the table TNC, then your approach is utterly wrong. If this is the case, you need to look at the UPDATE statement.
|
|
|
|
|
|
|
|
|
|
|
Re: fetch whole records [message #275709 is a reply to message #275701] |
Mon, 22 October 2007 08:19 |
kuwait
Messages: 55 Registered: October 2007
|
Member |
|
|
It will just result the contname having upper case like what in srchf, contname with lower case won't be fetched so I think it's better to keep it case sensitive
Thanks
|
|
|
|
|
Re: fetch whole records [message #275898 is a reply to message #275845] |
Tue, 23 October 2007 04:48 |
kuwait
Messages: 55 Registered: October 2007
|
Member |
|
|
Hi, here's a portion of my code, which is written in the pre_query trigger of tnc data block:
elsif :cb.srcht = 'd' then
if :cb.srchf is not null then
:tnc.contname := :cb.srchf||'%';
:cb.srchf := '';
:cb.it :=:tnc.contname;
else
:tnc.contname := :cb.it;
end if;
- cb.srcht id a radio button
- cb.srchf is a text field
- contname is a field in table (tnc)
It's working fine just it's case sensitive, so that I first
thought of using cursor to solve that problem as in my first post.
[Updated on: Tue, 23 October 2007 04:49] Report message to a moderator
|
|
|
|
|