ORACLE7 DDL Code Generator?
Date: 15 Feb 93 15:50:51 GMT
Message-ID: <33284_at_nntp_server.ems.cdc.com>
We need to create ORACLE7 syntax and the obvious way is to use PL*SQL code to read from the Version 6 data dictionary to generate version 7 syntax.
I have written an example that creates Primary key constraints, but that still leaves foreign keys, check constraints, Create table, create view, create index, create sequence, synonym .... and on and on.
Has any one done any of these?
I would be glad to collect any that are out there, and forward them to anyone who asks.
REM This first script sets up structures necessary for the example REM script to run.
REM Create the plsql_output table.
REM create_date and user name will help identify output that
REM was not cleaned up by the procedure that put it there
REM
REM
Create table plsql_output
( create_date date default sysdate,
user_name char(30) default user,
sessionid number default userenv('sessionid'),
output_name char(10),
seq number,
line char(132)
)
storage (initial 1k next 1k)
;
Comment on table plsql_output is
' Holding table for any output from PLSQL procedures. ';
comment on column plsql_output.create_date is
' The date the record was inserted. ';
comment on column plsql_output.user_name is
' The user name inserting the record. ';
comment on column plsql_output.sessionid is
'unique identifier for output from a particular process.
Used for cleanup of table. '
comment on column plsql_output.output_name is
'An optional identifier that may be used to select output into
various spool files from sqlplus. Use DEBUG for debug messages.' ;
comment on column plsql_output.seq is
'Order of the output for retrieval.';
comment on column plsql_output.line is
'One line of output text.';
REM Create the sequence for ordering lines of output. Create sequence plsql_output_seq;
REM This shows an example of debug output.
insert into plsql_output
(output_name, seq, line)
values ('DEBUG' , plsql_output_seq.nextval, 'Starting Loop.' );
REM This shows an example of error output caused by an exception:
REM when others then
insert into plsql_output
(output_name, seq, line)
values ('ERROR',
plsql_output_seq.nextval,
substr(SQLERRM,1,132 );
REM This shows all output being retrieved. select line from plsql_output
where sessionid=userenv('sessionid') order by seq;
Prompt Deleting debug output from plsql table
REM This shows all output for this session being deleted. delete from plsql_output where sessionid=userenv('sessionid');
REM This extracts primary keys.
REM A similar procedure will extract foreign keys.
Prompt Deleteing from plsql_output;
delete from plsql_output where SESSIONID = userenv('sessionid');
Declare cursor cons is
select con.table_name, con.constraint_name, con.constraint_type,
con.owner,
con.r_constraint_name,
con.r_owner,
con.status,
col.column_name,
col.position
from all_constraints con,
all_cons_columns col
where
con.constraint_type = 'P'
and con.owner = col.owner
and con.constraint_name = col.constraint_name
order by con.constraint_name, col.position
;
error_message char(32);
cons_row cons%ROWTYPE; cons_name VARCHAR2(30); loops number;
Begin
open cons;
insert into plsql_output
(output_name, seq, line)
values ('DEBUG' , plsql_output_seq.nextval, 'Starting Loop.' );
fetch cons into cons_row;
cons_name := cons_row.CONSTRAINT_NAME;
loops := 1;
- Limit loops for testing while loops <= 100 loop EXIT WHEN cons%NOTFOUND; loops := loops+1;
insert into plsql_output (output_name, seq, line)
values ('PKEY' , plsql_output_seq.nextval,
'alter table '||cons_row.TABLE_NAME||' add constraint '||
cons_row.CONSTRAINT_NAME||' Primary Key ('
) ;
insert into plsql_output (output_name, seq, line)
values ('PKEY' , plsql_output_seq.nextval,
' '||cons_row.COLUMN_NAME
);
insert into plsql_output
(output_name, seq, line)
values ('DEBUG' , plsql_output_seq.nextval,
cons_row.CONSTRAINT_NAME||' ?= '||cons_name );
fetch cons into cons_row;
insert into plsql_output
(output_name, seq, line)
values ('DEBUG' , plsql_output_seq.nextval,
'After fetch,'||cons_row.CONSTRAINT_NAME||' ?= '||cons_name );
while (cons_row.CONSTRAINT_NAME = cons_name) loop
insert into plsql_output (output_name, seq, line)
values ('PKEY' , plsql_output_seq.nextval,
' ,'||cons_row.COLUMN_NAME
);
fetch cons into cons_row;
end loop;
cons_name := cons_row.CONSTRAINT_NAME;
insert into plsql_output (output_name, seq, line)
values ('PKEY' , plsql_output_seq.nextval,
' ); '
);
insert into plsql_output
(output_name, seq, line)
values ('DEBUG' , plsql_output_seq.nextval, 'End Loop.' );
end loop;
EXCEPTION /* Main */
when others then
error_message := substr(SQLERRM,1,132); insert into plsql_output (output_name, seq, line)
values ('PKEY' , plsql_output_seq.nextval,
error_message
);
close cons;
end;
/
set heading off linesize 132;
select line from plsql_output where SESSIONID = userenv('sessionid')
order by seq
select line from plsql_output where SESSIONID = userenv('sessionid') and output_name = 'PKEY' order by seq; Received on Mon Feb 15 1993 - 16:50:51 CET