Re: DBMS_SQL problem
Date: Sat, 03 Jul 1999 10:24:33 GMT
Message-ID: <7lkocv$46f$1_at_nnrp1.deja.com>
Hi there!
You can't get packaged variables that way.
So, you have to define your own function in the database.
For example FUNCTION Get_V RETURN Number IS
BEGIN
Return DBMS_SQL.Native;
END;
That's the most clean way to solve this task. No one could guarantee that those variables will not be changed in the next releases.
Sincerely yours, Ilya.
In article <7lj9jv$moj$1_at_nnrp1.deja.com>,
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.
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Sat Jul 03 1999 - 12:24:33 CEST