FTP from Oracle Database

PL/SQL FTP API

Create below package in your database.

Package Spec:

CREATE OR REPLACE PACKAGE ftp AS
TYPE t_string_table IS TABLE OF VARCHAR2(32767);
FUNCTION login (p_host    IN  VARCHAR2,
                p_port    IN  VARCHAR2,
                p_user    IN  VARCHAR2,
                p_pass    IN  VARCHAR2,
                p_timeout IN  NUMBER := NULL)
  RETURN UTL_TCP.connection;
FUNCTION get_passive (p_conn  IN OUT NOCOPY  UTL_TCP.connection)
  RETURN UTL_TCP.connection;
PROCEDURE logout (p_conn   IN OUT NOCOPY  UTL_TCP.connection,
                  p_reply  IN             BOOLEAN := TRUE);
PROCEDURE send_command (p_conn     IN OUT NOCOPY  UTL_TCP.connection,
                        p_command  IN             VARCHAR2,
                        p_reply    IN             BOOLEAN := TRUE);
PROCEDURE get_reply (p_conn  IN OUT NOCOPY  UTL_TCP.connection);
FUNCTION get_local_ascii_data (p_dir   IN  VARCHAR2,
                               p_file  IN  VARCHAR2)
  RETURN CLOB;
FUNCTION get_local_binary_data (p_dir   IN  VARCHAR2,
                                p_file  IN  VARCHAR2)
  RETURN BLOB;
FUNCTION get_remote_ascii_data (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                p_file  IN             VARCHAR2)
  RETURN CLOB;
FUNCTION get_remote_binary_data (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                 p_file  IN             VARCHAR2)
  RETURN BLOB;
PROCEDURE put_local_ascii_data (p_data  IN  CLOB,
                                p_dir   IN  VARCHAR2,
                                p_file  IN  VARCHAR2);
PROCEDURE put_local_binary_data (p_data  IN  BLOB,
                                 p_dir   IN  VARCHAR2,
                                 p_file  IN  VARCHAR2);
PROCEDURE put_remote_ascii_data (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                 p_file  IN             VARCHAR2,
                                 p_data  IN             CLOB);
PROCEDURE put_remote_binary_data (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                  p_file  IN             VARCHAR2,
                                  p_data  IN             BLOB);
PROCEDURE get (p_conn       IN OUT NOCOPY  UTL_TCP.connection,
               p_from_file  IN             VARCHAR2,
               p_to_dir     IN             VARCHAR2,
               p_to_file    IN             VARCHAR2);
PROCEDURE put (p_conn       IN OUT NOCOPY  UTL_TCP.connection,
               p_from_dir   IN             VARCHAR2,
               p_from_file  IN             VARCHAR2,
               p_to_file    IN             VARCHAR2);
PROCEDURE get_direct (p_conn       IN OUT NOCOPY  UTL_TCP.connection,
                      p_from_file  IN             VARCHAR2,
                      p_to_dir     IN             VARCHAR2,
                      p_to_file    IN             VARCHAR2);
PROCEDURE put_direct (p_conn       IN OUT NOCOPY  UTL_TCP.connection,
                      p_from_dir   IN             VARCHAR2,
                      p_from_file  IN             VARCHAR2,
                      p_to_file    IN             VARCHAR2);
PROCEDURE help (p_conn  IN OUT NOCOPY  UTL_TCP.connection);
PROCEDURE ascii (p_conn  IN OUT NOCOPY  UTL_TCP.connection);
PROCEDURE binary (p_conn  IN OUT NOCOPY  UTL_TCP.connection);
PROCEDURE list (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                p_dir   IN             VARCHAR2,
                p_list  OUT            t_string_table);
PROCEDURE nlst (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                p_dir   IN             VARCHAR2,
                p_list  OUT            t_string_table);
PROCEDURE rename (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                  p_from  IN             VARCHAR2,
                  p_to    IN             VARCHAR2);
PROCEDURE delete (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                  p_file  IN             VARCHAR2);
PROCEDURE mkdir (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                 p_dir   IN             VARCHAR2);
PROCEDURE rmdir (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                 p_dir   IN             VARCHAR2);
PROCEDURE convert_crlf (p_status  IN  BOOLEAN);
END ftp;
Package Body

create or replace
PACKAGE BODY ftp AS
g_reply         t_string_table := t_string_table();
g_binary        BOOLEAN := TRUE;
g_debug         BOOLEAN := TRUE;
g_convert_crlf  BOOLEAN := TRUE;
PROCEDURE debug (p_text  IN  VARCHAR2);
-- --------------------------------------------------------------------------
FUNCTION login (p_host    IN  VARCHAR2,
                p_port    IN  VARCHAR2,
                p_user    IN  VARCHAR2,
                p_pass    IN  VARCHAR2,
                p_timeout IN  NUMBER := NULL)
  RETURN UTL_TCP.connection IS
-- --------------------------------------------------------------------------
  l_conn  UTL_TCP.connection;
BEGIN
  g_reply.delete;
  l_conn := UTL_TCP.open_connection(p_host, p_port, tx_timeout => p_timeout);
  get_reply (l_conn);
  send_command(l_conn, 'USER ' || p_user);
  send_command(l_conn, 'PASS ' || p_pass);
  RETURN l_conn;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
