Oracle – a simple function to format numbers for display

February 16, 2014

We have requirements to produce Payment Requests through Oracle E-Business suite custom interface, where the users are allowed to select different currencies based on the payment.

Different currencies means different precisions, US $ 2, Indian Rupees 2, while Kuwait, Bahrain have 3 precisions to maintain…

So the amounts displayed on reports needed to be formatted properly and we were doing some hardcoded formatting like

Now we have more currencies to deal with, hence came up with following simple function, which accepts the amount, precisions as inputs and returns a VARCHAR2 string as formatted number!

Function

CREATE OR REPLACE FUNCTION xx_format_number (P_AMOUNT IN NUMBER,
P_DEC_PREC IN NUMBER)
RETURN VARCHAR2
IS
frm_1 VARCHAR2 (30) := 'fm999999999990.';
new_num VARCHAR2 (30);
BEGIN
SELECT TO_CHAR (P_AMOUNT,
RPAD (frm_1, 15 + P_DEC_PREC, '90000000000000000'))
INTO new_num
FROM DUAL;

RETURN (new_num);

END;

You can check the function like following

SET SERVEROUTPUT ON;

DECLARE
l_new_num VARCHAR2 (30);
BEGIN

l_new_num := xx_format_number(.009,3);

DBMS_OUTPUT.PUT_LINE (l_new_num);
END;

Check it out, and let us know whether it worked for you!

for Windows7bugs

admin

Advertisements

Quest Toad or Oracle SQL developer 4.0 (Early build)

November 3, 2013

 

There are no disputes, Quest Toad for Oracle is the BEST, for a programmer, DBA in an enterprise environment. Toad comes with a high price TAG, which makes many organizations which are not software development oriented, however do minor scale development to fill the business gaps, against Oracle databases, to make compromises and thus silently supporting pirated copies in distribution.

We were using Oracle SQL developer from last many years as our scenarios were mostly restricted to custom development and the DBA activities were managed by the console interactions itself.

From last few days we started checking out Oracle’s latest build (Early build) of SQL developer 4.0 and the first impression is quite fascinating. The prerequisites are limited to JDK 7.x which is becoming standard deployment in almost all scenarios.

A quick note about installing JDK7. Do not install JRE 7 yet, your ERP R12 instance may note support it, or minimum in our case. Install JDK always in a folder without spaces, like C:\JDK, D:\JDK7 etc

You can download the archive for the early build from Oracle download repositories

(JDK is not included, however from the same page you can download the installer)

Unzip the Developer archive (.zip) file to a local folder. The initial run will prompt for JDK location, which you can point to.

What is New

According to Oracle, these are the few new features added with SQL Developer 4.0

Oracle SQL Developer

New Features in Release 4.0

  • Database Performance Monitoring

    SQL Developer now provides a full interface for Oracle Database Enterprise Edition’s Diagnostic pack, including Automatic Database Diagnostic Monitor (ADDM), and Active Session History (ASH). Accessible from the DBA panel, users can also manage their snapshots, baselines, and baseline templates.


  • Find Database Object – Search

    The database search is completely redesigned. The new tree layout allows for searches on multiple schema, object type, identifier types and usages. A history of your searches will be stored for later access. Search results can be viewed in a separate Editor where multiple search results can be pinned and displayed.

  • Command Line

    Command Line support has been extended to Reporting and to the Cart. You can run Reports and generate them into HTML format through the command line. The Cart  commands offer the ability to export objects and data. A set of predefined DDL generation options and data formats can be applied.


  • Cart

    The Cart has been greatly enhanced. You can create and work on multiple carts. The deployment of objects has been extended to give you more flexibility in selecting export DDL options and data formats. Export Object configurations can be saved and applied again. Compare Cart objects to another Cart or Connection with the Database Diff. Cart objects can also be copied with the Database Copy feature. Scripts are extended to include Before and After Data Load.

  • Reports

    Creating reports has never been easier. The create new report dialog has a tree layout for ease of navigation. An Advanced Option is available to instantly create Child and Drill Down type reports. Chart based reports include more chart types and you can test reports from anywhere within the report editor. Charts can be previewed in design mode. An HTML Report can be generated for any report. Enhancements in Database Doc generation include better layout and support for multiple threads to improve performance.


  • UI Enhancements

    The new UI changes in 4.0 enhances the overall look and feel of SQL Developer. A popular request on the Feature Exchange, a window border color can be dynamically set for a database connection. Useability improvements can be seen with with the new floating Editor and Navigation windows.

  • Data Modeler 4.0

    This version includes the new Data Modeler release 4.0. New features include 12c support for IDENTITY columns and masking of sensitive data with Redaction policy definitions at table and column level, create Transparent Sensitive Data Protection (TSDP) policies and associate it with sensitive types. See the full list of New Features here.

  • JDK 7
    JDK 7 Support

    This release supports JDK 7 and above. Download JDK 7 here.

 

