SCRIPT: Backup an Oracle Table with PHP

Posted by in Oracle EBS
No Comments

The following code will query some data from the Oracle Database and will export them on a CSV file.
The webserver where this script is executed should have the OCI8 library installed.
The execution of this script can be also scheduled to have a periodic backup of a certain table.

The script below will extract from the system the forms used by each Oracle EBS user.


$ORACLE_USERNAME = 'RAC_ACCNT';
$ORACLE_PASSWORD = 'PASSWORD';
$ORACLE_SID = 'SID';

$ORACLE_CONNECT = OCILogon($ORACLE_USERNAME, $ORACLE_PASSWORD, $ORACLE_SID);

$time = date("H_i");
$day = date("d");
$month = strtoupper(date("m"));
$year = date("Y");
$today = $year . "-" . $month . "-" . $day;

// FND_LOGINS
$query_table_to_backup = "
SELECT DISTINCT
D.USER_NAME,
E.RESPONSIBILITY_NAME,
TO_CHAR(A.START_TIME,'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(A.END_TIME,'DD-MON-YYYY HH24:MI:SS'),
G.USER_FORM_NAME,
TO_CHAR(F.START_TIME,'DD-MON-YYYY HH24:MI:SS') FORM_START_TIME,
TO_CHAR(F.END_TIME,'DD-MON-YYYY HH24:MI:SS') FORM_END_TIME,
F.START_TIME FORM_START_TIME_ORDER
FROM
FND_LOGIN_RESPONSIBILITIES A,
FND_RESPONSIBILITY B,
FND_LOGINS C,
FND_USER D,
FND_RESPONSIBILITY_TL E,
FND_LOGIN_RESP_FORMS F,
FND_FORM_TL G
WHERE
A.RESPONSIBILITY_ID = B.RESPONSIBILITY_ID
AND A.RESPONSIBILITY_ID = E.RESPONSIBILITY_ID
AND B.RESPONSIBILITY_ID = E.RESPONSIBILITY_ID
AND A.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND C.LOGIN_ID = A.LOGIN_ID
AND A.LOGIN_ID = F.LOGIN_ID
AND C.LOGIN_ID = F.LOGIN_ID
AND C.USER_ID = D.USER_ID
AND G.FORM_ID = F.FORM_ID
AND E.LANGUAGE = 'US'
AND G.LANGUAGE = 'US'
AND B.APPLICATION_ID = G.APPLICATION_ID
AND A.START_TIME > SYSDATE - 30
ORDER BY FORM_START_TIME_ORDER DESC
";

$output_file = "output/" . $ORACLE_SID . "_FND_LOGINS_BACKUP_" . $today . "_" . $time . ".csv";
$output_name = $ORACLE_SID . "_FND_LOGINS_BACKUP_" . $today . "_" . $time . ".csv";
$fh = fopen($output_file, 'w') or die("can't open file");

$ORACLE_STATIC = OCIParse($ORACLE_CONNECT, $query_table_to_backup);
OCIExecute($ORACLE_STATIC, OCI_DEFAULT);

while ($PRINT_RECORD = oci_fetch_array ($ORACLE_STATIC, OCI_BOTH))
{
$stringData = $PRINT_RECORD[0] . ";" . $PRINT_RECORD[1] . ";" . $PRINT_RECORD[2] . ";" . $PRINT_RECORD[3] . ";" . $PRINT_RECORD[4] . ";" . $PRINT_RECORD[5] . ";" . $PRINT_RECORD[6] . ";" . $PRINT_RECORD[7] . ";" . $PRINT_RECORD[8] . ";" . $PRINT_RECORD[9] . ";" . $PRINT_RECORD[10] . ";" . $PRINT_RECORD[11] . ";" . $PRINT_RECORD[12] . ";" . $PRINT_RECORD[13] . ";" . $PRINT_RECORD[14] . ";" . $PRINT_RECORD[15] . ";" . $PRINT_RECORD[16] . ";" . $PRINT_RECORD[17] . ";" . $PRINT_RECORD[18] . ";" . $PRINT_RECORD[19] . "n";
fwrite($fh, $stringData);
}

fclose($fh);

# # #

Reply