FUNCTION get_passive (p_conn  IN OUT NOCOPY  UTL_TCP.connection)
  RETURN UTL_TCP.connection IS
-- --------------------------------------------------------------------------
  l_conn    UTL_TCP.connection;
  l_reply   VARCHAR2(32767);
  l_host    VARCHAR(100);
  l_port1   NUMBER(10);
  l_port2   NUMBER(10);
BEGIN
  send_command(p_conn, 'PASV');
  l_reply := g_reply(g_reply.last);
  l_reply := REPLACE(SUBSTR(l_reply, INSTR(l_reply, '(') + 1, (INSTR(l_reply, ')')) - (INSTR(l_reply, '('))-1), ',', '.');
  l_host  := SUBSTR(l_reply, 1, INSTR(l_reply, '.', 1, 4)-1);
  l_port1 := TO_NUMBER(SUBSTR(l_reply, INSTR(l_reply, '.', 1, 4)+1, (INSTR(l_reply, '.', 1, 5)-1) - (INSTR(l_reply, '.', 1, 4))));
  l_port2 := TO_NUMBER(SUBSTR(l_reply, INSTR(l_reply, '.', 1, 5)+1));
  l_conn := utl_tcp.open_connection(l_host, 256 * l_port1 + l_port2);
  return l_conn;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE logout(p_conn   IN OUT NOCOPY  UTL_TCP.connection,
                 p_reply  IN             BOOLEAN := TRUE) AS
-- --------------------------------------------------------------------------
BEGIN
  send_command(p_conn, 'QUIT', p_reply);
  UTL_TCP.close_connection(p_conn);
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE send_command (p_conn     IN OUT NOCOPY  UTL_TCP.connection,
                        p_command  IN             VARCHAR2,
                        p_reply    IN             BOOLEAN := TRUE) IS
-- --------------------------------------------------------------------------
  l_result  PLS_INTEGER;
BEGIN
  l_result := UTL_TCP.write_line(p_conn, p_command);
  -- If you get ORA-29260 after the PASV call, replace the above line with the following line.
  -- l_result := UTL_TCP.write_text(p_conn, p_command || utl_tcp.crlf, length(p_command || utl_tcp.crlf));
  IF p_reply THEN
    get_reply(p_conn);
  END IF;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE get_reply (p_conn  IN OUT NOCOPY  UTL_TCP.connection) IS
-- --------------------------------------------------------------------------
  l_reply_code  VARCHAR2(3) := NULL;
BEGIN
  LOOP
    g_reply.extend;
    g_reply(g_reply.last) := UTL_TCP.get_line(p_conn, TRUE);
    debug(g_reply(g_reply.last));
    IF l_reply_code IS NULL THEN
      l_reply_code := SUBSTR(g_reply(g_reply.last), 1, 3);
    END IF;
    IF SUBSTR(l_reply_code, 1, 1) IN ('4', '5') THEN
      RAISE_APPLICATION_ERROR(-20000, g_reply(g_reply.last));
    ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND
           SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN
      EXIT;
    END IF;
  END LOOP;
EXCEPTION
  WHEN UTL_TCP.END_OF_INPUT THEN
    NULL;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
FUNCTION get_local_ascii_data (p_dir   IN  VARCHAR2,
                               p_file  IN  VARCHAR2)
  RETURN CLOB IS
-- --------------------------------------------------------------------------
  l_bfile   BFILE;
  l_data    CLOB;
BEGIN
  DBMS_LOB.createtemporary (lob_loc => l_data,
                            cache   => TRUE,
                            dur     => DBMS_LOB.call);
  l_bfile := BFILENAME(p_dir, p_file);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  IF DBMS_LOB.getlength(l_bfile) > 0 THEN
    DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
  END IF;
  DBMS_LOB.fileclose(l_bfile);
  RETURN l_data;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
FUNCTION get_local_binary_data (p_dir   IN  VARCHAR2,
                                p_file  IN  VARCHAR2)
  RETURN BLOB IS
-- --------------------------------------------------------------------------
  l_bfile   BFILE;
  l_data    BLOB;
BEGIN
  DBMS_LOB.createtemporary (lob_loc => l_data,
                            cache   => TRUE,
                            dur     => DBMS_LOB.call);
  l_bfile := BFILENAME(p_dir, p_file);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  IF DBMS_LOB.getlength(l_bfile) > 0 THEN
    DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
  END IF;
  DBMS_LOB.fileclose(l_bfile);
  RETURN l_data;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
FUNCTION get_remote_ascii_data (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                p_file  IN             VARCHAR2)
  RETURN CLOB IS
-- --------------------------------------------------------------------------
  l_conn    UTL_TCP.connection;
  l_amount  PLS_INTEGER;
  l_buffer  VARCHAR2(32767);
  l_data    CLOB;
BEGIN
  DBMS_LOB.createtemporary (lob_loc => l_data,
                            cache   => TRUE,
                            dur     => DBMS_LOB.call);
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'RETR ' || p_file, TRUE);
  --logout(l_conn, FALSE);
  BEGIN
    LOOP
      l_amount := UTL_TCP.read_text (l_conn, l_buffer, 32767);
      DBMS_LOB.writeappend(l_data, l_amount, l_buffer);
    END LOOP;
  EXCEPTION
    WHEN UTL_TCP.END_OF_INPUT THEN
      NULL;
    WHEN OTHERS THEN
      NULL;
  END;
  UTL_TCP.close_connection(l_conn);
  get_reply(p_conn);
  RETURN l_data;
