ORA-06502: PL/SQL: numeric or value error

ORA-06502: PL/SQL: numeric or value error

ORA-06502:	PL/SQL: numeric or value error string
Cause:	An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action:	Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

This kind of errors occurs when we are trying to convert character into number or trying to assign the value to the parameter larger than the length of parameter.

Yesterday user reported that 'Customized - Sale Invoice Report'  is error out. So I asked user to send me Log file of the report. Then I myself run the report on Test instance and the report was not willing to run :). You may ask why I run the report when user provided me the Log file which is enough to believe that user is right. Well, I run report myself for three reasons:

First reason is, I believe we cannot resolve any problem quickly and efficiently until we get into it. 

Secondly, sometime if we change the report definition in application like parameters, value sets etc. and then try to run the report by copying previously run report it will surely error out because definition of the report is changed and we are copying old report with old definition which is different from the current definition. In my case where I works, whenever user report the error first of all I used to ask user to run report by submitting new request instead of copying old report and if the error still exist then I take up. By my this (I must say) proactive step more than half of the times error vanished.

Lastly, By running report myself I can check the validity of the parameters and I can check the Log file and other helping materials too.

So, run the report and open the 'log file' by clicking the 'View Log...' button.

ORA-06502: PL/SQL: numeric or value error

In the log file I noted down the 'short name' of the report and then checked the parameters and the error message. 

"REP-1401: 'cf_proforma_inv_noformula': Fatal PL/SQL error occurred"
"ORA-06502: PL/SQL: numeric or value error"

In Oracle Report Builder 6i error occur like:

ORA-06502: PL/SQL: numeric or value error

from the error message I came to know problem is with 'cf_proforma_inv_no' formula column.

ORA-06502: PL/SQL: numeric or value error

So I pick the RDF from the server and opened it in the report builder 6i. I find out the formula column and opened its property palette.

ORA-06502: PL/SQL: numeric or value error

In property palette I just increased the width of the formula column to 350 from 200. Because I know this error can only occurs when we are trying to convert character into number or trying to assign the larger value to the small length of parameter. So in this case my formula column was character type so the only reason left was its length.

ORA-06502: PL/SQL: numeric or value error

 I then double click the button to open formula column to check the length of parameter if assign in code too.

ORA-06502: PL/SQL: numeric or value error

I also increased the length of parameters  lv_no1, lv_no2 from 200 to 350.

ORA-06502: PL/SQL: numeric or value error

This is enough to avoid this error. But I suggest you to use 'Exception' in your functions so that it never error out. Because if report is showing null is less worse than report error out. So put the exception in your formula columns as illustrated below to avoid this.

All the best. :)

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

ORA-00932: inconsistent datatypes: expected - got CLOB

ORA-00932: inconsistent datatypes: expected - got CLOB

One thing I must share is, I have explained what I was facing and that is explained below. If  you are facing same problem then it is pleasure for me, my post is helpful for you. But if this is not the case then please visit following links may be you can find some thing for you.

http://www.orafaq.com/forum/t/65196/2/
http://albert-myptc.blogspot.co.uk/2009/04/ora-00932-inconsistent-datatypes.html
https://github.com/rsim/oracle-enhanced/issues/72
http://tracker.moodle.org/browse/MDL-21890
Can't export my database using exp with XMLTYPE member column 
https://forums.oracle.com/forums/thread.jspa?threadID=572154

LEARNING TO LOVE LOBS

http://www.orafaq.com/node/895

And please let me know what kind of problem you are facing by leaving comment so that I can blog about that too.



Today when I was trying to execute following query it errors out "ORA-00932: inconsistent datatypes: expected - got CLOB". Though it works fine for "distinct cust.*", "distinct hp.*", "distinct hps.*" and "distinct hpsu.*","distinct cust.*" but when I try to SELECT distinct rows from raa ("distinct raa.*") it errors out.
I have googled this problem but no cure because everyone suggested to check database and change the data types. Though in other cases it could be only achieved by making changes in database but in my case I just remove "DISTINCT" from query and it works fine. :)

