Re: Sql Query as an Item formula

Thanks Frank,
[Quoted] After a bit of frustration in Forms, I think I've got it working now, although not directly in Forms, I don't think Forms supports Execute Immediate (no underscore). I finally put the Function onto the Server Side, [Quoted] and then Execute Immediate works, (why is that? can forms not compile the Dynamic sql?).
Thanks again Frank, I'm brimming with applications for this technique.

--Aaron V

"Frank" <fbortel_at_nescape.net> wrote in message news:bqpg3k$j3a$1_at_news4.tilbu1.nb.home.nl...
> Aaron V wrote:
>
> > Hello all,
> >
> > I'm trying to improve/simplify a function I wrote to use a generic SQL
> > statement to Use as a Formula calculation for an Item.
> > Example:
> > Item Formula = sql_query('SELECT last_name FROM emp_table WHERE emp_no =
> > '''||:EMP_NO'''')
> >
> > I have an extra Dummy table I'd rather be rid of...
> > BTW Forms6i, Oracle9i
> > Here's what I have so far.
> >
> > FUNCTION sql_query(select_query IN VARCHAR2)
> > RETURN VARCHAR2 IS
> > sql_statement VARCHAR2(2000);
> > result VARCHAR2(200);
> > BEGIN
> > -- clear all entries from Dummy table --
> > DELETE FROM dummy_table;
> >
> > -- built Dynamic Select Statement --
> > sql_statement := 'INSERT INTO dummy_table ('||select_query||')';
> >
> > -- Issue Statement --
> > FORMS_DDL(sql_statement);
> >
> > -- Assign Local Variable --
> > SELECT dummy_val INTO result FROM dummy_table;
> >
> > RETURN result;
> > EXCEPTION
> > WHEN NO_DATA_FOUND THEN
> > RETURN NULL;
> > END;
> >
> > Any Help would be great, Thanks
> >
> > -- Aaron V
> >
> >
>
> execute_immediate('select :fld from :tab ')
> into p_result
> using p_field, p_table;
>
> or even
> execute_immediate(l_stmt)
> into p_result
> using p_field, p_table;
> --
> Regards, Frank van Bortel
>
Received on Fri Dec 05 2003 - 23:40:41 CET