EXCEPTION
  WHEN OTHERS THEN
    UTL_TCP.close_connection(l_conn);
    RAISE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
FUNCTION get_remote_binary_data (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                 p_file  IN             VARCHAR2)
  RETURN BLOB IS
-- --------------------------------------------------------------------------
  l_conn    UTL_TCP.connection;
  l_amount  PLS_INTEGER;
  l_buffer  RAW(32767);
  l_data    BLOB;
BEGIN
  DBMS_LOB.createtemporary (lob_loc => l_data,
                            cache   => TRUE,
                            dur     => DBMS_LOB.call);
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'RETR ' || p_file, TRUE);
  BEGIN
    LOOP
      l_amount := UTL_TCP.read_raw (l_conn, l_buffer, 32767);
      DBMS_LOB.writeappend(l_data, l_amount, l_buffer);
    END LOOP;
  EXCEPTION
    WHEN UTL_TCP.END_OF_INPUT THEN
      NULL;
    WHEN OTHERS THEN
      NULL;
  END;
  UTL_TCP.close_connection(l_conn);
  get_reply(p_conn);
  RETURN l_data;
EXCEPTION
  WHEN OTHERS THEN
    UTL_TCP.close_connection(l_conn);
    RAISE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE put_local_ascii_data (p_data  IN  CLOB,
                                p_dir   IN  VARCHAR2,
                                p_file  IN  VARCHAR2) IS
-- --------------------------------------------------------------------------
  l_out_file  UTL_FILE.file_type;
  l_buffer    VARCHAR2(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_clob_len  INTEGER;
BEGIN
  l_clob_len := DBMS_LOB.getlength(p_data);
  l_out_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);
  WHILE l_pos <= l_clob_len LOOP
    DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer);
    IF g_convert_crlf THEN
      l_buffer := REPLACE(l_buffer, CHR(13), NULL);
    END IF;
    UTL_FILE.put(l_out_file, l_buffer);
    UTL_FILE.fflush(l_out_file);
    l_pos := l_pos + l_amount;
  END LOOP;
  UTL_FILE.fclose(l_out_file);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_out_file) THEN
      UTL_FILE.fclose(l_out_file);
    END IF;
    RAISE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE put_local_binary_data (p_data  IN  BLOB,
                                 p_dir   IN  VARCHAR2,
                                 p_file  IN  VARCHAR2) IS
-- --------------------------------------------------------------------------
  l_out_file  UTL_FILE.file_type;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  l_blob_len := DBMS_LOB.getlength(p_data);
  l_out_file := UTL_FILE.fopen(p_dir, p_file, 'wb', 32767);
  WHILE l_pos <= l_blob_len LOOP
    DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(l_out_file, l_buffer, TRUE);
    UTL_FILE.fflush(l_out_file);
    l_pos := l_pos + l_amount;
  END LOOP;
  UTL_FILE.fclose(l_out_file);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_out_file) THEN
      UTL_FILE.fclose(l_out_file);
    END IF;
    RAISE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE put_remote_ascii_data (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                 p_file  IN             VARCHAR2,
                                 p_data  IN             CLOB) IS
-- --------------------------------------------------------------------------
  l_conn      UTL_TCP.connection;
  l_result    PLS_INTEGER;
  l_buffer    VARCHAR2(32767);
  l_amount    BINARY_INTEGER := 32767; -- Switch to 10000 (or use binary) if you get ORA-06502 from this line.
  l_pos       INTEGER := 1;
  l_clob_len  INTEGER;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'STOR ' || p_file, TRUE);
  l_clob_len := DBMS_LOB.getlength(p_data);
  WHILE l_pos <= l_clob_len LOOP
    DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
    IF g_convert_crlf THEN
      l_buffer := REPLACE(l_buffer, CHR(13), NULL);
    END IF;
    l_result := UTL_TCP.write_text(l_conn, l_buffer, LENGTH(l_buffer));
    UTL_TCP.flush(l_conn);
    l_pos := l_pos + l_amount;
  END LOOP;
  UTL_TCP.close_connection(l_conn);
  -- The following line allows some people to make multiple calls from one connection.
  -- It causes the operation to hang for me, hence it is commented out by default.
  -- get_reply(p_conn);
EXCEPTION
  WHEN OTHERS THEN
    UTL_TCP.close_connection(l_conn);
    RAISE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE put_remote_binary_data (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                  p_file  IN             VARCHAR2,
                                  p_data  IN             BLOB) IS
-- --------------------------------------------------------------------------
  l_conn      UTL_TCP.connection;
  l_result    PLS_INTEGER;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'STOR ' || p_file, TRUE);
  l_blob_len := DBMS_LOB.getlength(p_data);
  WHILE l_pos <= l_blob_len LOOP
    DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
    l_result := UTL_TCP.write_raw(l_conn, l_buffer, l_amount);
    UTL_TCP.flush(l_conn);
    l_pos := l_pos + l_amount;
  END LOOP;
  UTL_TCP.close_connection(l_conn);
  -- The following line allows some people to make multiple calls from one connection.
  -- It causes the operation to hang for me, hence it is commented out by default.
  -- get_reply(p_conn);