SELECT  DISTINCT raa.*

    FROM hz_cust_accounts cust,

                 hz_parties hp,

                 hz_party_sites hps,

                 hz_party_site_uses hpsu,

                 ra_addresses_all raa
 WHERE cust.party_id = hp.party_id

       AND hp.party_id = hps.party_id

       AND hps.PARTY_SITE_ID = hpsu.PARTY_SITE_ID

       AND hps.PARTY_SITE_ID = raa.PARTY_SITE_ID

       AND raa.org_id =:p_org_id

       AND hpsu.SITE_USE_TYPE = 'BILL_TO'

ora 00932 inconsistent datatypes expected got clob

ORA-00932: inconsistent datatypes: expected - got CLOB

In addition when I try to drill the error what I found that we cannot ORDER BY or Distinct a CLOB. I first Describe the table and checked which field is having CLOB data type.

ora 00932 inconsistent datatypes expected got clob

Now I know that "ADDRESS_TEXT" is the field. I try to run the following queries

select distinct raa.ADDRESS_TEXT

  from ra_addresses_all raa

select raa.ADDRESS_TEXT

  from ra_addresses_all raa

order by raa.ADDRESS_TEXT

ora 00932 inconsistent datatypes expected got clob

each query returned the same error.

Now I try the following query and it works fine.

select distinct dbms_lob.substr(raa.ADDRESS_TEXT)

  from ra_addresses_all raa

order by dbms_lob.substr(raa.ADDRESS_TEXT)


You can too use this function to avoid the error dbms_lob.substr()


Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

Change The Message Template Of Purchase Order Email Notification in Oracle EBS

Change The Message Template Of Purchase Order Email Notification in Oracle EBS

Change The Message Template, Remove the Approve & Reject Links, and Hide Request More Information Buttons from a Workflow E-mail Notification.

1.    Log-in to oracle apps, and switch to responsibility ‘Work Flow Administrator’.

2.    Open the Work Flow Manager under Oracle Application Manager

Purchase Order Email Notification in Oracle

3.    Open the link ‘Service Components’ at bottom of the page

4.    Find the link ‘Workflow Notification Mailer’, press edit after selecting it.

Purchase Order Email Notification in Oracle

5.    Press ‘Advanced’ button for advance setting.

Purchase Order Email Notification in Oracle

6.    Go to step 4 by pressing next button

Purchase Order Email Notification in Oracle

7.    Scroll down to templates section and change the value of ‘Open Notification’ from Workflow Open Mail (Templated) to Workflow Open Mail (Outlook Express).

Purchase Order Email Notification in Oracle

8.    Click ‘Finish’ to fix your changes.

9.    Open ‘Work Flow Builder’ on your local system, and open the workflows stored on your database.

Purchase Order Email Notification in Oracle

10.  Select ‘PO Approval’ work flow from the list.

Purchase Order Email Notification in Oracle

11.  Open the ‘PO Approval’ > ‘Messages’ > ‘Approve PO’

Purchase Order Email Notification in Oracle

12.  Add new attribute by right clicking on ‘Approve PO’, fill the information as follows.

Purchase Order Email Notification in Oracle

13.  Save the workflow to your database. And regenerate the mail.

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

Oracle ADPatch

ADPatch 

Applying a patch updates your existing system in various ways, from adding a new
feature or product to improving system performance.

patches are applied for a number of reasons, including:

■ Fixing an existing issue

■ Adding a new feature or functionality

■ Updating to a higher maintenance level
■ Applying the latest product enhancements
■ Providing interoperability to new technology stacks
■ Determining the source of an issue
■ Applying online help

Depending on the type of patch, it may update the file system, or the database, or
both.

Unified Driver File

The unified driver, named u.drv, contains the commands necessary to
change files and database objects, and to generate new objects. It contains copy,
database, and generate portions and performs the copy, database, and generate actions
in the stated order. You typically run the unified driver on all APPL_TOPs. AutoPatch
runs only the actions that are required for the current APPL_TOP.

C,D and G Driver File
C – copy driver
D – database driver
G – forms and reports generation driver

