Why would concatenating a variable to a variable give an error in pl/sql?

From: colin_lyse <colin_lyse_at_98fgfgs.com>
Date: 6 Jun 2005 15:50:01 -0500
Message-ID: <42a4b693$0$50296$bb4e3ad8@newscene.com>

using oracle 9.2.0.3.0 running on unix starfire Sun OS 2.7

when doing a stored procedure we are getting the following errror when trying to concatenate 2 variables (variable sqlSqtmt)

                Undeclared Identifier ' sqlStmt'

we are doing the following (code not complet took parts out for brevity)

CREATE OR REPLACE procedure getPCBDetailResults( txtLocation in Varchar2, errResultMessage OUT VARCHAR2, errResultValue OUT INTEGER )

        as
sqlStmt Varchar2(2000);
sqlStmt2 Varchar2(4000);
whereClause Varchar2(2000);
orderClause Varchar2(2000);

        
                begin
           If txtLocation is not null and length(txtLocation) > 0 Then
                whereClause := ' toa_equipmt.location like ''' || txtLocation 
                || ''' and ';
            End If;

           whereClause := whereClause;
            orderClause := ' order by position.equipment_position, 
oa_equipmt.apprtype ';
                        
            sqlStmt :='insert into cas_data_tmp(location, manufacturer, 

serial_num, equip_num, appr_type, equip_type, region, area, e_code, ' and e2.ref_id = e1.enterprise_id and ';

                                          
       whereClause := whereClause || orderClause;

       sqlStmt2 := sqlStmt || whereClause;
                        

the error occurs on the last line. we get the Undeclared Identifier ' sqlStmt2' error. we tried just using sqlStmt but got the same error

        we tried also sqlStmt := sqlStmt || ' xx '; and got the same error.

any ideas?! Received on Mon Jun 06 2005 - 15:50:01 CDT