EXCEPTION
  WHEN OTHERS THEN
    UTL_TCP.close_connection(l_conn);
    RAISE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE get (p_conn       IN OUT NOCOPY  UTL_TCP.connection,
               p_from_file  IN             VARCHAR2,
               p_to_dir     IN             VARCHAR2,
               p_to_file    IN             VARCHAR2) AS
-- --------------------------------------------------------------------------
BEGIN
  IF g_binary THEN
    put_local_binary_data(p_data  => get_remote_binary_data (p_conn, p_from_file),
                          p_dir   => p_to_dir,
                          p_file  => p_to_file);
  ELSE
    put_local_ascii_data(p_data  => get_remote_ascii_data (p_conn, p_from_file),
                         p_dir   => p_to_dir,
                         p_file  => p_to_file);
  END IF;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE put (p_conn       IN OUT NOCOPY  UTL_TCP.connection,
               p_from_dir   IN             VARCHAR2,
               p_from_file  IN             VARCHAR2,
               p_to_file    IN             VARCHAR2) AS
-- --------------------------------------------------------------------------
BEGIN
  IF g_binary THEN
    put_remote_binary_data(p_conn => p_conn,
                           p_file => p_to_file,
                           p_data => get_local_binary_data(p_from_dir, p_from_file));
  ELSE
    put_remote_ascii_data(p_conn => p_conn,
                          p_file => p_to_file,
                          p_data => get_local_ascii_data(p_from_dir, p_from_file));
  END IF;
  get_reply(p_conn);
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE get_direct (p_conn       IN OUT NOCOPY  UTL_TCP.connection,
                      p_from_file  IN             VARCHAR2,
                      p_to_dir     IN             VARCHAR2,
                      p_to_file    IN             VARCHAR2) IS
-- --------------------------------------------------------------------------
  l_conn        UTL_TCP.connection;
  l_out_file    UTL_FILE.file_type;
  l_amount      PLS_INTEGER;
  l_buffer      VARCHAR2(32767);
  l_raw_buffer  RAW(32767);
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'RETR ' || p_from_file, TRUE);
  IF g_binary THEN
    l_out_file := UTL_FILE.fopen(p_to_dir, p_to_file, 'wb', 32767);
  ELSE
    l_out_file := UTL_FILE.fopen(p_to_dir, p_to_file, 'w', 32767);
  END IF;
  BEGIN
    LOOP
      IF g_binary THEN
        l_amount := UTL_TCP.read_raw (l_conn, l_raw_buffer, 32767);
        UTL_FILE.put_raw(l_out_file, l_raw_buffer, TRUE);
      ELSE
        l_amount := UTL_TCP.read_text (l_conn, l_buffer, 32767);
        IF g_convert_crlf THEN
          l_buffer := REPLACE(l_buffer, CHR(13), NULL);
        END IF;
        UTL_FILE.put(l_out_file, l_buffer);
      END IF;
      UTL_FILE.fflush(l_out_file);
    END LOOP;
  EXCEPTION
    WHEN UTL_TCP.END_OF_INPUT THEN
      NULL;
    WHEN OTHERS THEN
      NULL;
  END;
  UTL_FILE.fclose(l_out_file);
  UTL_TCP.close_connection(l_conn);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_out_file) THEN
      UTL_FILE.fclose(l_out_file);
    END IF;
    RAISE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE put_direct (p_conn       IN OUT NOCOPY  UTL_TCP.connection,
                      p_from_dir   IN             VARCHAR2,
                      p_from_file  IN             VARCHAR2,
                      p_to_file    IN             VARCHAR2) IS
-- --------------------------------------------------------------------------
  l_conn        UTL_TCP.connection;
  l_bfile       BFILE;
  l_result      PLS_INTEGER;
  l_amount      PLS_INTEGER := 32767;
  l_raw_buffer  RAW(32767);
  l_len         NUMBER;
  l_pos         NUMBER := 1;
  ex_ascii      EXCEPTION;
BEGIN
  IF NOT g_binary THEN
    RAISE ex_ascii;
  END IF;
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'STOR ' || p_to_file, TRUE);
  l_bfile := BFILENAME(p_from_dir, p_from_file);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  l_len := DBMS_LOB.getlength(l_bfile);
  WHILE l_pos <= l_len LOOP
    DBMS_LOB.READ (l_bfile, l_amount, l_pos, l_raw_buffer);
    debug(l_amount);
    l_result := UTL_TCP.write_raw(l_conn, l_raw_buffer, l_amount);
    l_pos := l_pos + l_amount;
  END LOOP;
  DBMS_LOB.fileclose(l_bfile);
  UTL_TCP.close_connection(l_conn);
EXCEPTION
  WHEN ex_ascii THEN
    RAISE_APPLICATION_ERROR(-20000, 'PUT_DIRECT not available in ASCII mode.');
  WHEN OTHERS THEN
    IF DBMS_LOB.fileisopen(l_bfile) = 1 THEN
      DBMS_LOB.fileclose(l_bfile);
    END IF;
    RAISE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE help (p_conn  IN OUT NOCOPY  UTL_TCP.connection) AS
