RSS
 

Posts Tagged ‘oracle’

Oracle Database Tips #3: Get object DDL

24 Feb

Sometimes is useful to get a table sql definition without including any data.

Even if you can use the Oracle exp utility (link) by using the option rows = n, you can also use the DBMS_METADATA package, by executing the function GET_DDL as shown below:

SQL> set heading off;
SQL> set echo off;
SQL> Set pages 10;
SQL> set long 1000;
SQL> select dbms_metadata.get_ddl('TABLE','MY_TABLE') from dual;

  CREATE TABLE "MY_SCHEMA"."MY_TABLE"
   (    "COL_1" NUMBER,
        "COL_2" VARCHAR2(8)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

Depending on your needs, you can get the DDL of many object types (not only tables, but also views, synonyms…); you just have to specify the object type in the first argument of the function, by referring to the official documentation.

 
2 Comments

Posted in Database

 

Oracle Database Tips #2: How to replace the occurrence of two or more spaces with a single space using REGEXP_REPLACE

17 Dec

The REGEXP_REPLACE is an Oracle PLSQL function that works almost in the same way of the REPLACE function but extends its functionalities by using regular expression pattern.

In the following example I show you how to replace in a given string each occurrence of two or more spaces with a single space.

SQL> SELECT REGEXP_REPLACE('Too        Many       Spaces','( ){2,}', ' ') REPLACED_STRING FROM DUAL;

REPLACED_STRING
----------------
Too Many Spaces
 
No Comments

Posted in Database, Development

 

OBIEE Tips #2: Remove BI Publisher link form the “More Products” menu list

24 Nov



To remove the BI Publisher link from the More Products menu list in the dashboard, we need to edit the  <OracleBI>/web/msgdb/messages/commonuitemplates.xml file as follows:

1. Take a backup of the original file

2. Open the file and find the following line:

<a class="NQWMenuItem" name="advReporting" href="@{advReportingURL}" target="@{target}"><sawm:messageRef name="kmsgUIAdvancedReporting"/></a>

3. Comment the line as follows:

<!-- <a class="NQWMenuItem" name="advReporting" href="@{advReportingURL}" target="@{target}"><sawm:messageRef name="kmsgUIAdvancedReporting"/></a> -->

4. Now we should restart the Presentation Services in order to ge the desired result.

This tip is also useful if you want to add/edit new links to the menu list; you just need to preserve the structure of the file and use the right xml syntax to add new items.

 
2 Comments

Posted in Business Intelligence

 

Natively compiled Java stored procedures using NCOMP

24 Oct

1 – Introduction

Few years ago I had the chance to make an integration between the Oracle Database and a rating engine (“WARP4“) developed by ERIS4 company.
The goal was to rate telephone traffic records stored in the database, by sending them to the rating engine.

WARP4 provided some API to allow external application to send records and rate them; this comunication was made via socket layer. In order to increase the performances I had to make several trials and one of my test cases was about the native compilation.

I personally think that Java stored procedure native compilation it’s not worthy, especially when your Java classes need to open frequently a socket connection.
Because the native compilation is a really rare activity, I thought that this post could be useful for those who want to test the ncomp benefits.

Read the rest of this entry »

 
3 Comments

Posted in Database, Development

 

How to catch report physical query and increase the log level in OBIEE 10g

18 Oct

1 – Introduction

I write this post because many times people asked me to help them by setting up the OBIEE log level in order to do not face the “No Log Found” warning message in the Manage Session console.

2 – Catch a report physical query

Sometimes is useful to catch the physical query that OBIEE executes when a report is running. To get the query we need to access the presentation services with the Administrator’s user and identify the running report by click on Settings -> Administration -> Manage Sessions; once the report is identified we click on the related “View log” link.

If we get a “No Log Found” warning message, we need to increase the OBIEE 10g log level by following the instructions below. Read the rest of this entry »

 
2 Comments

Posted in Business Intelligence

 
« Older Entries