Re: Help on button Trigger for Query

nick048 wrote:
> Hi,
>
> I have create create with the Developer Suite 10g a Form with 3
> Section:
> 1) The First Section has a Data Block (BLOCK_HEADER) with this entries
> used as filter in a Query:
> a. TEXT_ANA_TYPE -> text item
> b. TEXT_ANA_CODE -> text item
> c. DOC_TYPE -> text item
> d. ACCOUNT_SITUATION -> radio button
> e. FROM_DATE -> text item (date format)
> f. TO_DATE -> text item (date format)
> 2) The second Section has a Data Block (BLOCK_BALANCE) based on Table
> (example MYTABLE) with a grid of data;
> 3) The third Data Block (BLOCK_CONTROL) has a Query Button.
>
> After entering the data in the BLOCK_HEADER, I need to run a Query
> with the button, in order to populate my grid with data, wich I apply
> the filter.
> In order to perform this I have created a trigger WHEN-BUTTON-PRESSED
> with this code:
>
> Declare
> LC$Req Varchar2(256) ;
> AC$Req Varchar2(256);
> TD$Req Varchar2(256);
> TI$Req Varchar2(256);
> DT$Req Varchar2(256);
>
> Begin
> LC$Req := '';
> AC$Req := '';
> TD$Req := '';
> TI$Req := '';
> DT$Req := '';
>
> -- Radio button Value in the Header block
> If :BLOCK_HEADER.ACCOUNT_SITUATION = 1 then
> TI$Req := ' ';
> Elsif :BLOCK_HEADER.ACCOUNT_SITUATION = 2 then
> TI$Req := ' AND DOC_PAID <> 0 ';
> Elsif :BLOCK_HEADER.ACCOUNT_SITUATION = 3 then
> TI$Req := ' AND DOC_PAID = 0 ';
> Else
> TI$Req := ' AND DOC_INS <> 0 ';
> END IF;
>
> -- Type of Doc in the Header Block
> If :BLOCK_HEADER.DOC_TYPE is not null then
> TD$Ref := ' AND TYPE_DOC = :BLOCK_HEADER.DOT_TYPE' ;
> End if;
>
> -- Date Filters in the Header Block
> If ((:BLOCK_HEADER.ACCOUNT_FROM_DATE is not null) AND
> (:BLOCK_HEADER.ACCOUNT_TO_DATE is not null)) then
> DT$Req := ' AND (DOC_DATE >= :BLOCK_HEADER.ACCOUNT_FROM_DATE OR
> DOC_DATE <= :BLOCK_HEADER.ACCOUNT_TO_DATE)';
> End if;
>
> If ((:BLOCK_HEADER.TEXT_ANA_TYPE is not null) AND
> (:BLOCK_HEADER.TEXT_ANA_CODE is not null)) Then
> AC$Req := ' D_MYTABLE WHERE ANA_TYPE = :BLOCK_HEADER.TEXT_ANA_TYPE AND
> ANA_CODE = :BLOCK_HEADER.TEXT_ANA_CODE' ;
> -- Set SQL Statement
> LC$Req := RTRIM(AC$Req) || RTRIM(TD$Req) || RTRIM(TI$Req) || RTRIM(DT
> $Req) ;
> Go_Block('BLOCK_BALANCE');
> Set_Block_Property( 'BLOCK_BALANCE', QUERY_DATA_SOURCE_NAME, LC
> $Req ) ;
> Execute_Query ;
> Go_Block('BLOCK_HEADER');
> End if;
> Go_Block('BLOCK_HEADER');
> End ;
>
> I have verified that the SQL string applied with choices is for
> example
>
> SELECT TYPE_CODE, DOC_CODE, DOC_DATE, ANA_TYPE, ANA_CODE, DOC_TOTAL,
> DOC_INS, DOC_PAID, DOC_NOPAID FROM MYTABLE WHERE ANA_TYPE
> = :BLOCK_HEADER.TEXT_ANA_TYPE AND ANA_CODE
> = :BLOCK_HEADER.TEXT_ANA_CODE AND TYPE_CODE = :BLOCK_HEADER.CODE_TYPE
>
> When I run the form and press the Query button, the error: ORA-01008:
> Not all variables bound
>
> I have searched this error on the Net, but I am not able to correct
> the problem without help.
>
> I am a newbie in Oracle programming and I hope in Your help.
>
> Thank You and best Regards
>
> Gaetano

Consider:

 > -- Type of Doc in the Header Block
 > If :BLOCK_HEADER.DOC_TYPE is not null then
 > TD$Ref := ' AND TYPE_DOC = :BLOCK_HEADER.DOT_TYPE' ;
 > End if;

TD$Ref is never declared and never used.

Without your real code ... no help is possible.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org