Re: Forms sql/plsql question:

"Binz" <byas62NoSpam_at_yahoo.com> wrote in message news:39DC21B7.DC91A178_at_yahoo.com...
> Just wondering if there's a way in substituting strings in sql like
> below. Assuming that the parameter is a value from the current item in a
> form taken from the SYSTEM.CURRENT_ITEM, how are you going to deal with
> the table, item/field in this case? I 've tries using &, &&, $, $$ of
> any combination but it doesn't help.
> Here's my code.
>
> FUNCTION Check_Duplicate
> (
> L_ITEM_STRING varchar2
> )
> RETURN Char IS
>
> L_ITEM_NAME varchar2(50) := ':'||L_ITEM_STRING;
> L_TABLE_NAME varchar2(50) := Substr( L_ITEM_STRING,
> 1,
> Instr( L_ITEM_STRING, '.', 1, 1) - 1
> );
> L_FIELD_NAME varchar2(50) := Substr( L_ITEM_STRING,
> Instr( L_ITEM_STRING, '.', 1, 1 ) + 1,
> Length( L_ITEM_STRING )
> );
>
> L_DUMMY char(1);
> BEGIN
> if L_ITEM_NAME is not null then
> begin
> SELECT 'Y'
> INTO L_DUMMY
> FROM L_TABLE_NAME
> WHERE L_FIELD_NAME = L_ITEM_NAME;
> --
> if L_DUMMY = 'Y' then
> message('Please revise the name, name is duplicated!');
> Return ('Y');
> end if;
> exception
> when No_Data_Found then
> Return ('N');
> end;
> end if;
> END;
>
>
> Thanks.
>
> Binz

Yes but you need to use dynamic SQL. Look at the DBMS_SQL PL/SQL package in a PL/SQL reference book.

-Matt Received on Fri Oct 06 2000 - 06:58:06 CEST