ORA-01653: unable to extend table by 64 in tablespace APPS_TS_TX_DATA

Unable to extend tablespace error occurs when the tablespace where the data is being loaded is running out of allocated space.
Ex: Table X is created in APPS_TS_TX_DATA with an allocated space of 10GB which might have man other tables.

When a program is trying to load data when the tablespace is full we encounter this error.

Below are couple of queries which I use. (Pls Note :These are from Ask Tom) I am sharing here to help others.

-----Tablespace stats-----
select (select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc/1024/1024 kbytes,
(kbytes_alloc-nvl(kbytes_free,0))/1024/1024 used,
nvl(kbytes_free/1024/1024,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/ kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
and a.tablespace_name like '%APPS%DATA%'
order by 1

---Table Sizes------
select segment_name,sum((BYTES)/(1024*1024*1024)) "Allocated(GB)"
from dba_extents
where segment_name like 'XX_%' or segment_name like 'XXCOM_TEST'
group by segment_name
order by 2 desc;

Account Generator.

Found a good document about the basics of Account generator. Thought I would share.

repo.solutionbeacon.net/BrownfieldKaren_AcctGen_CP05.pdf

XML Publisher Excel output numbers getting truncated.

For XML Publisher reports when the output is in excel it is a common issue number fields are truncated. To avoid this use the following piece of code.

--XML Publisher Excel avoid numbers being truncated
TAGfo:bidi-override direction="ltr" unicode-bidi="bidi-override"TAGTAG?YOUR_FIELD?TAGTAG/fo:bidi-overrideTAG

Note this might effect the users from SUM etc on the fields.

FRM-40654 Record Has Been Updated. Requery Block To See Change (Oracle Fix)

Blank/space characters cause a lot of issues paticularly in Oracle forms.
Most of the times when you try to update a record you get an error message

FRM-40654: Record has been updated. Requery block to see change.

Most of the times this is caused by extra chars in the string columns. There is an Oracle fix issue for this issue. Apply the fix in TEST instance before applying in PROD. The fix is as follows.

Run script $FND_TOP/sql/afchrchk.sql

Option1= fnd_flex_values_tl
Option2= description
Option3 = N
Option4 =Y

Below is the explanation for all the options from the oracle script

--Table Name and Column Name (Always better to work on one table and column if known)
prompt Enter the table and column names to check for leading or trailing
prompt spaces and control characters. Like-style expressions are ok.
prompt Leave either table or column blank to check table/column.
prompt Table name (blank for all):
define tblarg = '&1'
prompt Column name (blank for all):
define colarg = '&2'

--I would recommend to input Y with caution for the newline option as oracle stores data with new lines chars in some cases.
--Ex : Atributes info on valuesets are stored with new lines chars
--Default N checks for only leading/trailing spaces
prompt Newline characters are acceptable in columns never queried in
prompt Forms, on only queried in multi-line items.
prompt Enter Y to also look for newline characters.
prompt Check for newline characters (Y/N)?
define newlinearg = '&3'

--option to fix or just list the errors. recommend to first list and then fix errors.
prompt Enter Y to automatically strip all leading/trailing spaces
prompt and control characters found.
prompt *** WARNING ***
prompt It is highly recommended to run first without stripping
prompt to be sure all detected values really should be fixed!

prompt Automatically fix all errors found (Y/N)?
define fixarg = '&4'

Oracle Find Locked Objects (SQL)

Finding Locked in Oracle can be tricky. I use the below approach.

--Locked Objects (Get SID from this query)
select o.object_name,lo.* from v$locked_object lo , dba_objects o
where lo.object_id = o.object_id
and o.object_name like '%' ;
--and o.object_type = 'PACKAGE'

This query finds all the locked objects. You can filter on packages. Sometimes the object may not appear in the above list. Then the below query might help.

--get serial and sid from this query
select * from v$session where sid = 215 or module like 'XX%';

--sid,#serial
alter system kill session '316,25481';

Oracle WEB ADi - Excel Macro settings in MS Office 2010

Well seting the macr settings in Office 2010 is as below. Excel Options -> Trust Center -> Trust Center Settings -> Macro Settings Trust access to the VBA project object model - check.

Project Expense Report Account Generator(PAAPWEBX)

Project Expense Report Account Generator(PAAPWEBX):

This workflow generates the account during the iExpenses process.This called from the workflow 'Expenses' (APEXP).
The process calling the account generator is
'AP Validate Expense Report' -> which calls the API AP_WEB_EXPENSE_WF.APVALIDATEEXPENSEREPORT
which internally calls various API's before calling tge account generator from pa_acc_gen_wf_pkg.ap_inv_generate_account
which calls the standard FND API's for generating the code combination for the generator.
fnd_flex_workflow.generate( l_itemtype,l_itemkey, etc
l_error_message which passes back the workflow error.

So the API calling is as follows

iExpenses
Calls AP_WEB_EXPENSE_WF.APVALIDATEEXPENSEREPORT
Calls pa_acc_gen_wf_pkg.ap_inv_generate_account
Calls fnd_flex_workflow.generate

To debug the account generator use the following profile.
Account Generator:Run in Debug Mode preferably at the user level which shows the account generator workflow in workflow admin.

ORA-01722 – Invalid number with SQL*Loader in unix

When tring to load data using SQL*Loader Invalid number error popped up.

The last column in the staging table was a number. Having tried trim and nullable etc. I found that the data file has got funny chars. The data file was copied from windows.

Solution:
dos2unix filename.dat

Oracle First day of given month or given year.

I noticed trunc has a skillful way of working with dates.

First day of the month :
select trunc(sysdate,'MM') from dual;
Gives me 01-FEB-13

First day of the year:
select trunc(sysdate,'YY') from dual;
Gives me 01-JAN-13

Oracle SUBSTR (Seperate strings) based on a delimiter

Using regexp_substr we can match a expression in the parent string the delimiter here is '.'
[^.]* --> match one or more occurences of the string [any chars]ending with . so this matches anything like abc. xyz. 123. or even .

select regexp_substr('1.00..60200....','[^.]*.',1,4) from dual;
Result: 60200.

Using the below rtrim we can trim the '.' in the end.

select rtrim('123.','.') from dual;

Final SQL:
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,5),';') seg from dual;
Result: null
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,2),';') seg from dual;
Result: 00
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,3),';') seg from dual;
Result: 123

