Comments

  • 1-11 of 11
  • Karthik Ramachandran

    I had tried when you type in query and validate it show as success, but it doesn't return the result at all when i use select statement with in queries.

    In one of the scenario i had use the sql procedure to do that. But it is very tough to map the sql procedure output in mapping.

    if we write select statement you can map column to element which is very straight forward. if you write a sql procedure you have to return the output in table type. And then while doing mapping we have to loop through row & then column and compare the @name with column name to get the field value. (see screenshot)

    when you map like 20 + field with everytime having for loop & if condition it is very tiresome. 

    oic5.png (9KB)
  • Karthik Ramachandran

    this are steps in my OIC Integration.

    1) Rest Service connection

    2) Mapping request to Oracle ATP connection (invoking sql procedure)

    3) Oracle ATP connection

    4) Mapping response from Oracle ATP sql procedure to response restAPI.

    I am facing issue in step 4, where i am trying to map Oracle ATP sql procedure output to rest api response structure. PL/SQL procedure doesn't display fields which we have to map.

    Hope I am answering right question.

     

  • Karthik Ramachandran

    sql procedure returns cursor, which contains the record set. cursor is again a variable which hold the value.

    when you select sql procedure in oic and create a map. the output of sql procedure is recordset which is rows & column. But because i don't see individual column i am not able to map to target response structure. Is there any other way to read that response.

     

    if you see current response structure and county column contain all the values, state column contain the name of the column & city column contain datatype. I know here mapping is incorrect. But i am not sure how to map individual column because i don't see the entity (postal code, city , state, county) in the source side of mapping.

    {
    "values": [
    {
    "county": "Westmoreland United States Pottawatomie KS 66549",
    "state": "CITY COUNTRY_NAME COUNTY STATE_CODE POSTAL_CODE",
    "city": "VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2"
    },
    {
    "county": "Blaine United States Pottawatomie KS 66549",
    "state": "CITY COUNTRY_NAME COUNTY STATE_CODE POSTAL_CODE",
    "city": "VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2"
    }
    ]
    }

     

  • Karthik Ramachandran

    in mapping on the left side i don't see individual element. 

    If u see previous post, i created procedure and return recordset in cursor. When i map source to destination response. I don't see the individual element to map. Column return all the columns, i want to split and map it to postal code , county , state and city.. 

  • Karthik Ramachandran

    Hi Ankur,

    I tried the pl sql procedure with package. but i am missing something when I am doing mapping. Data are not coming in separate entity to map it. please see the screenshot & procedure.

    see below response -

    all values are coming together in county tag. 

    {
    "values": [
    {
    "county": "Westmoreland United States Pottawatomie KS 66549",
    "state": "CITY COUNTRY_NAME COUNTY STATE_CODE POSTAL_CODE",
    "city": "VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2"
    },
    {
    "county": "Blaine United States Pottawatomie KS 66549",
    "state": "CITY COUNTRY_NAME COUNTY STATE_CODE POSTAL_CODE",
    "city": "VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2"
    }
    ]
    }
     
    Please find the procedure code :
     

    create or replace Procedure
                    ZEB_PROC_ADDRESS_LKP(post_cd  in varchar2, state1  in varchar2, city1  in varchar2, county1  in varchar2, l_rc out sys_refcursor)
    AS

    begin

    open l_rc
       for
    SELECT  CITY,COUNTRY_NAME,COUNTY,STATE_CODE,POSTAL_CODE
    FROM zeb_vertex_lookup 
    WHERE NVL(POSTAL_CODE,0)   = NVL(post_cd, NVL(POSTAL_CODE,0)) AND NVL( STATE_CODE,0)  = NVL(state1 , NVL( STATE_CODE,0)  )
    AND NVL( CITY ,0) = NVL(city1 , NVL( CITY ,0)) AND NVL( COUNTY,0) = NVL(county1 , NVL( COUNTY,0));

    END;

  • Karthik Ramachandran

    Ankur ... procedure or package won't return table or list of records.

  • Karthik Ramachandran

    Guys,

    Once again .. different queries but related to same topic.. i tried different approach but somehow it is not working as expected. Not able to find more details in the net.

    Please see the image.. 

    https://ibb.co/1JFLNCR

  • Karthik Ramachandran

    nope.. latest created record should display first.. anyway thanks for quick response. i will try to see if i can write a trigger to populate the rowid into another custom column so that i can use that instead. 

  • Karthik Ramachandran

    Thanks Hemanth. Understood. Only concern was sequence number will be visible in UI and business will see a gap of 50 between each sequence number.

    If you have any suggestion different approach for generating sequence number it will help.

  • Karthik Ramachandran

    Sushma I understood, it is due to pool. But is it possible to get it fixed so that we get it in sequence. 

    Also is it possible can we send back ROWID itself to OIC after insert operation? Because ROWID is internal column in ATP, column is not visible in OIC.

  • Karthik Ramachandran

    Thanks Sushma for quick reply. 

    Now I changed the increment to 50, but i am facing another wierd issue. 

    I am getting response in sequence  but after every alternate trigger. I have mentioned response for each trigger.

    trigger 1

    {
    "seqNum": "200009"
    }

    trigger 2

    {
    "seqNum": "199959"
    }
     
    trigger 3
     
    {
    "seqNum": "200010"
    }
     
    trigger 4
    {
    "seqNum": "199960"
    }
     
    for every alternate trigger i am getting different sequence.