-- --------------------------------------------------------------------------
BEGIN
  send_command(p_conn, 'HELP', TRUE);
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE ascii (p_conn  IN OUT NOCOPY  UTL_TCP.connection) AS
-- --------------------------------------------------------------------------
BEGIN
  send_command(p_conn, 'TYPE A', TRUE);
  g_binary := FALSE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE binary (p_conn  IN OUT NOCOPY  UTL_TCP.connection) AS
-- --------------------------------------------------------------------------
BEGIN
  send_command(p_conn, 'TYPE I', TRUE);
  g_binary := TRUE;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE list (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                p_dir   IN             VARCHAR2,
                p_list  OUT            t_string_table) AS
-- --------------------------------------------------------------------------
  l_conn        UTL_TCP.connection;
  l_list        t_string_table := t_string_table();
  l_reply_code  VARCHAR2(3) := NULL;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'LIST ' || p_dir, TRUE);
  BEGIN
    LOOP
      l_list.extend;
      l_list(l_list.last) := UTL_TCP.get_line(l_conn, TRUE);
      debug(l_list(l_list.last));
      IF l_reply_code IS NULL THEN
        l_reply_code := SUBSTR(l_list(l_list.last), 1, 3);
      END IF;
      IF SUBSTR(l_reply_code, 1, 1) IN ('4', '5') THEN
        RAISE_APPLICATION_ERROR(-20000, l_list(l_list.last));
      ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND
             SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN
        EXIT;
      END IF;
    END LOOP;
  EXCEPTION
    WHEN UTL_TCP.END_OF_INPUT THEN
      NULL;
  END;
  l_list.delete(l_list.last);
  p_list := l_list;
  utl_tcp.close_connection(l_conn);
  get_reply (p_conn);
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE nlst (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                p_dir   IN             VARCHAR2,
                 p_list  OUT            t_string_table) AS
-- --------------------------------------------------------------------------
  l_conn        UTL_TCP.connection;
  l_list        t_string_table := t_string_table();
  l_reply_code  VARCHAR2(3) := NULL;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'NLST ' || p_dir, TRUE);
  BEGIN
    LOOP
      l_list.extend;
      l_list(l_list.last) := UTL_TCP.get_line(l_conn, TRUE);
      debug(l_list(l_list.last));
      IF l_reply_code IS NULL THEN
        l_reply_code := SUBSTR(l_list(l_list.last), 1, 3);
      END IF;
      IF SUBSTR(l_reply_code, 1, 1) IN ('4', '5') THEN
        RAISE_APPLICATION_ERROR(-20000, l_list(l_list.last));
      ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND
             SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN
        EXIT;
      END IF;
    END LOOP;
  EXCEPTION
    WHEN UTL_TCP.END_OF_INPUT THEN
      NULL;
  END;
  l_list.delete(l_list.last);
  p_list := l_list;
  utl_tcp.close_connection(l_conn);
  get_reply (p_conn);
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE rename (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                  p_from  IN             VARCHAR2,
                  p_to    IN             VARCHAR2) AS
-- --------------------------------------------------------------------------
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'RNFR ' || p_from, TRUE);
  send_command(p_conn, 'RNTO ' || p_to, TRUE);
  logout(l_conn, FALSE);
END rename;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE delete (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                  p_file  IN             VARCHAR2) AS
-- --------------------------------------------------------------------------
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'DELE ' || p_file, TRUE);
  logout(l_conn, FALSE);
END delete;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE mkdir (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                 p_dir   IN             VARCHAR2) AS
-- --------------------------------------------------------------------------
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'MKD ' || p_dir, TRUE);
  logout(l_conn, FALSE);
END mkdir;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE rmdir (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                 p_dir   IN             VARCHAR2) AS
-- --------------------------------------------------------------------------
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'RMD ' || p_dir, TRUE);
  logout(l_conn, FALSE);
END rmdir;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE convert_crlf (p_status  IN  BOOLEAN) AS
-- --------------------------------------------------------------------------
BEGIN
  g_convert_crlf := p_status;
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE debug (p_text  IN  VARCHAR2) IS
-- --------------------------------------------------------------------------
BEGIN
  IF g_debug THEN
    DBMS_OUTPUT.put_line(SUBSTR(p_text, 1, 255));
  END IF;
END;
-- --------------------------------------------------------------------------
END ftp;


Once the API is loaded into the appropriate schema simple FTP commands can be initiated as follows.

-- Retrieve an ASCII file from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.ascii(p_conn => l_conn);
  ftp.get(p_conn      => l_conn,
          p_from_file => '/u01/app/oracle/test.txt',
          p_to_dir    => 'MY_DOCS',
          p_to_file   => 'test_get.txt');
  ftp.logout(l_conn);
END;
/

-- Send an ASCII file to a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.ascii(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'test_get.txt',
          p_to_file   => '/u01/app/oracle/test_put.txt');
  ftp.logout(l_conn);
END;
/