Command Line Patching Utilities
You run the following utilities from the command line.

AutoPatch

AutoPatch is the utility used to apply all patches to the Oracle Applications file system or database.

AD Merge Patch

When you apply patches individually, you must perform patching tasks multiple
times. For example, for every individual patch there may be duplicate link and
generate processes. AD Merge Patch merges multiple patches into a single patch so
that the required patching tasks and processes are performed only once.

i.e. admrgpch -s ./source -d ./target -merge_name merged001

AutoPatch

Use AutoPatch to apply patches to the Oracle Applications file system or database. It
gathers necessary information about your system through a series of prompts. When
you have completed the prompts, AutoPatch performs all the tasks required to apply
the patch, including the following:

■ Reads patch metadata to determine patch dependencies and requirements.
■ Uploads patch information from a prior patch session to the database (if needed).
■ Reads and validates the patch driver file and reads the product driver files.
■ Compares version numbers of object modules from the product libraries and
version numbers of the existing files against the patch files.
■ Backs up all existing files that will be changed by the patch.
■ Copies files.
■ Archives files in libraries.
■ Relinks executables.
■ Generates forms, reports, message, graphics, and Java archive (JAR) files.
■ Compiles JSP files and invalid database objects.
■ Updates database objects.
■ Runs AutoConfig to update configuration files, if any template files are introduced
or updated by the patch.
■ Saves patch information to the database.

AutoPatch takes no action if a patch contains no new updates to files or database
objects in your system. If AutoPatch detects that there is a previously failed AutoPatch session, it will attempt to recover that session.Preparing your System for Patching.

Before you begin a patching session, there are some important tasks you need to
complete.

Enable Maintenance Mode

Before you initiate an AutoPatch session, you should enable maintenance mode and Shutdown concurrent manager. During a maintenance mode downtime, user login is restricted. Users are redirected to a system downtime URL, which informs them that the maintenance session is in progress.

To enable or disable maintenance mode, use the Change Maintenance Mode menu in
AD Administration.

Shut Down Services

If you are applying a patch that updates or relinks files, shut down the corresponding concurrent manager, Web server listeners.

Log File Description

adpatch.log main AutoPatch log file (default name)
adpatch.lgi for AutoPatch informational messages (default name)
adrelink.log for relinking

Command Line Arguments

You can direct the way the AutoPatch operates by adding modifiers to the AutoPatch
start command. These modifiers may be in the form of arguments or options.

$ adpatch logfile=test.log
You can enter more than one token=value argument on a single command line by
separating them with one blank space as in the following AutoPatch command.

$ adpatch printdebug=y flags=hidepw
In some cases, you can include more than one value for a token. In this case, separatethe values with commas.

$ adpatch flags=nohidepw,trace
Comma-separated lists must not contain blank spaces. For example, this command is
not valid:

$ adpatch flags=nohidepw, trace
The following arguments are specific to AutoPatch and can be used to modify and
refine its behavior.

AutoPatch Options
The options= argument is used to pass generic options to AutoPatch. It takes the form
of a comma-separated list. Enter one option or a comma-separated list of options. For
example, options=nocopyportion,nogenerateportion. Do not include a space after the

Option Description

autoconfig Purpose: Tells AutoPatch to run AutoConfig automatically.

Default: autoconfig

Use options=noautoconfig if you are applying a number of patches in
sequence and want to run AutoConfig once, after applying the last patch of
the sequence.

Default: checkfile
Use options=nocheckfile to turn off the checkfile feature.

Default: compiledb for standard patches. nocompiledb for standard patch
translations, documentation patches, and documentation patch translations.
Use options=nocompiledb to save time when multiple non-merged patches are
applied in a maintenance window.

Default: compilejsp for standard patches. nocompilejsp for standard patch
translations, documentation patches, and documentation patch translations.
Use options=nocompilejsp to save time when multiple non-merged patches
are applied in a maintenance window.

Default: copyportion
Use options=nocopyportion to tell AutoPatch not to perform copy actions of
the driver.

