Feed aggregator

Pivot or group (multiple columns transpose)

Tom Kyte - Mon, 2019-01-14 09:26
Hi, I try to transpose multiple columns but i couldn't. Here is my table. This data create automatically. <code>BODY_ID EQUPMENT DATA_NO DATA_TYPE RESULT DATA PRG DATETIME 52972 t000001119 1 1 @ 381 1120000000000000...
Categories: DBA Blogs

UK Retailer Matalan Selects Oracle to Map Inventory Against Consumer Demand

Oracle Press Releases - Mon, 2019-01-14 09:03
Press Release
UK Retailer Matalan Selects Oracle to Map Inventory Against Consumer Demand Homeware and Clothing Seller Creates Single Source of Truth with Holistic Retail Planning Solution

National Retail Federation, New York, New York—Jan 14, 2019

A leading clothing and homewares retailer Matalan has chosen Oracle to help maximize its data insights and improve inventory availability and customer service. Matalan’s dynamic growth, planned expansions, and evolving consumer demands across an omnichannel environment drove the need for an integrated planning solution that could provide a single source of truth for its business. With Oracle Retail Cloud solutions Matalan will be able to accurately map inventory assortments against consumer preferences and seasonal trends across 225 stores in the UK and 25 in international markets.

“We continue to invest in innovative technology and tools that enable us to present our customers with a fantastic product range that is accessible where and when they choose to shop,” said Howard Reed, Chief Technology Officer, Matalan.

Matalan continues to innovate more efficient retail experiences that support the needs of the modern family with late operating hours, free click and collect service and a wide assortment of products. As Matalan strives to become the omnichannel leader for family value, the team craved a more efficient approach to planning that would allow them to offer customers a broader range of products with iterative, coherent assortment plan. With the implementation of Oracle Retail Merchandise Financial Planning Cloud Service and Oracle Retail Assortment Planning Cloud Service, Matalan will be able to leverage a single version of truth for inventory and assortment planning, enabling the business to better align to customer preferences while expanding the assortment of relevant products at a macro and store level.

“Our SaaS solutions provide retailers like Matalan with a quick and consistent path to adopt innovations, standardize business processes, and leverage best practice templates through our continuous delivery model,” said Mike Webster, Senior Vice President and General Manager, Oracle Retail. “We look forward to continuing to support the Matalan team in their continued growth as they begin to capture the value of more dynamic inventory assortments for customers.”

Matalan has previously implemented Oracle Marketing Cloud to support online and in-store traffic levels with data-driven insights, build effective campaigns and tune the mobile experience.

Contact Info
Matt Torres
Oracle
4155951584
matt.torres@oracle.com
About Oracle Retail

Oracle is the modern platform for retail. Oracle provides retailers with a complete, open, and integrated platform for best-of-breed business applications, cloud services, and hardware that are engineered to work together. Leading fashion, grocery, and specialty retailers use Oracle solutions to accelerate from best practice to next practice, drive operational agility and refine the customer experience. For more information, visit our website at www.oracle.com/retail.

About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Matt Torres

  • 4155951584

Grupo Nazan Drives Seamless Shopping Experiences with Oracle Retail

Oracle Press Releases - Mon, 2019-01-14 09:01
Press Release
Grupo Nazan Drives Seamless Shopping Experiences with Oracle Retail Mexican Footwear Retailer Taps Oracle to Maximize Omnichannel Customer Engagement

National Retail Federation, New York, New York —Jan 14, 2019

Creating a seamless customer experience across channels, Grupo Nazan has embarked on a digital transformation initiative with Oracle. Grupo Nazan sells footwear brands Impuls, Destroyer, and Playa Mayoreo through 100 stores and wholesale channels in Mexico. With a business that was historically driven by catalog, the company recognized an opportunity to expand digitally and physically with the adoption of a modern retail platform. With Oracle, Grupo Nazan will be able to better reach customers on the channel of their choice, while gaining a centralized view of engagement.

The initiative is supported by a suite of Oracle Retail omnichannel solutions including: Oracle Retail Xstore Point-of-Service, Oracle Retail Customer Engagement, Oracle Retail Order Broker and Oracle Commerce.

