Feed aggregator

Links for 2017-01-11 [del.icio.us]

Categories: DBA Blogs

elegant way to map columns names to row values without system tables?

Tom Kyte - Wed, 2017-01-11 20:46
Hi Tom, We are adding a new component to the client's system. It will need to ingest some data and then display it to users in the GUI based on their customised preferences. We want to load data into columns with sensible names and tie them to t...
Categories: DBA Blogs

Concatenate multiple results into one row

Tom Kyte - Wed, 2017-01-11 20:46
When I query a database that includes a particular field (Condition), it returns multiple rows of Conditions associated with the same result from another column (NCT_ID). See sample output below. NCT_ID CONDITION NCT00531284 Mult...
Categories: DBA Blogs

Limit client connectivity sessions using trigger

Tom Kyte - Wed, 2017-01-11 20:46
Hi, I need to limit/allow client connectivity sessions (ie: toad) connecting to database. My synario: user should not have more than 2 toad connections to database. I need to restrict the client connectivity. Please let me know is this possib...
Categories: DBA Blogs

RefCursors Vs Pipelined Functions (11g)

Tom Kyte - Wed, 2017-01-11 20:46
Hi Team, Could you please go through below scenario - I have a stored procedure lets say SP_BULK_PDF_CARD_DATA( pindate IN VARCHAR2, vcountry IN VARCHAR2, vregion IN VARCHAR2, vpdfemailflag ...
Categories: DBA Blogs

Update-Statement performance differences between childs (v$sql)

Tom Kyte - Wed, 2017-01-11 20:46
Hello, yesterday we recognized that one of our Applications runs very slow. After we did a little bit of researching, we found out that one of our Update-Statements takes significant longer than the day before. After looking into v$sql we saw that...
Categories: DBA Blogs

PeopleSoft at Collaborate 2017

PeopleSoft Technology Blog - Wed, 2017-01-11 19:08

We have a nice slate of sessions planned for the Collaborate conference, which takes place from April 2nd-6th in Las Vegas.  This valuable conference is hosted by Quest, the Oracle user group.  Quest has posted a  PeopleSoft page for the conference, so if you are focused on PeopleSoft, this makes it easier to review the content and choose your agenda. Registration is open now, and you can save by registering before February 16th.

The PeopleTools team will provide several sessions focused on our three main subjects:

  • Fluid User Interface
  • Life-cycle, including Selective Adoption and PeopleSoft Test Framework
  • Cloud deployment for PeopleSoft

We'll also be covering important subjects like security, Elasticsearch, and platforms, and we'll also be doing the ever-popular PeopleTools panel session, where you have the opportunity to ask us any questions you want regarding PeopleSoft Technology.

Of course our applications teams, partners, and customers are presenting many interesting sessions too.  Visit the PeopleSoft page for the conference and go to the Key Topics section.  This makes it easy to discover sessions by topic and focus your selections on the subjects of interest to you. 

Hope to see you there!


Ovum Analyst Report: SWOT Assessment: Oracle Web Experience Management (WebCenter Sites 12.2.1.1.0 and Sites Cloud Service)

WebCenter Team - Wed, 2017-01-11 09:20


The focus of web experience management (WEM) is generally on supporting the customer by offering relevant content and experiences through multiple channels. But that is only part of the story. WEM is also an important system that can be just as valuable when used internally to help employees fulfill their roles more effectively. Oracle offers an extensive WEM portfolio, which includes WebCenter Sites and Sites Cloud Service and combines experience management, mobile collaboration, and process automation to help organizations build compelling experiences for customers and employees. WebCenter Sites can be deployed on-premise or hosted in cloud. Sites Cloud Service is a pure cloud offering. Thus, flexible deployment options are available, making it suitable for a wide range of organizations. This SWOT analysis by Ovum provides recommendations for enterprises considering Web Experience Management, and specifically Oracle's WEM portfolio.

Mexican Retailer Almacenes García Deploys Best in Class Oracle Retail Planning Solutions

Oracle Press Releases - Wed, 2017-01-11 07:00
Press Release
Mexican Retailer Almacenes García Deploys Best in Class Oracle Retail Planning Solutions Fast Fashion Retailer Increases Inventory Turnover and Localizes Assortment

Redwood Shores, Calif.—Jan 11, 2017

Today, Oracle announced that fashion retailer Almacenes García has deployed Oracle Retail Planning and Optimization solutions to support growth and transformation. Almacenes García is a 70-year-old retailer that has shifted from a traditional apparel retailer to a fast fashion retailer. Today Garcia serves insatiable fashion-oriented millennial shoppers.

The demands of fast fashion retailing required Almacenes García to consider finding a solution provider who can help facilitate new production introduction, assortment planning and lifecycle pricing. The fashion retailer selected Oracle Retail to replace an outdated planning solution, to reduce the total cost of ownership through automation, adopt best practices and create a unified technology infrastructure.

“Garcia drives loyalty and gains market share through a single price strategy, the right mix of private label brands, fast inventory turnover and a compelling assortment specific to store type. With the Oracle Retail implementation, we transformed disparate and disconnected functions into a centralized and streamlined process with a collaborative and holistic planning approach,” said Andrei Savtchenko, Director IT, Almacenes García.

Almacenes Garcia selected Retail Consult to deploy Oracle Merchandise Financial Planning, Oracle Retail Assortment Planning, Oracle Retail Clearance Optimization and Oracle Retail Item Planning to support over 150 store locations across Mexico in just under 16 months.

“The Oracle Retail solutions include a rich tapestry of retail best practices, process workflows and architecture recommendations called the Retail Reference Library. This library gives you the confidence to deploy a vanilla implementation, and rapidly realize benefits.” said Andrei Savtchenko, Director IT, Almacenes García.

“At Oracle Retail, we are honored to serve over 5,000 retailers around the world. The power of this community is reflected in the development of our solutions,” said Ray Carlin, Senior Vice President and General Manager, Oracle Retail. “As an early adopter of Oracle Retail Assortment Planning and Clearance Optimization, Almacenes García’s experience will help to enhance future releases and improve the solutions for future customers.”

Oracle Retail at NRF 2017

Oracle Retail will be showcasing the full suite of Oracle Retail solutions and cloud services at the National Retail Federation Big Show Jan. 15-17, 2017, in New York City at the Jacob K. Javitz Convention Center. Oracle Retail will be located at booth #2521. For more information check out: www.oracle.com/nrf2017

Contact Info
Matthew Torres
Oracle
+415 595 1584
matt.torres@oracle.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit 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.

Safe Harbor

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Matthew Torres

  • +415 595 1584

Web-Based RPD Upload and Download for OBIEE 12c

Rittman Mead Consulting - Wed, 2017-01-11 06:15

I was among the people who were dancing and singing after finding out some of the OBIEE 12c new features. The feature I liked the most was a scripted deploy of an RPD file from a developer’s computer. I hate to make dozens of clicks for every deploy of an RPD in 11g. You may object and say that there is WLST in 11g which can do the same and even more. Well, you are right. Except for one thing: WLST is a server-side thing. Information security folk don’t like to give direct access to a server to OBIEE developers. And not every developer is capable of using it.

In OBIEE 12c the only way to upload and download RPDs from a developer’s local machine to the OBIEE server is through the command line. We’re big fans of the command-line approach because it enables automation, reduces the risk of error, and so on. But not all people like a script everything approach as we do. Many of OBIEE developers don’t like to use a command line to do what they used to do with their mouse for years. And today we have a solution for them!

Disclaimer. Everything below is a result of our investigation. It’s not a supported functionality or Oracle’s recommendation. It makes use of undocumented web services that Oracle may remove or change at any time.

Some time ago Robin Moffatt lifted the lid on OBIEE 12c Web Services. He found out how to use curl to do the same things Oracle does with their data-model-cmd (datamodel now) script. But that was purely for geek interest and intended to give us more understanding of what's going on inside of the OBIEE, not give us a new tool. So the next obvious step was to make a user-friendly interface over that web services so any OBIEE developer could utilise this sacred knowledge.

The Simplest Sample

Modern computer technologies offer us a lot of tools to build GUIs, but we wanted to keep it as simple as possible and because OBIEE’s front end is web-based, use of HTML for our RPD tool was the obvious choice too.

Download

Let's start with RPD download.
Here is the curl script to call OBIEE web service and get RPD file.

curl -X "POST" "http://192.168.0.66:7780/bi-lcm/v1/si/ssi/rpd/downloadrpd" \  
--data-urlencode "target-password=Admin123" \
--basic --user weblogic:Admin123 \
> downloadrpd.rpd

an animated gif of curl download

As you can see it's pretty simple. We send a message to http://<host>:<port>/bi-lcm/v1/si/ssi/rpd/downloadrpd using POST method. As a parameter, we send a password to set to the downloaded RPD file (target-password=Admin123) and authentication information (weblogic:Admin123). As a result, we get bytes of the RPD which we redirect to the downloadrpd.rpd file. And now we want a GUI for this script. Actually, Robin already did it.

<html>  
   <body>
      <FORM action="http://192.168.0.66:7780/bi-lcm/v1/si/ssi/rpd/downloadrpd"
         method="post" target=_blank>
         <P>
           New password for downloaded RPD file? <INPUT type="password" name="target-password"><BR>
            <INPUT type="submit" value="Send"> <INPUT type="reset">
      </FORM>
   </body>
</html>  

pic of a local download HTML form

This is not a snippet of code you somehow should incorporate into your system. No. That's almost complete GUI for RPD download! The only thing you need to do is to change hostname and port to match your system. That's all. Simply create an HTML file, put this code into it, change host and port, open with a browser, Enjoy!

This form has no field for authentication because OBIEE server will ask us for login and password at the first call and will maintain this session later.

Upload

The upload was a little bit more tricky from the curl side. Let's take a look at the script:

curl -X POST \  
     "http://192.168.0.66:7780/bi-lcm/v1/si/ssi/rpd/uploadrpd" \
     --form "file=@sample.rpd;type=application/vnd.oracle.rpd" \
     --form "rpd-password=Admin123" \
     --basic --user weblogic:Admin123

an animated gif of curl upload

Here we call another service to upload our file. Our parameters are:

  1. sample.rpd - the RPD file to upload
  2. type=application/vnd.oracle.rpd - MIME type of this file (that was the main trick).
  3. rpd-password=Admin123 - the password of sample.rpd
  4. weblogic:Admin123 - information for authentication.

But the GUI for this task is surprisingly simple. I expected it to be monstrous with lots of JS but in fact, it is small and easy. The minimum viable version is almost as simple as the upload one.

<html>  
   <body>
      <FORM action="http://192.168.0.66:9502/bi-lcm/v1/si/ssi/rpd/uploadrpd"
         method="post" enctype="multipart/form-data" target=_blank>
           File to upload <INPUT type=file name="file"><BR>
           Password for uploaded RPD file? <INPUT type="password" name="rpd-password"><BR>
           <INPUT type="submit" value="Send"> <INPUT type="reset">
      </FORM>
   </body>
</html>  

pic of a local upload HTML form

The use of this piece of code is exactly the same as for download. Simply put it into an HTML file, change host and port. Use it.

Keep in mind that for the both forms field names are fixed and shouldn't be changed. For example, the field for a file to upload should have name "file" and for a password - "rpd-password". Without it, magic won't work.

But there is a thing about this part that we could still improve. Depending on the browser you use it shows the response message either in the same window or downloads it as a text file. And this message is a JSON file.

In real life, this message is a one line JSON but here it is a more human-readable formatted with jq and slightly polished by hands.

{
  "clazz": ["rpd-response"],
  "links": [
    {
      "href": "http://192.168.0.66:7780/bi-lcm/v1/si/ssi/rpd/uploadrpd",
      "rel": ["self"]
    }
  ],
  "properties": {
    "entry": [
      {
        "key": "si",
        "value": {
          "type": "string",
          "value": "ssi"
        }
      },
      {
        "key": "description",
        "value": {
          "type": "string",
          "value": "RPD upload completed successfully."
        }
      },
      {
        "key": "desc_code",
        "value": {
          "type": "string",
          "value": "DESC_CODE_RPD_UPLOAD_SUCCESSFUL"
        }
      },
      {
        "key": "status",
        "value": {
          "type": "string",
          "value": "SUCCESS"
        }
      }
    ]
  },
  "title": "RPD-LCM response, SI=ssi, action=Upload RPD"
}

As you can see here, we have "description" field which holds a human readable message, "desc_code" field is the same but more suitable for automated processing and "status" field which is the first candidate to be used in automatic procedures.

It's easy to read this file but most of the time you'd prefer a simple "Success" message, right?

Going Further

These HTML forms do the trick. A developer can now download and upload RPD file easily with a minimum of clicks and without a need to learn a command-line interface. Security is managed by Weblogic server. Sounds good, right? But we can do it even better. From my point of view absolutely necessary improvements are:

  1. Add some JS to make diagnostics more user-friendly.
  2. Put these forms to a server so every developer in an organisation can use them.
Adding Some JavaScript Magic

My intent from the very beginning was to keep things as simple as possible. I’m not sure that this time my choice of JavaScript library (JQuery) was the simplest for this task, but anyways the code I have to write is very small so I like it.

<html>  
<head>  
<script src="./jquery-3.1.1.min.js"></script>  
</head>

<body>

<script>  
$(document).ready(function(){
$("#upload").on('submit', function( e ) {
        e.preventDefault();
    $.ajax( {
        url: $(this).attr('action'),
        type: 'POST',
        data: new FormData( this ),
        processData: false,
        contentType: false,
        dataFilter: function (data, type){$("#response").html(JSON.parse(data).properties.entry[1].value.value);$("#response").append('<details>'+data+'</details>');}
    });
});
});
</script>

<FORM id=upload action="http://192.168.0.136:9502/bi-lcm/v1/si/ssi/rpd/uploadrpd"  
         method="post" enctype="multipart/form-data">
           File to upload <INPUT type=file name="file"><BR>
           Password for uploaded RPD file? <INPUT type="password" name="rpd-password"><BR>
           <INPUT type="submit" value="Send"> <INPUT type="reset">
</FORM>

<div id=response></div>

</body>  
</html>  

The script reads the form and sends it content to the server, then it reads the answer, parses it and shows in a user-friendly way. Note that it does need the Jquery library to work. The problem with this code is that it won't work locally. If you try to use it in the same way as previous samples it won't do anything. But if we take a look at the developer console of the browser we immediately find the answer. OBIEE blocks my cross-domain JavaScript call.

a pic of developer console

That could become a problem but I was going to put these files on a server anyway so that all developers could access it.

Deploying It to a Server

What I want to do now is to put my forms to some place accessible with a browser from a server where OBIEE works. To achieve that I should do a few steps.

  1. Create a directory on the server.
  2. Put my files to that directory.
  3. Expose the directory with a web server.

There are no special requirements for the place for a directory I will create. It just should be accessible by a web server (Weblogic). I prefer to keep all user content in one place so my choice is to place it somewhere inside $ORACLEHOME/userprojects.

a pic of location

But there is one special requirement for the directory content. It should have a subdirectory WEB-INF with web.xml file inside.

a pic of web.xml

For my current purposes, almost empty web.xml is just fine. That may be not the best option for the real life but I'm trying to keep things simple, remember?

<?xml version="1.0" encoding="UTF-8"?>  
<web-app xmlns="http://java.sun.com/xml/ns/javaee"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
    version="2.5"/>

I combined both download and upload forms into one rpdtools.html file and added some styling for a nicer look. From the functional point of view, these forms and script were not changed. And then I put this combined file and Jquery library into my "static" directory.

a pic with directory contents

Now everything is ready for the final step. I need to deploy this directory to Weblogic server so the users can access it with a browser.

Login to Weblogic console and deploy static folder as an application.
a pic of wls console

For more details on deploying folders to WLS, see the Official documentation on web.xml and Deploying Using Shared Folders.

And now the most exciting part of the process. Witness the power of this fully operational battle station! I mean RPD tools.
a pic of the final result

Summary

We showed here a very simple way of adding a web-based GUI for uploading and downloading RPD to any OBIEE 12c system. You can take it and adjust to suit your needs and be a useful day-to-day tool. Deploying this code to a server allows to give an access to it to all OBIEE developers in an organisation and add some cool JavaScript effects. But keep in mind that it uses non-documented services and is not supported by Oracle in any way. This means that it can stop working after an upgrade. Well, in that case, we'll have to invent something new for you.

In case you want to play with this tool, here is a link to our GitHub obi-web-rpd-tools.

Categories: BI & Warehousing

Opening Cursor

Tom Kyte - Wed, 2017-01-11 02:26
Hi Sir, I have a query of size 70,000 character when I open cursor, it is not opening. what should i do for this type of query. How to find length of string which has more than 4000.
Categories: DBA Blogs

undo buffer

Tom Kyte - Wed, 2017-01-11 02:26
Hi Team, Can you explain below questions in details?. 1.Does undo buffer exists or changes will directly write to undo datafiles? 2.Does redo contains both undo & redo related changes? 3.If db crashes with uncommitted data in undo buffer ...
Categories: DBA Blogs

Library cache lock for wrong passwords

Tom Kyte - Wed, 2017-01-11 02:26
Hi, I want to know what exactly happens in the library cache which in turn puts the users into lock/delay logins when a user tries to login multiple time with wrong password. If you could explain me regarding this it would be a great help. T...
Categories: DBA Blogs

Questions about Oracle TimesTen cache Oracle DB data under Java application

Tom Kyte - Wed, 2017-01-11 02:26
Hi Team: One of our customer wanna construct a db system which includs both OLTP and OLAP architecture(like SAP HANA) to gurantee the speed of historical data searching and everyday transactional processing. As Jim Gray said "Tape is Dead, Disk is ...
Categories: DBA Blogs

Operator Error

Tom Kyte - Wed, 2017-01-11 02:26
Trying to use an operator to create an "overloaded" PL/SQL function. The goal of the function is to accept 3 different formats (Timestamp with timezone, Timestamp, and Date) and return just a timestamp withzone. I am getting different errors ...
Categories: DBA Blogs

expdp error

Tom Kyte - Wed, 2017-01-11 02:26
Hi, I have taking the expdp logical backup for schema it shows an error like - ORA-31693: Table data object "CPG_PROD"."TBGPSDATA" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-01555...
Categories: DBA Blogs

Wait event

Tom Kyte - Wed, 2017-01-11 02:26
Hi, I have awr report in there Foreground events are there can you pls help to know how to solve this events to improve the db performance. Top 5 Timed Foreground Events library cache: mutex X DB CPU log file switch (checkpoint inco...
Categories: DBA Blogs

When oracle use different plans when the statements use bind variables

Tom Kyte - Wed, 2017-01-11 02:26
An application frequently executed similar types of queries that vary only in the usage of literals in the WHERE clause. You plan to use bind variable in place of literal values. The CURSOR_SHARING parameter to set to EXACT. Which two statement...
Categories: DBA Blogs

How to create an alert to monitor RMAN archive backup job that exceeds one hour runtime.

Tom Kyte - Wed, 2017-01-11 02:26
Is there a SQL script, template or OEM alert that can send an email alert when a RMAN archive backup job exceeds one hour? This is for a standalone 11.2.0.4 database running AIX 7.1. The OEM version we have is 12.1.0.5. Thank you
Categories: DBA Blogs

Upgrade nach #Oracle 12c selbst testen: Mainz, 2. bis 3. Februar

The Oracle Instructor - Wed, 2017-01-11 02:10

noon2noon

Die Deutsche Oracle Anwender Gruppe (DOAG) bietet diesen interaktiven Workshop an. Eine sehr gute Gelegenheit, das Upgrade einmal selbst mit der Unterstützung renommierter Experten durchzuführen. Ich werde einen Vortrag zum Thema Common vs. Local beisteuern. Das ist eins der tendenziell komplizierten Themen, mit denen Einsteiger in Multitenant konfrontiert werden.


Tagged: DOAG
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator