Category Archives: Analytics

Generate DDL script for all tables of a schema in SQL Developer

Hello Friends,

I am back after a long vacay !! and thought to add some pointers to my blog. I have searched a lot about this topic and found least information. So here is step by step process to Generate DDL scripts for all tables in a schema in SQL developer, without much work.

If you want only DDL scripts together in 1 view, you can use below query to run in SQL developer schema, where you want output:

SELECT DBMS_METADATA.GET_DDL(‘TABLE’, TABLE_NAME) FROM USER_TABLES;

Next way to get separate DDL scripts is to :

  1. Go to FILE -> DATA MODELLER -> EXPORT -> DDL FILE
  2. New pop up window appear
  3. i1Click on Generate button
  4. New pop window appears
  5. Now click on “Generate DDL scripts in Separate Files”, on screen at bottom right.i2
  6. Now go to tab “Include TABLE DDL scripts
  7. Select table you want to have DDL scripts
  8. click ok. It will ask for location, where to store. Just set all this and you are good to go.

 

Happy Working 🙂

 

 

 

 

Advertisements

ETL ODI Error |ORA-01792: MAXIMUM NUMBER OF COLUMNS IN A TABLE OR VIEW IS 1000

Are you seeing the error “ORA-01792: MAXIMUM NUMBER OF COLUMNS IN A TABLE OR VIEW IS 1000” when the original query does not select as many columns.

Cause
——–

The problem matches Unpublished bugs as per the Oracle documents:

Bug 19653859 – CI BACKPORT OF BUG 19509982 FOR INCLUSION IN DATABASE BP 12.1.0.2.2
Bug 19509982 – DISABLE FIX FOR RAISING ORA-1792 BY DEFAULT

Solutions:

  1. Oracle suggests to alter the query and/or view definitions to avoid the error.
  2. However in cases where the SQL cannot be adjusted then the checking can be disabled by:
    SQL> alter system set “_fix_control”=’17376322:OFF’;
  3. Alternatively, an interim patch(patch 19509982) can be applied to disable the error by default.

References:

•Bug 19509982 Disable raising of ORA-1792 by default

You can click here to read more

 

 

Examples:

SQL> alter session set “_fix_control”=’17376322:OFF’;

or at system level :

SQL> alter system set “_fix_control”=’17376322:OFF’;

OR

Apply Patch 19509982 if available for your DBVersion and Platform

To download the patch, please follow the steps below:
1) Go To MOS
2) Click on Patches & Updates
3) In the Patch Search Section, enter your patch number <19509982> in the Patch Name or Number field.
4) Click Search
5) Click the patch that meets your DbVersion and platform
6) Read any applicable notes before downloading, then click the download button.

 

Information courtesy : Google/Oracle Docs

How to Google any column value from OBIEE

Now a days end users demand a lot of GUI interactivity in Obiee reports. They are really happy if obiee reports are full of helpful functionalities. One of them is to Google out the value they click on. So this was actually my business requirement few days back. Its not much tough to implement and requires no config or RPD changes.

Steps to do it :

  1. Create a new Action. Go to New -> Action -> Navigate to a Web PAge
  2. Type http://www.google.com/search?q=:   in url section on window
  3. then click on Define Parameters in right side of window.Remove the “optional” mark for the parameter like below:
  4. img1

    Save this action with any name you remember.

  5. Now go to your Analysis, where you wanna call this action through any column Interaction.
  6. On column properties -> Interaction -> Primary Interaction, select Action Links.
  7. Now click on + sign to add Action link
  8. Now in new pop up window, clink on Select existing action
  9. Identify your Saved action and click ok.
  10. Now edit this Action link and select the “column value” as the parameter value, as shown below
  11. then Select the relevant column and tick the boxes for “hidden” (and “fixed”) options. click ok to save.
  12. click ok and check your results. When you click on any mapped column value, Google window will open up searching that value.

 

Follow For more !!

 

How to get YYYYMM format from calendar date without using CAST as Char

So today i am here to discuss the scenarios of CAST function in OBIEE11g or 12c both. We always see that we always run behind using CAST function for conversion to Char/Int/Date etc. But issue appears when we have to get a diff format in Date functions.

For example, i had a scenario where user wanted to see report in pivot form, with Prompt  Date range as 2017 / 01  to 2017 / 04.

I tried getting above values in Prompt by using Convert Calendar date to Char using Cast function. But when i have to pass this prompt value to my report, it was not working as expected. If  i select Date range from 2017 / 01  to 2017 / 04, it use to show only data in report for 2017 / 01 , 2017 / 02, 2017 / 03.  It use to omit 2017 / 04.

To resolve this issue, we used a simple logic in Report Prompt, as below. This gives us the output in little different format , but still no CAST is used,so no conversion to Char and then having issues passing value.

Using below idea, we are now able to get output in proper format and it passes correctly to report and filters it.

imgg

Output shows below:

Follow for more 🙂

How to navigate to external links from obiee reports

Sometimes we get different business requirements, which are meaning-full but little trick to implement in OBIEE. Similarly , last week i got a requirement for business to add a hyperlink on a report column, which when clicked goes to an external link. also, along with it i have to pass the CodePin and Number.

I tired many ways of passing parameters in Go Nav, GO url format. But it didnt work. Finally <a Href> worked, so sharing my solution with you.

Step 1: Edit column formula and place your code in below format. Concat all the parameters to be passed. Set Target and give it a name.

Step 2: Modify the same column and set its data format as below.

Let me know if you face any issues.

Follow for more !!

 

How to Remove Null Value from Dashboard prompts in OBIEE

Currently i am facing a weird issue in Dashboard prompts of OBIEE 12c.There are invalid Null values that are not present in database but showing in prompt columns in OBIEE. For some of the columns Null Values is valid but for other it is not. So Business wants to display null value in prompt only if it is valid and appears in DB column as well.

There are two ways to fix this issue,

a) edit this prompt and check the ‘Require user input’ property (doesnt work max times)

b) At RPD level , uncheck the Nullable option under the physical column as shown below:

obiee snippet

Then restart / recycle all the servers. Untill then this change wont get impacted. Do it for only necessary columns which will never have NULL value in DB also.

Follow for more on my FB page

or my OBIEE group

%d bloggers like this: