FTP integration in oracle apex
FTP integration in oracle apex
1. FTP Grants Required
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
ACL => 'ftp.xml',
DESCRIPTION => 'Network Access Control for HRDEPT',
PRINCIPAL => 'HRDEPT',--Schema name in which app is installed
IS_GRANT => TRUE,
PRIVILEGE => 'connect');
END;
/
--------------------------------------------------------------------------------
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
ACL => 'ftp.xml',
PRINCIPAL => 'HRDEPT',
IS_GRANT => TRUE,
PRIVILEGE => 'connect',
POSITION => NULL);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
ACL => 'ftp.xml',
PRINCIPAL => 'APEX_050100',
IS_GRANT => TRUE,
PRIVILEGE => 'connect',
POSITION => NULL);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
ACL => 'ftp.xml',
PRINCIPAL => 'HRDEPT',
IS_GRANT => TRUE,
PRIVILEGE => 'resolve');
COMMIT;
END;
/
--------------------------------------------------------------------------------
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
ACL => 'ftp.xml',
HOST => '192.168.2.150',--FTP Host Name
LOWER_PORT => NULL,
UPPER_PORT => NULL);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
ACL => 'ftp.xml',
HOST => '192.168.2.150',
LOWER_PORT => '9999',
UPPER_PORT => '9999');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
ACL => 'ftp.xml',
HOST => '127.0.0.1', -- Local host IP
LOWER_PORT => '8080', --(this is jasper port)
UPPER_PORT => '8080'); --(this is jasper port)
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
ACL => 'ftp.xml',
HOST => '192.168.2.150',
LOWER_PORT => '22',
UPPER_PORT => '22');
COMMIT;
END;
/
SELECT * FROM DBA_NETWORK_ACLS;2. FTP Connection Package
CREATE OR REPLACE PACKAGE FTP_UTIL_PKG
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_TO_FILE IN VARCHAR2,
P_FILE IN BLOB DEFAULT NULL,
P_FILE_CLOB IN CLOB DEFAULT NULL,
P_FROM_DIR IN VARCHAR2 DEFAULT NULL,
P_FROM_FILE IN VARCHAR2 DEFAULT NULL);
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_UTIL_PKG;
-----------------------------------------Body-----------------------------------------------------
CREATE OR REPLACE PACKAGE BODY FTP_UTIL_PKG
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;
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_TO_FILE IN VARCHAR2,
P_FILE IN BLOB DEFAULT NULL,
P_FILE_CLOB IN CLOB DEFAULT NULL,
P_FROM_DIR IN VARCHAR2 DEFAULT NULL,
P_FROM_FILE IN VARCHAR2 DEFAULT NULL) AS
-- --------------------------------------------------------------------------
BEGIN
IF G_BINARY THEN
PUT_REMOTE_BINARY_DATA(P_CONN => P_CONN,
P_FILE => P_TO_FILE,
P_DATA => CASE WHEN P_FILE IS NOT NULL THEN P_FILE ELSE GET_LOCAL_BINARY_DATA(P_FROM_DIR, P_FROM_FILE)END);
ELSE
PUT_REMOTE_ASCII_DATA(P_CONN => P_CONN,
P_FILE => P_TO_FILE,
P_DATA => CASE WHEN P_FILE_CLOB IS NOT NULL THEN P_FILE_CLOB ELSE GET_LOCAL_ASCII_DATA(P_FROM_DIR, P_FROM_FILE)END);
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_UTIL_PKG;3.Download FTP Content
CREATE OR REPLACE PACKAGE HR_GET_FTP_IMAGES
AS
PROCEDURE GET_FILE(
P_ID IN NUMBER,
P_PATH_NAME IN VARCHAR2,
P_FILE OUT BLOB);
END HR_GET_FTP_IMAGES;
-----------------------------------------Package Body-----------------------------------------------------
CREATE OR REPLACE PACKAGE BODY HR_GET_FTP_IMAGES
AS
PROCEDURE GET_FILE(
P_ID IN NUMBER,
P_PATH_NAME IN VARCHAR2,
P_FILE OUT BLOB)
IS
L_CONN UTL_TCP.CONNECTION;
L_BLOB BLOB;
L_FTPHOST VARCHAR2(200);
L_FTPPORT VARCHAR2(200);
L_FTPUSERNAME VARCHAR2(200);
L_FTPPASSWORD VARCHAR2(200);
L_FTPPATH VARCHAR2(200);
L_FILENAME VARCHAR2(500);
L_MIMETYPE VARCHAR2(500);
BEGIN
SELECT VALUE INTO L_FTPHOST FROM HR_SYSTEM_PARAMETER WHERE NAME = 'FTP_HOST';
SELECT VALUE INTO L_FTPPORT FROM HR_SYSTEM_PARAMETER WHERE NAME = 'FTP_PORT';
SELECT VALUE INTO L_FTPUSERNAME FROM HR_SYSTEM_PARAMETER WHERE NAME = 'FTP_USERNAME';
SELECT VALUE INTO L_FTPPASSWORD FROM HR_SYSTEM_PARAMETER WHERE NAME = 'FTP_PASSWORD';
SELECT VALUE INTO L_FTPPATH FROM HR_SYSTEM_PARAMETER WHERE NAME = P_PATH_NAME;
SELECT FILENAME,MIME_TYPE INTO L_FILENAME,L_MIMETYPE FROM MYIMAGES_TBL WHERE ID=P_ID;
L_CONN := FTP_UTIL_PKG.LOGIN(L_FTPHOST, L_FTPPORT, L_FTPUSERNAME, L_FTPPASSWORD);
FTP_UTIL_PKG.BINARY(P_CONN => L_CONN);
L_BLOB := FTP_UTIL_PKG.GET_REMOTE_BINARY_DATA (L_CONN, L_FTPPATH||L_FILENAME);
FTP_UTIL_PKG.LOGOUT(L_CONN);
SYS.HTP.INIT;
SYS.OWA_UTIL.MIME_HEADER( 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', FALSE);
SYS.HTP.P('Content-length: ' || SYS.DBMS_LOB.GETLENGTH( L_BLOB ));
SYS.HTP.P('Content-Disposition: inline; filename="'||L_FILENAME||'"' );
SYS.OWA_UTIL.HTTP_HEADER_CLOSE;
P_FILE :=L_BLOB;
SYS.WPG_DOCLOAD.DOWNLOAD_FILE(L_BLOB);
APEX_APPLICATION.STOP_APEX_ENGINE;
EXCEPTION
WHEN OTHERS THEN
NULL;
END GET_FILE;
END HR_GET_FTP_IMAGES;
Comments
Post a Comment