Re: Sql Query as an Item formula

From: Frank <fbortel_at_nescape.net>
Date: Sat, 06 Dec 2003 16:30:51 +0100
Message-ID: <bqss70$gc1$1_at_news2.tilbu1.nb.home.nl>

Aaron V wrote:

> Thanks Frank,
> 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,
> 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
>>

> 
> 
> 

Oops - no underscore, indeed.
And from the post it wasn't clear if this was a server side function or forms - just make it a server side one, then

-- 
Regards, Frank van Bortel

Received on Sat Dec 06 2003 - 16:30:51 CET