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?
I'm having the same issue with SQL Developer 3.2.20.09 on Oracle 22.214.171.124.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.
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
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"
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?
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.
EXECUTE IMMEDIATE 'CREATE TABLE TMP$DONOTHING AS SELECT * FROM XXDSS_FACT_DETAIL_HISTORY';
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';
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.