PROGRAM OraSession;
{-- ***************************************************************************
 -- ** Descr. : This script shows how to use the TOraSession object to connect to
 -- **          an Oracle database and execute PLSQL and a query.
 -- ***************************************************************************
 -- ** 04/10/01 1.001.00 Initial Creation, sm@orcl-toolbox.com
 -- ***************************************************************************}

VAR
  ps : TParamScreen;
  os : torasession;
  oq : toraquery;
  i : integer;
  s : string;

//Begin Main
BEGIN

    ps := TParamScreen.create;
    with ps.AddBoard('Database Connect Example',picOptions) do
    begin
      addparam(parLabel,'MYLABEL','Enter the Connection to use for the query:','','');
      addparam(parDatabaseLogon,'P_DB_CONNECT','Connection','scott/tiger','');
      addparam(parLabel,'MYLABEL2','Enter your query <B>without any semicolon</B> at the end:','','');
      ps.parambyname('MYLABEL2').IsNewGroup := true;
      addparam(parPLSQL,'P_QUERY','Query to execute','select table_name, tablespace_name'+C_CR+'from user_tables'+C_CR+'where rownum<11','');
    end;


    if ps.ShowParamScreen('Enter your query ...') then
    begin
      //create a oracle session and connect
      os := torasession.create(ps.paramvalue('P_DB_CONNECT'));

      //execute a simple PL/SQL block with binded parameters
      os.execsqlp('begin :A := sysdate - :B; end;',['dummy',3]);
      logadd('3 days ago it was the : '+os.parambyname('A').asString);

      //or we can also use the parameter :result which makes it little bit easier as
      //call to the execsql function returns what we store in in this bindvariable!
      logadd('5 days ago it was the : '+os.execsqlp('begin :RESULT := sysdate - :B; end;',['dummy',5]));

      //create a query object from this session and execute the specified query
      oq := os.execquery(ps.paramvalue('P_QUERY'));

      //now loop through all records until EndOfFile=TRUE
      while oq.eof = false do
      begin
        s := '';

        //get all fields of this record
        for i := 0 to oq.fieldcount-1 do
          s := s + oq.field[i].asstring + ',';

        //display it
        logadd(s);

        //go to the next record
        oq.next;
      end;

      //disconnect from oracle
      os.disconnect;

      {free all oracle objects}
      oq.free;
      os.free;
    end else
    begin
      logadd('Pressed cancel on parameterscreen ...');
    end;

    ps.free;

END.
//End Main