Problem is the pl/sql variable can have only 1000 bytes despite being varchar2(4000) is that a bug?

From: colin_lyse <colin_lyse_at_98fgfgs.com>
Date: 7 Jun 2005 09:37:04 -0500
Message-ID: <42a5b0ab$0$50268$bb4e3ad8@newscene.com>

In article <42a4b693$0$50296$bb4e3ad8_at_newscene.com>, colin_lyse_at_98fgfgs.com (colin_lyse) wrote:
>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?!

here is the program where the 1000k problem exists

CREATE OR REPLACE procedure getPCBDetailResults( txtLocation in Varchar2, txtFromPCB in Varchar2,

        txtToPCB in Varchar2, txtFromSampleDate in Varchar2, txtToSampleDate in Varchar2,

        txtEquipmentType in Varchar2, txtRegion in Varchar2, txtArea in Varchar2, txtEquipNum in Varchar2,

        txtManufacturer in Varchar2, txtSampleNumber in Varchar2, errResultMessage OUT VARCHAR2,
 errResultValue OUT INTEGER
)

        as
sqlStmt Varchar2(4000);
sqlStmt2 Varchar2(4000);
whereClause Varchar2(2000);
orderClause Varchar2(2000);
sqlStmtFluid Varchar2(2000);
orderByFluid Varchar2(2000);
whereClauseFluid Varchar2(2000);
len number;
len_order number;

        
                begin
                                   
orderClause := ' order by position.equipment_position, toa_equipmt.apprtype ';
len_order:= length(orderClause);                        

--this one failes it is 1042 bytes in size (982 plus the 60 bytes in the orderclause

sqlStmt := 'insert into cas_data_tmp(location, manufacturer, serial_num, equip_num, appr_type, equip_type, region, area, e_code, equip_id, equiptype_id, toa_equip_id, position_id) select toa_equipmt.location, toa_equipmt.mfr as mfr, toa_equipmt.serialnum as serialnum, position.equipment_position as equipment_number, toa_equipmt.apprtype as

apprtype, equiptype.description as equipment_type,        e1.name as region, 
e2.name as area,       position.pos_no as e_code, equipment.equip_id, 
equiptype.equiptype_id,        toa_equipmt.toa_equip_id, position.position_id  
from toa_equipmt, position, equipment, equiptype, enterprise E4,        

enterprise E3, enterprise E2, enterprise E1where toa_equipmt.equip_id = equipment.equip_id and equipment.position_id = position.position_id and equiptype.equiptype_id = equipment.equiptype_id and equipment.enterprise_id = e4.enterprise_id and e4.ref_id = e3.enterprise_id and e3.ref_id = e2.enterprise_id and e2.ref_id = e1.enterprise_id and ';

• this one works it is 999 bytes in size (939 plus the 60 bytes in the orderclause)

--sqlStmt := 'insert into cas_data_tmp(location, manufacturer, serial_num, equip_num, appr_type, equip_type, region, area, e_code, equip_id, equiptype_id, toa_equip_id, position_id) select toa_equipmt.location, toa_equipmt.mfr as mfr, toa_equipmt.serialnum as serialnum, position.equipment_position as equipment_number, toa_equipmt.apprtype as

apprtype, equiptype.description as equipment_type,        e1.name as region, 
e2.name as area,       position.pos_no as e_code, equipment.equip_id, 
equiptype.equiptype_id,        toa_equipmt.toa_equip_id, position.position_id  
from toa_equipmt, position, equipment, equiptype, enterprise E4,        

enterprise E3, enterprise E2, enterprise E1where toa_equipmt.equip_id = equipment.equip_id and equipment.position_id = position.position_id and equiptype.equiptype_id = equipment.equiptype_id and equipment.enterprise_id = e4.enterprise_id and e4.ref_id = e3.enterprise_id and e3.ref_id =

e2.enterpr ';                                       
                        sqlStmt := sqlStmt || orderClause;
                        len:= length(sqlStmt);

                --      execute immediate sqlStmt;
                        
                --      commit;
                        
                        exception
                        when others then
                            errResultValue := SQLCODE;
                                errResultMessage := SQLERRM;
                end;

/ Received on Tue Jun 07 2005 - 09:37:04 CDT