-- Retrieve a binary file from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.binary(p_conn => l_conn);
  ftp.get(p_conn      => l_conn,
          p_from_file => '/u01/app/oracle/product/9.2.0.1.0/sysman/reporting/gif/jobs.gif',
          p_to_dir    => 'MY_DOCS',
          p_to_file   => 'jobs_get.gif');
  ftp.logout(l_conn);
END;
/

-- Send a binary file to a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.binary(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'jobs_get.gif',
          p_to_file   => '/u01/app/oracle/jobs_put.gif');
  ftp.logout(l_conn);
END;
/

-- Get a directory listing from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
  l_list  ftp.t_string_table;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.list(p_conn   => l_conn,
           p_dir   => '/u01/app/oracle',
           p_list  => l_list);
  ftp.logout(l_conn);
 
  IF l_list.COUNT > 0 THEN
    FOR i IN l_list.first .. l_list.last LOOP
      DBMS_OUTPUT.put_line(i || ': ' || l_list(i));
    END LOOP;
  END IF;
END;
/

-- Get a directory listing (file names only) from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
  l_list  ftp.t_string_table;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.nlst(p_conn   => l_conn,
           p_dir   => '/u01/app/oracle',
           p_list  => l_list);
  ftp.logout(l_conn);
 
  IF l_list.COUNT > 0 THEN
    FOR i IN l_list.first .. l_list.last LOOP
      DBMS_OUTPUT.put_line(i || ': ' || l_list(i));
    END LOOP;
  END IF;
END;
/

-- Rename a file on a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.rename(p_conn => l_conn,
             p_from => '/u01/app/oracle/dba/shutdown',
             p_to   => '/u01/app/oracle/dba/shutdown.old');
  ftp.logout(l_conn);
END;
/

-- Delete a file on a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.delete(p_conn => l_conn,
             p_file => '/u01/app/oracle/dba/temp.txt');
  ftp.logout(l_conn);
END;
/

-- Create a directory on a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.mkdir(p_conn => l_conn,
            p_dir => '/u01/app/oracle/test');
  ftp.logout(l_conn);
END;
/

-- Remove a directory from a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.rmdir(p_conn => l_conn,
            p_dir  => '/u01/app/oracle/test');
  ftp.logout(l_conn);
END;
/

you need to configure an access control list (ACL) to allow UTL_TCP to access the network

Extract xml content of CDATA Using Stylesheets in Oracle - useful in Apex Applications

This code will extract XML elements in CDATA section of XML file using stylesheet.

DECLARE
  P_XML XMLTYPE;
  P_STYLESHEET XMLTYPE;
  v_Return XMLTYPE;
  v_test CLOB;
