Forum Discussion

Jasper175's avatar
Jasper175
Frequent Contributor
15 years ago

SQL Select Statement Groovy

Hello,
Can someone show me a SIMPLE example of a select * statement that would be written in a Groovy Script console?
Here is the select statement I need to run: SELECT * FROM CSR_SESSION;
If you need to know the column names: 'CSR_SESSION_ID', 'SYSTEM_USER_ID', 'ASM_TICKET', 'CREATE_DATE'

Thank you,
Rob L.

1 Reply

  • Jasper175's avatar
    Jasper175
    Frequent Contributor
    15 years ago
    Took me a long time to get it - but here it is.
    Note: This pulls everything, so you can build from this snippet to pull only one most recent value using (select max(create_date). Email if you want a copy of my script.

    CODE:
    import groovy.sql.Sql

    def sql = Sql.newInstance("jdbc:oracle:thin:@qafat1.db.qa.domain.net:1521:SIDName",
    "userName", "password", "oracle.jdbc.OracleDriver")

    sql.eachRow("SELECT asm_ticket, create_date FROM DATAVAULT.CSR_SESSION"){ row ->
    log.info("|ASM: " +row.asm_ticket + " | DATE: " + row.create_date)
    }
    END CODE

    Description of Fields
    DATAVAULT = Schema
    CSR_SESSION = Table
    asm_ticket = column name
    create_date = column name

    Rob (Jasper175)