Home » Developer & Programmer » Forms » fetch whole records
fetch whole records [message #274777] Wed, 17 October 2007 02:10 Go to next message
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 #274781 is a reply to message #274777] Wed, 17 October 2007 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: fetch whole records [message #274783 is a reply to message #274777] Wed, 17 October 2007 02:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #274800 is a reply to message #274798] Wed, 17 October 2007 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Returns the cursor to your program and fetch it inside the program.

Regards
Michel
Re: fetch whole records [message #274803 is a reply to message #274798] Wed, 17 October 2007 03:18 Go to previous messageGo to next message
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 #274805 is a reply to message #274800] Wed, 17 October 2007 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe the main problem is here:
Quoted and modified a little bit because punctuation exists. Use it!

I'm outputting the tnc.contname on the form data records into block tnc. The problem is that I have the output only of the last fetched record because it keeps writing on the previous results. How to get all the output?


You are trying to populate a form block, right? As the focus is always on the first record, every new cursor value overwrites a previous one. What you miss is the NEXT_RECORD built-in. Something like this example based on Scott's DEPT table:
begin
  for cur_r in (select deptno, dname, loc from dept)
  loop
    :dept_test.deptno := cur_r.deptno;
    :dept_test.dname := cur_r.dname;
    :dept_test.loc := cur_r.loc;
		
    next_record;            --> this is what you miss!
  end loop;
end;
Re: fetch whole records [message #274824 is a reply to message #274805] Wed, 17 October 2007 06:06 Go to previous messageGo to next message
kuwait
Messages: 55
Registered: October 2007
Member
Thanks all,

I've solved the problem without using a cursor.
I appreciate all of ur responses.

Regards
Abrar
Re: fetch whole records [message #275240 is a reply to message #274777] Thu, 18 October 2007 22:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
hey, how bout letting the rest of us know what you did as your final solution? I am still trying to figure out what you were trying to do?

1) load a set of form records (solution being as was suggested, use next_record to navigate your form records as you read your cursor records)

2) load a single column with all rows from the cursor? (in which case, maybe you should try a database object collection type, and whole object assignment)

3) something else? (I still don't comprehend your problem or its solution).

As a follower of the thread, I sure would like to know what you landed on.

good luck, Kevin
Re: fetch whole records [message #275242 is a reply to message #275240] Thu, 18 October 2007 23:00 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I believe that the poster has found that if he removes ALL of his code and uses the standard features of Oracle Forms that everything is done for him.

David
Re: fetch whole records [message #275678 is a reply to message #275242] Mon, 22 October 2007 05:39 Go to previous messageGo to next message
kuwait
Messages: 55
Registered: October 2007
Member
I've simply used that statement:

:tnc.contname := :cb.srchf||'%'; 


but it will be case sensitive

Thanks all

[Updated on: Mon, 22 October 2007 05:39]

Report message to a moderator

Re: fetch whole records [message #275681 is a reply to message #275678] Mon, 22 October 2007 06:03 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you don't want it to be case sensitive, you might try with something like
UPPER(:tnc.contname) := UPPER(:CB.SRCHF) || '%';
Re: fetch whole records [message #275685 is a reply to message #274777] Mon, 22 October 2007 06:09 Go to previous messageGo to next message
kuwait
Messages: 55
Registered: October 2007
Member
This is cannot be done, I've tried it before it is not allowed since it's an assignment

Thanks
Re: fetch whole records [message #275701 is a reply to message #275685] Mon, 22 October 2007 07:57 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, right ... stupid me. But
:tnc.contname := UPPER(:CB.SRCHF) || '%';
might do something useful (all 'contnames' would be in uppercase).
Re: fetch whole records [message #275709 is a reply to message #275701] Mon, 22 October 2007 08:19 Go to previous messageGo to next message
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 #275791 is a reply to message #275709] Mon, 22 October 2007 12:52 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
True. An option (if it is an option) would be updating the whole table and putting all existing records to uppercase (or lowercase).

There are ways to create case insensitive queries; they were discussed on OraFAQ forums so, if you are interested in this feature, search the board. Perhaps you'll get an idea how to improve your code.
Re: fetch whole records [message #275845 is a reply to message #275685] Tue, 23 October 2007 01:04 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I don't agree with your statement "This is cannot be done, I've tried it before it is not allowed since it's an assignment".

Please post more of the code, ":tnc.contname := :cb.srchf||'%'; " is not sufficient for me to see what you are trying to do.

David
Re: fetch whole records [message #275898 is a reply to message #275845] Tue, 23 October 2007 04:48 Go to previous messageGo to next message
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

Re: fetch whole records [message #276077 is a reply to message #275898] Tue, 23 October 2007 19:33 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Why don't you use the default_where statement to modify your selection criteria? Then you WILL be able to use "UPPER(:tnc.contname) := UPPER(:CB.SRCHF) || '%';"

David
Re: fetch whole records [message #276226 is a reply to message #276077] Wed, 24 October 2007 07:29 Go to previous message
kuwait
Messages: 55
Registered: October 2007
Member
Hi,

Thanks djmartin I'll try that
Previous Topic: how to manage many list items in forms 6i?
Next Topic: Help For TNS
Goto Forum:
  


Current Time: Sat Nov 09 17:34:52 CST 2024