BEGIN
  P_XML := xmltype(
  '<log>
<details> 
<![CDATA[
<inputVariable><part name="request" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance%22%3E%3Cns1:CreateInvoiceRequest xmlns:ns1="urn:au:gov:vic:sbs:services:ar:create:1"><ns1:ConsumerInformation><ns1:OrgId>DPI</ns1:OrgId><ns1:ContentSource>RRAM</ns1:ContentSource></ns1:ConsumerInformation><ns1:Invoice><ns2:ExternalCustomerReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">321831</ns2:ExternalCustomerReference><ns2:OracleCustomerReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">4192364</ns2:OracleCustomerReference><ns2:TransactionType xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">FV - Application</ns2:TransactionType><ns2:ExternalTransactionReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">90000134</ns2:ExternalTransactionReference><ns2:TransactionDate xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">2012-03-23</ns2:TransactionDate><ns2:SalesPerson xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">Commercial Fishing Licences</ns2:SalesPerson><ns2:InvoiceCurrenyCode xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">AUD</ns2:InvoiceCurrenyCode><ns2:PaymentTerms xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">7 DAYS</ns2:PaymentTerms><ns2:PaymentMethod xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">BP</ns2:PaymentMethod><ns2:SendToMailhouse xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">N</ns2:SendToMailhouse><ns2:LineItems xmlns:ns2="urn:au:gov:vic:sbs:transaction:1"><ns2:LineItemId>10</ns2:LineItemId><ns2:LineType>LINE</ns2:LineType><ns2:MemoLine>Commercial Fishing</ns2:MemoLine><ns2:Description>Aqua (CL - Eels) Licence</ns2:Description><ns2:Quantity>1.0</ns2:Quantity><ns2:Amount>1835.53</ns2:Amount><ns2:Taxed>0</ns2:Taxed><ns2:TaxRate>0</ns2:TaxRate><ns2:TaxAmount>0.0</ns2:TaxAmount></ns2:LineItems></ns1:Invoice></ns1:CreateInvoiceRequest></part></inputVariable>]]>
</details> 
<details> 
<![CDATA[
<inputVariable><part name="request" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance%22%3E%3Cns1:CreateInvoiceRequest xmlns:ns1="urn:au:gov:vic:sbs:services:ar:create:1"><ns1:ConsumerInformation><ns1:OrgId>DPI</ns1:OrgId><ns1:ContentSource>RRAM</ns1:ContentSource></ns1:ConsumerInformation><ns1:Invoice><ns2:ExternalCustomerReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">321831</ns2:ExternalCustomerReference><ns2:OracleCustomerReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">4192364</ns2:OracleCustomerReference><ns2:TransactionType xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">FV - Application</ns2:TransactionType><ns2:ExternalTransactionReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">90000134</ns2:ExternalTransactionReference><ns2:TransactionDate xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">2012-03-23</ns2:TransactionDate><ns2:SalesPerson xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">Commercial Fishing Licences</ns2:SalesPerson><ns2:InvoiceCurrenyCode xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">AUD</ns2:InvoiceCurrenyCode><ns2:PaymentTerms xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">7 DAYS</ns2:PaymentTerms><ns2:PaymentMethod xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">BP</ns2:PaymentMethod><ns2:SendToMailhouse xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">N</ns2:SendToMailhouse><ns2:LineItems xmlns:ns2="urn:au:gov:vic:sbs:transaction:1"><ns2:LineItemId>10</ns2:LineItemId><ns2:LineType>LINE</ns2:LineType><ns2:MemoLine>Commercial Fishing</ns2:MemoLine><ns2:Description>Aqua (CL - Eels) Licence</ns2:Description><ns2:Quantity>1.0</ns2:Quantity><ns2:Amount>1835.53</ns2:Amount><ns2:Taxed>0</ns2:Taxed><ns2:TaxRate>0</ns2:TaxRate><ns2:TaxAmount>0.0</ns2:TaxAmount></ns2:LineItems></ns1:Invoice></ns1:CreateInvoiceRequest></part></inputVariable>]]>
</details>
</log>'
  );
  P_STYLESHEET := xmltype('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<log> 
<xsl:for-each select="//details/text()"> 
<sections>  
<xsl:value-of select="."/> 
</sections> 
</xsl:for-each>
</log>  
</xsl:template>
</xsl:stylesheet>
');
  v_Return     := p_xml.transform (p_stylesheet);
  dbms_output.put_line( REPLACE(REPLACE(REPLACE (v_Return.getClobVal(),'&lt;','<'),'&gt;','>'),'&quot;','"'));
END;

Tools to convert .Class files to .Java files

Converting .Class files to .Java file is very important step in OAFramework Extensions. Below are the most used tools to perform this.


Tool : JAD  
             
    Download Link for JAD
  

Command to Convert all class files to java files under all subdirectories with Original Folder Structure

jad -o -r -sjava -dsrc c:/jadnt158/Samp/**/*.class

Command to delete all class files in subdirectories

Del /s *.class –

Tool: FrontEnd                


    Download Link for FrontEnd


Tool: Cavaj

                    

    Download Link for CAVAJ

Important Profile Options realted to OA Framework

Personalize Self-Service Defn / FND_CUSTOM_OA_DEFINTION

This is intended for system administrators who wish to personalize regions at the localization, site, verticalization, org and responsibility levels. On enabling this profile option for the administrator, every OA Framework page will contain a global Personalize button. By clicking on this global button, the administrator can personalize the regions available on that page.

Disable Self-service Personal / FND_DISABLE_OA_CUSTOMIZATIONS

This is a system profile option specifically created for use by Oracle Support. You can set this profile option to "Yes" or "No" at the site or application level. If this system profile option is set to Yes, any personalizations made by the customer, regardless of the level at which the personalizations were made, will not be applied. All pages using OA Framework will now display the regions based on their original definitions.

Note: When this profile is set to "Yes", a warning message that all personalizations are disabled is displayed on every page to which a user navigates.

FND: Personalization Region Link Enabled / FND_PERSONALIZATION_REGION_LINK_ENABLED

Enables the "Personalize Region" links on a page if the Personalize Self-Service Defn / FND_CUSTOM_OA_DEFINTION profile is set to Yes.

Fnd Xliff Export Root Path / FND_XLIFF_EXPORT_ROOT_PATH

Use this profile option to set the root path used to generate the full path where the Xliff files are exported to when users extract their translated personalizations using the Extract Translation Files page in OA Personalization Framework. The permissions for the root path directory that you specify must be set to read, write, create for all users, using chmod 777 [dir_path].

Xliff Import Root Path / FND_XLIFF_IMPORT_ROOT_PATH

Use this profile option to set the root path used to derive the full path from where the Xliff files are uploaded when users use the Upload Translations page in OA Personalization Framework to upload translated personalizations.

FND: Personalization Document Root Path / FND_PERZ_DOC_ROOT_PATH

Use this profile option to define the root path where personalizations documents are exported to or imported from when users use the Database page or the File System page of the Functional Administrator responsibility's Document Manager, respectively.

Recommended this profile set to the $APPL_TOP staging area:

              $APP_TOP/<CompanyIdentifier>/

             <CustomProductShortName>/

            <product-version>/mds/webui

of the current deployed environment, where personalization documents are to be imported from or exported to. This profile option should be set at the Site level.

FND: Diagnostics / FND_DIAGNOSTICS

Setting this to “Yes” causes a Diagnostics global button to render on every page. Select this button to view the log messages for the page. Enabling this profile also automatically renders the "About this page" link at the bottom of every OA Framework page.

Useful Scripts in OA Framework

Upload a Single PG.xml file

From Command Prompt

·         <JDEV_DIR>\jdevbin\jdev\bin\import <JDEV_DIR>\jdevbin\jdev\myclasses\xxt\oracle\apps\pon\registration\webui\XXTSupplierRegistrationPG.xml -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<DB_HOST_NAME>)(PORT=<DB_PORT>))(CONNECT_DATA=(SID=<DB_SID>)))" -rootDir <JDEV_DIR>\jdevbin\jdev\myclasses\ -rootPackage /

