FAQ
Hi List

I am trying to load data from a file into an oracle table.
the file is in matrix format (rows is timestamp and columns are sids where
the value is a status) and needs to be put into the table as rows as the
number of columns is variable in time
so the layout of the table is

dbname varchar2(20)
timestamp varchar2(20) ---> this I would like to change to date
status varchar2(6)

I have created a little code that loops through the files and the data in
the files and inserts the data into a table. the first row contains the sids
and should not be loaded, but just used in every insert.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

function insert_OLA_data_2($file_in)
{
global $conn, $FileDir; //define variables as global
$lines = file($FileDir.$file_in,FILE_IGNORE_NEW_LINES |
FILE_SKIP_EMPTY_LINES);

$line_n=0;
$stmt = oci_parse ($conn,"insert into OLA_DATA2(DBNAME,TIMESTAMP,STATUS)
values (:dbs,:datum,:stat)");
foreach ($lines as $line_num => $line)
{
if ($line_n < 1 )
{
$a_dbs=explode(',',rtrim($line));
}
else
{
$a_line=explode (',',rtrim($line));
$cntr=1;
for ($item_num=0;$item_num < count($a_line)-1; $item_num++)
{

oci_bind_by_name($stmt,"dbs",$a_dbs[$cntr]);
oci_bind_by_name($stmt,"datum",$a_line[0]);
oci_bind_by_name($stmt,"stat",$a_line[$cntr]);
oci_execute($stmt,OCI_DEFAULT);

$e = oci_error($stmt);
if(count($e) > 1)
{
log_message('Error while loading file '.$file_in.'('.$e['message'].')');

}$cntr=$cntr+1;
}
}

$line_n=$line_n + 1;
}
$commited = oci_commit($conn);
move_files($file_in);
log_message('File '.$file_in.' processed');
}//EoF insert_OLA_data_2

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I am using bind variables as there are many executions of the same query
with different values.

Above code works fine, but the date is going into the database as a varchar
and I would really like it to go in as a date
Anyone done this and can point me in the right direction?

format for the string containing the date is 2008:07:29:12:15:00

Search Discussions

  • Remigiusz Sokolowski at Jul 29, 2008 at 10:53 am

    Jack van Zanen pisze:
    Hi List
    Anyone done this and can point me in the right direction?



    format for the string containing the date is 2008:07:29:12:15:00

    you sql shoould look like this one
    "insert into OLA_DATA2(DBNAME,TIMESTAMP,STATUS) values
    (:dbs,to_date(:datum,'yyyy:mm:dd:hh24:mi:ss'),:stat)"

    regards

    --
    --

    ----------------------------------------------------------------------
    Remigiusz Sokolowski
    pos : DBA at DUSB
    addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
    phone: +48 58 667 17 43

    -----------------------------------------------------------------------------------------
    Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia
    wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem: 0000021828,
    dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku,
    VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego,
    o kapitale zakladowym i wplaconym w wysokosci: 227.593.500,00 zlotych,
    NIP: 586-000-78-20, REGON: 190024711
    -----------------------------------------------------------------------------------------
    --
    http://www.freelists.org/webpage/oracle-l
  • Jack van Zanen at Aug 4, 2008 at 5:43 am
    Hi,

    I am getting senior moment every now and than I think.

    That worked a treat, thanks

    Jack
    On 29/07/2008, Remigiusz Sokolowski wrote:

    Jack van Zanen pisze:
    Hi List
    Anyone done this and can point me in the right direction?



    format for the string containing the date is 2008:07:29:12:15:00

    you sql shoould look like this one
    "insert into OLA_DATA2(DBNAME,TIMESTAMP,STATUS) values
    (:dbs,to_date(:datum,'yyyy:mm:dd:hh24:mi:ss'),:stat)"

    regards

    --
    --

    ----------------------------------------------------------------------
    Remigiusz Sokolowski
    pos : DBA at DUSB
    addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
    phone: +48 58 667 17 43


    -----------------------------------------------------------------------------------------
    Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia
    wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod
    numerem: 0000021828,
    dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku,
    VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego,
    o kapitale zakladowym i wplaconym w wysokosci: 227.593.500,00 zlotych,
    NIP: 586-000-78-20, REGON: 190024711
    -----------------------------------------------------------------------------------------
    --
    J.A. van Zanen

    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Dick at Jul 30, 2008 at 1:25 pm
    Jack,



    I see two possible problems.



    Since your saying that the date is going in as a varchar I have
    to assume that the table's definition has it as a varchar vs a date.
    Change the timestamp column to date & that will correct part of the
    problem.

    Wrap the datum bind variable in the to_date function. PHP sees it as
    a character string & binds it as such.

    Dick Goulet / Capgemini
    North America P&C / East Business Unit
    Senior Oracle DBA / Hosting
    Office: 508.573.1978 / Mobile: 508.742.5795 / www.capgemini.com
    Fax: 508.229.2019 / Email: richard.goulet_at_capgemini.com
    45 Bartlett St. / Marlborough, MA 01752

    Together: the Collaborative Business Experience

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jack van Zanen
    Sent: Monday, July 28, 2008 10:20 PM
    To: oracle-l
    Subject: PHP, Oracle and bind variables



    Hi List





    I am trying to load data from a file into an oracle table.

    the file is in matrix format (rows is timestamp and columns are sids
    where the value is a status) and needs to be put into the table as rows
    as the number of columns is variable in time

    so the layout of the table is



    dbname varchar2(20)

    timestamp varchar2(20) ---> this I would like to change to date

    status varchar2(6)



    I have created a little code that loops through the files and the data
    in the files and inserts the data into a table. the first row contains
    the sids and should not be loaded, but just used in every insert.



    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    function insert_OLA_data_2($file_in)
    {
    global $conn, $FileDir; //define variables as global
    $lines = file($FileDir.$file_in,FILE_IGNORE_NEW_LINES |
    FILE_SKIP_EMPTY_LINES);

    $line_n=0;
    $stmt = oci_parse ($conn,"insert into OLA_DATA2(DBNAME,TIMESTAMP,STATUS)
    values (:dbs,:datum,:stat)");
    foreach ($lines as $line_num => $line)
    {
    if ($line_n < 1 )
    {
    $a_dbs=explode(',',rtrim($line));
    }
    else
    {
    $a_line=explode (',',rtrim($line));
    $cntr=1;
    for ($item_num=0;$item_num < count($a_line)-1; $item_num++)
    {

    oci_bind_by_name($stmt,"dbs",$a_dbs[$cntr]);
    oci_bind_by_name($stmt,"datum",$a_line[0]);
    oci_bind_by_name($stmt,"stat",$a_line[$cntr]);
    oci_execute($stmt,OCI_DEFAULT);

    $e = oci_error($stmt);
    if(count($e) > 1)
    {
    log_message('Error while loading file '.$file_in.'('.$e['message'].')');

    }$cntr=$cntr+1;
    }
    }

    $line_n=$line_n + 1;
    }
    $commited = oci_commit($conn);
    move_files($file_in);
    log_message('File '.$file_in.' processed');
    }//EoF insert_OLA_data_2

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    I am using bind variables as there are many executions of the same query
    with different values.

    Above code works fine, but the date is going into the database as a
    varchar and I would really like it to go in as a date
    Anyone done this and can point me in the right direction?



    format for the string containing the date is 2008:07:29:12:15:00

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 29, '08 at 2:20a
activeAug 4, '08 at 5:43a
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase