Announcement Announcement Module
Collapse
No announcement yet.
SPRING access oracle metadata to execute DB procs, grabing cpu cycle time! Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SPRING access oracle metadata to execute DB procs, grabing cpu cycle time!

    In one of our applications uses spring "SimpleJdbcCall" to invock a DB procedure.
    But whenever procedure invocks, the below mentioned sql is executing in the background. This is grabbing CPU time as well.

    Is this desired behaviour ? Please advise if any alternative way to stop executing this sql in the background ..?

    SELECT
    -- Packaged procedures with no arguments
    package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Packaged procedure' AS remarks, 1 AS procedure_type
    FROM all_arguments
    WHERE argument_name IS NULL AND data_type IS NULL AND package_name LIKE :3 ESCAPE '/' AND owner LIKE :4 ESCAPE '/' AND object_name LIKE :5 ESCAPE '/' UNION ALL
    SELECT
    -- Packaged procedures with arguments
    package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Packaged procedure' AS remarks, 1 AS procedure_type
    FROM all_arguments
    WHERE argument_name IS NOT NULL AND position = 1 AND position = sequence AND package_name LIKE :3 ESCAPE '/' AND owner LIKE :4 ESCAPE '/' AND object_name LIKE :5 ESCAPE '/' UNION ALL
    SELECT
    -- Packaged functions
    package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Packaged function' AS remarks, 2 AS procedure_type
    FROM all_arguments
    WHERE argument_name IS NULL AND in_out = 'OUT' AND data_level = 0 AND package_name LIKE :3 ESCAPE '/' AND owner LIKE :4 ESCAPE '/' AND object_name LIKE :5 ESCAPE '/' ORDER BY procedure_schem, procedure_name


    The code used in the application:
    Map<String,Object> result =null;
    SqlParameterSource in =null;

    simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
    .withCatalogName(catalog).withProcedureName(callab leString);
    in = new MapSqlParameterSource()
    .addValue("pxxxx_Name", xxxxName)
    .addValue("pxxxx_Name", xxxxName)
    .addValue("pxxxx_Type", xxxxxType);
    result = simpleJdbcCall.execute(in);

    Thanks
    Vibin
    Last edited by vibin12; Apr 14th, 2012, 09:15 PM.

  • #2
    Any body know the reason why below sql is executed behind the screen when using "SimpleJdbcCall".
    Is Spring does this or JDBC thin client ?

    SELECT
    -- Packaged procedures with no arguments
    package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Packaged procedure' AS remarks, 1 AS procedure_type
    FROM all_arguments
    WHERE argument_name IS NULL AND data_type IS NULL AND package_name LIKE :3 ESCAPE '/' AND owner LIKE :4 ESCAPE '/' AND object_name LIKE :5 ESCAPE '/' UNION ALL
    SELECT
    -- Packaged procedures with arguments
    package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Packaged procedure' AS remarks, 1 AS procedure_type
    FROM all_arguments
    WHERE argument_name IS NOT NULL AND position = 1 AND position = sequence AND package_name LIKE :3 ESCAPE '/' AND owner LIKE :4 ESCAPE '/' AND object_name LIKE :5 ESCAPE '/' UNION ALL
    SELECT
    -- Packaged functions
    package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Packaged function' AS remarks, 2 AS procedure_type
    FROM all_arguments
    WHERE argument_name IS NULL AND in_out = 'OUT' AND data_level = 0 AND package_name LIKE :3 ESCAPE '/' AND owner LIKE :4 ESCAPE '/' AND object_name LIKE :5 ESCAPE '/' ORDER BY procedure_schem, procedure_name

    Comment

    Working...
    X