Startup

Startup

Welcome screen

NWScreen

You will be asked whether the previous configurations should be migrated, incase if you were using previous versions of Oracle SQL developer. “Yes” will help you to save certainly a great amount of time, recreating the connections

The major difference between SQL developer and Quest Toad you may notice will be the look and feel, while Toad has a clear crisp interface to work with, which looks like earlier versions of Microsoft Office, the total look and feel of Oracle SQL Developer scores many less points. If you are from a pure #Linux environment, there is a high possibility that such look and feel experiences will not affect you much. On the other hand, sorry, your beauty expectations may be compromised to a certain extend.

As the name goes, SQL developer is mainly targeting SQL/PLSQL developers, hence if you are going to compare apple to apple, there are high chances that you will find Quest Toad a better partner, especially if you want to deal with the DBA activities also from the same console.

Memory consumption footprints

Let us look at a major concern for most of us, the memory consumption. Until recent days, laptops/notebooks were mostly equipped with decent 4GB memory, which was more than enough to do your stuff. However, with the emergence of 64Bit operating systems like Windows 7 & 8 the requirement for more memory has significantly changed and the latest default has become a minimum of 8GB.

MEMORY CON

TOAD MEM

A session with just connection established, SQL developer consumes 700 and more memory, while Quest Toad consumes less than 90MB the maximum, which could draw a line of concern, unless you have plenty of memory to spare. Please check the images above

DATAMANIPULATE

Other than the CPU & Memory demands, SQL developer provides almost all functionalities against populated data as in with Quest Toad.

Now, if you or your organization could afford to pay the high license fee Quest Toad demands,we will recommend Quest Toad as a primary choice as it comes with multiple flavors tailored for particular requirements like

  1. SQL/PLSQL development
  2. DBA Activities
  3. Performance monitoring

while Oracle SQL Developer is absolutely free, provide almost 70-80% of Toad’s functionality for a developer, at a high memory, CPU consumption compromise.

If you have thoughts, concerns, suggestions towards this post, please post them to us in the comments section.

regards,

for Windows7bugs

admin


Are you in Kuwait & looking for free trainings on Oracle technologies?

October 10, 2013

 

Hey, are you in Kuwait & looking for free trainings on following Oracle technologies/topics?

  1. Oracle database 10g onwards : Basics (Installation, level 1 interactions with database, sorry no advance level DBA activities)
  2. Oracle developer suite 6i, 10g & 11g : Basic & Advanced
  3. SQL/PLSQL (Oracle): Basic & Advanced
  4. Weblogic Server 10.3.6: Basic & Advanced (Collaborative)
  5. Oracle E-Business Suite: Concepts, basics, technical advanced level involving first level DBA activities like cloning and simple patching
  6. Linux: Basics which can help you to manage Oracle products on Linux (Red Hat Linux, Oracle Unbreakable Linux, CentOS)

