Re: DBMS_SQL problem

Zhao, you did not state the version of Oracle that you are running. I would recommend that you use the 'dbms_sql.native’ on the dbms_sql.parse comand. The v7 may not be valid if you are running Oracle8. The syntax of the command looks correct to me.

Your second problem with the define column is a syntax problem. For varchar columns, you need to have a length parm after the column name: DBMS_SQL.DEFINE_COLUMN(cur,1,v_column_name,_n_);

HTH
    James

In article <7li6ov$e1e$1_at_nuscc.nus.edu.sg>,   scip6125_at_leonis.nus.edu.sg (Zhao Fu) wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 02 1999 - 11:02:55 CDT