“The Oracle Retail solutions will further strengthen our business, with the availability of real-time centralized information,” said Moises Macias Gonzalez, systems and logistics director, Grupo Nazan. “Today we have all the stores implemented. With the close integration between our systems and processes, we have the intelligence we need to reach and attract customers through the channels.”

“Grupo Nazan is inspiring the industry to look for new ways to improve customer experience. After completing their digital transformation, Grupo Nazan can better segment customers to provide more personalized and engaging promotions,” said Carlos Victoria, Vice President Latin America, Oracle Retail. “For example, a recent study showed that 85 percent of Mexican consumers expect granular tracking of their delivery status. With Oracle, Grupo Nazan can meet these kinds of customer expectations as they grow in an omnichannel environment.”

Grupo Nazan worked with Oracle PartnerNetwork Platinum level partner, SkillNet, based on their international Oracle Retail Xstore implementation and digital transformation experience.

“SkillNet conducted a capabilities assessment against our business needs. We took a balanced approach to implement best practices with a few modifications that made sense for our business,” said Javier Alvarado, Technology Project Manager, Grupo Nazan. 

Contact Info
Matt Torres
Oracle
415-595-1584
matt.torres@oracle.com
About Oracle Retail

Oracle is the modern platform for retail. Oracle provides retailers with a complete, open, and integrated platform for best-of-breed business applications, cloud services, and hardware that are engineered to work together. Leading fashion, grocery, and specialty retailers use Oracle solutions to accelerate from best practice to next practice, drive operational agility and refine the customer experience. For more information, visit our website at www.oracle.com/retail.

About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Matt Torres

  • 415-595-1584

Oracle and One Door Collaborate to Deliver Compelling In-Store Retail Experiences

Oracle Press Releases - Mon, 2019-01-14 09:00
Press Release
Oracle and One Door Collaborate to Deliver Compelling In-Store Retail Experiences Combination of Market Expertise, Artificial Intelligence, and Automation Enables Retailers to Optimize In-Store Merchandising and Maximize Sales

National Retail Federation Retail’s Big Show New York, New York—Jan 14, 2019

Oracle Retail and One Door™, a leading provider of cloud-based visual merchandising software, today announced they are collaborating to help retailers improve their in-store merchandising. By integrating the artificial-intelligence infused Oracle Retail Assortment Optimization Cloud Service and One Door Merchandising Cloud, retailers will be able to easily optimize store layouts and assortments, then automatically convert them into interactive, store-specific plans. With this new end-to-end capability, merchandising and store teams will be empowered to both create and execute localized, space-aware assortments that maximize returns at each store.

According to Gartner, Inc., “Retailers will use AI to improve and automate existing and new business processes, and to drive human creativity through deep learning and innovation.”1 Through this new partnership leading retailers can take advantage of Oracle’s AI capabilities and then leverage One Door automation to deliver personalized experiences at scale.

“Oracle is the modern platform for retail that lets brands accelerate their business to drive beyond best practice and focus on customer acquisition and interactions that deliver a lifetime of value,” said Jeff Warren, Vice President Solution Management, Oracle Retail. “This strategic collaboration empowers retailers to plan and optimize inventory strategically, communicate effectively with stores, confirm in-store execution and compliance, and deliver a tailored and high-quality customer experience.”

“By combining our visual merchandising and in-store capabilities with the strength of the Oracle Retail portfolio, we collectively deliver the first-of-its-kind end-to-end capability for merchandising transformation,” said Tom Erskine, CMO and SVP of Product, One Door, “We see the potential to broaden Oracle's already extremely comprehensive product portfolio and the Oracle Retail Reference Library, giving Oracle customers and prospects even more opportunity to affect the required transformation and change with a single vendor (Oracle).”

1 Gartner, Inc., Cool Vendors in AI for Retail, Robert Hetu and Miriam Burt, May 2, 2018

Contact Info
Matt Torres
Oracle
415-595-1584
matt.torres@oracle.com
About Oracle Retail

Oracle is the modern platform for retail. Oracle provides retailers with a complete, open, and integrated platform for best-of-breed business applications, cloud services, and hardware that are engineered to work together. Leading fashion, grocery, and specialty retailers use Oracle solutions to accelerate from best practice to next practice, drive operational agility and refine the customer experience. For more information, visit our website at www.oracle.com/retail.

