Nathan Brake: February 2012

Thursday, February 23, 2012

Restoring table to a certain time

I recently made a mistake while doing a non standard update in banner and had to call Jim Rouff to restore the table to a previous point in time. I have been reading some of a book that Christina has and read about FLASHBACK. Now, if I make that same issue, I can resolve it myself. Example

FLASHBACK TABLE <table name> to timestamp systimestamp - interval '0 00:02:00' day to second;

This will restore the table to what it was 2 minutes ago, so you can see how the interval works and where seconds, hours, and (I think) days.

You may get an error/warning that the row movement is not enabled so then do this:

ALTER <table name> ENABLE ROW MOVEMENT;

then after the flashback you can disable row movement again with:

ALTER <table name> DISABLE ROW MOVEMENT;

No need to call Jim again :)

NOTE: If the structure of table was changed (like dropped column) then the FLASHBACK won't work. But, if you drop a table you can use:

FLASHBACK <table name> TO BEFORE DROP;

A dropped table is restored from the oracle recycling bin, so, if it is purged, it is lost. Restoring to a point in time is different. Oracle Flashback features use the Automatic Undo Management (AUM) system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions. For example, if a user executes an UPDATE statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data.

If you want to just select from a point in time you can do this:

select * from <table name> as of timestamp systimestamp - interval '0 06:00:00' day to second;

That will select from the table as of 6 hours earlier.

AJAX Cross domain with PHP and PL/SQL using JSONP

Currently, if you try to use a regular ajax call cross domain there is a same origin policy that will not allow you to do it. One way around this is using JSONP (JavaScript Object Notation with Padding). The name makes no sense and trying to think what the padding means is useless. But this will allow cross domain communication and that is the important part. On my P drive I made a PHP page like this:

<?php
header("content-type: text/javascript");

    if(isset($_GET['name']) && isset($_GET['callback']))
    {
        $obj->name = $_GET['name'];
        $obj->message = "Hello " . $obj->name;

        echo $_GET['callback']. '(' . json_encode($obj) . ');';
    }
?>

The JSON is $obj, and the encoding will format it to a Javascript object notation to allow accessing $obj name and message.

Next, the PL/SQL page:

create or replace procedure ajax_test is
begin
htp.prn('
<!DOCTYPE html>
<html lang="en">
<head>
    <title>JQuery JSONP</title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
    <script>
    $(document).ready(function(){

        $("#useJSONP").click(function(){
            $.ajax({
                url: "http://www.wou.edu/~braken/test_stuff.php",
                data: {name: "Chad"},
                dataType: "jsonp",
                jsonp: "callback",
                jsonpCallback: "jsonpCallback",
                success: function(){
                    alert("success");
                }
            });
        });

    });

    function jsonpCallback(data){
        $("#jsonpResult").text(data.message);
    }
    </script>
</head>

<body>
    <input type="button" id="useJSONP" value="Use JSONP"></input><br /><br />
    <span id="jsonpResult"></span>
</body>
</html>
');
end ajax_test;

With this, as soon as you hit the button, it will call the http page and send it the data "Chad" then the PHP page will get that name and make a $obj->name and message, and then will encode it in JSON. Then it will call the function named in jsonpCallback in the ajax call which will take the data and display it. Notice, the message, since it was encoded in JSON, can be accessed as data.message which is the $obj from the PHP page.

Nathan Brake

Thursday, February 23, 2012

Restoring table to a certain time

AJAX Cross domain with PHP and PL/SQL using JSONP

About Me

Blog Archive