Home » Developer & Programmer » Forms » How to use Varchar2 Varible with Cursor Parameter
How to use Varchar2 Varible with Cursor Parameter [message #320130] Wed, 14 May 2008 02:55 Go to next message
brijal_j_patel
Messages: 28
Registered: May 2007
Location: Surat,India
Junior Member
Hello Gurus,
In my form, I have a cursor with v1(Varchar2) Parameter. This cursor use this parameter in where field1 in (v1)

Cursor c1(v1 varchar2)
is
select empid,empname
from emp
where dept in (v1);

Can anybody tell how to pass values in v1, as
I tried following
'ABC','PQR'
but it will give me no record found message. There is 3 records with dept='ABC' and 4 records with dept='PQR'. dept field is of varchar2(10).

I tried in sqlplusw and it work fine. But it is not working in Form 6i.

Please help me

Regards
Brijal

Upd mod: Text from second message which I have deleted.

[Updated on: Wed, 14 May 2008 03:09] by Moderator

Report message to a moderator

Re: How to use Varchar2 Varible with Cursor Parameter [message #320140 is a reply to message #320130] Wed, 14 May 2008 03:11 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Why are you doing this in a Form? Just base your block on 'emp'.

Pleae post all the code and use 'code' tags and NOT colour.

David
Re: How to use Varchar2 Varible with Cursor Parameter [message #320141 is a reply to message #320130] Wed, 14 May 2008 03:12 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is the varying elements in IN list problem; check what Tom Kyte has said about it, possible solutions etc.
Re: How to use Varchar2 Varible with Cursor Parameter [message #320170 is a reply to message #320140] Wed, 14 May 2008 04:17 Go to previous messageGo to next message
brijal_j_patel
Messages: 28
Registered: May 2007
Location: Surat,India
Junior Member
Martin Sir,
Floowing code is placed in cntrl block's "Search Emp" button.
declare
  cursor c1(v1 varchar2) is
    select empcode,empname 
    from emp
    where dept in (v1);
  v2 varchar2(2000);
begin
  go_block('dept');
  first_record;
  if :dept.recSel='Y' then
    v2:=''''||v1||'''';
  end if;
  loop
    next_record;
    exit when :system.last_record='TRUE';
    if :dept.recSel='Y' then
      if v2 is null then
        v2:=''''||v1||'''';
      else
        v2:=v2||','''||v1||'''';
      end if;
    end if;
  end loop;
  first_record;
  go_block('emp');
  clear_block;
  for rec in c1(v2)
  loop
    create_record;
    :emp.empname:=rec.empcode;
    :emp.empname:=rec.empname;    
  end loop;
  first_record;
end;  

Regards
Brijal
Re: How to use Varchar2 Varible with Cursor Parameter [message #320369 is a reply to message #320170] Wed, 14 May 2008 19:25 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
To display and maintain two inter-related tables, normally a simple master-detail structure is used so that Oracle Forms does most of the work for you.

In your case, using the first loop to build the list of 'department' acronyms is okay but I suggest using the information in a "set_block_property('default_where')" clause for the 'emp' block. Then going to the block and doing an 'execute_query'.

Also, when creating new code and getting strange results I suggest putting 'message;pause;' pairs into your code so that you can see what it is doing. Also, use the 'message' command to display the contents of values, for example, your 'v1' and 'v2' either as you build them or before you use them.

David
Re: How to use Varchar2 Varible with Cursor Parameter [message #320738 is a reply to message #320369] Fri, 16 May 2008 02:50 Go to previous messageGo to next message
brijal_j_patel
Messages: 28
Registered: May 2007
Location: Surat,India
Junior Member
Thanks Martin sir,
I will try this and tell you if any error come.
I have other problem also, I want to short control block as per one field. How can we do so.
Regards
Brijal
Re: How to use Varchar2 Varible with Cursor Parameter [message #320756 is a reply to message #320738] Fri, 16 May 2008 03:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I want to short control block as per one field

Remove any item you don't need; or, alternatively, hide it.
Re: How to use Varchar2 Varible with Cursor Parameter [message #321064 is a reply to message #320738] Sun, 18 May 2008 21:24 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please expand on what you mean by "short control block".

David
Previous Topic: Mohankumar : read_image_file
Next Topic: form changed
Goto Forum:
  


Current Time: Sat Dec 10 20:18:08 CST 2016

Total time taken to generate the page: 0.12535 seconds