ORA-22337: the type of accessed object has been evolved

Tags

,

Man, I am loving working with these pl/sql types.

So I have a table which has nested table. When I modified the underlying type, it started throwing this error:

ORA-22337: the type of accessed object has been evolved

Solution:

Disconnect your current session and reconnect and query again.

It worked for me!!

Advertisements

Submitting Request Set from backend

Tags

, ,

how to submit request set through database.

CREATE OR REPLACE PROCEDURE XXAP_SUBMIT_REQUEST_SET (
P_errbuf    OUT VARCHAR2,
P_retcode   OUT NUMBER)
AS
V_REQUEST_SET_EXIST   BOOLEAN := FALSE;
req_id                INTEGER := 0;
l_CONC_PROG_SUBMIT    BOOLEAN := FALSE;
srs_failed            EXCEPTION;
submitprog_failed     EXCEPTION;
submitset_failed      EXCEPTION;
l_start_date          VARCHAR2(250);
BEGIN
fnd_file.put_line (fnd_file.LOG, ‘Calling set_request_set…’);
V_REQUEST_SET_EXIST :=
FND_SUBMIT.set_request_set (application   => ‘XXAP’,
request_set   => ‘FNDRSSUB1786’);

IF (NOT V_REQUEST_SET_EXIST)
THEN
RAISE srs_failed;
END IF;

fnd_file.put_line (fnd_file.LOG, ‘Calling submit program first stage’);
l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program (‘XXAP’,
‘XXAP_FIRST_PROGRAM’,
‘STAGE10’,
‘ARGUMENT1’);

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program (‘XXAP’,
‘XXAP_SECOND_PROGRAM’,
‘STAGE20’);

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program (‘XXAP’,
‘XXAP_THIRD_PROGRAM’,
‘STAGE30’);

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program (‘XXAP’,
‘XXAP_FOURTH_PROGRAM’,
‘STAGE40’);

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

fnd_file.put_line (fnd_file.LOG, ‘Calling submit_set…’);

–l_start_date is to schedule the request
select to_char(sysdate,’DD-MON-YYYY HH24:MI:SS’)
into l_start_date
from dual;

req_id :=
FND_SUBMIT.submit_set (start_time    => l_start_date,
sub_request   => FALSE);

IF (req_id = 0)
THEN
RAISE submitset_failed;
END IF;
EXCEPTION
WHEN srs_failed
THEN
p_errbuf := ‘Call to set_request_set failed: ‘ || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitprog_failed
THEN
p_errbuf := ‘Call to submit_program failed: ‘ || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitset_failed
THEN
p_errbuf := ‘Call to submit_set failed: ‘ || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN OTHERS
THEN
p_errbuf := ‘Request set submission failed – unknown error: ‘ || SQLERRM;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
END;

Oracle Forms Trace

Tags

, , , , ,

To debug a form we can use trace files.

1. Log in to the environment that will be traced.

2. Set the profile option “Utilities:Diagnostics“ (internal name DIAGNOSTICS) user-level value to “Yes”.

3. Choose the relevant responsibility for the form and navigate to the form that will be traced.

4. Enable SQL Trace using the menu option : Help > Diagnostics > Trace > Trace with waits. Also select “Unlimited Trace File Size” to ensure an unlimited size for the trace file.

5. The trace file name and location are displayed in a dialog box. Make a note of the trace file name. This trace will be on the database tier.

6. Execute the required actions on the Form.

7. When complete, exit the form and log out.

8. Disable SQL Trace using the menu option : Help > Diagnostics > Trace > No Trace

9. Obtain the raw trace file from the trace file location, using the filename that was displayed above when enabling the trace. Alternatively the trace file identifier (user name), process id or timestamp can be used to identify the trace file.

The initialization parameter user_dump_dest defines the directory to which the trace file is written. Except for 11g, where initialization parameter<diagnostic_dest> (if set) will define this as follows:

<diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace

10. Use the tkprof command to produce a formatted file.

TKPROF Command

The following typical command is recommended for generating TKPROF files:

tkprof <filename1> <filename2> sort= fchela,exeela,prsela

Where <filename1> is the input trace file or a consolidated file of traces produced by the trcsess utility and <filename2> is the file to which TKPROF writes its formatted output.

Introduction to Inventory Orgnizations

Tags

, ,

Inventory Organization

Inventory Organization, is an organization for which you track inventory transactions and balances, and/or an organization that manufactures or distributes products.

Examples of Inventory Organizations

Screenshot_2

The following applications secure information by inventory organization: Oracle Inventory, Bills of Material, Engineering, Work in Process, Master Scheduling/MRP, Capacity, and Purchasing receiving functions.

To run any of these applications, you must choose an organization that has been classified as an inventory organization.

