Re: DBMS_SQL problem

From: Zhao Fu <scip6125_at_leonis.nus.edu.sg>
Date: 3 Jul 1999 13:58:36 GMT
Message-ID: <7ll4uc$svi$1_at_nuscc.nus.edu.sg>


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

whodo1_at_my-deja.com wrote:
: 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
: 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 Sat Jul 03 1999 - 15:58:36 CEST

Original text of this message