Re: Sql Query as an Item formula

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