An Inventory organization may be one of the following:

  • A physical entity like a warehouse where inventory is stored and transacted.
  • A logical entity like an item master organization which only holds items with no transactions.

Organization Structure

Org Structure

Inventory Organization Structure

Inventory Org Structure

Defining Organizations:

Classify the Organization

  • Choose an organization classification to describe the general purpose of your organization.
  • Examples of organization classifications are inventory organization, legal company, and HR organization.
  • Choose inventory organization as your organization classification to use your organization for inventory management.

Set of Books

  • Tie each inventory organization to an operating unit. Each operating unit is associated to a legal entity and a general ledger set of books.

Inventory Parameters

  • Use the Organization Parameters window to complete your organization definition for inventory purposes.
  • Define receiving parameters if you receive items on purchase orders, internal orders, and intransit interorganization shipments.

Things to keep in mind:

  • Sets of Books: You can tie one Oracle General Ledger set of books to each inventory organization.
  • Costing Methods: You set your costing method (Standard or Average) at the organizational level. The item attribute control level determines the costing organization.
  • Item Costs: Oracle Inventory keeps one cost per item per inventory organization.
  • Movement Between Inventory Sites: You can use intransit inventory for interorganization transfers.
  • Planning Method: You can choose how to plan your items.
  • Forecasting: You can forecast your items.
  • Accuracy Analysis: You can perform a cycle count or a physical inventory.

Problems with installation – This file has no digital signature or the publisher is untrusted. ALM 11.00 setup issue

Tags

, , ,

ALM 11.00 setup issue:

While installing ALM i received following messages:

Following client components were not downloaded successfully:

1 . QTGrid.dll :
This file has no digital signature or the publisher is untrusted.
2 . QAIAd.dll :
This file has no digital signature or the publisher is untrusted.
3 . WITestType.dll :
This file has no digital signature or the publisher is untrusted.
4 . SPIOTAModule.dll :
This file has no digital signature or the publisher is untrusted.
5 . Interop.SPIOTAMODULELib.dll :
This file has no digital signature or the publisher is untrusted.
6 . interop.APPENTITYMODULELib.dll :
This file has no digital signature or the publisher is untrusted.
7 . interop.APMUI.dll :
This file has no digital signature or the publisher is untrusted.
8 . interop.Mercury_AppEntityImporter.dll :
This file has no digital signature or the publisher is untrusted.
9 . SAPAppImporterSetup.exe :
This file has no digital signature or the publisher is untrusted.
10 . LrWsUDDIQuery.dll :
This file has no digital signature or the publisher is untrusted.

Close all connections to Server and try again.

I tried to uninstall, certificates,history, browser cache and trusted sites etc.

Quick Fix is to try following link:

http://<Your Domain>/qcbin/start_a.jsp?cancelcrlcheck=true

instead of

http://<Your Domain>/qcbin/start_a.jsp

Calling Oracle SQL in UNIX

Tags

, , , , ,

here is sample code to:

  • call oracle sql statements in UNIX and
  • use UNIX variable in SQL statement. See the variable ‘”$FILE”‘

#Sample code

for FILE in `ls`
do
echo $FILE
TEMP=`echo ”
set heading off;
set TERMOUT off;
set echo off;
Insert Into FILES_IN_DIRECTORY_TABLE
Values(SEQUENCE_S.NEXTVAL,
‘”$FILE”‘,
‘IN_PROCESS’,
NULL,
SYSDATE,
‘49238’,
SYSDATE,
‘49238’,
‘49238’);
COMMIT;
exit;” | sqlplus -s user/password@host:port/service`
echo “$TEMP”
done

Level 12 trace TKPROF

Tags

, , ,

1. To Enable Level 12 Trace:

Responsibility: System Administrator
Navigation: Profile > System

  1. Find the profile option ‘Initialization SQL Statement – Custom’ for User who is submitting the process to trace.

    User: User submitting the process
    Profile: Initialization SQL Statement – Custom

  2. Click on User column – Edit Field and enter

    begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,’TRUE’,’LOG’,’ALTER SESSION SET EVENTS=’||””||’10046 TRACE NAME CONTEXT FOREVER,LEVEL 12’||””); end;

  3. Save

2.  To Enable FND debug:

Responsibility attached to Oracle user

Tags

, , , , ,

Quick query to find out a particular responsibility attached to which Oracle users:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT fu.user_name, fu.description,furg.end_date
FROM
apps.fnd_user_resp_groups furg,
apps.FND_RESPONSIBILITY fr,
apps.fnd_responsibility_tl frt,
apps.fnd_user fu
WHERE 1=1
AND fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
AND  frt.RESPONSIBILITY_NAME=:responsibility_name
ORDER BY 1