Connect SnapLogic to Oracle Cloud ATP database
We are moving our ERP to Oracle Cloud and I am trying to connect SnapLogic to an Oracle Cloud ATP (Autonomous Transaction Processing) database. I’ve been trying to set up an Oracle Thin Account in SnapLogic with the settings for our ATP database but keep getting error “IO Error: Remote host terminated the handshake”. Does anyone have experience with using SnapLogic with ATP and have any suggestions?Oracle to Redshift - Dynamic Metadata
Created by Chris Ward, SnapLogic This Pipeline replicates table data obtained from an Oracle database and upserts the data into Redshift. Firstly, the Parent Pipeline reads the Source and Target schema & tables and a where clause used to filter the source data from an expression file “oracle_to_redshift__c.expr” (the customer would need to modify this file to correspond with the table & schemas they are looking to target), then obtains the primary key constraints for each target table in Redshift. The Pipeline then constructs an array of documents for up to 10 key columns that will be used to determine the unicity of the data when being upserted into Redshift. Each document is then passed into the child pipeline that reads the data from the source Oracle table and then upserts the data into Redshift. A router Snap is used between the Oracle read and Redshift upsert to achieve concurrency for large data volumes. The Pattern used within this Pipeline could potentially be reworked to operate with different source and target databases, eg SQL Server to Google Big Query. Configuration To make this Pipeline reusable, make use of the expression file to store the source and target tables & schemas. Sources: Oracle Table Targets: Redshift Table Snaps used: Mapper, JSON Splitter, Redshift Execute, Group By Fields, Pipeline Execute, Oracle Select, Router, Redshift Upsert Downloads Oracle to Redshift - Dynamic Metadata.slp (15.8 KB) Oracle_to_Redshift_02.slp (11.3 KB) oracle_to_redshift_c.expr (3.2 KB)
Oracle-SAP-Salsify-GetProductByUPC
Created by @asharifian, SnapLogic This pattern serves as a triggered task REST endpoint that would allow users to pass in a UPC code and retrieve product details from Salsify. A lookup is done in Oracle for product details and SAP for additional product details as well as the materials that compose the product. That information is then passed into Salsify, an eCommerce platform that will retrieve sales data regarding the product, and send that back to the end-user. Use case is an application integration with Salesforce that can display that information via Salesforce Lightning or Apex. Configuration The material ID from SAP is sent directly to the Salsify Rest GET API to retrieve the eCommerce product sales data. Sources: Oracle and SAP product and material details tables Targets: Salsify Products Snaps used: Mapper, Filter, Oracle - Lookup, SAP Execute, REST Get Downloads Oracle-SAP-Salsify-GetProductByUPC.slp (10.2 KB)Salesforce Contacts - Database Replication (SQL Server-MongoDB-Oracle)
Created by @asharifian This pattern provides an example pipeline to replicate data from Salesforce to multiple different database types: SQL Server, MongoDB, and Oracle. Data replication is useful for disaster recovery, analytics, and improves overall system resilience and reliability. Configuration Two approaches are covered in this pattern: Data replication post success of an insert into a dependent database, such as data first getting inserted into MongoDB, and upon success, inserted into Oracle. Data replication in parallel, such as data being inserted to MongoDB and SQL Server in a parallel fashion. Sources: Salesforce Contacts Targets: Database tables for customer contact information in SQL Server, MongoDB, Oracle Snaps used: Salesforce Read, Mapper, Copy, MongoDB - Insert, SQL Server - Insert, Oracle - Insert Downloads Salesforce Contacts-Database Replication (SQL Server-MDB-Oracle).slp (14.5 KB)Workday to Oracle and BambooHR of Employee Information
Created by @asharifian This pattern provides an HRIS integration from Workday to Oracle and BambooHR. Oracle will store employee information in a database table for analytics purposes. BambooHR is an HRIS system that will store employee information for human resources and onboarding. The source is the Human_Resources service, Get_Workers object in Workday. Configuration You can get a free trial of BambooHR and test out their API’s. API is relatively easy to configure. Sources: Workday Get_Workers Targets: Database Table for Oracle, Employees for BambooHR Snaps used: Workday Read, Mapper, Copy, Oracle - Insert, JSON Splitter, REST Post Downloads Workday to Oracle-BambooHR.slp (13.2 KB)
Migrating Oracle Number to SQL Server Numeric
I have data I need to move from Oracle to SQL Server. In Oracle the data type is just “NUMBER”. No precision or scale specified. I know from analysis that these values are in some of the rows for one of the columns. .201025955675839502926546893128461404242 239.41789473684210526315789473684210526 SQL Server has a Numeric data type. Max precision is 38. I cannot define a numeric data type that will work with these values. If I did something like Numeric(12,26), it won’t hold the first value. If I did something like Numeric(38,38), it won’t hold the second value. I want to just lop off some of the decimals so that I never exceed 26 numbers to the right of the decimal. I have been trying to do this in the mapper and am stuck. The toPrecision function doesn’t do what I want. If X = 239.41789473684210526315789473684210526 x.toPrecision(26) returns 239.41789473684210065584921 It starts changing everything instead of just rounding to 26 decimal points. Any ideas on what I can do here?
How do you use the Result Set output on the Oracle Stored Procedure snap?
I have an Oracle stored function that returns a ref cursor. When I call it from an Oracle Stored Procedure snap, I’d like the data returned to the Result Set output (I think), but instead, it’s getting returned to the regular output with each column in the data as it’s own array. I haven’t been able to locate any documentation how to use the result set output. Has anyone used the result set output successfully?
Using Oracle Execute to run complicated SQL Select not returning any data
I’m new to SnapLogic and was told by one of our senior SL developers that instead of using the Oracle Select snap, I could use the Oracle Execute snap to run a complex SQL select statement. When I try to do that though, I get no data and I can’t figure out how to get a “snap”, such as a csv formatter, to “connect” to it. The pieces are next to each other in the pipeline, but there is nothing in the spot in between them showing they have fitted together. the only way I have been able to get it to work is by creating a database view representing the complex sql statement, then using an Oracle Select snap to pull the data from the db view.
Discard empty rows while writing an excel file
I am trying to write an excel after comparing a database with salesforce data, I want to ignore the null rows after comparison is done in a mapper post Join. The data I am getting is perfect, only issue is with the way excel looks like. Please advise how can these empty rows be ignored
Snap Execute snap with Oracle Cloud HCM Data Loader (HDL)
We are new customers of Snaplogic and Oracle Cloud HCM and are trying to use Snaplogic for integrations with Oracle Cloud HCM (OC). One of our first projects is to update a flexfield on OC with our custom ID number for each person listed in the a csv file that we will receive from another process. We are using the OC HCM Data Loader (HDL). Outside of Snaplogic we transform the info. from the csv to a “.dat” file in a format that HDL uses, and then zip the file as required by HDL. I have loaded and processed the file manually using HDL so that I know that the file and HDL are working properly. Now I want to use Snaplogic to automate a process to run nightly to update OC What I want Snaplogic to do is: Send the zip file to OC. Initiate the process on OC to process the contents of the zip file. Store/log the results of the transactions. I plan on using the SOAP Execute snap for this unless I find a better method. The first thing I need to know is how to use Snaplogic to send the zip file to OC. It looks like I need the file to be loaded into the envelope in BASE64 format as “Contents”. How do I use Snaplogic to get the file from the server into snaplogic, transform it to BASE64 format and get it into the envelope? Thanks in advance, Scott
