Re: DBMS_SQL problem

[Quoted] In article <7lgrct$6nl$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
>
>
Oralce forms can not read package global variables. DMBS_SQL.NATIVE is [Quoted] a package global variable. However, DBMS_SQL.NAVTIVE is just a variable that points to a constant integer (I think it's 1). You can look in the DBMS_SQL package spec to find out which integer DBMS_SQL.NATIVE represents. Once you find out the integer you can put that in pace of the DBMS_SQL.NATIVE (DBMS_SQL.PARSE(cur_id,sql_stmt,1)).

Sean Kelleher
The Burgundy Group

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 02 1999 - 23:06:15 CEST