& more

Feel free to drop me a e-mai address (please avoid absolute account names like abc@xyz.com, instead use abc at xyz dot com format to avoid spams reaching your inbox)l, contact numbers (post them like 9 9 9 9 1 2 4 5) with the comment session and I’ll contact you. As the topic header says, I’ll provide you free sessions based on my available timings

Language(s) I can handle

  1. Malayalam
  2. Tamil
  3. Hindi
  4. English

And you as a “student” must have a laptop/notebook with minimum 4GB memory and 500GB HDD (internal or external) to carry out the exercises. I won’t go to your place, you will to my place :)

 

regards,

for Windows7bugs

rajesh


Install Oracle database 10.2.0.3 onwards on Windows 8.1 Preview

July 4, 2013

 

If you came here for answer, the first answer is yes, you can install Oracle 10.2.0.3 onwards database, as in you have done it on Windows 7, 8 following our other posts.

Just change the refhost.xml and add an additional entry for Windows version 6.2 (If have done it for Windows 8 already, you don’t have to change anything)

Go ahead and install the database and enjoy

image

Regards,

Admin


Oracle imp VS Quest Toad’s import utility Wizard

May 19, 2012

 

Quest Toad may the best Oracle database tool developed by a third party. However, this costly tool may not be the best one for importing objects when you are in a process of migrating from an older version of database to relatively new one! (Or minimum our experiences prove this multiple occasions)

Oracle’s import tool (imp.exe  on windows) is a console based utility and could be used without worrying the headaches usually Toad creates.

Below we are providing a “kind” of instructions about importing objects from a full database backup “.dmp” file which is created using Oracle’s export console utility

image

Prior importing objects, please make sure you have created the following with fresh installed database

  1. Tablespaces
  2. Users
  3. Grants to the users (CONNECT, RESOURCE, DBA etc)
  4. A parameter file, call it “myparam.txt” (preferably in the same directory where you store the “.dmp” file. We prefer to keep both the files with root of a partition itself)
  5. image(Sample parameter file)

Once you created the above objects, you may proceed towards importing the user objects (tables, views, functions, packages, procedures etc)

Now open command prompt and type the following

C:\> imp system/<<password>> PARFILE=paramtext.txt LOG=C:\19052012imp.log

Now sit back while Oracle does the import job for you!

 

Regards,

Admin


Oracle Developer 10g 10.1.2.0.2 Crash on Windows 7 while opening forms modules!

May 21, 2011

We are using Oracle developer 10g, details:

Forms [32 Bit] Version 10.1.2.0.2 (Production)
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
Oracle Toolkit Version 10.1.2.0.2 (Production)
PL/SQL Version 10.1.0.4.2 (Production)
Oracle Procedure Builder V10.1.2.0.2 – Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle Query Builder 10.1.2.0.2 – Production
Oracle Virtual Graphics System Version 10.1.2.0.2 (Production)
Oracle Tools GUI Utilities Version 10.1.2.0.2 (Production)
Oracle Multimedia Version 10.1.2.0.2 (Production)
Oracle Tools Integration Version 10.1.2.0.2 (Production)
Oracle Tools Common Area Version 10.1.2.0.2
Oracle CORE 10.1.0.4.0 Production

and recently noticed additional crashing behavior on Windows 7 (64Bit) without any apparent reasons. Based on one of our previous posts, after dwelling into possible reasons figured out the following!

Few of the database level procedures were modified with more parameters(IN and OUT) which was causing the developer suite to crash down.

So, prior you make changes to database level procedures or functions, MAKE SURE YOU DO CHANGE THE PROCEDURE/FUNCTION CALLS FROM YOUR FORM MODULES with all new IN and OUT parameters, then proceed with compiling your Procedures and Functions @ database level. If anybody is aware of another method to deal with this situation, please pass the information through comments.

for Windows7bugs,

Admin