DBMS_SQL problem

Hi,

I am writing a button trigger in Form45 using DBMS_SQL package to execute a dynamic SQL query. The query is like:

select v_column_name from v_table_name; where v_column_name and v_table_name are form variables.

I suppose the value retrieved from v_column_name is varchar2(n),

It seems that I can do it in this way:

declare
v_column_name varchar2(30);

...

DBMS_SQL.PARSE(cur,'select '||v_column_name||' from '||v_table_name,
DBMS_SQL.V7)
DBMS_SQL.DEFINE_COLUMN(cur,1,v_column_name);

I met some strange problems while I was trying to compile the code:

DBMS_SQL.PARSE(cur,'select '||v_column_name||' from '||v_table_name, DBMS_SQL.V7)
--error1: component 'V7' must be declared

DBMS_SQL.DEFINE_COLUMN(cur,1,v_column_name);
--error2: too many declaration of 'DEFINE_COLUN' match this call

For error1, I enclose DBMS_SQL.V7 in the quotation mark as 'DBMS_SQL.V7', or replace it using a number e.g. 1, the error disappear.

For error2, I re-define the v_column_name with DATE or NUMBER type, the error also disappear. But for the VARCHAR2 type, I cannot find the solution. I tried to use DBMS_SQL.DEFINE_COLUMN_CHAR(cur,1,v_column_name) as some sample code teach me, but my compiler shows the error msg: component 'DEFINE_COLUMN_CHAR' must be declared.

Would anyone point out my problems in doing this task?

Thank you in advance.

Zhao Fu,
National University of Singapore Received on Fri Jul 02 1999 - 06:11:27 CDT