This discussion is archived
5 Replies Latest reply: Apr 24, 2013 4:58 PM by DarrenSpurgeon RSS

SQL Developer Unit Test

990427 Newbie
Currently Being Moderated
I'm trying out the Unit Test tool in SQL Developer version 3.2.09 and I keep getting the following error on the Teardown Process:

Teardown Table or Row Restore (1/1) failed: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 22

I have no idea what line 22 refers to? I cannot seem to find more detailed information in order to correct the problem.

Any insight or help would be great!

Thanks,
Paul
  • 1. Re: SQL Developer Unit Test
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    I'm trying out the Unit Test tool in SQL Developer version 3.2.09 and I keep getting the following error on the Teardown Process:

    Teardown Table or Row Restore (1/1) failed: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 22

    I have no idea what line 22 refers to? I cannot seem to find more detailed information in order to correct the problem.
    >
    Just saying you have a problem isn't enough.

    When you post you need to provide your full DB name and version as well as the specific things that you are doing when the problem occurs.

    In your case that would include the code that you are trying to unit test and all of the steps you are taking to create and execute the test.
  • 2. Re: SQL Developer Unit Test
    990427 Newbie
    Currently Being Moderated
    I'm not running any specific code in the Teardown Process. It's a generic Table or Row Restore using the intrinsic Target Table = "TABLE_A", Row Identifier = ROWID, and Source Table = "TMP$TABLE_A" with the option Drop Source Table When Restore Completed set to checked. My Startup Process, Test Implementation and Process Validation all work successfully and I've tested my code outside of the unit test implementation to ensure it compiles and runs correctly. My inquiry is about the vague Teardown Process.

    The only step taken is to click on the Run Test button in the Unit Test Editor. Is there a way to see underneath the hood of what a Table or Row Restore is doing?

    The Oracle database version is 11.5.2
  • 3. Re: SQL Developer Unit Test
    DarrenSpurgeon Newbie
    Currently Being Moderated
    I'm having the same issue with SQL Developer 3.2.20.09 on Oracle 11.2.0.3.0. However, I think it's somewhat data dependent as I've had success and I've also encountered the ORA-06502 error.

    I ran a generic test on a very simple table and was successful using the teardown restore of the table. The procedure isn't important in this scenario, but is given as reference to creating the test. The simple, successful case is given first.

    Note that in both cases/tests, the original table of data already existed, so I question the error message pointing to data being inserted is longer than the field allows or that data types are somehow different...+unless+ SQL Developer is translating or getting it wrong. ALSO NOTE, running the SQL inserts as given works if run through the worksheet. It's only in the Teardown that I see the ORA-06502 error message.

    Data
    CREATE TABLE EMPLOYEES (EMPLOYEE_ID NUMBER, COMMISSION_PCT NUMBER, SALARY NUMBER);
    Insert into EMPLOYEES (EMPLOYEE_ID,COMMISSION_PCT,SALARY) values (1001,0.2,8400);
    Insert into EMPLOYEES (EMPLOYEE_ID,COMMISSION_PCT,SALARY) values (1002,0.25,6000);
    Insert into EMPLOYEES (EMPLOYEE_ID,COMMISSION_PCT,SALARY) values (1003,0.3,5000);

    CREATE OR REPLACE PROCEDURE AWARD_BONUS AS
    BEGIN
    null;
    END award_bonus;
    /

    Startup: Table or Row Copy
    description = "TMP$AWARD_BONUS_TEST" := SELECT ROWID AS SRC$ROWID, s.* FROM "EMPLOYEES" s
    source = "EMPLOYEES"
    temp = "TMP$AWARD_BONUS_TEST"

    Teardown: Table or Row Restore
    description = "TMP$AWARD_BONUS_TEST" -> "EMPLOYEES"
    source = "EMPLOYEES"
    key = PRIMARY KEY
    temp = "TMP$AWARD_BONUS_TEST"

    The above worked without error.

    The following did not work.* (Again, the procedure isn't relevant.)

    Data
    CREATE TABLE "xxdss_fact_detail_history"
    (
    "fact_trx_id" NUMBER(15, 0),"number_of_records" NUMBER,
    "product_code" VARCHAR2(250 byte),"item_code" VARCHAR2(250 byte),
    "item_description" VARCHAR2(250 byte),"trade" VARCHAR2(250 byte),
    "item_system" VARCHAR2(250 byte),"unit" VARCHAR2(250 byte),
    "fuel_source" VARCHAR2(250 byte),"component" VARCHAR2(250 byte),
    "assembly" VARCHAR2(250 byte),"part" VARCHAR2(250 byte),
    "symptoms" VARCHAR2(250 byte),"diagnosis_item" VARCHAR2(250 byte),"coa"
    VARCHAR2(250 byte),"cchs_parts" VARCHAR2(250 byte),
    "region" VARCHAR2(250 byte),"state" VARCHAR2(250 byte),
    "county" VARCHAR2(250 byte),"zip" VARCHAR2(250 byte),"vendor_id" NUMBER,
    "vendor_site_id" NUMBER,"vendor_name" VARCHAR2(250 byte),
    "vendor_site" VARCHAR2(250 byte),"c_service_call_cost" NUMBER,
    "c_labor_cost" NUMBER,"c_part_cost" NUMBER,"c_tax" NUMBER,
    "c_cchs_oh_cost" NUMBER,"c_cchs_total_low_cost" NUMBER,
    "c_cchs_total_high_cost" NUMBER,"c_cchs_avg_total_cost" NUMBER,
    "c_nc_c_labor_cost" NUMBER,"c_nc_c_part_cost" NUMBER,"c_nc_tax_cost" NUMBER
    ,"c_nc_total_low_cost" NUMBER,"c_nc_total_high_cost" NUMBER,
    "c_nc_avg_total_cost" NUMBER,"c_flat_low_cost" NUMBER,
    "c_flat_high_cost" NUMBER,"c_flat_avg_cost" NUMBER,
    "p_cycle_time_day_low" NUMBER,"p_cycle_time_day_high" NUMBER,
    "p_cycle_avg_time_day" NUMBER,"p_reasgn_low" NUMBER,"p_reasgn_high" NUMBER,
    "p_reasgn_avg" NUMBER,"p_recall_low" NUMBER,"p_recall_high" NUMBER,
    "p_recall_avg" NUMBER,"s_sp_nps_score_low" NUMBER,
    "s_sp_nps_score_high" NUMBER,"s_sp_avg_nps_score" NUMBER,
    "s_cchs_nps_score_low" NUMBER,"s_cchs_nps_score_high" NUMBER,
    "s_cchs_avg_nps_score" NUMBER,"cps_cost" NUMBER,"cps_perfrmce" NUMBER,
    "cps_sat" NUMBER,"coa_score" NUMBER,"comments" VARCHAR2(4000 byte),
    "complete_flag" VARCHAR2(1 byte),"last_update_date" DATE,
    "last_updated_by" NUMBER(15, 0),"creation_date" DATE,
    "created_by" NUMBER(15, 0),"last_update_login" NUMBER(15, 0),
    "vendor_number" VARCHAR2(200 byte)
    );
    SET DEFINE OFF;
    Insert into XXDSS.XXDSS_FACT_DETAIL_HISTORY (FACT_TRX_ID,NUMBER_OF_RECORDS,PRODUCT_CODE,ITEM_CODE,ITEM_DESCRIPTION,TRADE,ITEM_SYSTEM,UNIT,FUEL_SOURCE,COMPONENT,ASSEMBLY,PART,SYMPTOMS,DIAGNOSIS_ITEM,COA,CCHS_PARTS,REGION,STATE,COUNTY,ZIP,VENDOR_ID,VENDOR_SITE_ID,VENDOR_NAME,VENDOR_SITE,C_SERVICE_CALL_COST,C_LABOR_COST,C_PART_COST,C_TAX,C_CCHS_OH_COST,C_CCHS_TOTAL_LOW_COST,C_CCHS_TOTAL_HIGH_COST,C_CCHS_AVG_TOTAL_COST,C_NC_C_LABOR_COST,C_NC_C_PART_COST,C_NC_TAX_COST,C_NC_TOTAL_LOW_COST,C_NC_TOTAL_HIGH_COST,C_NC_AVG_TOTAL_COST,C_FLAT_LOW_COST,C_FLAT_HIGH_COST,C_FLAT_AVG_COST,P_CYCLE_TIME_DAY_LOW,P_CYCLE_TIME_DAY_HIGH,P_CYCLE_AVG_TIME_DAY,P_REASGN_LOW,P_REASGN_HIGH,P_REASGN_AVG,P_RECALL_LOW,P_RECALL_HIGH,P_RECALL_AVG,S_SP_NPS_SCORE_LOW,S_SP_NPS_SCORE_HIGH,S_SP_AVG_NPS_SCORE,S_CCHS_NPS_SCORE_LOW,S_CCHS_NPS_SCORE_HIGH,S_CCHS_AVG_NPS_SCORE,CPS_COST,CPS_PERFRMCE,CPS_SAT,COA_SCORE,COMMENTS,COMPLETE_FLAG,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,VENDOR_NUMBER) values (324,2,'L&F-MPP03TR','STCOL-SPL','Straight Cool Split System','HVAC',null,'STCOL-SPL',null,null,null,null,null,null,'REAPIR','N',null,'VA','ASHBURN','20148',22024,118957,'Air Lingus Inc',null,50,55,44,12,180,40,78,76.5,60,50,20,100,180,130,50,110,80,2,8,3.9323148148178,40,60,43,9,10,8.2,100,201,150,20,40,35,50,30,20,100,null,null,to_date('01-JAN-13','DD-MON-RR'),1,to_date('01-JAN-13','DD-MON-RR'),1,null,'122023');
    Insert into XXDSS.XXDSS_FACT_DETAIL_HISTORY (FACT_TRX_ID,NUMBER_OF_RECORDS,PRODUCT_CODE,ITEM_CODE,ITEM_DESCRIPTION,TRADE,ITEM_SYSTEM,UNIT,FUEL_SOURCE,COMPONENT,ASSEMBLY,PART,SYMPTOMS,DIAGNOSIS_ITEM,COA,CCHS_PARTS,REGION,STATE,COUNTY,ZIP,VENDOR_ID,VENDOR_SITE_ID,VENDOR_NAME,VENDOR_SITE,C_SERVICE_CALL_COST,C_LABOR_COST,C_PART_COST,C_TAX,C_CCHS_OH_COST,C_CCHS_TOTAL_LOW_COST,C_CCHS_TOTAL_HIGH_COST,C_CCHS_AVG_TOTAL_COST,C_NC_C_LABOR_COST,C_NC_C_PART_COST,C_NC_TAX_COST,C_NC_TOTAL_LOW_COST,C_NC_TOTAL_HIGH_COST,C_NC_AVG_TOTAL_COST,C_FLAT_LOW_COST,C_FLAT_HIGH_COST,C_FLAT_AVG_COST,P_CYCLE_TIME_DAY_LOW,P_CYCLE_TIME_DAY_HIGH,P_CYCLE_AVG_TIME_DAY,P_REASGN_LOW,P_REASGN_HIGH,P_REASGN_AVG,P_RECALL_LOW,P_RECALL_HIGH,P_RECALL_AVG,S_SP_NPS_SCORE_LOW,S_SP_NPS_SCORE_HIGH,S_SP_AVG_NPS_SCORE,S_CCHS_NPS_SCORE_LOW,S_CCHS_NPS_SCORE_HIGH,S_CCHS_AVG_NPS_SCORE,CPS_COST,CPS_PERFRMCE,CPS_SAT,COA_SCORE,COMMENTS,COMPLETE_FLAG,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,VENDOR_NUMBER) values (465,4,'L&F-MPP03TR','STCOL-SPL','Straight Cool Split System','HVAC',null,'STCOL-SPL',null,null,null,null,null,null,'REPLACE','N',null,'VA','ASHBURN','20148',1768,1090523,'Airmax Heating And Air Conditioning Llc',null,65,50,80,15,110,50,60,70,70,40,20,100,180,130,50,110,80,3,5,0.878969907411374,10,70,20,1,2,2,130,150,140,20,40,60,40,30,30,100,null,null,to_date('01-JAN-13','DD-MON-RR'),1,to_date('01-JAN-13','DD-MON-RR'),1,null,'101767');

    CREATE OR REPLACE PROCEDURE dss AS
    BEGIN
    null;
    END dss;
    /

    Startup: Table or Row Copy
    description = "TMP$DSS_TEST" := SELECT ROWID AS SRC$ROWID, s.* FROM "XXDSS_FACT_DETAIL_HISTORY" s
    source = "XXDSS_FACT_DETAIL_HISTORY"
    temp = "TMP$DSS_TEST"

    Teardown: Table or Row Restore
    description = "TMP$DSS_TEST" -> "XXDSS_FACT_DETAIL_HISTORY"
    source = "XXDSS_FACT_DETAIL_HISTORY"
    key = PRIMARY KEY
    temp = "TMP$DSS_TEST"
  • 4. Re: SQL Developer Unit Test
    DarrenSpurgeon Newbie
    Currently Being Moderated
    I have an update on this, perhaps some insight, but still no resolution.

    #1 I removed all data from the table. I still received a failure. So, I no longer think it has anything to do with data in the table.

    #2 I reviewed the temp table created during the startup table copy and compared to the original. The DDL was the same--that appears correct.

    #3 I began removing columns from the table, one at a time, and reran the test each time. I thought that there may be an issue with one of the columns or their data type. After I'd removed four columns, the test passed. Now, here's the thing, I recreated the table and started again, but this time removed a completely different set of columns. Again, once I'd removed four columns, the test passed. In fact, as long as I had 64 columns or less, I was fine--the test passed.

    My question now is, does SQL Developer do anything in the teardown process to generate SQL statements on the fly where an issue of "character string buffer too small" would be a factor?
  • 5. Re: SQL Developer Unit Test
    DarrenSpurgeon Newbie
    Currently Being Moderated
    UPDATE #2 (see prior responses for the whole story to this point)

    I switched over to manually creating the temp tables and performing essentially what I believe SQL Developer should be doing. Instead of the Table Copy startup and teardown, I used the following PL/SQL.

    Startup
    BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE TMP$DONOTHING AS SELECT * FROM XXDSS_FACT_DETAIL_HISTORY';
    END;

    Teardown
    BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE XXDSS_FACT_DETAIL_HISTORY';
    EXECUTE IMMEDIATE 'INSERT INTO XXDSS_FACT_DETAIL_HISTORY SELECT * FROM TMP$DONOTHING';
    EXECUTE IMMEDIATE 'DROP TABLE TMP$DONOTHING';
    END;

    The above worked.* It appears I have a work around, but it's ugly. I do expect the table copy to work as intended, and I'm hoping the SQL Developer team can still provide a response.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points