Oracle Applications 11i Technical FAQ's

1. What is Concurrent Programming?

Concurrent Processing in Oracle Apps simultaneously executes programs running in the Background with online operations to fully utilize your hardware capacity.

Use Concurrent Programming for

Long Running – Data intensive tasks such as Posting a Journal or generating a report.

2. What is the Role of Concurrent Managers?

A Concurrent Manager is a component of Concurrent processing that monitors and runs tasks without tying up your computer.

3. What is AOL?

Oracle Applications are constructed and maintained using the Application Object Library (AOL).

The Three main areas of AOL are

• Applications Security
• Operating Profile
• Concurrent Processing
•  
3. Define Interfaces. What are the different types of Interfaces?

Interfaces are used to integrate external systems and data conversion in Oracle Applications.

These can be used to either transfer data from Oracle Applications to a Flat File or Data from Legacy System to Oracle Applications.

There are two types of Interfaces. Inbound and Outbound Interfaces.

Inbound Interfaces are used to transfer data from external system to Oracle Applications.

Outbound Interfaces are used to transfer data from Oracle Applications to External System.

Open Interface is the interface whose interface logic is provided by Oracle.

Custom Interface is an Interface whose logic is developed by implementation team.

5. What are alerts? What are the different types of alerts? Explain.

Alerts

• Immediately inform you of the database activity as it happens.
• Can Periodically trigger off events as and when required
• Can take predefined actions
• Allow you to define distribution list
• Can keep history of the exceptions and actions taken against them.

There are two types of alerts.

• Event Alerts
• Periodic Alerts
•  
5.  What is the Flex field? What are the types of Flex field?
• Flex Field is “Flexible Field”
• A Flexfield is made up of Segments.
• Each segment has a name that can be assigned and has set of valid values.
• There are two types of Flex field Key Flex Field and Descriptive Flex Fields
•  
6. What are the tables related to flex field?
• FND_FLEX_VALUES
• FND_FLEX_VALUE_SETS
• FND_FLEX_VALUES_TL     
7. What is AD_DD package?

AD_DD Package is used to register the Table, Columns, and Primary Key in Oracle Applications.

            PROCEDURE REGISTER_TABLE

          Arguments: 

• P_APPL_SHORT_NAME     
• P_TAB_NAME            
• P_TAB_TYPE            
• P_NEXT_EXTENT         
• P_PCT_FREE            
• P_PCT_USED            

         PROCEDURE REGISTER_COLUMN

   Arguments

·         P_APPL_SHORT_NAME     

·         P_TAB_NAME            

·         P_COL_NAME            

·         P_COL_SEQ             

·         P_COL_TYPE            

·         P_COL_WIDTH           

·         P_NULLABLE            

·         P_TRANSLATE           

·         P_PRECISION           

·         P_SCALE                

9. What are the types of Value sets?

·         None

·         Table

·         Special

·         Pair

·         Dependent

·         Independent

·         Translatable Dependent

·         Translatable Independent

10. What are the Special and Pair Flex Field?

Special – Value Sets uses FlexField itself

Pair – Two Flex Fields together specifies a range of valid values.

11. What are the Translatable Dependent and Independent Flex Fields?

   Translatable Independent – Input must exist on previously defined set List of        

   Values. Translated value can be used.

   Translatable Dependent means Input is checked against a subset of values  

   Based on a prior value. Translated value can be used.

12. What is FND_REQUEST.SUBMIT_REQUEST?

Submits a Concurrent Request for Processing by a Concurrent Manager.

Arguments – Application,program,description,start_time,sub_request,arg1..

13. What is Client Info?

By calling this Program in SQL*PLUS or reports with correct parameters user can achieve concurrent program environment for testing.

FND_CLIENT_INFO.setup_client_info(application_id Number,

                                                                     Responsibility_id Number,

                                                                    User_id  Number,

                                                                     Security_Group_id Number);

14. Give the Directory structure in apps?

$APPL_TOP  - Product Directory- Version-

15. What are the steps in Registering Concurrent Program?
• Go to Programs and Define Executables.
• Go to Programs and Define Concurrent Program
• Go to Responsibility and attach the Request group you want.
  
  
  
  
16. What are the different types of executable available in Concurrent Programming?
• Host
• Oracle Reports
• PL/SQL Stored Procedures
• SQL*LOADER
• SQL*PLUS
• Spawned
• JSP
  
  
  
  
17. What are Request Sets?