Default: databaseportion
Use options=nodatabaseportion to tell AutoPatch not to perform database
actions. of the driver

Default: generateportion
Use options=nogenerateportion to tell AutoPatch not to perform generate
actions of the driver.

Default: nohotpatch
integrity Purpose: Tells AutoPatch whether to verify that the version of each file
referenced in a copy action matches the version present in the patch.

Default: nointegrity
Comments: Using options=nointegrity is safe and avoids some AutoPatch
overhead.

Default: nophtofile
Use options=phtofile to tell AutoPatch not to upload patch history
information to the database.

Interactive or Non-interactive Patching
You can apply patches interactively or non-interactively.

Interactive patching means that you supply basic information that AutoPatch needs by responding to a series of prompts.

Non-interactively substantially reduces the need for user intervention when AutoPatch processes patching tasks. You create a defaults file that contains much of the information you would have supplied at the AutoPatch prompts. Then, when you run AutoPatch, you specify the name of the defaults file, the location of the patch top directory, the name of a driver file, and other parameters on the command line.

Create the defaults file.

Start AutoPatch, using the defaultsfile= argument, and specify the file name and the
path to the defaults file. This creates a defaults file for the current environment.

UNIX:
The file must be under the $APPL_TOP/admin/ directory, where is
the database name (ORACLE_SID/TWO_TASK). For example:

$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/adpatchdef.txt

$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt logfile=1234567.log \
patchtop=$APPL_TOP/patches/1234567 driver=u1234567.drv workers=3 \
interactive=no

Restarting a Non-interactive AutoPatch Session

When AutoPatch is running non-interactively and encounters an error, it exits to the
operating system and reports a failure. The restart argument is intended specifically
for this circumstance. When AutoPatch sees the restart=yes argument, it assumes that
there is an old session, and expects to find one. If it cannot, it will fail. Do not indicate restart=yes to start a new AutoPatch session.

Complete the following steps:

1. Look through the log files, diagnose the error, and fix it.
2. Use the same command line options that you used initially, but add restart=yes.

UNIX:
$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt logfile=1234567.log \
patchtop=$APPL_TOP/patches/1234567 driver=u1234567.drv workers=3 \
interactive=no restart=yes

Windows:
C:\> adpatch defaultsfile=%APPL_TOP%\admin\testdb1\def.txt \
logfile=1234567.log patchtop=%APPL_TOP%\patches\1234567 \
driver=u1234567.drv workers=3 interactive=no restart=yes

Abandoning a Non-interactive AutoPatch Session

When you specify interactive=no on the AutoPatch command line, AutoPatch expects
that there is no existing failed session. AutoPatch aborts if it finds restart files from a failed session. Running AutoPatch with the interactive=no and restart=yes command line arguments restarts the previously incomplete session.

To start a completely new AutoPatch session when there is an existing failed session,
specify interactive=no and abandon=yes on the AutoPatch command line. With this
command, AutoPatch deletes the restart files and any leftover database information
from the failed session.

Complete the following steps:

1. Verify that you do not want to restart the previous failed session.
2. Start AutoPatch with the abandon=yes option:

UNIX:
$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt logfile=7654321.log \
patchtop=$APPL_TOP/patches/7654321 driver=c7654321.drv workers=3 \
interactive=no abandon=yes

Windows:
C:/> adpatch defaultsfile=%APPL_TOP%\admin\testdb1\def.txt \
logfile=7654321.log patchtop=%APPL_TOP%\patches\7654321 \
driver=c7654321.drv workers=3 interactive=no abandon=yes

/* Script to find out Patch level of mini Pack */

Select product_version,patch_level from fnd_product_installations
where patch_level like '%FND%';

I.e.
PATCH_LEVEL

11i.FND.H
11i.AX.I
11i.AK.G
11i.XLA.H
11i.GL.J
11i.FA.O

AD Patches Tables

• AD_APPLIED_PATCHES
• AD_PATCH_DRIVERS
• AD_PATCH_RUNS
• AD_APPL_TOPS
• AD_RELEASES 
• AD_FILES
• AD_FILE_VERSIONS
• AD_PATCH_RUN_BUGS
• AD_BUGS
• AD_PATCH_COMMON_ACTIONS 
• AD_PATCH_RUN_BUG_ACTIONS
• ad_comprising_patches

