Announcement Announcement Module
Collapse
No announcement yet.
Calling Oracle Sql Function using SimpleJdbcCall Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calling Oracle Sql Function using SimpleJdbcCall

    Hi,

    I have a function

    Code:
    CREATE OR REPLACE FUNCTION RATELIMIT_OWN.Get_Conns_By_Login_IP_CallerId (p_yyyymm VARCHAR2, p_login VARCHAR2, p_framed_ip VARCHAR2, p_caller_id VARCHAR2 )
        RETURN CONNECTION_RECORD_4_IPV6_TABLE PIPELINED IS
    
    TYPE        ref0 IS REF CURSOR;
    cur0        ref0;
    v_where_clause VARCHAR2(512);
    
    out_rec     CONNECTION_RECORD_4_IPV6 := connection_record_4_ipv6(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    
    BEGIN
    
      if p_login is not NULL
      then
        v_where_clause := 'where login=''' || p_login || '''';
        if p_framed_ip is not NULL
        then
          v_where_clause := v_where_clause || ' and framed_ip=''' || p_framed_ip || '''';
        end if;
        if p_caller_id is not NULL
        then
          v_where_clause := v_where_clause || ' and caller_id=''' || p_caller_id || '''';
        end if;
      else
        v_where_clause := '';
        if p_framed_ip is not NULL
        then
          v_where_clause := 'where framed_ip=''' || p_framed_ip || '''';
          if p_caller_id is not NULL
          then
            v_where_clause := v_where_clause || ' and caller_id=''' || p_caller_id || '''';
          end if;
        else
          if p_caller_id is not NULL
          then
            v_where_clause := 'where caller_id=''' || p_caller_id || '''';
          end if;    
        end if;
      end if;
    
      OPEN cur0 FOR
        'SELECT login,acct_ts_start,acct_ts_stop,cause,duration,inb,outb,framed_ip,caller_id,called_id,nas_id,radius_type,format_ipv6_prefix(framed_ipv6_prefix),format_ipv6_prefix(delegated_ipv6_prefix) from ticket partition (TICKETS_P' || p_yyyymm || ') ' || v_where_clause;
    
       LOOP
       FETCH cur0 INTO out_rec.login, out_rec.acct_start, out_rec.acct_stop, out_rec.cause, out_rec.duration, out_rec.in_bytes, out_rec.out_bytes, out_rec.public_ip, out_rec.caller_id, out_rec.called_id, out_rec.nas_id, out_rec.radius_type, out_rec.framed_ipv6_prefix, out_rec.delegated_ipv6_prefix;
       EXIT WHEN cur0%NOTFOUND;
       PIPE ROW(out_rec);
      END LOOP;
      CLOSE cur0;
    
    RETURN;
    END Get_Conns_By_Login_IP_CallerId;
    /
    I am trying to execute the function using SimpleJdbcCall. However I am running into several issues

    Code:
    SimpleJdbcCall caller = new SimpleJdbcCall(this.jdbcTemplateMartinique).withSchemaName("RATELIMIT_OWN").withFunctionName("Get_Conns_By_Login_IP_CallerId").withReturnValue()
    				.declareParameters(new SqlOutParameter("LOGIN", Types.VARCHAR)).declareParameters(new SqlOutParameter("ACCT_START", Types.DATE))
    				.declareParameters(new SqlOutParameter("ACCT_STOP", Types.DATE))
    				.declareParameters(new SqlOutParameter("CAUSE", Types.VARCHAR))
    				.declareParameters(new SqlOutParameter("DURATION", Types.INTEGER))
    				.declareParameters(new SqlOutParameter("IN_BYTES", Types.INTEGER))
    				.declareParameters(new SqlOutParameter("OUT_BYTES", Types.INTEGER))
    				.declareParameters(new SqlOutParameter("PUBLIC_IP", Types.VARCHAR))
    				// .declareParameters(new SqlOutParameter("PRIVATE_IP", Types.VARCHAR))
    				.declareParameters(new SqlOutParameter("CALLER_ID", Types.VARCHAR)).declareParameters(new SqlOutParameter("CALLED_ID", Types.VARCHAR))
    				.declareParameters(new SqlOutParameter("NAS_ID", Types.VARCHAR)).declareParameters(new SqlOutParameter("RADIUS_TYPE", Types.INTEGER))
    				.declareParameters(new SqlOutParameter("FRAMED_IPV6_PREFIX", Types.VARCHAR)).declareParameters(new SqlOutParameter("DELEGATED_IPV6_PREFIX", Types.VARCHAR))
    				.declareParameters(new SqlParameter(P_YYYYMM, Types.VARCHAR)).declareParameters(new SqlParameter(P_LOGIN, Types.VARCHAR))
    				.declareParameters(new SqlParameter(P_FRAMED_IP, Types.VARCHAR)).declareParameters(new SqlParameter(P_CALLER_ID, Types.VARCHAR));
    
    		SearchAccountBean resultBean = null;
    
    		SqlParameterSource paramMap = new MapSqlParameterSource().addValue(P_YYYYMM, searchAccountBean.getDate(), Types.VARCHAR).addValue(P_LOGIN, searchAccountBean.getLogin(), Types.VARCHAR)
    				.addValue(P_FRAMED_IP, searchAccountBean.getIpAddress(), Types.VARCHAR).addValue(P_CALLER_ID, searchAccountBean.getCaller(), Types.VARCHAR);
    		caller.compile();
    		Object result = caller.execute(paramMap);

    But I am getting below error
    org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call RATELIMIT_OWN.GET_CONNS_BY_LOGIN_IP_CALLERID(?, ?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 24: PLS-00653: aggregate/table functions are not allowed in PL/SQL scope ORA-06550: line 1, column 13: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored at org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator.doTranslate(SQLStateSQLExceptionTran slator.java:98) at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:1030) at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:1064) at org.springframework.jdbc.core.simple.AbstractJdbcC all.executeCallInternal(AbstractJdbcCall.java:388) at org.springframework.jdbc.core.simple.AbstractJdbcC all.doExecute(AbstractJdbcCall.java:351) at org.springframework.jdbc.core.simple.SimpleJdbcCal l.execute(SimpleJdbcCall.java:181)

  • #2
    Please kindly support this question.

    Comment


    • #3
      Please can any one help me?

      Comment


      • #4
        Please some one from spring team help

        Comment

        Working...
        X