PIVOT (Oracle) - Convert multiple Rows to Columns

I had to convert multiple rows into columns today. Fetch balances from gl_balances and apply multiple conversions(say GBP,USD,AUD,HKD).

First approach:

select period_net_dr , period_net_dr*gt1.avg_rate gbp_value
, period_net_dr*gt2.avg_rate usd_value
, period_net_dr*gt3.avg_rate aud_value
, period_net_dr*gt4.avg_rate hkd_value
from gl_balances glb,
gl_translation_rates GT1,
gl_translation_rates GT2,
gl_translation_rates GT3,
gl_translation_rates GT4
where glb.period_name = 'AUG-12'
and GT1.period_name = 'AUG-12'
and GT2.period_name = 'AUG-12'
and GT3.period_name = 'AUG-12'
and GT4.period_name = 'AUG-12'
and GT1.to_currency = 'GBP'
and GT2.to_currency = 'USD'
and GT3.to_currency = 'AUD'
and GT4.to_currency = 'HKD';

The table gl_translation_rates appears 4 times in the query. So how we can do it better.

Using Oracle PIVOT feature this is how it can be done.

select period_net_dr, period_net_dr*gbp_rate,period_net_dr*usd_rate, period_net_dr*aud_rate,period_net_dr*hkd_rate from gl_balances glb,

(SELECT period_name,gbp_rate,usd_rate,aud_rate,hkd_rate
FROM (SELECT period_name,TO_CURRENCY_CODE, conversion_rate
FROM gl_translation_rates
where period_name = 'AUG-12' and set_of_books_id = 1)
PIVOT (sum(conversion_rate) FOR TO_CURRENCY_CODE IN ('GBP' gbp_rate,'USD' usd_rate,'EUR' eur_rate ,'HKD' hkd_rate,'SGD' sgd_rate,'AUD' aud_rate)) ) gl_rates

where glb.period_name = 'AUG-12'
and gl_rates.period_name = glb.period_name
and period_net_dr <> 0;

-----

As you can see the gl_rates pseudo table which I built in the view is the key and the beauty of pivot feature is it allows us to name the columns like gbp_rate etc.

So we have only one hit on the rates table. And the query is more readable

Unable to set NLS_LANGUAGE - Workflow Builder

When I installed workflow builder for the first time I had the following error

220: Unable to set NLS_LANGUAGE.
210: Oracle Error: ORA-00942: table or view does not exist. SQL text: SELECT NLS_LANGUAGE FROM WF_LANGUAGES WHERE :l IN (NLS_LANGUAGE, CODE)

To fix the error.
1.Open REGEDIT from cmd promt
2.Navigate to My Computer -> HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
3. Find key NLS_LANG and update it to charset value from the below query
select nls_language||'_'||nls_territory||'.'||nls_codeset from wf_languages where code = 'US';
4. Also recommended to do the same for the NLS_LANG key under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME2 (or whatever home workflow is installed.