Automic Workload Automation

 View Only
  • 1.  Need to insert the csv data into OracleDB Table

    Posted Jul 14, 2024 02:20 AM

    Hi Community,

    I need some info regarding the automation to insert the csv file data into the Oracle DB Table and CSV file is placed in AE path. CSV file is having the 12 columns and 100000 rows. Kindly provide the script for this.

    Thanks and regards,

    Shaik.



  • 2.  RE: Need to insert the csv data into OracleDB Table

    Posted Jul 15, 2024 05:25 AM

    Hi Shaik,

    I would use the external tool "SQL Loader".

    In order to use Automic mechanisms you could use the following:

    1. create a JOBS.SQL object
    2. run PREP_PROCESS_FILE to read the CSV file line-by-line
    3. from the loop use the SQL command "INSERT INTO"

    But this method only allows 32767 lines/rows, so you have to split the CSV input file into multiple parts.

    regards,
    Peter



    ------------------------------
    Automic Certified Professional/Expert & Broadcom Knight

    For AUTOMIC trainings please check https://www.qskills.de/qs/workshops/automic/
    ------------------------------



  • 3.  RE: Need to insert the csv data into OracleDB Table

    Broadcom Employee
    Posted Jul 16, 2024 08:29 AM

    Another option would be to use a SQL script to load the data:

    load data

    infile '<path_to_csv_file.csv>' into table <name>

    fields terminated by "," optionally enclosed by '"'

    ( col1, col2 )

    You can use the SQL Agent / Job to do this (if the same machine can "see" the csv file) or use an OS Job and use the sqlldr <dbuser>/<password> CONTROL=<path_to_sql_file> command 




  • 4.  RE: Need to insert the csv data into OracleDB Table

    Posted Jul 18, 2024 05:34 AM

    Hi Peter Grundler,

    Could you share me some sample examples to automate this request.

    Thanks & regards,

    Shaik




  • 5.  RE: Need to insert the csv data into OracleDB Table

    Posted Jul 19, 2024 03:09 AM

    Hi Shaik,

    please find an example below:

    sqlldr <username>/<password>@<oracle server> control=import.ctl

    content of import.ctl:

    load data
     infile 'c:\temp\file.csv'
     into table <table name>
     fields terminated by "," optionally enclosed by '"'          
     ( <col1>, <col2>, <col3>, etc. )

    regards,
    Peter



    ------------------------------
    Automic Certified Professional/Expert & Broadcom Knight

    For AUTOMIC trainings please check https://www.qskills.de/qs/workshops/automic/
    ------------------------------



  • 6.  RE: Need to insert the csv data into OracleDB Table

    Posted Jul 15, 2024 08:12 AM

    If the content of the csv is a replacement for the previous content of the csv, you can use the Oracle construct of an "external table".  It is a file location that is configured in Oracle, at which you place an updated csv file.  Oracle has it mapped as a database table.  The next process to read that table triggers Oracle to do the physical read.  

    Benefits: Simple file drop for Automic.  Relatively simple config for DBA.

    Negatives: If you want this csv added to an existing table (instead of replacing the content), you'd have to manage that some other way.



    ------------------------------
    Thanks,
    Doug
    ------------------------------