Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: View Creation script?

RE: View Creation script?

From: Dunbar, Norman <norman.dunbar_at_environment-agency.gov.uk>
Date: Fri, 10 Aug 2007 12:42:26 +0100
Message-ID: <919FC80F27E0C6428106496EDF92A752561767@EXCCLUS05.PRODDS.NTNL> 





>> -----Original Message-----
>> From: oracle-l-bounce_at_freelists.org 
>> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still





>> It has been pointed out to me that I failed to do this in Perl.
>> Now rectified. 



And here's a version I made up using OCCI - which I'm starting to 'play'
with - now that it's working on Linux. This one has a few optionals
added - you pass username, password, databasename, prefix and suffix on
the command line and views are created for that user. 



The view name is prefix + table name + suffix and is checked for maximum
length.



To use call ./CreateViews username password database prefix suffix
>script_file.sql



The database is "" if using ORACLE_SID.


The prefix is "" if you don't want one.


The suffix is "" if you don't want one, or you can omit it from the
command line.



If prefix and suffix are both 'empty' a previx of 'V_' is used by
default.



Errors go to stderr, output to stdout.



A nice little test of my abilities to use OCCI - thanks for the 'hint'
from the OP.



Here's the 'script' :


#include <string>
#include <iostream>
#include "occi.h"

using std::cout;
using std::cerr;




using std::endl;


using std::string;



using namespace oracle::occi;


//----------------------------------------------------------------------




// Define a few constant 'codes' for 'ease of use'.


//----------------------------------------------------------------------





// Exit codes.



const int OK = 0;


const int CANNOT_LOGIN = 1;


const int EXCEPTION_OCCURRED = 2;


const int USAGE = 3;



// Parameter positions.


const int PROGNAME = 0;
const int USERNAME = 1;
const int PASSWORD = 2;
const int DATABASE = 3;




const int PREFIX = 4;


const int SUFFIX = 5;



// Parameter counts



const int MINPARAMS = 4;


const int MAXPARAMS = 6;



// Statement positions 



const int TABLE_NAME = 1;


const int COLUMN_NAME = 1;



// MIscellaneous stuff



const int MAXNAMELENGTH = 30;


//----------------------------------------------------------------------




// By making these Globals, it makes the TidyUp() function work. I  know

// all about 'not using globals' - but I put that in the same league as

// 'do not ever use GOTO' - so there !


//----------------------------------------------------------------------


Environment *env;


Connection *conn;

Statement *stmtTables;
Statement *stmtColumns;
ResultSet *rsTables;
ResultSet *rsColumns;


//----------------------------------------------------------------------




// Clean up anything Oracle-like that may exist. This gets called at the

// very end of processing, or in the event that a fatal exception was

// detected.


//----------------------------------------------------------------------


void TidyUp()


{


  if (rsColumns) {


    stmtColumns->closeResultSet(rsColumns);
  }



  if (rsTables) {


    stmtTables->closeResultSet(rsTables);
  }



  if (stmtColumns) {


    conn->terminateStatement(stmtColumns);
  }



  if (stmtTables) {


    conn->terminateStatement(stmtTables);
  }



  if (conn) {


    env->terminateConnection(conn);


  }



  if (env) {


    Environment::terminateEnvironment(env);
  }


}



//----------------------------------------------------------------------




// Display details of any OCCI exceptions that were trapped. This code

// returnes to the caller where the program *may* continue - if at all

// appropriate.


//----------------------------------------------------------------------


void HandleException(SQLException &e) 


{

  cout << "SQLException : " << endl;
  cout << "ErrorCode : " << e.getErrorCode() << endl;
  cout << "Message   : " << e.getMessage() << endl;


}




//----------------------------------------------------------------------




// It all starts here ....


//----------------------------------------------------------------------


int main(int argc, char **argv)


{


  int tableCount = 0;


  int errorCount = 0;



  if (argc < MINPARAMS || argc > MAXPARAMS) {

    cerr << endl << endl ;
    cerr << "Usage : " << endl << endl;
    cerr << argv[PROGNAME] << " Username Password DatabaseName Prefix
Suffix" << endl << endl;
    cerr << "Username = the user who's tables you want to create views


over" << endl;


    cerr << "Password = the user's password." << endl;
    cerr << "DatabaseName = the database you wish to connect to." <<
endl;

    cerr << "               Use \"\" to pick up ORACLE_SID from the
environment." << endl;
    cerr << "Prefix = used to prefix the table name when creating the
view name." << endl;
    cerr << "         Use \"\" if no prefix is required." << endl;


    cerr << "Suffix = used to suffix the table name when creating the
view name." << endl;


    cerr << "         Use \"\" if no suffix is required." << endl;
    cerr << endl << "NOTE : If neither prefix or suffix are supplied,
the default ";


    cerr << "will be to create a view named 'v_TABLE_NAME'." << endl;
    cerr << endl << endl;


    return USAGE;


  }



  try { // Outer level exception block ...
    env = Environment::createEnvironment();



    try { // to connect to the database

      if (argc == MINPARAMS) // No database passed ...
        conn = env->createConnection(argv[USERNAME], argv[PASSWORD]);
      else
        conn = env->createConnection(argv[USERNAME], argv[PASSWORD],


argv[DATABASE]);


    } catch (SQLException &e) { // Connection failures

      // Report error, then bale out - we have a real error this time.
      HandleException(e);
      TidyUp();
      return EXCEPTION_OCCURRED;




    }
   


    try { // Try to create the two statements ...
      stmtTables = conn->createStatement("Select table_name from
user_tables");


      stmtColumns = conn->createStatement("select column_name from
user_tab_columns "

                                          "where table_name =


:TableName");


    } catch (SQLException &e) { // Statement creation failed.

      HandleException(e);
      TidyUp();
      return EXCEPTION_OCCURRED;




    }



    try { // Fetch a list of tables and for each, get the column names
...

      string tableName;
      string viewPrefix = "V_";
      string viewSuffix = "";
      bool isFirstColumn;

      if (argc > MINPARAMS) 
        viewPrefix = argv[PREFIX];

      if (argc == MAXPARAMS)
        viewSuffix = argv[SUFFIX];

      rsTables = stmtTables->executeQuery();
      while(rsTables->next()) {
        ++tableCount;
        isFirstColumn = true;
        tableName = rsTables->getString(TABLE_NAME);

        // Sanity check ...
        if (tableName.length() > (MAXNAMELENGTH - viewPrefix.length() -
viewSuffix.length())) {
          ++errorCount;
          cerr << "Cannot create view named '" << viewPrefix <<
tableName << viewSuffix;
          cerr << " because the name exceeds " << MAXNAMELENGTH << "
characters." << endl;
          continue;
        }

        cout << "create or replace view " << viewPrefix << tableName <<
viewSuffix << " as" << endl;
        cout << "\tselect" << endl;

        // Bind up the table_name & fetch the columns ...
        stmtColumns->setString(TABLE_NAME, tableName);
        rsColumns = stmtColumns->executeQuery();

        while(rsColumns->next()) {
          cout << "\t\t";

         if (isFirstColumn) {
           isFirstColumn = false;
           cout << " ";
         } else {
           cout << ", ";
         }

         cout << rsColumns->getString(COLUMN_NAME) << endl;
        }

        cout << "\tfrom " << tableName << endl;
        cout << "/" << endl << endl;


     }
    } catch (SQLException &e) { // Statement execution failures.
      HandleException(e);
      TidyUp();
      return EXCEPTION_OCCURRED;




    } 



    TidyUp();



  } catch (SQLException &e) { // Outer level error handler.
    HandleException(e);


    TidyUp();


    return EXCEPTION_OCCURRED;


  }


  cerr << endl;
  cerr << "Creation of " << tableCount << " views attempted. " << endl;
  cerr << errorCount << " views failed to create due to name length


restrictions" << endl;


  return OK;


}
  



Cheers,


Norman.



Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.



We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.



We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation.  Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.



If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506.  Find out more about the Environment Agency at www.environment-agency.gov.uk

--
http://www.freelists.org/webpage/oracle-l


Received on Fri Aug 10 2007 - 06:42:26 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US