4 Replies Latest reply: Jul 3, 2013 1:30 AM by 903706 RSS

    ORA-12519, TNS:no appropriate service handler found on OdiSqlUnload

    903706

      Hi all,

       

      I'm using OdiSqlUnload in a target code of a procedure, where the output of the source code is the parameter.I'm using the source connection as parameters to the target connections:

      OdiSqlUnload "-FILE=#filename"   "-DRIVER=<%=odiRef.getInfo("SRC_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("SRC_ENCODED_PASS")%>" -FILE_FORMAT=variable "-FIELD_SEP=," "-ROW_SEP=;" "-DATE_FORMAT=yyyyMMddHHmmss"

       

      It loops ok for a few times but then I get ORA-12519, TNS:no appropriate service handler found error.

       

      Do you have an idea, or do you think the best way to export the data to csv using regular IKMs?

       

      Nimrod

        • 1. Re: ORA-12519, TNS:no appropriate service handler found on OdiSqlUnload
          Ayush Ganeriwal-Oracle

          ORA-12519, TNS:no appropriate service handler found error.

          The jdbc driver is throwing this exception. I has seen it sometimes when there are too many connections created to DB and it is not accepting more connections.

          • 2. Re: ORA-12519, TNS:no appropriate service handler found on OdiSqlUnload
            903706

            I know the meaning of the error. I think that ODISQLUnload is launched in parallel for each record on the source code.

            • 3. Re: ORA-12519, TNS:no appropriate service handler found on OdiSqlUnload
              DecaXD

              Hi,

               

              please paste your code here (both source and target). Could you tell us how do you compute "-FILE=#filename" ?

               

              If you have got a souce select put a distinct into it.

              • 4. Re: ORA-12519, TNS:no appropriate service handler found on OdiSqlUnload
                903706

                Source:

                SELECT mvno_id, mvno_name

                  FROM (SELECT DISTINCT mvno_id, mvno_name

                          FROM <?=snpRef.getObjectName("L", "COS_TO_MVNO", "ODI_USER", "", "D") ?>

                         WHERE EXISTS

                                  (SELECT 1

                                     FROM <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>

                                    WHERE CDRS_TO_EXPORT.mvno_id = COS_TO_MVNO.mvno_id))

                 

                Target:

                OdiSqlUnload "-FILE=#V_BASE_DIR/#MVNO_NAME/gprs/GPRS_#WHOLESALE_SEQ_NEXTVAL.csv"   "-DRIVER=<%=odiRef.getInfo("SRC_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("SRC_ENCODED_PASS")%>" -FILE_FORMAT=variable "-FIELD_SEP=," "-ROW_SEP=;" "-DATE_FORMAT=yyyyMMddHHmmss"  "-DATE_FORMAT=yyyyMMddHHmmss"

                WITH Samso_NPA_NXX_VH

                     AS (SELECT HOME_SID, npa, nxx

                           FROM <?=snpRef.getObjectName("L", "SAMSON_NPA_NXX_VH", "REPORT", "", "D") ?>

                          WHERE expiration_date IS NULL)

                SELECT Event_Parameter1,

                       #MVNO_ID,

                       wholesale_seq.NEXTVAL,

                       event_parameter14,

                       event_parameter17,

                       Answer_Time,

                       Event_Parameter18,

                       Event_Parameter15,

                       (SELECT HOME_SID

                          FROM Samso_NPA_NXX_VH

                         WHERE NPA || NXX = SUBSTR (Event_Parameter17, 0, 6)),

                       NULL,

                       Event_Parameter19,

                       NULL,

                       NULL,

                       Event_Parameter5,

                       Event_Parameter13,

                       NULL,

                       Event_Parameter4,

                       PRICE_ITEM_ID1,

                       Event_Parameter5 * Event_Parameter14,

                       Event_Parameter4,

                       Event_Parameter23,

                       Event_Parameter20,

                       Event_Parameter21,

                       BILLING_NUMBER,

                       transaction_time,

                       Event_Parameter24,

                       Event_Parameter22

                  FROM <?=snpRef.getObjectName("L", "CDR_PARTITIONED", "USAGE", "", "D") ?>, <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>

                WHERE     UPPER (Event_Parameter1) = 'DATA'

                       AND <?=snpRef.getObjectName("L", "CDR_PARTITIONED", "USAGE", "", "D") ?>.transaction_time =

                              <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>.day

                       AND <?=snpRef.getObjectName("L", "CDR_PARTITIONED", "USAGE", "", "D") ?>.SERVER_ID =

                              <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>.SERVER_ID

                       AND <?=snpRef.getObjectName("L", "CDR_PARTITIONED", "USAGE", "", "D") ?>.SYS_DR_NUMBER =

                              <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>.sys_dr_number

                       AND <?=snpRef.getObjectName("L", "CDR_PARTITIONED", "USAGE", "", "D") ?>.parent =

                              <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>.parent

                       AND <?=snpRef.getObjectName("L", "CDR_PARTITIONED", "USAGE", "", "D") ?>.child_index =

                              <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>.child_index

                       AND <?=snpRef.getObjectName("L", "CDR_PARTITIONED", "USAGE", "", "D") ?>.BILL_CYCLE_ID =

                              <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>.bill_cycle_id

                       AND <?=snpRef.getObjectName("L", "CDR_PARTITIONED", "USAGE", "", "D") ?>.LRE_ID =

                              <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>.lre_id

                       AND <?=snpRef.getObjectName("L", "CDRS_TO_EXPORT", "ODI_USER", "", "D") ?>.mvno_id = #MVNO_ID