About One Door™, Inc.

One Door™, Inc. is a leading provider of cloud-based visual merchandising software, optimizing merchandising at each store for the world’s leading brands. One Door is privately held, based in Boston, MA, and led by an experienced team of retail and technology visionaries. One Door™ Merchandising Cloud is the only application that combines product and promotional visual merchandising with store information, providing each store with a unique, unified, and interactive merchandising plan.  For more information, visit www.onedoor.com.

About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Matt Torres

  • 415-595-1584

Announcement: New “Oracle Diagnostics and Performance Tuning” Seminar Now Available !!

Richard Foote - Mon, 2019-01-14 00:26
It’s been a work in progress for quite some time, with many of my customers asking when will it be completed. Well, I’m very excited to announce that I have finally completed my new 2 day “Oracle Diagnostics and Performance Tuning” seminar and that it’s ready to be presented. I already have a number of […]
Categories: DBA Blogs

Getting ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Tom Kyte - Sun, 2019-01-13 15:06
HI happy new year... i have a procedure which executes a big big big query...i will share the behavior of my procedure <code>CREATE OR REPLACE PROCEDURE DUMMY_PROC(DATASET OUT SYS_REFCURSOR,FSID NUMBER) as STR VARCHAR2(32000); BEGIN STR:=' ...
Categories: DBA Blogs

RAMN configuration for daily backup

Tom Kyte - Sun, 2019-01-13 15:06
hi.. we have a production database (Archive log mode) which generates a huge number of archived redo log files(size is getting near 118 GB per day for this files)and we are running out of space in the machine. Before, we were taking backup of the d...
Categories: DBA Blogs

Inserting rows with BULK BIND

Tom Kyte - Sun, 2019-01-13 15:06
Hi, I want insert 500002 rows to my table by applying LIMIT with 10k , now how many times my LIMIT is iterate. Is it 5 times or 6 times. Could plz give a sample example ?
Categories: DBA Blogs

Index creation time estimation with XML Type data in the index

