OraFAQ Forum: Forms » Populate Record in List Item

Home » Developer & Programmer » Forms » Populate Record in List Item (Forms 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Populate Record in List Item [message #469918]

Fri, 06 August 2010 06:15

allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan

Senior Member

Hi all gurus...

i want to popultate record in list item....
and write the follwing code...

PROCEDURE POPULATE_REC_IN_COMBO_BOX IS
	RG_NAME VARCHAR2(500) :='RGGROUP';
	RG_ID RECORDGROUP;
	ERRCODE NUMBER;

BEGIN

 rg_id := Find_Group( rg_name ); 
IF ID_NULL(RG_ID) THEN
	RG_ID := CREATE_GROUP_FROM_QUERY(RG_NAME,'SELECT DISTINCT TO_CHAR(WORK_DATE,''''RRRR'''') 
	SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC');
END IF;

ERRCODE := POPULATE_GROUP(RG_ID);

Populate_List('SAL_YEAR', RG_ID);

 
END;

what is the problem in this code....

when i call this code then it returns the 2 errors:

1. FRM-41072: Cannot create Group RGGROUP.
2. FRM-41076: Error populating Group.

Report message to a moderator

Re: Populate Record in List Item [message #469921 is a reply to message #469918]

Fri, 06 August 2010 07:10

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

List item record groups have two columns - one for the value displayed on screen one for the value stored.
Your query only selects one column.

Report message to a moderator

Re: Populate Record in List Item [message #469923 is a reply to message #469918]

Fri, 06 August 2010 07:27

allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan

Senior Member

THANKS cookiemonster for your reply...

old code

RG_ID := CREATE_GROUP_FROM_QUERY(RG_NAME,'SELECT DISTINCT TO_CHAR(WORK_DATE,''''RRRR'''') 
	SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC');

YOUR MEAN THAT THIS...

RG_ID := CREATE_GROUP_FROM_QUERY(RG_NAME,'SELECT DISTINCT TO_CHAR(WORK_DATE,''''RRRR'''') ,TO_CHAR(WORK_DATE,''''RRRR'''')
	SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC');

THIS CODE ALSO NOT WORKING... SAME ERROR COMES...

Report message to a moderator

Re: Populate Record in List Item [message #469924 is a reply to message #469923]

Fri, 06 August 2010 07:30

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

First rule of debugging dynamic sql (and this is dynamic sql):
Assign the select statement to a variable.
Display contents of variable.
See if you can run displayed statement in sqlplus.

Report message to a moderator

Re: Populate Record in List Item [message #469925 is a reply to message #469924]

Fri, 06 August 2010 07:42

allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan

Senior Member

Thanks again your quick reply...

i run this query in sqlplus it working fine..

and i assign this string to variable and after that i show that string through message...
but i can not find any mistake/problem..

sir i send you a sample form please check that...
use scott.emp table... .

Attachment: EMP_TEST.fmb
(Size: 44.00KB, Downloaded 1100 times)

[Updated on: Fri, 06 August 2010 07:43]

Report message to a moderator

Re: Populate Record in List Item [message #469926 is a reply to message #469925]

Fri, 06 August 2010 07:53

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

You did not run that query in sqlplus because if you did you would have got an error.
So presumably you ran the sql you think your string generates.
Do what I said.

Report message to a moderator

Re: Populate Record in List Item [message #469928 is a reply to message #469925]

Fri, 06 August 2010 07:55

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Why do you need those many quotes?

select 'SELECT DISTINCT TO_CHAR(WORK_DATE,''''RRRR'''') ,TO_CHAR(WORK_DATE,''''RRRR'''')
	SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC' from dual;

Try the above and cross check the sql in the output is correct or not.

By
Vamsi

Report message to a moderator

Re: Populate Record in List Item [message #469930 is a reply to message #469926]

Fri, 06 August 2010 08:04

allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan

Senior Member

Quote:

You did not run that query in sqlplus because if you did you would have got an error.

how can you "cookiemonster" say that i can not run that query..
i run that query in sqlplus its run fine..

----------------------------------------------------------------
thanks vamsi kasina

select 'SELECT DISTINCT TO_CHAR(WORK_DATE,''''RRRR'''') ,TO_CHAR(WORK_DATE,''''RRRR'''')
	SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC' from dual;

i try that but i can not find any thing/problem...
i think you want to tell me about the quotes
Quote:

''''RRRR''''

that requirement in forms 6i.. if i m not use them then i can not working...

Report message to a moderator

Re: Populate Record in List Item [message #469936 is a reply to message #469930]

Fri, 06 August 2010 08:41

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

allianz2010 wrote on Fri, 06 August 2010 14:04

Quote:
You did not run that query in sqlplus because if you did you would have got an error.

how can you "cookiemonster" say that i can not run that query..
i run that query in sqlplus its run fine..

Because I know SQL better than you and I know that the sql you've coded in your form is invalid.
The error message tells you it's invalid anyway.
And Vamsi has seen the obvious problem as well.

What exact query did you run in sqlplus?

Report message to a moderator

Re: Populate Record in List Item [message #469950 is a reply to message #469930]

Fri, 06 August 2010 09:17

allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan

Senior Member

I WRITE THIS CODE IN SQL

SELECT DISTINCT TO_CHAR(HIREDATE,'RRRR') ,TO_CHAR(HIREDATE,'RRRR') SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC

AND YOU BOTH SAYS THAT QUOTE PROBLEM

my dear.. if i write just one Quote around of Quote:

RRRR

Then it can not working in Forms.

you both see one time again that code then decided that its write or wrong...

i m working in forms not working in SQLplus ..

RG_ID := CREATE_GROUP_FROM_QUERY(RG_NAME,[color=red][size=4]'[/size][/color]SELECT DISTINCT TO_CHAR(WORK_DATE,[color=red][size=4]'[/size][/color]'''RRRR'''') 
	SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC[color=red][size=4]'[/size][/color]);

now please see one time again.. and i face that problem in Forms not in SQLplus....
i know that its not working... as it is in SQLplus....

Report message to a moderator

Re: Populate Record in List Item [message #469951 is a reply to message #469926]

Fri, 06 August 2010 09:26

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

The SQL you've run in sqlplus is not the same as what you have coded in your form!

So this:
cookiemonster wrote on Fri, 06 August 2010 13:53

You did not run that query in sqlplus because if you did you would have got an error.
So presumably you ran the sql you think your string generates.

Is completely true.

Now do this:
cookiemonster wrote on Fri, 06 August 2010 13:30

First rule of debugging dynamic sql (and this is dynamic sql):
Assign the select statement to a variable.
Display contents of variable.
See if you can run displayed statement in sqlplus.

And you'll see why.

Report message to a moderator

Re: Populate Record in List Item [message #470019 is a reply to message #469951]

Sat, 07 August 2010 00:09

allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan

Senior Member

ok that enough....

you would not to understand my problem... that why thank you very much... for your time... which you spend for me...

i'll see this problem.... myself or discuss with my friends/colleagues...

thanks you very much......

Report message to a moderator

Re: Populate Record in List Item [message #470020 is a reply to message #469928]

Sat, 07 August 2010 00:37

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

vamsi kasina wrote on Fri, 06 August 2010 18:25

Why do you need those many quotes?
select 'SELECT DISTINCT TO_CHAR(WORK_DATE,''''RRRR'''') ,TO_CHAR(WORK_DATE,''''RRRR'''')
	SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC' from dual;
Try the above and cross check the sql in the output is correct or not.

allianz2010

you would not to understand my problem

You didn't get what I'm pointing out.
When I say "cross check the sql in the output" means:

SQL> select 'SELECT DISTINCT TO_CHAR(WORK_DATE,''''RRRR'''') ,TO_CHAR(WORK_DATE,''''RRRR'''')
  2   SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC' from dual;

'SELECTDISTINCTTO_CHAR(WORK_DATE,''''RRRR''''),TO_CHAR(WORK_DATE,''''RRRR'''')SA
--------------------------------------------------------------------------------
SELECT DISTINCT TO_CHAR(WORK_DATE,''RRRR'') ,TO_CHAR(WORK_DATE,''RRRR'')
 SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC

Get the output and run it again.

SQL> SELECT DISTINCT TO_CHAR(WORK_DATE,''RRRR'') ,TO_CHAR(WORK_DATE,''RRRR'')
  2   SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC;
SELECT DISTINCT TO_CHAR(WORK_DATE,''RRRR'') ,TO_CHAR(WORK_DATE,''RRRR'')
                                    *
ERROR at line 1:
ORA-00907: missing right parenthesis

Your requirement is to pass the following to CREATE_GROUP_FROM_QUERY.

SELECT DISTINCT TO_CHAR(WORK_DATE,'RRRR') ,TO_CHAR(WORK_DATE,'RRRR')
SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC;

So, you need to pass

'SELECT DISTINCT TO_CHAR(WORK_DATE,''RRRR'') ,TO_CHAR(WORK_DATE,''RRRR'')
 SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC'

Basically, what we both wanted to tell you is use '' instead of ''''. Hope it is clear now.

By
Vamsi

[Updated on: Sat, 07 August 2010 00:40]

Report message to a moderator

Re: Populate Record in List Item [message #470035 is a reply to message #470020]

Sat, 07 August 2010 06:32

allianz2010
Messages: 101
Registered: October 2009
Location: Pakistan

Senior Member

hi again Vamsi...

Your requirement is to pass the following to CREATE_GROUP_FROM_QUERY.
 
SELECT DISTINCT TO_CHAR(WORK_DATE,'RRRR') ,TO_CHAR(WORK_DATE,'RRRR')
SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC;

So, you need to pass
 
'SELECT DISTINCT TO_CHAR(WORK_DATE,''RRRR'') ,TO_CHAR(WORK_DATE,''RRRR'')
 SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC'

i say in my previous post that i use this query in Forms not in SQLplus...

i send you forms which i make with Scott.emp table... please see just one time.. carefully.... and then tell me.. that what can i do now...

EDIT by VK: Removed the attachment, as it is fmx (NOT the fmb).

[Updated on: Sat, 07 August 2010 07:23] by Moderator

Report message to a moderator

Re: Populate Record in List Item [message #470037 is a reply to message #470035]

Sat, 07 August 2010 07:17

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

You have uploaded fmx.
Anyway I checked the fmb in the previous post (5th one from top).
That contains

RG_ID := CREATE_GROUP_FROM_QUERY(RG_NAME,'SELECT DISTINCT TO_CHAR(HIREDATE,''''RRRR'''') ,TO_CHAR(HIREDATE,''''RRRR'''') SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC');

If your newly uploaded form also having the same, then read the following. Otherwise post the code here (no need to upload the fmb).Quote:

i say in my previous post that i use this query in Forms not in SQLplus...

Whether it is Forms / Reports / SQL*Plus / etc, you are writing SQL or PL/SQL.
It doesn't matter in your case. But it does matters in version differences, which Forms / Reports uses.

Keep one thing in mind.
CREATE_GROUP_FROM_QUERY takes the sql in varchar2.
That's why I've taken select '.....' from dual as the example
and showing you that how the sql in the parameter will be interpreted and passed to the function CREATE_GROUP_FROM_QUERY.
If you are still using the above code, you need to use '' instead of ''''.
Which means, you need to code in Form as the following.

RG_ID := CREATE_GROUP_FROM_QUERY(RG_NAME,'SELECT DISTINCT TO_CHAR(HIREDATE,''RRRR'') ,TO_CHAR(HIREDATE,''RRRR'') SAL_YEAR FROM V_DAILY_WORK ORDER BY SAL_YEAR  DESC');

By
Vamsi

Report message to a moderator

Re: Populate Record in List Item [message #470689 is a reply to message #470037]

Tue, 10 August 2010 08:06

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

So have your friends/colleagues told you that you have too many quotes in your string,
or has Vamsi's last post convinced you that we really do understand the problem?

Report message to a moderator

Re: Populate Record in List Item [message #470691 is a reply to message #470689]

Tue, 10 August 2010 08:14

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Report message to a moderator

Previous Topic:	possible to check item_instance_property?
Next Topic:	Read data from excel and store it in blob

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 12:53:31 CDT 2024