/*********************************************************

  This program shows how to execute a dynamic SQL.

  To use this program, you need to create a table
    employee_tbl by using the following commands:

  SQL> CREATE TABLE  employee_tbl (
    2    employee_id   NUMBER(6), 
    3    first_name    VARCHAR2(20),
    4    last_name     VARCHAR2(25)
    5      CONSTRAINT  emp_last_name_nn  NOT NULL,
    6    email         VARCHAR2(25)
    7      CONSTRAINT  emp_email_nn      NOT NULL,
    8    phone_number  VARCHAR2(20) ) ;

  SQL> ALTER TABLE  employee_tbl  ADD (
    2    CONSTRAINT  emp_emp_id_pk  PRIMARY KEY( employee_id ) );

*********************************************************/

// Import the following packages to use JDBC.
import  java.sql.*;
import  java.io.*;
import  oracle.sql.*;
import  oracle.jdbc.*;
import  oracle.jdbc.pool.OracleDataSource;

class   DynamicSQL {
  public static void  main( String args[ ] ) throws SQLException {
    String user     = "C##user_id";
    String password = "password";
    String database = "20.185.147.112:1521/xe";

    // Open an OracleDataSource and get a connection.
    OracleDataSource ods = new OracleDataSource( );
    ods.setURL     ( "jdbc:oracle:thin:@" + database );
    ods.setUser    ( user );
    ods.setPassword( password );
    Connection conn = ods.getConnection( );

    try {
      // The parameter, sql, is too long as an argument.
      // Read the parameter, sql, saved in the file p1.txt.
      FileInputStream    stream     = new FileInputStream  ( "p1.txt" );
      InputStreamReader  iStrReader = new InputStreamReader( stream );
      BufferedReader     reader     = new BufferedReader   ( iStrReader );
      String  sql = reader.readLine( );
      if ( sql == null )  sql = "";
      else  sql = sql.trim( );
 
      try {
        Statement  stmt = conn.createStatement( );
        System.out.print( "
" + sql + "" );
        if ( stmt.execute( sql ) ) {
          ResultSet          results = stmt.getResultSet( );
          ResultSetMetaData  meta    = results.getMetaData( );
          int  cols = meta.getColumnCount( );
          while ( results.next( ) )
            for ( int i = 1;  i <= cols;  i++ ) {
              Object  ob = results.getObject( i );
              System.out.print( meta.getColumnLabel( i ) + " ➜ " + ob.toString( ) );
            }
          results.close( );
        } 
        stmt.close( );      
      }
      catch ( SQLException ex ) { System.out.println( ex ); } 
    }
    catch ( IOException ex ) { System.out.println( ex ); } 
    conn.close( );
  }
}