Home » Developer & Programmer » Forms » How to include variable on block's query
How to include variable on block's query [message #238442] Fri, 18 May 2007 01:22 Go to next message
anna marie
Messages: 26
Registered: June 2006
Location: Philippines
Junior Member
I have a block where I fetch data using Query with sub-querry on Query data source name. The problem is that I need to put a variable in a sub-query, but it returned error: ORA-01008 not all variables bound. I'm retrieving multiple records here.
SELECT DISTINCT --fields
FROM table1 a, table2 b, table3 c, table4 d 
WHERE a.line_cd = b.line_cd 
AND a.line_cd = 21
--other conditions
AND a.clm_seq_no = 7100003
AND d.eff_dt = (SELECT MAX(eff_dt)
		  FROM table4
		   WHERE 1=1
		     AND srvc_code = a.srvc_code 
		     AND line_cd = a.line_cd 
		     AND sched_no = a.sched_no 
		     AND cover_code = a.cover_code 
		     AND eff_dt <= :global.variable --problem
                ) 
Re: How to include variable on block's query [message #238455 is a reply to message #238442] Fri, 18 May 2007 02:20 Go to previous messageGo to next message
Flyhard
Messages: 21
Registered: April 2007
Location: Germany
Junior Member
Try assigning the global variable to a local one. Don't forget to convert it to the correct datatype, since the globals are always character variables
Re: How to include variable on block's query [message #238560 is a reply to message #238442] Fri, 18 May 2007 09:20 Go to previous messageGo to next message
vitas_haiyi
Messages: 36
Registered: April 2007
Member
Use global variable is a good way.
I met this problem ago,too.
I used Package as the block data source to solve this problem.
You can search example that Package as the block data source in the web.
Re: How to include variable on block's query [message #238903 is a reply to message #238442] Sun, 20 May 2007 21:37 Go to previous messageGo to next message
anna marie
Messages: 26
Registered: June 2006
Location: Philippines
Junior Member
How to do that? The only available options in Query data source type are None, Table, Procedure, transactional triggers and from clause query. I'am using table.

Flyhard, I already use package for my variable instead of global.
AND eff_dt <= variables.v_eff_dt

..now the error is ora-00904, "variables.v_ef_dt":invalid identifier.

vitas_haiyi, can you give me exactly what you did.
Re: How to include variable on block's query [message #240713 is a reply to message #238903] Fri, 25 May 2007 10:01 Go to previous messageGo to next message
vitas_haiyi
Messages: 36
Registered: April 2007
Member
Give you a example.

But my example can only query, can't update record,I post a example, you can download it if you only need query record.

I also posted this question that update record in the block that data source is package.
  • Attachment: Example.rar
    (Size: 10.07KB, Downloaded 176 times)
Re: How to include variable on block's query [message #240931 is a reply to message #238442] Sun, 27 May 2007 21:54 Go to previous messageGo to next message
anna marie
Messages: 26
Registered: June 2006
Location: Philippines
Junior Member
it's not working on my side. The problem is on the query data source arguments. the Type Name gives me an error. example, if I specify "pgk.x", it gives me pgk.x must be declared error on query-procedure trigger.
I noticed on your sample that you specify "B_PKG.BONTAB", yet ther's no BONTAB procedure on your package(I downloaded file on your other post).
Can you explain a little more about it?

[edit] - don't bother, I got it, I just forgot the SYNONYM. thanks

[Updated on: Sun, 27 May 2007 22:53]

Report message to a moderator

Re: How to include variable on block's query [message #241133 is a reply to message #240931] Mon, 28 May 2007 07:53 Go to previous message
vitas_haiyi
Messages: 36
Registered: April 2007
Member
See it:
http://sheikyerbouti.developpez.com/tutoforms10g/tutoforms10g.htm

In 2.3.2 Block based on stored procedures

His example much more detail than mine,and he have two way, but please note this,in his example,the first way,the 'DML Data Target Type' of block must set Transactional Triggers, and if you need complex query, you can use the function 'SetV' in my example.

[Updated on: Mon, 28 May 2007 08:02]

Report message to a moderator

Previous Topic: When tree node selected trigger
Next Topic: connect to different database
Goto Forum:
  


Current Time: Fri Dec 09 15:48:35 CST 2016

Total time taken to generate the page: 0.11185 seconds