You do not have permission to delete messages in this group
Copy link
Report message
Sign in to report message
Show original message
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
to
I'm trying to make sense of the code generated by various COPYDB options to generate a scriot to recreate all the Database Procedures (axm.vms v9.2.1)
copydb -with_proc
produces a script which looks something like this
CREATE PROCEDURE PROC_1 AS BEGIN RETURN; END
\P\G
GRANT EXECUTE ON PROC_1 TO PUBLIC
\P\G
CREATE PROCEDURE PROC_2 AS BEGIN RETURN; END
\P\G
GRANT EXECUTE ON PROC_1 TO PUBLIC
\P\G
DROP PROCEDURE PROC_1
\P\G
CREATE PROCEDURE PROC_1 (PARAM_1 CHAR(4) NOT NULL) AS
BEGIN SELECT SOME_VAL INTO :PARAM_1 FROM VIEW_1 END
\P\G
etc
Executing this against the existing database fails with
E_US0960 Procedure 'PROC_1' already exists.
copydb -with_proc -add_drop
which you might expect fixes this problem produces a script which looks something like this
DROP PROCEDURE PROC_1
\P\G
DROP PROCEDURE PROC_2
\P\G
DROP VIEW VIEW_1
\P\G
CREATE PROCEDURE PROC_1 AS BEGIN RETURN; END
\P\G
GRANT EXECUTE ON PROC_1 TO PUBLIC
\P\G
CREATE PROCEDURE PROC_2 AS BEGIN RETURN; END
\P\G
GRANT EXECUTE ON PROC_1 TO PUBLIC
\P\G
DROP PROCEDURE PROC_1
\P\G
CREATE PROCEDURE PROC_1 (PARAM_1 CHAR(4) NOT NULL) AS
BEGIN SELECT SOME_VAL INTO :PARAM_1 FROM VIEW_1 END
\P\G
etc
Running this script fails with
E_US0845 Table 'VIEW_1' does not exist or is not owned by you
So two questions
(1) why does -with_proc create every procedure as "begin return; end", then drop them and create them "properly"
(2) why does -with_proc -add_drop also drop the views