Request set is a collection of Reports/Programs that you group together and can be submitted to run is a single interaction.

18. What is Standard Request Submission (SRS Feature)?

SRS provides you with a set of windows for running reports and Programs and a set of windows for creating groups of reports and programs to run together.

Features

• Specify whether reports or programs in a request set run sequentially or simultaneously
• Specify whether to continue with a request set if a report or program in a sequential set fails
• View a log file
• Specify alternative requests based on completion status of previously run requests in a request set.
  
  
  
  
18. What are the different API’s for Concurrent Programming?
• FND_CONCURRENT
• FND_FILE
• FND_PROGRAM
• FND_SET
• FND_REQUEST
• FND_REQUEST_INFO
• FND_SUBMIT
  
  
  
  
19. What are the Different PLL’s Used in Forms?
• CUSTOM.pll
• FNDSQF.pll
• APPCORE.pll
• APPCORE2.pll
• appdaypk.pll
• APPSTAND.pll
  
  
  
  
20. What are the Steps in Forms Customization?
• Define the Form Name in FORM Screen
• Define Form Functions
• Attach to Menu/Attach to Request group
  
  
  
  
21. What are the triggers that can be modified during Forms Customization?
• Pre-Forms
• When-New-Form-Instance
• Query_Find
• Post-Form
• Key-Clrfrm
• Accept
  
  
  
  
22. What are the triggers that cannot be modified during Forms Customization?
• STANDARD_ATTACHMENT
• ZOOM
• FOLDER_ACTION
• KEY-HELP
• KEY-EXIT
• KEY-COMMIT
• WHEN-WINDOW_CLOSED
• CLOSE_WINDOW
  
  
  
  
23. What are the FlexField Qualifiers?

A Flex field qualifier identifies a particular segment of a key flex field.

25. What are the Segment Qualifiers?

A Segment Qualifier identifies a particular type of value in a single segment of a key flex field.

26. What is a Dynamic Insertion?

Dynamic Insertion is the insertion of new valid combination into a Key Flexfields Combinations Table from a form other than the combinations form.

All Validation rules still will apply during insertion.

27. What are the different Level of Profiles?

User Profiles are used

• To set options that affect your applications behavior o your preference.
• A Collection of changeable options that affect the way your applications run
• Modify Product Specific variables
• Gives Control over certain Oracle Applications features.

            Profile Levels

·         Site Level

·         Application Level

·         Responsibility Level

·         User Level

Site Level is the lowest level.

28. Explain Multi-Organization Structure?

Set of book

A financial reporting entity that uses a particular chart of accounts, functional currency and accounting calendar.

Business Group

This is highest level in the Organization Structure. The Business group secures HR Information. Multiple set of books can share same business group.

Legal Entity

A legal company for which you prepare fiscal or tax reports.

Balancing Entity

Represents an accounting entity for which you prepare financial statements.

This is the segment in Accounting Flexfield.

Operating Unit

An Organization that Uses Oracle Cash Management, Order Management and shipping Execution, Oracle Payables, Oracle Purchasing, Oracle receivables.

It may be a Sales office division or a department. An Operating Unit is associated with legal entity.

Inventory Organization

An Organization for which you track Inventory transactions and balances and/or an Organization that manufactures or distributes products.

HR Organization

HR Organization represents the basic work structure of any enterprise.            They usually represent Functional Management or reporting groups that exists within a business group.

29. How can u see Multi-Organization is enabled or not from SQL Prompt?

SELECT MULTI_ORG_FLAG FROM fnd_product_groups;

30. What are the two mandatory parameters required for PL/SQL stored Procedure Concurrent Program?

Errbuf and retcode two OUT Parameters are required while defining PL/SQL stored Procedure Concurrent Program.

Errbuf Returns any error messageand retcode returns completion status.

Retcode returns 0 for success, 1 for warnings and 2 for error.

OCA Oracle Database 11g: Administration I Exam Guide (Exam 1Z0-052) 

Oracle Alert FAQ’s

1. What are the event alerts?

Triggered off when a particular event occurs in the Database.

Event can be Insert or Update.

      What are Alert Action Level?

There are two levels of actions that can be performed

            Detail Action and Summary Action.

2. What is a Summary Threshold?

Oracle Alerts automatically determine whether to perform a detail action or Summary action based on the number of exceptions in the database.

3. What are the different actions that can be performed in Alerts?
• Message Actions
• Concurrent Program Actions
• Operating Script Actions
• SQL Statement Script Actions
4. What are the steps involved in Creating Periodic alerts?

·         Write a select statement

·         Verify and Run the SQL

·         Specify Alert Inputs

·         Define Alert Outputs

·         Create Periodic alert actions

·         Define Periodic alert message actions

·         Create a periodic alert action set

·         Check your periodic alert.

5. What is the Distribution List in Alerts?

A Pre-defined set of electronic mail ids and printer Instructions that you can use on message actions instead of re-entering all the recipient names each time.

6. What are the Advance features available in alerts?

·         Distribution List

·         Periodic sets

·         Summary Thresholds

·         Action Set Checks

7. What are the four implicit Inputs in Alerts?

Mailid, rowid, org_id, date_last_checked

Different Report Queries...

1. ITEM QUANTITY VALUES CURRENCY

SELECT V.TOTAL_QOH, V.ITEM_DESCRIPTION,V.ORGANIZATION_NAME,M.ORGANIZATION_ID,M.SUBINVENTORY_CODE ,

NVL(C.CURRENCY_CODE,'USD')

FROM MTL_ONHAND_ITEMS_V V,MTL_ONHAND_QUANTITIES M, MTL_MATERIAL_TRANSACTIONS C

WHERE V.ORGANIZATION_ID=M.ORGANIZATION_ID

AND C.SUBINVENTORY_CODE = M.SUBINVENTORY_CODE

And

2. INTRANSIT REPORT

select 

T.INVOICED_FLAG, T.ACTUAL_COST , T.TRANSACTION_DATE ,T.TRANSACTION_QUANTITY, T.INVENTORY_ITEM_ID , T.SUBINVENTORY_CODE

from mtl_material_transactions T, MTL_ONHAND_QUANTITIES O

where

T.SUBINVENTORY_CODE = 'INTRANSIT'

AND T.INVENTORY_ITEM_ID NOT IN O.INVENTORY_ITEM_ID

3. replenished item quantities to be ordered

SELECT  b.SECONDARY_INVENTORY,t.total_qoh,

b.MINIMUM_ORDER_QUANTITY, b.FIXED_LOT_MULTIPLE, nvl(t.total_qoh - b.MIN_MINMAX_QUANTITY,0) QUANT_TO_BE_ORDERED

, s.description

 FROM MTL_SYSTEM_ITEMS_B s, MTL_ITEM_SUB_INVENTORIES b, MTL_ONHAND_items_v t

WHERE PURCHASING_ITEM_FLAG ='Y'

AND SHIPPABLE_ITEM_FLAG ='Y'

and INVOICEABLE_ITEM_FLAG='Y'

and b.SECONDARY_INVENTORY = 'Consumable'

and s.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID

and t.ORGANIZATION_ID= b.ORGANIZATION_ID

4. organisation wise , subinventory code wise DATE WISE ALSO

CREATE OR REPLACE VIEW E108_INVENTORY

AS

select HR.organization_id, HR.name ,TR.transaction_quantity,

 TR.transaction_date, TR.inventory_item_id, TR.subinventory_code,

TR.actual_cost from mtl_material_transactions TR ,hr_all_organization_units HR

 WHERE TR.ORGANIZATION_ID= HR.ORGANIZATION_ID

AND Tr.ORGANIZATION_ID= HR.ORGANIZATION_ID

5. organisation wise , subinventory code wise and product wise also

CREATE OR REPLACE VIEW E108_INVENTORY1

AS

select IT.INVENTORY_ITEM_ID,IT.ITEM_DESCRIPTION, IT.PADDED_CONCATENATED_SEGMENTS,

 HR.organization_id, HR.ORGANIZATION_NAME ,TR.transaction_quantity, 

TR.transaction_date,  TR.subinventory_code,

TR.actual_cost

from mtl_material_transactions TR ,MTL_ORGANIZATIONS HR,

mtl_onhand_items_v it

WHERE TR.ORGANIZATION_ID= HR.ORGANIZATION_ID

AND IT.ORGANIZATION_CODE= HR.ORGANIZATION_CODE

AND ROWNUM <=500

