Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Passing a string to SQL via an Item
Passing a string to SQL via an Item [message #169433] Wed, 26 April 2006 16:55 Go to next message
tombert
Messages: 1
Registered: April 2006
Location: San Diego
Junior Member
I have a report in a region that I want to pass a string to the where clause

SELECT unit_id, name
FROM unit
WHERE node_key in (:P61_NODE_KEY)

Where P61_NODE_KEY would be something like 1,2,4 but it gets 'ORA-01722: invalid number'. It works if I just past one number.

Is there a way around this? A better way of doing it?

Basically I have another report with checkboxes on the same page. The user can select one or multiple boxes then press a button. A Page Processing Process parses the htmldb_application.g_f01 and adds commas if necessary and puts the result in P61_NODE_KEY. So that the "unit" report updates and displays only the rows that match the node_key from the first table/report.

Thanks

Tom


icon14.gif  Re: Passing a string to SQL via an Item [message #170092 is a reply to message #169433] Tue, 02 May 2006 02:02 Go to previous message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Yea, there is a better solution for ur problem.
better u use lexical variables insted of bind variable

like
SELECT unit_id, name
FROM unit
&P61_NODE_KEY

and pass the where condition on the parameter
P61_NODE_KEY := 'WHERE node_key in (1,2,3)' ....
It will solve ur problem.

Cheers
Sanka
Previous Topic: Problem with translation
Next Topic: no set up option??
Goto Forum:
  


Current Time: Thu Mar 28 05:08:54 CDT 2024