Re: DBMS_SQL problem

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 Fri Jul 02 1999 - 07:27:08 CDT