6.  STATEMENT OF EXPENSES INCURRED IN PARTICULAR GL ENTRY ACCOUNT

CREATE OR REPLACE VIEW E108_EXP_BAL1 AS

select GL.CHART_OF_ACCOUNTS_ID, GL.ACCOUNT_TYPE, GL.SEGMENT12 ACCOUNT_DEP, GL.SEGMENT14 COMPANY, GL.SEGMENT16 DEPARTMENT, BL.CURRENCY_CODE, BL.PERIOD_NET_DR ,BL.PERIOD_NET_CR ,substr(a.description,1,50) descr

, HE.PERIOD_NAME

FROM

gl_code_combinations GL ,

GL_BALANCES BL,

fnd_flex_value_sets c ,

fnd_flex_values b ,

fnd_flex_values_tl a,

GL_JE_HEADERS HE

where GL.CODE_COMBINATION_ID =BL.CODE_COMBINATION_ID

AND GL.CHART_OF_ACCOUNTS_ID=51389

AND b.flex_value_set_id = c.flex_value_set_id

and a.flex_value_id = b.flex_value_id

AND HE.SET_OF_BOOKS_ID=BL.SET_OF_BOOKS_ID

and a.language = 'US'

and c.FLEX_VALUE_SET_NAME    like 'Operations Account'

AND B.FLEX_VALUE= GL.SEGMENT12;

7. VENDOR STATEMENT NOT COMPLETE

SELECT * O.TOTAL_QOH FROM MTL_SUPPLIER_SITES_V S , MTL_ONHAND_ITEMS_V  O

 WHERE S.ORGANIZATION_ID = O.ORGANIZATION_ID

8 Multiple View Multiple Organization Quantity Report

SELECT

                &p_item_flex  c_item_flex,

                o.org_report_order report_order,

                mp.organization_code    organization_code,

                msi.primary_uom_code      uom_code,

                msi.description        item_description,

                moh.inventory_item_id      item_id,

                moh.organization_id   org_id,  

                msi.inventory_asset_flag  asset_item,

                  &p_rev_col  item_revision,     

                to_number(&p_avail_type)  nettable,

                to_number(&p_asset_inv)  asset,

                round(moh.item_cost,:C_extended_precision) item_cost,

                sum(moh.transaction_quantity)   item_qty

 FROM

                MTL_SYSTEM_ITEMS            msi, 

                MTL_ONHAND_QTY_COST_V moh, 

                MTL_ORG_REPORT_TEMP  O,

                &p_sub_inv

WHERE

                &P_WHERE_item

                and o.report_id = :p_report_id

                and o.organization_id in &P_report_orgids

               and moh.organization_id in &P_report_orgids

               and mp.organization_id = o.organization_id 

               and mp.organization_id = msi.organization_id

                and  msi.inventory_item_id = moh.inventory_item_id

                and msi.organization_id = moh.organization_id 

                &p_sub_clause

                &p_net_asset

                 

 GROUP BY

                &p_item_flex,

                o.org_report_order,

                 mp.organization_code, 

                msi.primary_uom_code,

                msi.description,

                moh.inventory_item_id,

                msi.inventory_asset_flag,

                &P_rev_col,

                &p_avail_type,

                &p_asset_inv,

                moh.item_cost,

                moh.organization_id

UNION ALL