Tom Kyte - Sun, 2019-01-13 15:06
We have a few tables, each of them has a structure something like this: <code> CREATE TABLE "myTABLE" ( "FIELD1" CHAR(36 BYTE) NOT NULL ENABLE, "FIELD2_XML" "SYS"."XMLTYPE" , "FIELD3" NUMBER DEFAULT 0 NOT NULL ENABLE, "FIELD4" NUMBER(...
Categories: DBA Blogs

UTL_MAIL with AUTHENTICATION OF SENDER

Tom Kyte - Sun, 2019-01-13 15:06
i m trying to send an email with utl_mail using this procedure: <code>BEGIN UTL_MAIL.send(sender => 'o.adahanifi@teletic.dz', recipients => 'ao_ada_hanifi@esi.dz', subject => 'UTL_MAIL Test', ...
Categories: DBA Blogs

Oracle Retail Omnichannel Suite Innovations Power New Consumer Experiences

Oracle Press Releases - Sun, 2019-01-13 08:00
Press Release
Oracle Retail Omnichannel Suite Innovations Power New Consumer Experiences New Cloud Service, Greater Inventory Visibility and Mobile Reporting Help Retailers Engage Customers While Improving Operational Efficiency and Insights

National Retail Federation, New York, New York —Jan 13, 2019

Oracle Retail continues its momentum in enabling digital transformation in the retail community with the launch of Oracle Retail Xstore Office Cloud Service. The service centralizes all back-office elements of store operations, eliminating the need for data center investment, providing a faster implementation timeline and creating a more responsive business model attuned to shifts in consumer preferences. With Oracle Retail Xstore Office Cloud Service, retailers can continue to take advantage of cloud solutions while maintaining critical point-of-service functions on- premises.

“Historically retailers have been slow to upgrade their POS solutions, often waiting 5-7 years before considering an upgrade, which has thwarted their ability to keep pace and provide dynamic, engaging experience to their customers,” said Jeff Warren, Vice President, Oracle Retail. “By moving Xstore back office to the cloud, customers can benefit from a continuous cadence of innovation that enables lower their total cost of ownership through centralized services and store operations.”

New Omnichannel Suite Updates

Xstore Point-of-Service, which is operating today in more than 83 countries, is the cornerstone of the Oracle Retail Omnichannel Suite. The solution delivers a comprehensive set of customer and associate experiences that meet the expectations of today’s modern retailers.

“During the 2018 Holiday season, successful retailers relied on accurate inventory as the foundation for fulfilling omnichannel journeys and meeting customer demand,” said Chris Sarne, senior director of omnichannel strategy, Oracle Retail. “This new generation of cloud services provides a real-time view into critical enterprise data that is the foundation for great brand experiences for consumers.”

Today, the Oracle Retail Omnichannel Suite of solutions enables 64 different shopper journey combinations. As part of Oracle’s Omnichannel product portfolio, Oracle Retail Xstore Point of Service is pre-integrated with several cloud services, including Oracle Retail Customer Engagement, Oracle Retail Order Management, Oracle Retail XBRi Loss Prevention and Oracle Commerce Cloud offerings, enabling end-to-end omnichannel retailing. Additional updates to the Oracle Retail Omnichannel Portfolio of Solutions and Cloud Services include:

  • Mobility Enhancements: Retailers look to enable their associates to engage consumers sooner to drive interactions with the brand and deliver a higher level of service. Oracle Retail Xstore Point-of-Service can be deployed on thin client and mobile offerings to allow an associate to capture the sale at the point of intent. Additionally, the store associates and managers are equipped with reporting that enables faster and more efficient decision making.
  • RFID Integration: Many retailers have invested in RFID-based supply chain processes to help ensure the accuracy of supply right through to store inventory counting. To enable maintenance of the accuracy of stock position in the store, Oracle Retail Xstore Point-of-Service can extend the sales and returns processes to include RFID data and pass this upstream through services to supporting RFID enabled inventory systems.
  • Supporting Customer Experiences: As the retailers reinvent their brick and mortar footprints they are recognizing the value of the physical store as a vehicle to provide customers with unique experiences. With investment increasing in destination-based activities such as in-store coffee shops, candy and wine bars, the POS needs to address the requirements of these additional services without alternate software. Oracle Retail Xstore has extended functionality to address these evolving experiences, all within the same Xstore Point-of-Service code base.
  • Easier Workflow: Oracle enhanced the workflow of Oracle Retail Order Management Cloud Service to provide customer service with a more intuitive interface with coordinated and transparent outcomes to fulfillment consumer demand.
  • Customer Context: Oracle Retail Customer Engagement Cloud Service has adopted a role-based, modern user experience for better usability in supporting marketing operations and execution. The cloud service integrates to Oracle Retail Offer Optimization and taps into the productivity of artificial intelligence with integration to digital assistants.
  • Exploiting Science for Speed and Scale: Oracle Retail XBRi Loss Prevention can go directly from identifying exceptions, for example, excessive gift cards, refunds, and exchanges, to identifying either the account or the cashiers that are at risk, or leveraging machine learning to flag business anomalies. This means that loss prevention officers no longer have to chase down the data behind the risk report in order to determine who or what to investigate. Now more than ever, the ability to deliver best-in-class functionality to the field can happen in a fraction of the time required for an enterprise, on-premises solutions. Retailers can implement Oracle Retail Cloud Services in as little as seven weeks from provisioning.
  • Hardware Innovation: The Oracle Retail Xstore Suite has always offered a highly flexible deployment model that includes thick, thin, tablet, handheld options at the store. We are pleased to unveil a new self-service experience on an Oracle point-of-sale kiosk at NRF 2019.
 

Join Oracle Retail at NRF 2019 booth #2321 to demo these experiences.

Contact Info
Matt Torres
Oracle
415-595-1584
matt.torres@oracle.com
About Oracle Retail

Oracle is the modern platform for retail. Oracle provides retailers with a complete, open, and integrated platform for best-of-breed business applications, cloud services, and hardware that are engineered to work together. Leading fashion, grocery, and specialty retailers use Oracle solutions to accelerate from best practice to next practice, drive operational agility and refine the customer experience. For more information, visit our website at www.oracle.com/retail.

About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Matt Torres

  • 415-595-1584

Partitioning -- 13b : Splitting a Partition

Hemant K Chitale - Sun, 2019-01-13 05:46
Let's say the business anticipates growing sales volume in 2019 and new reporting requirements.  IT analyses the requirements and decides that the SALES_DATA Table that is currently Partitioned by YEAR, needs to be Partitioned by HALF-YEAR from 2019 onwards.

SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2016 ARCHIVE_SALES_DATA
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

6 rows selected.

SQL>
SQL> alter table sales_data
2 split partition P_2019 at (to_date('01-JUL-2019','DD-MON-YYYY'))
3 into (partition P_2019_H1, partition P_2019_H2)
4 update indexes
5 /

Table altered.

SQL>
SQL> col high_value format a26 trunc
SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019_H1 TBS_YEAR_2019 TO_DATE(' 2019-07-01 00:00
P_2019_H2 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

7 rows selected.

SQL>


I used the UPDATE INDEXES clause to ensure that all (specifically Global) Indexes affected by the SPLIT are updated so that they don't go into an UNUSABLE state.

I could have optionally used a TABLESPACE clause for each of the two new Partitions P_2019_H1 and P_2019_H2


(Also see a previous BlogPost on using SPLIT PARTITION to add a new Partition at the "end" of the table by splitting the last Partition)



Categories: DBA Blogs

Error while running ggsci

Amardeep Sidhu - Sat, 2019-01-12 08:21

This was another issue that I faced while trying to configure GoldenGate in HA mode. ggsci was working fine after normal installation but after configuring it in HA mode and trying to run ggsci, it resulted in this:

[oragg@node2 product]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Apr 16 2018 00:53:30
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
2019-01-08 16:28:37.913
CLSD: An error occurred while attempting to generate a full name. Logging may not be active for this process
Additional diagnostics: CLSU-00100: operating system function: sclsdgcwd failed with error data: -1
CLSU-00103: error location: sclsdgcwd2
(:CLSD00183:)
GGSCI (exadatadb02.industowers.com) 1>

No obvious clues in the error message but little searching revealed that it had something to do with permissions. It was on Exadata so i tried to do a strace of ggsci and see if it could give some clues. There we go:

[oragg@node2 product]$ strace ggsci
.
.
mkdir("/u01/app/oracle/product/12.1.0.2/dbhome_4/log/exadatadb02", 01777) = -1 EACCES (Permission denied)

That is the Oracle database home, GoldenGate is supposed to use. It is trying to create a directory at the mentioned path and not able to do it. There was another directory called client needed inside this. I created both of them and set the needed permissions & the sticky bit and it worked fine. It was working fine on the other node, so i could check the permissions over there and do the same on this node.

Categories: BI & Warehousing

On-Premise Machine Learning with XGBoost (Katana 19.1)

Andrejus Baranovski - Sat, 2019-01-12 05:14
Happy to announce Katana 19.1 release with complete on-premise support for Machine Learning.



You can run Machine Learning (ML) models on Cloud (Amazon SageMaker, Google Cloud Machine Learning, etc.). I believe it is important to understand how to run Machine Learning in your own environment too. Without this knowledge ML skills set would not be complete. There are multiple reasons for this. Not everyone is using Cloud and you must provide on-premise solution. Without getting your hands dirty and configuring environment yourself, you would miss an exciting opportunity to learn more about ML.

Read more here.

SQL solution to read file with one line content and format them

Tom Kyte - Sat, 2019-01-12 02:26
Hello, Is it possible to write a control file to read a file in the below format (only one line containing multiple records) and split into multiple rows and simply dump into a table (containing only one column TEXT) and load/store these lines ? ...
Categories: DBA Blogs

Sequence of selecting from ordered inner query

Tom Kyte - Sat, 2019-01-12 02:26
Hi Tom, Does selecting from an inner query ,which has an order by clause, guarantee that the final output to be sorted by the order by column in inner query? <code>Select * from ( select * ...
Categories: DBA Blogs

Copy or Migrate a SSISDB environment

Yann Neuhaus - Fri, 2019-01-11 10:42

Integration catalog is easy to work with and the environments feature is as well very convenient.

In SQL Server management Studio, there are a lot of features available to administrate your SQL Server instances, but some are missing that can be very useful. You have the possibility to move an SSISDB environment but this is only possible in another folder of your SSIS catalog. But how if you will to duplicate your environment or move it on another server.

With the below steps, you will be able to generate the script, to create your environment, on the same server changing the folder or/and the environment name or to run it on another server.

  1. Run the script below where you have initially created your SSIS catalog environment

 

DECLARE @FolderName nvarchar(128) = N'SSISDemo'
DECLARE @EnvName nvarchar(128)= 'Env_SSISDemo1'
SELECT 
'DECLARE @FolderName nvarchar(128) = N''' + @FolderName + '''
DECLARE @EnvName nvarchar(128)= ''' + @EnvName + '''
EXEC [SSISDB].[catalog].[create_environment] @folder_name=@FolderName, @environment_name=@EnvName, @environment_description=N''' + e.description + '''' 
as tsql_EnvCopy 
FROM SSISDB.catalog.folders f
INNER JOIN SSISDB.catalog.environments e on e.folder_id = f.folder_id
WHERE f.name = @FolderName
AND e.name = @EnvName
UNION ALL 
SELECT 
'EXEC [SSISDB].[catalog].[create_environment_variable] 
@folder_name=@FolderName, 
@environment_name=@EnvName, 
@variable_name=N'''+ ev.name + ''', 
@data_type=N'''+ ev.type + ''', 
@sensitive='+ CONVERT(NCHAR,ev.sensitive) +', 
@value = ' + 
CASE ev.sensitive
WHEN 0 THEN 
CASE ev.type 
WHEN 'Date Time' THEN ''''+ CONVERT(NVARCHAR(max),ev.value) + '''' 
WHEN 'String' THEN 'N'''+ CONVERT(NVARCHAR(max),ev.value) + '''' 
ELSE CONVERT(NVARCHAR(max),ev.value)
END 
WHEN 1 THEN 
'##########'
END + ',
@description=N'''+ ev.description + ''''
as tsql_EnvVarcreate
FROM SSISDB.catalog.folders f
INNER JOIN SSISDB.catalog.environments e on e.folder_id = f.folder_id
INNER JOIN SSISDB.catalog.environment_variables ev on ev.environment_id = e.environment_id
WHERE f.name = @FolderName
AND e.name = @EnvName
  1. Copy the result of the script (column tsql_EnvCopy) in a copy it in a SSMS Query window connected to the server where your want to deploy your environment.
  2. Adapt the script if needed.
  • On the server where you want to create the new environment, be sure that:
    • The folder with the defined FolderName is existing.
    • FolderName/EnvName combination is not already existing on the server.
    • Change the values of the @FolderName or/and @EnvName if needed.
  • Change the values of the sensitive parameters:

As you can imagine the parameter values cannot be retrieved when they are set as sensitive. If you have such parameter replace the values ########## with the real ones before running the script.

  1. Run the script and you’re done

Cet article Copy or Migrate a SSISDB environment est apparu en premier sur Blog dbi services.

Output to large xml file

Tom Kyte - Fri, 2019-01-11 08:06
Dear all, I need to output a large xml file. I created a procedure and it outputs the file correctly when the number of records is small. Code: DECLARE l_file UTL_FILE.file_type; l_xmltype xmltype; BEGIN SELECT XMLRoot( ...
Categories: DBA Blogs

Real Time SQL monitoring doesn't show my query

Tom Kyte - Fri, 2019-01-11 08:06
Hi TOM, I've got a big query, which normally takes to complete around 90 seconds. I'd like to see the execution details using Real Tim SQL monitoring. Unfortunately, it didn't appear there. Even when I specifically added /*+ MONITOR */ to the qu...
Categories: DBA Blogs

Create a Concurrent Program Manager

Tom Kyte - Fri, 2019-01-11 08:06
Hi Tom, I am trying to develop a concurrent program manager (same Oracle EBS does). My Question is which options you recomend to do it and why? Advanced queue Library, pure PL/SQL, java, OS Level, OCI ??? Thanks in advance. Best Regards...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator