Home » Developer & Programmer » Forms » Cursor in program unit (Developer 10g)
Cursor in program unit [message #416022] Thu, 30 July 2009 03:35 Go to next message
I_AM_TOO
Messages: 37
Registered: July 2009
Location: cc
Member

hi,

PROCEDURE POPULATELIST show results without clause parameter
is there any problem in code any suggestion
PROCEDURE POPULATELIST(CLause in varchar2) IS
	CURSOR CURACC(z  varchar2) IS SELECT TBLACCOUNT.ID,TBLACCOUNT.ID,TBLACCOUNT.DESCRIPTION,TBLACCOUNT.STATUS 
	FROM SCC.TBLACCOUNT where ID > z;
BEGIN
	GO_BLOCK('ACCTYPEVW');
	CLEAR_BLOCK;
	FIRST_RECORD;
	OPEN CURACC(Clause);
LOOP
	IF :ACCTYPEVW.ACCID IS NOT NULL THEN
  	NEXT_RECORD;
  	END IF;

FETCH CURACC INTO  :ACCTYPEVW.ACCID,:ACCTYPEVW.N4,:ACCTYPEVW.ACCDESC,:ACCTYPEVW.STATUS;
EXIT WHEN CURACC%NOTFOUND;

--RETURN TEXT FOR GROUP OF DETAIL
IF :ACCTYPEVW.STATUS=15 THEN 
	 :ACCTYPEVW.STATUS:='Group'; 
ELSIF :ACCTYPEVW.STATUS=16 THEN
	:ACCTYPEVW.STATUS:='Detail'; 
END IF;
--HIGHT LIGHT GROUP FIELDS
   Declare
     v_curr_rec PLS_INTEGER := get_block_property('ACCTYPEVW',CURRENT_RECORD);
   Begin
       IF :ACCTYPEVW.STATUS='Group' THEN 
       set_block_color('ACCTYPEVW', v_curr_rec, 'BOLD');
       END IF;	
    END;
END LOOP;
GO_BLOCK('ACCTYPEVW');
CLOSE CURACC;
exception when others then 
	message(dbms_error_text);
END;


Calling procedure
POPULATELIST(' 0 and ID ='|| :Search_blk.search||' Group by ID' );
Re: Cursor in program unit [message #416028 is a reply to message #416022] Thu, 30 July 2009 04:03 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't use dynamic SQL in an explicit cursor.
Re: Cursor in program unit [message #416030 is a reply to message #416022] Thu, 30 July 2009 04:15 Go to previous messageGo to next message
I_AM_TOO
Messages: 37
Registered: July 2009
Location: cc
Member

Thanx Allot sir

can i ask alternative way ?
Re: Cursor in program unit [message #416037 is a reply to message #416022] Thu, 30 July 2009 04:31 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You could use a ref_cursor.

But I don't think you need to use dynamic SQL at all.
Re: Cursor in program unit [message #416151 is a reply to message #416037] Thu, 30 July 2009 20:25 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What's wrong with using a standard 'record_group' in the Forms Builder?

David
Re: Cursor in program unit [message #416302 is a reply to message #416022] Fri, 31 July 2009 09:06 Go to previous messageGo to next message
I_AM_TOO
Messages: 37
Registered: July 2009
Location: cc
Member

hi,

i try this but parameter in where clause not working
CREATE OR REPLACE PROCEDURE PRO_SEARCH(CLAUSE IN VARCHAR2,C_CUR OUT SYS_CURSOR)
AS
BEGIN
OPEN C_CUR FOR 
	SELECT * FROM TBLACCOUNT
		WHERE ID > CLAUSE;
EXCEPTION WHEN OTHERS THEN
PRO_eXCEPRION(SQLCODE,SQLERRM);
END PRO_SEARCH;
/

DECLARE
 C_CUR SYS_REFCURSOR;
 R_ACC TBLACCOUNT%ROWTYPE;
BEGIN
 PRO_SEARCH(' 0 AND TO_NUMBER(ID) =1',C_CUR);
 LOOP
  FETCH C_CUR INTO R_ACC;
  EXIT WHEN C_CUR%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(R_ACC.ID||'  '||R_ACC.DESCRIPTION);
END;
/

Quote:

What's wrong with using a standard 'record_group' in the Forms Builder?


i realy dont know how to do that, record group in three text fields?
Re: Cursor in program unit [message #416425 is a reply to message #416022] Sun, 02 August 2009 05:10 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
When you use dynamic SQL you always have a build up a string of the full SQL statement you want to execute. You've only got part of the statement in a string and the rest as static SQL, which won't work.
Re: Cursor in program unit [message #416471 is a reply to message #416302] Mon, 03 August 2009 00:53 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
You can have as many fields in a 'record_group' as you wish.

David
[EDITED by DJM: fixed typo]

[Updated on: Tue, 04 August 2009 18:41]

Report message to a moderator

Re: Cursor in program unit [message #416687 is a reply to message #416022] Mon, 03 August 2009 23:59 Go to previous messageGo to next message
I_AM_TOO
Messages: 37
Registered: July 2009
Location: cc
Member

Thanx you cookiemonster

Thanx you djmartin
i googled but not get the desired results where i get the full story of "record_group"
Re: Cursor in program unit [message #416867 is a reply to message #416687] Tue, 04 August 2009 18:41 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Open Forms Builder and press F1 (help). Its all there.

David
Previous Topic: Failed to view my photo
Next Topic: Trigger to Reduce Quantity
Goto Forum:
  


Current Time: Sun Dec 04 16:31:43 CST 2016

Total time taken to generate the page: 0.15470 seconds