SELECT

                &p_item_flex  c_item_flex,

                o.org_report_order report_order,

                mp.organization_code    organization_code,

                msi.primary_uom_code      uom_code,

                msi.description        item_description,

                t.item_id     item_id,

                t.intransit_owning_org_id   org_id,

                msi.inventory_asset_flag  asset_item,

                t.item_revision    item_revision,

                9 nettable,

                9  asset,

                round(to_number(&p_cst_item_cost),:C_extended_precision) item_cost, 

                --sum(decode(t.intransit_owning_org_id,t.from_organization_id,   Bug #1316350

                sum(decode(t.unit_of_measure,  msi.primary_unit_of_measure,

                t.quantity, t.to_org_primary_quantity)) item_qty

FROM

                MTL_SYSTEM_ITEMS            msi,

                MTL_SUPPLY  t ,

                &p_gl_cost_tab

                MTL_PARAMETERS MP

               

                 

 WHERE

                &P_WHERE_item

                 and o.report_id = :p_report_id

                and   t.intransit_owning_org_id = o.organization_id

                and &p_intrans_col

                and msi.organization_id = t.intransit_owning_org_id

                and mp.organization_id = msi.organization_id

                and msi.inventory_item_id = t.item_id

                &p_and_cost_clause

                    

GROUP BY

                &p_item_flex,

                o.org_report_order,

                mp.organization_code,

                msi.primary_uom_code,

                msi.description,

                msi.inventory_asset_flag,

                t.item_id,

                t.intransit_owning_org_id,

                t.item_revision,

                &p_cst_item_cost

               

select ood.organization_name org_name,

            ood.organization_code  org_code,

             gl.currency_code currency_code,

               c.precision standard_precision,

            nvl(c.extended_precision,c.precision) extended_precision

from org_organization_definitions            ood

        ,mtl_org_report_temp o,

         gl_sets_of_books gl,

         fnd_currencies c

where       ood.organization_id = o.organization_id

and   o.report_id  = :p_report_id

and   ood.set_of_books_id = gl.set_of_books_id

and             gl.currency_code = c.currency_code (+)

Generating Excel Outputs from existing standard Oracle Reports 6i Output

Most end-users use Excel as a very generic tool, because of their familiarity and the control in analysing data using the power of MS Excel.

There is a trick to capture Oracle Reports output (text output) into a excel sheet.

Basic Steps :
1) Set the output format for the report to XML
2) Run the report to generate output.
3) Save the output file locally as a XML file.
4) Open the file using MS Excel.
5) To make it more beautiful, you may use a MS Excel Template.

The Excel Templates can be used to do complex data analysis and formatting. Displayed example below is a simple excel template, to make the point.











Oracle AS Discoverer 10g: Create Queries & Reports (Web)

setting the Password for PDF File sent through XML Publisher

setting the Password for PDF File sent through XML Publisher

Open the rtf for which you want to set password and do the following things

1) Open the .rtf
2) Go to File - > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true

Note : property name should always start with xdo- .



Cracking the Oracle Apps DBA Interview: 325 Frequently Asked Questions

Script To find Oracle API's for any module

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'PA_%API%'
order by
a.owner, a.name;


Oracle Database 11g The Complete Reference (Osborne ORACLE Press Series)

How to Cancel PO Requisition created

Cancellation of PO Requisition which is created

Go to PO Super User responsibility --->Reqisition Summary ---> Here open the Requisition Number which you want to cancel and Go to Menu Bar TOOLS Option--->Control and Mention Proper reason and Cancel the Requisition.

Once the Requisition is cancelled it will also Cancel the workflow and the status of workflow will be in Canclled mode.


OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM: Exams 1Z0-051, 1Z0-052, 1Z0-053 (Osborne ORACLE Press Series)

Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities

Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities...

SELECT fu.user_id, fu.user_name, fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'SAIRAM_GOUD' --- for all user or for perticular user
-- AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

select * from V$SESSION
where OSUSER like 'c_sgoud' -- User name of the terminal ( may be your windows login name )

Alter system kill session '146,46619'


select sid, serial# from v$session where username = 'USER';

alter system kill session 'SID,SERIAL#';

you need to find first lock

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
;

by this query you can find lock

then you can kill

col program for a25
col status for a10
col SER# for a10
col LOGON_TIME for a20
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program ,
b.status,
-- b.module,
b.LOGON_TIME
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
and b.status='ACTIVE'
--and b.module like '%blbn%'
--and b.username='ENBAPP23'
order by program ,OS_USER

 

Kindle Wireless Reading Device (6" Display, Global Wireless, Latest Generation)



 How to Unlock the Objects

The following query could be useful :


Select SPID from V$PROCESS where ADDR in
(select PADDR from V$SESSION where SID in
(select SESSION_ID from V$LOCKED_OBJECT where OBJECT_ID in
(select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME=’Locked Object’)))

Get the Process id from the query. Login as unix user and run the following command to kill the process.

 Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)

What is Differnce between Decode and Case?

Decode
1.It’s a function
2.can compare only discrete vaules
3.Pl/Sql standards
4.cannot process null

Case
1.It's an Expression
2.can hande range values
3.ANSI Standards
4.Processing time is faster when compared to Decode
5.can process null


Decode
The default operator for comparison is '=',
for e.g select deptno,decode
(deptno,10,'dept10',20,dept20,default)

