Normally Oracle will scan SQL for variables and prompt for the value at runtime, to disable this feature use the “set scan off” command:

   1  set scan off;
   2  
   3  select * from links where href = 'http://...&....';

You can use the exp and imp executables to perform data backup and restoration.

   1  -- Export
   2  exp <username>/<password>@<server> file=data.dmp consistent=y grants=no statistics=none
   3  
   4  -- Import
   5  imp <username>/<password>@<server> file=data.dmp fromuser=<source_username> touser=<username>

Oracle variables are a great way of removing duplicate SQL statements from your Oracle SQL scripts. By using a variable, you can store the return value of a query and use it later in another, as this example illustrates:

   1  COLUMN your_column new_value your_variable;
   2  
   3  SELECT DISTINCT (your_column)
   4                                   FROM ...
   5                                  WHERE ...;
   6  
   7  SELECT &your_variable FROM dual;

The above example stores the value of the SELECT DISTINCT query in a variable named your_variable. You declare the variable with new_value, and set the value by executing an SQL statement that returns one row and column.

A PL /SQL example of a for loop that prints out the primary key for all rows returned by a query:

   1  DECLARE
   2    ID VARCHAR2(50);
   3  BEGIN
   4      DBMS_OUTPUT.ENABLE (1000000);
   5      FOR current_row IN (SELECT id FROM your_table)
   6  	LOOP
   7  		ID := current_row.id;    -- Assign value to variable
   8  		DBMS_OUTPUT.PUT_LINE( '' || ID);
   9  		DBMS_OUTPUT.PUT_LINE( '' || current_row.column_xxx);
  10  	END LOOP;
  11  END;
  12  /

This example works with Oracle:

   1  private String getBlobAsString(Blob blob)
   2  {
   3      StringBuffer result = new StringBuffer();
   4      
   5      if ( blob != null ) 
   6      {
   7          int read = 0;
   8          Reader reader = null;
   9          char[] buffer = new char[1024];
  10                                  
  11          try
  12          {
  13              reader = new InputStreamReader(blob.getBinaryStream(), "UTF-8");
  14  
  15              while((read = reader.read(buffer)) != -1) 
  16              {
  17                  result.append(buffer, 0, read);
  18              }
  19          }
  20          catch(SQLException ex)
  21          {
  22              throw new RuntimeException("Unable to read blob data.", ex);
  23          }
  24          catch(IOException ex)
  25          {
  26              throw new RuntimeException("Unable to read blob data.", ex);
  27          }
  28          finally
  29          {
  30              try { if(reader != null) reader.close(); } catch(Exception ex) {};
  31          }
  32      }
  33      
  34      return result.toString();
  35  }

Then use the method like this:

   1  ResultSet resultSet = your JDBC result set;
   2  
   3  String utf8 = getBlobAsString(resultSet.getBlob("xml"));
   4