From Unix Box

·         java oracle.jrad.tools.xml.importer.XMLImporter CarLoanViewOnlyPG.xml  -username <user name> -password <password> -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=<port number>))(CONNECT_DATA=(SID=<SID>)))" -rootdir ./ -rootPackage /xxmhp/oracle/apps/per/carloan/webui

Upload multiple PG.xml files

·         <JDEV_DIR>/jdevbin/jdev/bin/import <JDEV_DIR>/jdevbin/jdev/myclasses /oracle/apps/xxpo/pdt/ordering/webui -rootDir <JDEV_DIR>/jdevbin/jdev/myclasses / -username apps -password <APPS_PASSWORD> -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<DB_HOSTNAME>)(PORT=<DB_PORT>))(CONNECT_DATA=(SID=<DB_SID>)))" -includeSubpackages -jdk13 -mmddir <JDEV_DIR>/jdevbin/jdev/lib/ext/jrad/config/mmd -rootPackage /

When you have the Substitutions upload the JPX using the following script from the UNIX prompt

From Unix Box

·         java oracle.jrad.tools.xml.importer.JPXImporter XXMHPIprocChargeAccountDesc.jpx -username <Database User Id> -password <Password> -dbconnection "(description=(address_list=(address=(protocol=tcp)(host=<Host Name>)(port=<Port Number>)))(connect_data =(sid=<SID>)))"

Download Components From Server (Important For Standard Pages Downloading)

From Command Prompt

·         export /custom/oracle/apps/apc/webui/dseInvoiceReviewRn -rootdir U:\viji\Jdeveloepr9i\jdevhome\jdev\myprojects -username apps -password applsys97 -dbconnection "(description = (address_list = (address = (community = tcp.world)(protocol = tcp)(host =d00725)(port = 1525)))(connect_data = (sid = DEVF)))"

From Unix Box

·         java oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/rrs/site/user/webui/CreateMethodforSiteTempPG -rootdir $JAVA_TOP/chola -username apps -password apps -dbconnection "(description = (address_list = (address = (community = tcp.world)(protocol = tcp)(host = ) (port = )))(connect_data = (sid = )))"

View  Documents

Begin

Jdr_utils.printDocument(‘/oracle/apps/pon/award/completion/webui/ponCompleteAward2PG’);

End;

To find the personalized file path for a particular PG.xml

begin

jdr_utils.listCustomizations('/oracle/apps/fnd/framework/navigate/webui/AppsNavigateMobilePG');

end;

To download the personalized file, first run the above script in SQL, that will display the full path for the personalized file, then run the below command

Begin

Jdr_utils.printDocument (‘/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/AppsNavigateMobilePG’);

End;

DELETE A DOCUMENT OR DELETE A PERSONALIZATION OR     DELETE AN EXTENSION OR A SUBSTITUTION

The title says it all. Once you have found the exact name of the document with its full path [using jdr_utils.listdocuments], you can then delete the same by using command below.

For example to delete the Application Module substitution, use command

DECLARE
BEGIN
jdr_utils.deletedocument(p_document => '/oracle/apps/per/irc/candidateSelfService/server/customizations/site/0/VisitorAM');
END;

To create translations for the personalized page or for a custom page, download the corresponding xlf file for the personalized page or the custom page. Then change the prompts in the destination language and import the page again.

Extract XLF file for a particular directory (For arabic language, for a different language, change accordingly).

·         <JDEV_DIR>\jdevbin\jdev\bin\xliffextract /xxt/oracle/apps/pon/registration/webui -includeSubpackages -mmd_dir D:\Jdev1150CU2\jdevbin\jdev\lib\ext\jrad\config\mmd -root D:\Temp\XLIF\registration -xliff_dir D:\Temp\XLIF\registration -source db -username apps -password <APPS_PASSWORD> -dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=<DB_HOSTNAME>)(PORT=<DB_PORT>))(CONNECT_DATA=(SID=<DB_SID>)))" -languages ar-AE

Extract XLF file for a particular file(For arabic language, for a different language, change accordingly).

·         <JDEV_DIR>\jdevbin\jdev\bin\xliffextract /oracle/apps/pon/outcome/creation/webui/customizations/site/0/ponCreatePOPG -root D:\temp -xliff_dir D:\temp -mmd_dir <JDEV_HOME>\jdevbin\jdev\lib\ext\jrad\config\mmd\ -source db -username apps -password <APPS_PASSWORD> -dbconnection " (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL= TCP)(HOST = <DB_HOSTNAME>)(PORT = <DB_PORT>)) ) (CONNECT_DATA = (SERVICE_NAME = <DB_SID>) ) )" -languages ar-AE

To find the personalization for a particular object or for the whole system or for a particular package.

Login with the user who has Functional Administrator responsibility, you search the Object you have substituted (Functional Administrator=>Personalization=>Import/Export).

Search with a particular path, Eg. /oracle/apps/icx will give all the personalization under the path.

If the the profile option “FND: Personalization Document Root Path” set properly, the personalization can be exported to the system directly.