so if the deptno=10 then the value is replaced by dept10
and so on,

whereas in Case:

we can use any operator other than '=',
for e.g
select deptno,
case when deptno=10 and sal>1000 then 'dept10'
else
'dept20'




Decode: using for reporting purpose. and it is implemented
by if stmt.
Ex: select decode(job,'CLERK','executive',job) from emp;

Case : implemented by if & elsif condition. it is using
pl/sql equality conditionality.
Ex : select case job='CLERK' then 'executive' else 'no' end
a from emp;

it is also used for multipul colms & multi conditions.

in above stmt a is reffered alias name.




There is one more Important difference between CASE and DECODE

DECODE can be used Only inside SQL statement....
But CASE can be used any where even as a paramtre of a function/procedure

Eg:-


Code: [Select all] [Show/ hide]SQL> create or replace procedure pro_01(n number) is
2 begin
3 dbms_output.put_line(' The number = '||n);
4 End;
5 /

Procedure created.

SQL> set serverout on
SQL> var a varchar2(5);
SQL> Begin
2 :a := 'ONE';
3 End;
4 /

PL/SQL procedure successfully completed.

SQL> Begin
2 pro_01(Decode(:a,'ONE',1,0));
3 End;
4 /
pro_01(Decode(:a,'ONE',1,0));
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored


SQL> Begin
2 pro_01(case :a when 'ONE' then 1 else 0 end);
3 End;
4 /
The number = 1

PL/SQL procedure successfully completed.

Pl-Sql: Null Values in Comparisons and Conditional Statements...

Lot of time we got this type of senerio When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

Comparisons involving nulls always yield NULL

Applying the logical operator NOT to a null yields NULL

In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed

If the expression in a simple CASE statement or CASE expression yields NULL, it cannot be matched by using WHEN NULL. In this case, you would need to use the searched case syntax and test WHEN expression IS NULL.

In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
x NUMBER := 5;
y NUMBER := NULL;
BEGIN
IF x != y THEN -- yields NULL, not TRUE
dbms_output.put_line('x != y'); -- not executed
ELSIF x = y THEN -- also yields NULL
dbms_output.put_line('x = y');
ELSE
dbms_output.put_line('Can''t tell if x and y are equal or not...');
END IF;
END;
/
In the next example, you might expect the sequence of statements to execute because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
a NUMBER := NULL;
b NUMBER := NULL;
BEGIN
IF a = b THEN -- yields NULL, not TRUE
dbms_output.put_line('a = b'); -- not executed
ELSIF a != b THEN -- yields NULL, not TRUE
dbms_output.put_line('a != b'); -- not executed
ELSE
dbms_output.put_line('Can''t tell if two NULLs are equal');
END IF;
END;
/
NULLs and the NOT Operator
Recall that applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent:

IF x > y THEN | IF NOT x > y THEN
high := x; | high := y;
ELSE | ELSE
high := y; | high := x;
END IF; | END IF;

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same value to high. However, if either x or y is null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high.


NULLs and Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and Boolean expressions. For example, the following statements assign nulls to the target variables:

DECLARE
null_string VARCHAR2(80) := TO_CHAR('');
address VARCHAR2(80);
zip_code VARCHAR2(80) := SUBSTR(address, 25, 0);
name VARCHAR2(80);
valid BOOLEAN := (name != '');
BEGIN
NULL;
END;
/
Use the IS NULL operator to test for null strings, as follows:

IF my_string IS NULL THEN ...

NULLs and the Concatenation Operator

The concatenation operator ignores null operands. For example, the expression

'apple' || NULL || NULL || 'sauce'

returns the following value:

'applesauce'

NULLs as Arguments to Built-In Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:

DECLARE
the_manager VARCHAR2(40);
name employees.last_name%TYPE;
BEGIN
-- NULL is a valid argument to DECODE. In this case, manager_id is null
-- and the DECODE function returns 'nobody'.
SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ' is managed by ' || the_manager);
END;
/
The function NVL returns the value of its second argument if its first argument is null. In the following example, if the column specified in the query is null, the function returns the value -1 to signify a non-existent employee in the output:

DECLARE
the_manager employees.manager_id%TYPE;
name employees.last_name%TYPE;
BEGIN
-- NULL is a valid argument to NVL. In this case, manager_id is null
-- and the NVL function returns -1.
SELECT NVL(manager_id, -1), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ' is managed by employee #' || the_manager);
END;
/
The function REPLACE returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For example, the following call to REPLACE does not make any change to the value of OLD_STRING:

