Re: DBMS_SQL problem

Hi,

Thanks for your followup. I have solved the problem by using the method recommended in Thomas Kyte's followup message in comp.databases.oracle.server
The FORM45 really has some problem referring to the built-in package's specification variable as in SQL*PLUS I can compile the code successfully.

Regards,
Zhao Fu

Kurt Laugesen (kul_at_post3.tele.dk) wrote:
: This is a Forms 4.5 - problem. I have never been able to use DBMS_SQL.V7 as
: a parameter. The only workabout I've found is to use the actual value - I
: don't remember offhand whether it is 0, 1 or 2, but you can see it in the
: package specification of the DBMS_SQL -package.
: Regards
: Kurt Laugesen
: kurtL_at_corebit.dk

: Zhao Fu wrote in message <7li6ov$e1e$1_at_nuscc.nus.edu.sg>...
: >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 Sat Jul 03 1999 - 08:59:08 CDT