Oracle Forms 6i trying to read a list of numbers separated by a comma

5 pts.
Tags:
ORA-01722
Oracle error messages
Oracle Forms 6i
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
ASKED: March 18, 2009  11:34 AM
UPDATED: March 18, 2009  8:14 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following