DECLARE
string_type VARCHAR2(60);
old_string string_type%TYPE := 'Apples and oranges';
my_string string_type%TYPE := 'more apples';
-- NULL is a valid argument to REPLACE, but does not match
-- anything so no replacement is done.
new_string string_type%TYPE := REPLACE(old_string, NULL, my_string);
BEGIN
dbms_output.put_line('Old string = ' || old_string);
dbms_output.put_line('New string = ' || new_string);
END;
/
If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, the following call to REPLACE removes all the dashes from DASHED_STRING, instead of changing them to another character:

DECLARE
string_type VARCHAR2(60);
dashed string_type%TYPE := 'Gold-i-locks';
-- When the substitution text for REPLACE is NULL,
-- the text being replaced is deleted.
name string_type%TYPE := REPLACE(dashed, '-', NULL);
BEGIN
dbms_output.put_line('Dashed name = ' || dashed);
dbms_output.put_line('Dashes removed = ' || name);
END;
/
If its second and third arguments are null, REPLACE just returns its first argument.
Posted by Sairamgoud at 3:05 PM 0 comments
PL/SQL -Advantages
Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

Support for SQL
Support for object-oriented programming
Better performance
Higher productivity
Full portability
Tight integration with Oracle
Tight security
Tight Integration with SQL

The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL and PL/SQL datatypes: a NUMBER or VARCHAR2 column in the database is stored in a NUMBER or VARCHAR2 variable in PL/SQL. This integration saves you both learning time and processing time. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.

Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages.

Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation.

Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.

Support for SQL
SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as SELECT, INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between your applications and the database.

PL/SQL also supports dynamic SQL, a programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements at run time, without knowing details such as table names and WHERE clauses in advance.

Better Performance
Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead.

With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application. PL/SQL even has language features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

Higher Productivity
PL/SQL extends tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits.

PL/SQL is the same in all environments. Once you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.

Full Portability
Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.

Tight Security
PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement.

Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.

Support for Object-Oriented Programming
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.

By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.

In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate.

REP-1219 : 'object name' has no size -- length or width is zero.

REP-1219 : 'object' has no size -- length or width is zero.


How to solve "REP-1219" error:


Cause: In creating your layout, you created a layout object that has itsWidth and/or Height set to zero.

Action: In the property sheet for the object, enter a value for Widthand/or Height.

SOLUTION DESCRIPTION:=====================
REP-1219 can occur when you have built a report as a bitmapped report, butthen run it in character mode.
When you run in character mode, the fields and boilerplate are rounded to thenearest character cell. Unfortunately, this can sometimes result in theobjects getting rounded down to have a height of zero, causing this error.

Re-default the layout with 'Use Character Units in the Layout Editor' reportproperty checked.
If you have done a lot of work on the layout that you don't want to lose, try:
o Increasing the appropriate field's height in the Layout Editor, oro Changing the field to use a larger font.For example: go from Courier 10 to Courier 12
SOLUTION EXPLANATION:=====================
A report can be defined using inches, centimeters, picas/points, orcharacters. In character mode, the unit is character. In bitmapped mode thedefault unit is inch, but it may also be set to centimeters or picas/points.
REP-1219 errors occur when the bitmapped object that you have converted tocharacter mode is less than half the height or width of a character cell. Asa result, Reports has mapped its size to zero during formatting.
Basically if the bitmap unit of measure equates to a size smaller than 1character, it will convert to 0 size, and REP-1219 will occur.


Note - This seems to be a common error with Oracle Reports 10g. The trick is to convert the report using Tools > File Conversion and convert it into character mode. The file extension stayed the same (RDF) in my situation.

ERROR : APP-FND-01242

APP-FND-01242: Cannot read value from field $OBJECT$.ORGANIZATION_ID

Cause: The field $OBJECT$.ORGANIZATION_ID could not be located or read.

Action: This error is normally the result of an incorrectly-entered field name string in a trigger, or a field name string that does not uniquely specify a field in your form. Correct your trigger logic to precisely specify a valid field.--------------------------------------------------------------Enter value in segment MFG_ORGANIZATION_ID before entering value in Warehouse segment.