Oracle Forms 6i trying to read a list of numbers separated by a comma
Hello,
I have an Oracle Forms 6i form with a text field that I want users to be able to put in a list of numbers seperated by commas, eg 12345,67890,34567 etc.
From this list, I want to be able to use the list of numbers in an IN statement such as:
CURSOR c_policy IS
SELECT pe.cust_no,pe.policy_no
FROM POLICY_ENTITY pe
WHERE policy_no IN (:header.policy_no);
where :header.policy_no is my text field and policy_no is defined as a number.
When trying to run the form I get an ORA-01722 error due to the list not being recognised as a number. Is there an easy way around this?
Thanks in advance
Software/Hardware used:
Software/Hardware used:
ASKED:
March 18, 2009 11:34 AM
UPDATED:
March 18, 2009 8:14 PM
Answer Wiki:
I guess you will need to use dynamic SQL to achieve that (but it will require more lines of code).
If you want to do it in forms, you should use the dbms_sql package. If in a stored procedure, you could use native dynamic SQL (execute immediate).
Here's an example of the use of dbms_sql:
<pre>DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
name varchar2(10);
salary int;
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT ename, sal FROM emp WHERE
job = :jobname';
dbms_sql.parse(cur_hdl, stmt_str, dbms_
sql.native);
-- supply binds (bind by name)
dbms_sql.bind_variable(
cur_hdl, 'jobname', 'SALESMAN');
-- describe defines
dbms_sql.define_column(cur_hdl, 1, name, 200);
dbms_sql.define_column(cur_hdl, 2, salary);
rows_processed := dbms_sql.execute(cur_hdl); --
execute
LOOP
-- fetch a row
IF dbms_sql.fetch_rows(cur_hdl) > 0 then
-- fetch columns from the row
dbms_sql.column_value(cur_hdl, 1, name);
dbms_sql.column_value(cur_hdl, 2, salary);
-- <process data>
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.close_cursor(cur_hdl); -- close cursor
END;</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
