WRAP

The WRAP procedure is used to obfuscate the definition string of a PL/SQL object into a CREATE WRAPPED statement string.

Syntax

DBMS_DDL.WRAP(DDL VARCHAR2) RETURN VARCHAR2;
DBMS_DDL.WRAP(DDL DBMS_SQL.VARCHAR2S,
              LB  PLS_INTEGER,
              UB  PLS_INTEGER) RETURN DBMS_SQL.VARCHAR2S;
DBMS_DDL.WRAP(DDL DBMS_SQL.VARCHAR2A,
              LB  PLS_INTEGER,
              UB  PLS_INTEGER) RETURN DBMS_SQL.VARCHAR2A;

Parameters

Examples

Use PL/SQL and the DBMS_DDL package to create object types, and execute these operations using different methods. The following three examples demonstrate different methods for executing SQL statements.

Example 1:

Use the DBMS_DDL.WRAP procedure to wrap a single-line SQL statement, and then parse and execute it using the DBMS_SQL package.

declare
wrapped varchar2(32767);
c number;
r number;
begin
wrapped := dbms_ddl.wrap('create or replace type typ0 as object (a number);');
c := dbms_sql.open_cursor;
dbms_sql.parse(c, wrapped, dbms_sql.native);
r := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
end;
/

In this example:

• wrapped: Stores the SQL statement after it has been processed by DBMS_DDL.WRAP.
• c: The cursor identifier. dbms_sql.open_cursor opens a new cursor, and dbms_sql.close_cursor(c) closes the cursor.
• r: The execution result. r := dbms_sql.execute(c) executes the parsed SQL statement.

Example 2:

Use the DBMS_DDL.WRAP procedure to wrap a single-line SQL statement, and then execute it directly using EXECUTE IMMEDIATE.

declare
  wrapped varchar2(32767);
begin
  wrapped := dbms_ddl.wrap('create or replace type typ1 as object (a number);');
  execute immediate wrapped;
end;
/

In this example:

• dbms_ddl.wrap('create or replace type typ1 as object (a number);'): Wraps the SQL statement for creating an object type.
• execute immediate wrapped: Executes the wrapped SQL statement directly.

Example 3:

Use the DBMS_DDL.WRAP procedure to wrap an array of multi-line SQL statements, and then parse and execute them using the DBMS_SQL package.

declare
  sqls dbms_sql.varchar2a;
  c number;
  r number;
begin
  sqls(1) := 'create or replace type typ2 as object (';
  sqls(2) := '  a number';
  sqls(3) := ');';
  sqls := dbms_ddl.wrap(sqls, 1, sqls.count);
  c := dbms_sql.open_cursor;
  dbms_output.put_line(sqls.count);
  dbms_sql.parse(c, sqls, 1, sqls.count, false, dbms_sql.native);-- The linefeed flag parameter of dbms_sql.parse must be set to false. Otherwise, extra line breaks will cause the syntax parsing of the create wrapped statement to fail.
  r := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);
end;
/

In this example:

• sqls := dbms_ddl.wrap(sqls, 1, sqls.count): Wraps the array of multi-line SQL statements.
• dbms_output.put_line(sqls.count): Outputs the length of the wrapped SQL statement array.
• dbms_sql.parse(c, sqls, 1, sqls.count, false, dbms_sql.native): Parses the wrapped SQL statement array.