How to add '/' at the end of each DDl using dbms_metadata(SQL terminator )-CodePudding

In the documentation for dbms_metadata, mentioned about SQLTERMINATOR:

If TRUE, append a SQL terminator (; or /) to each DDL statement. Defaults to FALSE.

by this below step:

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
END;
/

Default after enabled ';' is added at end of each DDL.

How can I add / instead of ; at the end of the DDL using dbms_metadata or any possibility.

If I try overload method of dbms_metadata.set_transform_param, it errors out

BEGIN
  DBMS_METADATA.set_transform_param(
    DBMS_METADATA.session_transform, 'SQLTERMINATOR', '/'
  );
END;
/

  2    3    4  BEGIN
*
ERROR at line 1:
ORA-31600: invalid input value "/" for parameter SQLTERMINATOR in function
SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8844
ORA-06512: at "SYS.DBMS_METADATA_INT", line 10288
ORA-06512: at "SYS.DBMS_METADATA", line 8418
ORA-06512: at line 2```

Please can anybody help this?

As said in my one of the comments below , attaching the use case. I want DDL to be added to file separated by '/' indicates end of ddl.

create table test (id number);
/
CREATE OR REPLACE EDITIONABLE PACKAGE "C##CLOUD$SERVICE"."MY_PACKAGE1" As
   M_VCSIZ_4K               CONSTANT PLS_INTEGER := 4000;

   procedure fun1(
     value number
   );
end my_package1;
/```

while importing I will get ddl and execute using 'execute immediate' mean
```execute immediate 'create table test (id number);' -- due to semi colon but success if no semi colon
execute immediate 'CREATE OR REPLACE EDITIONABLE PACKAGE "C##CLOUD$SERVICE"."MY_PACKAGE1" As
   M_VCSIZ_4K               CONSTANT PLS_INTEGER := 4000;

   procedure fun1(
     value number
   );
end my_package1;' -- works as it has semi colon but fails if no semicolon```

how can I get out of this situation and work for both the cases

CodePudding user response：

I think that what you want is wrong.

There are two terminators: semi-colon (;) and slash (/). However, they aren't "equal" as some DDL statements end with a colon, e.g.

create table test (id number);

It would work with a slash too:

create table test (id number)
/

But, DBMS_METADATA extracts PL/SQL as well, and these objects end with the end; and additional slash is required to execute those statements, e.g.

SQL> create procedure test is
  2  begin
  3    null;
  4  end;
  5  /           --> this; without it, procedure wouldn't be created, cursor
                     just keeps blinking (or, in SQL*Plus, you get number of 
                     subsequent lines (6, 7, 8, 9, ...)    
Procedure created.

SQL>

What you want is to have slash as a terminator, and that would lead to an error:

SQL> create procedure test is
  2  begin
  3    null;
  4  end/              --> "your" slash
  5  /                 --> PL/SQL block terminator

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4      PLS-00103: Encountered the symbol "/" when expecting one of the
         following:
         ; <an identifier> <a double-quoted delimited-identifier>
         current delete exists prior <a single-quoted SQL string>
         The symbol "; was inserted before "/" to continue.

SQL>

Therefore, it depends on that particular DDL - some of them don't care about the terminator (such as create table), while PL/SQL objects raise an error if sqlterminator is wrongly set.

CodePudding user response：

The problem is that execute immediate doesn't require any terminator. As long as it allows only a single statement as input, there's no need to identify individual statements by terminator.

You may use the original code returned by get_ddl without terminators.

create function f
return number
is
begin
  return null;
end;/

create table t(id int)

create view v_ddl
as
select
  dbms_metadata.get_ddl(
    'FUNCTION',
    'F'
  ) as ddl_
from dual
union all
select
  dbms_metadata.get_ddl(
    'TABLE',
    'T'
  ) as ddl_
from dual

select *
from v_ddl

| DDL_                                                                                                                                                                                                                                                                                                                                                                                                                   |
| :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| <br>  CREATE OR REPLACE FUNCTION "FIDDLE_CGDJQPMDTXHHROBDNHLJ"."F" <br>return number<br>is<br>begin<br>  return null;<br>end;                                                                                                                                                                                                                                                                                          |
| <br>  CREATE TABLE "FIDDLE_CGDJQPMDTXHHROBDNHLJ"."T" <br>   (        "ID" NUMBER(*,0)<br>   ) SEGMENT CREATION IMMEDIATE <br>  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING<br>  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br>  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br>  TABLESPACE "USERS"  |

declare
  f clob;
  t clob;
begin
  f := dbms_metadata.get_ddl(
    'FUNCTION',
    'F'
  );
  t := dbms_metadata.get_ddl(
    'TABLE',
    'T'
  );
  
  execute immediate 'drop table t';
  execute immediate 'drop function f';
  execute immediate t;
  execute immediate f;
end;
/

select *
from v_ddl

| DDL_                                                                                                                                                                                                                                                                                                                                                                                                                   |
| :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| <br>  CREATE OR REPLACE FUNCTION "FIDDLE_CGDJQPMDTXHHROBDNHLJ"."F" <br>return number<br>is<br>begin<br>  return null;<br>end;                                                                                                                                                                                                                                                                                          |
| <br>  CREATE TABLE "FIDDLE_CGDJQPMDTXHHROBDNHLJ"."T" <br>   (        "ID" NUMBER(*,0)<br>   ) SEGMENT CREATION IMMEDIATE <br>  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING<br>  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br>  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br>  TABLESPACE "USERS"  |

db<>fiddle here