Toad World® Forums

Testing DDL - adding a field to a table


#1

CREATE TABLE AA_UNIT_TEST

(AA_UNIT_TEST_ID INTEGER,

);

INSERT INTO AA_UNIT_TEST (AA_UNIT_TEST_ID, DESCRIPTION) VALUES (2,‘DESC 2’);INSERT

COMMIT;

CREATE OR REPLACE PROCEDURE ADD_DEPT_ID

AS

DDL_STRING VARCHAR2 (1000) DEFAULT NULL;

BEGIN

EXECUTE IMMEDIATE DDL_STRING;

END;

I have procedure which adds a field to a table, and I want to test that the outcome of a test is that the table now has a new field called DEPARTMENT_ID. For test purposes I have:-

DESCRIPTION VARCHAR2(20)INSERT INTO AA_UNIT_TEST (AA_UNIT_TEST_ID, DESCRIPTION) VALUES (1,‘DESC 1’); INTO AA_UNIT_TEST (AA_UNIT_TEST_ID, DESCRIPTION) VALUES (3,‘DESC 3’);
and the procedure which makes the change is:-

DDL_STRING := ‘ALTER TABLE AA_UNIT_TEST ADD DEPARTMENT_ID INTEGER’;

Is there a way of using the standard outcomes to achieve this? They all (quite rightly) seem to be based on data content. Although I appreciate the sheer lunacy of doing DDL ‘on the fly’ I have to do this, so really want a test.
I have attempted to achieve the outcome by modifying the generated code, which including my own addition of

l_string := ‘ALTER TABLE Q##COPY_1242070069 ADD DEPARTMENT_ID INTEGER’;

EXECUTE IMMEDIATE l_string;
is:-

/QCTO START generated code for TEST-CASE-SETUP-CODE. 1242070069-{620663FD-02DF-4F6E-A76C-8908082AD067}/

DECLARE
PROCEDURE exec_drop
IS
e_no_such_table EXCEPTION;
PRAGMA EXCEPTION_INIT (e_no_such_table, -942);

PRAGMA AUTONOMOUS_TRANSACTION; /**/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ’ || ‘Q##COPY_1242070069’;
EXCEPTION
WHEN e_no_such_table
THEN
NULL;
END exec_drop;

PROCEDURE exec_create
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_string VARCHAR2 (32767)
:= 'CREATE TABLE ’
|| ‘Q##COPY_1242070069
|| ’ ( “AA_UNIT_TEST_ID”,“DESCRIPTION” ) ’
|| ’ AS ’
|| 'SELECT “AA_UNIT_TEST_ID”,“DESCRIPTION” FROM ’
|| ‘AA_UNIT_TEST’;
BEGIN
EXECUTE IMMEDIATE l_string;

l_string := ‘ALTER TABLE Q##COPY_1242070069 ADD DEPARTMENT_ID INTEGER’;

EXECUTE IMMEDIATE l_string;
EXCEPTION
WHEN OTHERS
THEN
qcto#report_result (
qu_result_xp.current_outcome,
‘RUNTIME-ERROR’,
'Error attempting to create copy of ’
|| ‘Q##COPY_1242070069
|| '. Error reported: ’
|| CHR (10)
|| qcto#error_info);
RAISE;
END exec_create;
BEGIN
exec_drop ();
exec_create ();
END; /QCTO END generated code for TEST-CASE-SETUP-CODE. 1242070069-{620663FD-02DF-4F6E-A76C-8908082AD067}/

The test is failing, even though manual inspection of the table shows success. Can anyone suggest a better approach?


#2

John,

After you run your procedure, why not do a Query test:

select column_name
from all_table_columns
where table= 'AA_UNIT_TEST. and column_name = ‘DEPARTMENT_ID’

or some variation on that?

Regards, Steven


#3

Er, well Steven, that would be because I’m a “noob” and lack the knowledge to think of such an elegantly simple thing.

I’ll do that right away.

However, I will then need to go on to check the data content of the table - the department_id field will be populated with various values depending upon the content of a configuration table.

Would the best way then be to build a table with the target structure, and generate varied content for each scenarios, and compare against them? This is all a bit “in at the deep end” for me, so please bear with me whilst I train myself.

P.S. the on-line flash tutorials are very useful.


#4

John,

In terms of checking the contents of the table, since you are adding a column as part of the program’s activity, then yes I would say the best approach is to build that “control” table - you can put all the code to do that in initialization sections of the test definition itself.

You will likely want to use the external initialization section to create your table, then use the init. section to populate the table. That way, within the test definition itself you will not have to use dynamic SQL; the table will already exist when it is time to compile the test package. I hope this makes some sense.

Regards, SF


#5

Steven,

thanks for the response. I was unaware of external initialization, which I now know to look into. As it’s getting towards “close of play” my side of the pond, I’ll try that tomorrow. I’ll do my best to let you know how things turn out.

I’ve marked this thread as answered - my thanks.

J