Concurrent Manager 

• FND_CONCURRENT_QUEUES 
• FND_CONCURRENT_PROGRAMS 
• FND_CONCURRENT_REQUESTS 
• FND_CONCURRENT_PROCESSES 
• FND_CONCURRENT_QUEUE_SIZE

FND Tables

• FND_APPL_TOPS 
• FND_LOGINS 
• FND_USER 
• FND_DM_NODES 
• FND_TNS_ALIASES 
• FND_NODES 
• FND_RESPONSIBILITY 
• FND_DATABASES 
• FND_UNSUCCESSFUL_LOGINS 
• FND_LANGUAGES 
• FND_APPLICATION 
• FND_PROFILE_OPTION_VALUES

If you want any further clarification, check below metalink documents

Patching Best Practices and Reducing Downtime - Note:225165.1
Oracle Applications Patching FAQ - Note:174436.1
How to Merge Patches Using admrgpch - Note:228779.1

For the answer of the question 'How to apply patch' please visit http://oracleared.blogspot.co.uk/2012/04/adpatchhow-to-apply-patch.html

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

ORA 01652 unable to extend temp segment

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP05

ORA-01652: unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

For resolution you can use two methods by sql commands or through TOAD.

By using sql:

First of all check the status of your tablespace by using following queries.

SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE

group by tablespace_name

order by tablespace_name

SELECT tablespace_name, round(sum(BYTES)/power(2,20)) TOTAL_MB

group by TABLESPACE_NAME

order by TABLESPACE_NAME

select * from dba_tablespaces where tablespace_name='TEMP05'

select * from dba_data_files where tablespace_name ='TEMP05'

select bytes/1024/1024 from dba_free_space where tablespace_name='TEMP05'

select bytes/1024/1024 mb from dba_TEMP_files where tablespace_name like'TEMP05'

select bytes/1024/1024/1024 gb from dba_TEMP_files where tablespace_name like'TEMP05'

Now extend or add new datafile to existing tablespace accordingly.

Extend tablespace:

ALTER DATABASE DATAFILE '<path_and_file_name>'

RESIZE <n>K|M|G|T|P|E;

e.g.

ALTER DATABASE DATAFILE '/u02/oracle/oradata/TEMP05.DBF'

RESIZE 1024 M

Add new datafile to existing tablespace:

ALTER TABLESPACE <tablespace_name>

ADD DATAFILE '<path_and_file_name>' SIZE <n>K|M|G|T|P|E;

ALTER TABLESPACE TEMP05 ADD TEMPFILE '/u02/oracle/oradata/TEMP05A.DBF' SIZE 1G AUTOEXTEND OFF

Where data file name must be change from the existing data file. Suppose if your TEMP05.DBF is full you should add new datafile by new name with existing file path TEMP05A.DBF.

By Using TOAD:

Login to TOAD

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Now open SCHEMA BROWESER

You can open SCHEMA BROWSER by two ways as illustrated below.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Now go to Tablespaces tab and from the list select the Tablespace name which needs to extend as illustrated below.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Now if you want to extend the existing datafile then click on “Edit Button”. Otherwise click on “ADD Button” (which is illustrated after edit).

You can see current size of “TEMP05” is 1 GB we are going to extend it to 2 GB as illustrated below.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

In below picture you can see “TEMP05” is resized to 2 GB.  

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

If we have to add new datafile then click on “ADD Button” a small window “TEMP file definition” will appear now click on “Find/Copy” button to find the path one more small window “Select a file name” will appear click “OK” button it will bring us back to previous window “TEMP file definition” as illustrated below. 

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Now change the file name and change the datafile size and set other options as per requirement and click OK.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Now you can see new datafile “TEMP05A” in the list. Now click OK. That’s it.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

For detail study of TABLESPACES please visit http://oracleared.blogspot.com/2012/03/oracle-tablspaces.html.

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More