Home » Developer & Programmer » Forms » help in this Procedure Parameters (dynamic select statment)
help in this Procedure Parameters [message #389568] Mon, 02 March 2009 12:19 Go to next message
amr_wafa
Messages: 9
Registered: January 2009
Junior Member
hello all..check this procedure
PROCEDURE QUERY1(c1 VARCHAR2,s1 VARCHAR2) IS
CURSOR emp_curs IS SELECT * FROM emp WHERE c1 LIKE '%' || s1 || '%';
BEGIN
	
	GO_BLOCK('EMP');
	CLEAR_BLOCK(NO_COMMIT);
	FIRST_RECORD;
	
	FOR i IN emp_curs LOOP
		:empno := i.empno;
		:ename := i.ename;
		:job := i.job;
		:mgr := i.mgr;
		:sal := i.sal;
		:comm := NVL(i.comm,0);
		:emp.deptno := i.deptno;
		DOWN;
	END LOOP;
	
  
END;

and in a when-button-pressed trigger :
query1('ename',:txtitem_name)

its not working i dont know why ...i think you know what i want to do now....so is this possible or theres another way to do this....
thanks all..

[Updated on: Mon, 02 March 2009 12:46]

Report message to a moderator

Re: help in this Procedure Parameters [message #389590 is a reply to message #389568] Mon, 02 March 2009 18:26 Go to previous messageGo to next message
amr_wafa
Messages: 9
Registered: January 2009
Junior Member
by the way..i user Forms 6i under windows xp sp3 pro....and it only works when i remove the c1 parameter...the problem is in the column name....

any 1 ??

[Updated on: Mon, 02 March 2009 18:32]

Report message to a moderator

Re: help in this Procedure Parameters [message #389594 is a reply to message #389590] Mon, 02 March 2009 19:54 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

somehow a reserved name. have you tried changing c1 into another variable name?
Re: help in this Procedure Parameters [message #389597 is a reply to message #389568] Mon, 02 March 2009 20:20 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
amr_wafa wrote on Mon, 02 March 2009 19:19
its not working i dont know why ...

"Is not working" is not Oracle error message nor reasonable description of any possible problem. Please be more specific.
amr_wafa wrote on Mon, 02 March 2009 19:19
i think you know what i want to do now....so is this possible or theres another way to do this....

To do what? Compare two string parameters (not depending on table content) in LOOP? I see no for doing that; but, as you did not post the requirements, it is just place for guesses, what is this procedure supposed to do.
Re: help in this Procedure Parameters [message #389689 is a reply to message #389568] Tue, 03 March 2009 03:53 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're trying and failing to use dynamic SQL.
Suggest you read up on it in the documentation.
Re: help in this Procedure Parameters [message #389737 is a reply to message #389568] Tue, 03 March 2009 07:28 Go to previous messageGo to next message
amr_wafa
Messages: 9
Registered: January 2009
Junior Member
i want to use exact the same cursor in multiple forms with different column name and condition so i made that procedure.

and there are no FRM or ORCL errors occurring...but its not working right.

can someone guide me where to read about it ... i cant find a material or something..thanks
Re: help in this Procedure Parameters [message #389765 is a reply to message #389568] Tue, 03 March 2009 08:45 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
The complete oracle documentation can be found here:
http://tahiti.oracle.com/

Although I would suggest that you don't actually want to do this. Different cursors for different columns will be a lot less trouble for you in the long run.
Re: help in this Procedure Parameters [message #390087 is a reply to message #389568] Wed, 04 March 2009 22:17 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
"When in doubt, print it out." I suggest assigned the 'where' clause to a string and displaying that string via 'message' or an 'alert'.

David
Re: help in this Procedure Parameters [message #390306 is a reply to message #390087] Thu, 05 March 2009 16:59 Go to previous messageGo to next message
amr_wafa
Messages: 9
Registered: January 2009
Junior Member
thanx for this suggestion David...i did what you told me and i copy paste the sql statement in sql plus and it worked fine...

can you give me any suggestions or coding that performs the same action i want to do..

thanks

[Updated on: Thu, 05 March 2009 17:00]

Report message to a moderator

Re: help in this Procedure Parameters [message #393921 is a reply to message #390306] Tue, 24 March 2009 23:48 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Re: help in this Procedure Parameters [message #394142 is a reply to message #389568] Wed, 25 March 2009 16:14 Go to previous messageGo to next message
amr_wafa
Messages: 9
Registered: January 2009
Junior Member
unfortunatly no ... im still waiting for any suggestions...
i read about Dynamic SQL ... but it didnt help me in that situation...or i didnt understand it right

[Updated on: Wed, 25 March 2009 16:17]

Report message to a moderator

Re: help in this Procedure Parameters [message #394186 is a reply to message #389568] Thu, 26 March 2009 00:29 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As far as I know, dynamic cursor is possible to use only in PL/SQL procedure, not the Forms one. You can create PL/SQL procedure inside DB which returns the cursor based on dynamic query.

But, as you already fetch columns inside the FOR LOOP statically, it is questionable why the compared column name shall be "dynamic" and (as all compared columns are already known) you do not use statical comparison, something like
CASE LOWER(c1)
 WHEN 'ename' THEN ename
 WHEN 'job' THEN job
 <etc. for all possible c1 values>
END LIKE '%' || s1 || '%'
(not sure about correct Forms syntax).

Even if you would like to compare non-string (DATE, NUMBER) columns, you could convert its values into string explicitly in this static version and do not rely on implicit conversion.
Re: help in this Procedure Parameters [message #394247 is a reply to message #389568] Thu, 26 March 2009 05:23 Go to previous message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
I think you may need to use DBMS_SQL for dynamic SQL in Forms.

Rajy
Previous Topic: Order of execution have problem?
Next Topic: Wrong number or type of arugment call in funciton
Goto Forum:
  


Current Time: Fri Dec 02 16:57:12 CST 2016

Total time taken to generate the page: 0.11009 seconds