Re: DBMS_SQL problem

For those who are still interested in this problem, I post my feedback here:

I have solved the problem by using the method of creating a RECORD_GROUP as Thomas Kyte suggested. The FORM45 really has some problem referring to the built-in package's specification variable as I later tried compiling the code in SQL*PLUS at the server side, the compilation was successful.

Regards,
Zhao Fu

Thomas Kyte (tkyte_at_us.oracle.com) wrote:
: A copy of this was sent to scip6125_at_leonis.nus.edu.sg (Zhao Fu)
: (if that email address didn't require changing)
: On 2 Jul 1999 11:09:17 GMT, you wrote:

: in some versions of forms, it has problems referring to server side plsql
: variables in package specs. thats the cause of this. The solutions is don't use
: dbms_sql. its somewhat chatty across the network. it works great in a stored
: procedure but from a client application, its very chatty. instead, since you
: are in forms, use CREATE_GROUP_FROM_QUERY. That is very efficient and array
: fetches the data (something you won't be doing with dbms_sql from forms). This
: is the best approach in most cases.

: >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
: >

: --
: See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
: Current article is "Part I of V, Autonomous Transactions" updated June 21'st
:
: Thomas Kyte tkyte_at_us.oracle.com
: Oracle Service Industries Reston, VA USA

: Opinions are mine and do not necessarily reflect those of Oracle Corporation

-- Received on Sat Jul 03 1999 - 09:11:49 CDT