Tracing SQL sent from client to server
Date: Tue, 3 Aug 1999 13:23:44 -0400
From: Robert Kopf rkopf@mapmobile.com
Subject: Applications Tracing
Is there a way to trace all SQL that is being sent between an Oracle
Applications client and the database server? I have tried to use the Trace
functionality on the Apps Help menu with little success. Specifically, I am
trying to trace the queries executed when a report is run. I have copied
the
SQL out of the report and run it in SQL*Plus with the same parameters as
wehn
run from Applications. However, I receive no rows of data.
Any assistance is greatly appreciated.
Robert Kopf
Map Mobile Communications, Inc.
840 Greenbrier Circle
Chesapeake, VA 23320
(757) 424-1191
rkopf@mapmobile.com
Date: Tue, 3 Aug 1999 10:33:04 -0400
From: "Scott Frost" sfrost@noblestar.com
Subject: Re: Applications Tracing
you can get queries by doing TOOLS, EXAMINE....BLOCK choose SYSTEM and FIELD
choose LAST QUERY.
Might get you closer to what you want.
Scott
Scott Frost
Oracle HRMS Consultant
Noblestar Systems Corporation
Date: Tue, 3 Aug 1999 11:35:10 -0600
From: PKBATCHU@micronpc.com
Subject: RE: Applications Tracing
You may have to communicate with your DBA to set the trace on on your
session.
Date: Tue, 3 Aug 1999 10:36:42 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: Applications Tracing
You can set trace at the database level in the init.ora file for your
instance.
With the sql that you are running in sql*plus, are you running it as a
particular user? The application users use views. If you are selecting
from, say
a view called ra_customer_trx as APPS in SQL*Plus, and you are using
multi-org,
you won't get any data because the APPS user at the database level doesn't
have
an org_id associated with it. You have to use the ra_customer_trx_all
table.
Date: Tue, 3 Aug 1999 10:36:08 -0700 (PDT)
From: Ramesh Gupta ramgup@yahoo.com
Subject: Re: Applications Tracing
If you like to run your report query on SqlPlus
then 'set autotrace on'...that's it..and run..
Good Luck..
Ram
Date: Tue, 3 Aug 1999 14:02:22 -0400
From: "Yadav, Surendra" YadavS@usa.redcross.org
To: "'Robert Kopf'" oraapps-l@cpa.qc.ca, rkopf@mapmobile.com
Subject: RE: Applications Tracing
Try setting the Profile options 'Utilities:SQL Trace" as "Yes" and then
execute a report. As mentioned before, you need to have trace on at the DB
level before you can get any trace file output.
Date: Tue, 3 Aug 1999 13:16:39 -0500
From: "Kanakam, Venkat (c)" vkanakam@Carlson.com
Subject: RE: Applications Tracing
Since you want to tune the queries in the report , you can get the trace by
inserting
srw.do_sql('alter session set sql_trace = true');
in the BeforeReport trigger. This will generate the trace file.
HTH
Venkat Kanakam
Consultant
CIBER, Inc.
Date: Tue, 03 Aug 1999 13:23:36 -0500
From: "Stephens, Tad" tastephens@firstam.com
Cc: "'rkopf@mapmobile.com'" rkopf@mapmobile.com
Subject: RE: Applications Tracing
Robert,
If you are signed on as APPS in SQL*Plus and get no rows returned try
"execute dbms_application_info.set_client_info('&org_id');" before running
your query, where org_id = the organization id that your data is associated
with. Otherwise there is a debug option in the Reports designer under Tools
Options.
Tad Stephens
Birdwell Consulting Services, Inc.
birdwell@gte.net mailto:Birdwell@gte.net
Voice/Voice Mail/Pager: (817) 239-3632
FAX: (817) 424-1128
Date: Tue, 3 Aug 1999 19:01:26 -0500
From: "Voss, Tracey Lee" tracey.voss@au.unisys.com
Subject: RE: Applications Tracing
Hi Robert,
You need to have SQL_trace set to true at the database level. Then you can
either use the tools menu in the app interface or the command 'alter session
set SQL_TRACE = TRUE' in your SQL*plus session then run your sql statement
or script. You should end up with a trace file in your udump directory.
cheers
Tracey
Date: Tue, 3 Aug 1999 20:08:17 -0500
From: "Hendrix, Deborah A." HendrixDA@bvsg.com
Subject: RE: Applications Tracing
My experience with Trace functionality as it pertains to the apps is that
there are three ways you can get a trace file none of which require turning
trace on at the database level:
1) Trace from a Forms session - You need to have the profile option,
Utilities:SQL Trace, turned on. Once you have this profile option turned on
at some level that pertains to you, then you have the ability to toggle
trace on/off via Help:Tools:Trace on the menu. This works really well
because you can get a very specific trace file by turning trace on/off at
the point of interest within a form so you capture just what you need rather
than an entire user session.
2) Trace from a Concurrent Program
2a) Some of the applications have a debug mode profile option like PA:
Debug Mode which not only generates a trace file for the concurrent process,
it also writes out detailed debugging information to the log file.
2b) If an application doesn't have a debug mode profile option, in Rel 11
you can turn trace on for any concurrent program by setting the option in
the Concurrent Program definition. In Rel 11, you do this using the System
Administrator responsibility and navigating to Concurrent:Program:Define.
Query up the program of interest, then check the Enable Trace box. Once you
do this, each time this program is ran, it will generate a trace file. To
get it to stop, you have to requery the concurrent program definition and
uncheck the box. I don't recall this being an option prior to Rel 11.
Hope this helps.
Debbie
Debbie Hendrix
BV Solutions Group, A Black & Veatch Company
913-458-8519
hendrixda@bvsg.com
Date: Wed, 04 Aug 1999 11:18:24 -0400
From: "Michael Porter" MPorter@pgahq.com
Subject: Re: Applications Tracing: Profile Option Hosed?
When I set the system profile "SQL TRace" to yes, any sql reports launched
from the application concurrent manager fail with:
alter session set sql_trace true
*
error at line 1:
ora-03114: not connected to oracle
Showing the error gives the big info!!!
$ oerr ora 3114
03114, 00000, "not connected to ORACLE"
// *Cause:
// *Action:
Any reason idea on this???
TIA,
Mike
From: Hendrix, Deborah A. [mailto:oraapps-l@cpa.qc.ca]
Sent: Thursday, August 05, 1999 8:52 PM
To: HendrixDA@bvsg.com; oraapps-l@cpa.qc.ca
Subject: RE: Applications Tracing: Profile Option Hosed?
We used to get this on 10.7, but I haven't seen it since we have been on Rel
11.
Date: Fri, 6 Aug 1999 10:04:49 -0400
From: "Yadav, Surendra" YadavS@usa.redcross.org
To: "'Hendrix, Deborah A.'" oraapps-l@cpa.qc.ca, HendrixDA@bvsg.com
Subject: RE: Applications Tracing: Profile Option Hosed?
This is a bug in 10.7 and there is a patch to it. I do not remember the
patch number, but I have applied a patch for this problem. Contact Oracle
support and ask them for it. They will give you two options , either not to
use SQL trace in reports or apply this patch.
Date: Fri, 6 Aug 1999 10:15:44 -0400
From: helen_mijic@dofasco.ca
Subject: RE: Applications Tracing: Profile Option Hosed?
We had the same problem in 10.7. We applied patch # 494226 to fix this.
Capture numberic data from varchar2 field containing numeric & alphanumeric characters
Date: Tue, 3 Aug 1999 16:21:11 -0700 (PDT)
From: john Doe vijas@rocketmail.com
Subject: SQL Question ...URGENT
Hi All,
I am writing a host program and in that I am
including Sql statement. I have got 4 digit Equipment
No which is varchar2 and containts alpha characters,
sign characters or numeric as a part of Equip no,
i.e. ,
Equip No can be '510.' or '4709', '29*0', 'RENT' or
'35'
I have to retrieve only equipment no having numeric ,
i.e. in above example '4709' and '35'
I have written Sql but it's very lengthy. Is there
any better solution for this?
Thanks in advance.
Date: Tue, 3 Aug 1999 17:42:09 -0700
From: skirby@bco.com
Subject: RE: SQL Question ...URGENT
I would let OS level tools do the work for you. At then end of your SQL
have a 'host' command start up an OS level process to handle this for you.
If you run this on a Unix box you could spool the output your looking for,
fields and all, and use grep to filter out the text you want. This would
take some tuning, but something like:
SQL file
spool spoolfile
your SQL
spool off;
host filter;
end;
Filter File
for $i in spoolfile
if "cut 1-4 | grep [A-z,a-z,.]" || true then write $1 to a new
file
end;
I need to break out my UNIX shell scripting book. I should know this. If
you hand this to one of your systems guys I think he can help you out. If
there is another UNIX hack on the list I like to see the complete answer.
I wouldn't normally respond off the cuff, but if this is urgent...
Steve Kirby
skirby@bco.com
Date: Wed, 4 Aug 1999 07:49:29 -0400
From: "Mark W. Farnham" mwf@rsiz.com
Subject: RE: SQL Question ...URGENT
Well John Doe (really?) I'm not sure why the code would be long.
select equip_no from table
where
length(replace(translate(equip_no,'#0123456789','X##########'),'#','')) = 0
and equip_no is not null;
should do it. If the equip_no column is NOT NULL then you don't need the
last part.
The translate turns any #'s to X and the digits to #, then replace
eliminates the #'s, so if you start with only digits you will have a length
of zero. There is probably an even more concise way to do this, but it does
not immediately occur to me.
Mark W. Farnham
Date: Wed, 04 Aug 1999 09:35:36 -0400
From: Ramesh Gade ramesh.gade@Yale.Edu
Subject: Re: SQL Question ...URGENT
Hi John,
This can be achieved by:
SELECT equip_no
FROM table_name
WHERE LTRIM(equip_no,'0123456789') IS NULL;
This will retrieve all the numeric equip no's.
If you want all non-numeric equip no's:
SELECT equip_no
FROM table_name
WHERE LTRIM(equip_no,'0123456789') IS NOT NULL;
HTH,
Ramesh Gade.
Date: Wed, 4 Aug 1999 19:36:38 +0530
From: "Orafin" orafin@sohm.soft.net
Subject: Re: SQL Question ...URGENT
Hi
try this,
SQL select * from ash1;
A
-------------------------
ora
ora123
123
Now you want to select only numeric values then try the following SQL.
SELECT
decode(length(TRANSLATE(upper(a),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789')),length(a),a)
FROM ash1;
it will give you only nemeric values as
SQL DECODE(LENGTH(TRANSLATE(U
-------------------------
123
HTH,
Ashraf
Date: Wed, 4 Aug 1999 09:48:41 -0500
From: "Kanakam, Venkat (c)" vkanakam@Carlson.com
Subject: RE: SQL Question ...URGENT
Hi
I hope this is what you need:
select EQUIP_NO from what_ever_table
where DECODE(ltrim(rtrim(translate(EQUIP_NO,'0123456789',' '
))),null,'IS_NUMBER') IS NOT NULL ;
You got the idead !!
HTH
Venkat Kanakam
Consultant
CIBER, Inc.
How to extract Constraint information from Data Dictionary
Date: Thu, 12 Aug 1999 11:27:50 +0100
From: c073 c073.bbs@btinternet.com
Subject: COR: Extracting Constraint Information from the Data Dictionary
I am trying to extract information from the data dictionary, relating to
Primary & Foreign Keys.
I can get the information from the Technical Reference manuals, but when
I look in 'User_Constraints' or 'DBA_Constraints', I can not see any
details re the Primary - Foreign key link.
I need to be able to find out all the foreign keys for a particular
table and the tables those foreign keys link to.
Does anyone know which view I can use, or how to get this info.
Regards
Mark Mellor
IS Developer
Britannia Building Society
UK.
Date: Thu, 12 Aug 1999 14:21:22 -0400
From: "Salunke, Pradyot " Pradyot_Salunke@csx.com
Subject: RE: Extracting Constraint Information from the Data Dictionary
Join all_constraints and all_cons_constraint tables
With constraint_name column. You will get the proper result
Pradyot
Date: Thu, 12 Aug 1999 14:43:48 -0400
From: Mohan Thanikanti mthanika@camail2.harvard.edu
Subject: RE: Extracting Constraint Information from the Data Dictionary
You can use TOAD(Tool for Oracle Application Developers) from
www.toadsoft.com.
It is a wonderful tool. It is free.
Date: Thu, 12 Aug 1999 16:59:21 -0400
From: "Jaswinder S. Saini" jaswinder.saini@yale.edu
Subject: Re: COR: Extracting Constraint Information from the Data
Mark,
Oracle Apps does not enforce the Primary Keys/ Foreign Keys as database
constraints.
The Primary key is enforced by creating a unique index on the primary key
columns.
As for the Foreign Keys, these are implemented through the forms only.
Your best bet is the Tech Ref. Manual. I don't think you will find any of
this info in the database, unless somebody knows better otherwise.
Jas.
Date: Thu, 12 Aug 1999 17:19:22 -0400
From: "Innamuri, Chakrapani" cinnamuri@btg.com
Subject: Find here the Script for Extracting Constraint Information from t
Hi,
I feel we need not give much attention to this question.
You can get n number of scripts from www.oramag.com on this type of issues.
You can also search on oracle scripts on YAHOO. Every site having ORacle
Scripts
will surely have scripts on this issue.
If you do not want to go to these sites, check the following one.
Pass OWNER and TABLE name as parameters to the script when running from
SQL*Plus.
Thanks.
-chakrapani
REM Description : Info on objects that depend on a given table.
REM Usage : Run from sqlplus after connecting as system or sys
REM ********************************************************************
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
DEFINE owner = &Owner;
DEFINE tablename = &Table_Name;
set pagesize 60;
spool depend_tbl.lst
select substr(rtrim(owner)||'.'||OBJECT_NAME ,1,40) "Object Name",
OBJECT_TYPE "Object Type"
from sys.DBA_OBJECTS
where OBJECT_ID in (select distinct D_OBJ#
from sys.DEPENDENCY$
where P_OBJ# = (select OBJECT_ID
from sys.DBA_OBJECTS
where OBJECT_TYPE='TABLE'
and OWNER=UPPER('&owner')
and OBJECT_NAME=UPPER('&tablename')))
/
select substr(rtrim(a.owner)||'.'||a.table_name,1,40) "Object Name"
, 'TABLE ' "Object Type"
from dba_constraints a, dba_constraints b
where a.CONSTRAINT_TYPE='R' and
a.R_CONSTRAINT_NAME= b.CONSTRAINT_NAME and
a.R_OWNER=b.OWNER and
b.TABLE_NAME=UPPER('&tablename') and
b.owner=UPPER('&owner')
/
spool off
SQL Spool file name limitations
Date: Thu, 12 Aug 1999 10:05:34 -0400
From: "McElhinny, Steve A." Steve.McElhinny@alcoa.com
Subject: GEN: SQL Spool filename limitations?
All -
We are testing the 10.7 NCA interface on HP-UX 10.20 platform.
We have some custom interfaces that produce extract files using SQL SPOOL
commands.
Does anyone know anything about a path limitation of 64 characters?
It appears that when we spool to a path/filename greater than 64 characters,
we receive a "Memory fault" if run from the SQL*Plus command line, and a
"Signal 11" if run from a concurrent program inside the apps.
Thanks!
Steve McElhinny
Steve.McElhinny@alcoa.com
FND FLEXSQL user exit in a PL/SQL code?
Date: Thu, 12 Aug 1999 18:28:06 +0200
From: "Firake, Vinay (CAP, GCF)" Vinay.Firake@gecapital.com
Subject: FND FLEXSQL user exit in a PL/SQL code?
Hello All,
Is there any user exit which can be used in a PL/SQL code and which is
equivalent to the srw.user_exit FND FLEXSQL
I have the following code
srw.user_exit('FND FLEXSQL CODE="GL#" NUM=":STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
OUTPUT=":WHERE_FLEX" TABLEALIAS="CC"
MODE="WHERE" DISPLAY="ALL"
OPERATOR="BETWEEN"
OPERAND1=":P_MIN_FLEX"
OPERAND2=":P_MAX_FLEX"');
I want to use this inside a PL/SQL code
Does anyone know how I can do this?
Thanks in advance
Vinay Firake
SQL script to get Total cost of capitalized assets by State
Date: Fri, 13 Aug 1999 15:47:12 -0400
From: Melanie Jubinville Melanie.Jubinville@future.ca
Subject: FA SQL- urgent!!!!!
Hi,
I need to do a SQL Statement in order to get Total Cost by State in Fixed
Assets for Capitalized Asset type only and for the Year 1998. I never been
in Fixed Asset before so I am not familiar with the tables.
Can somebody tell me which tables I need and how to link them ? This is
very important.
Thanks
Melanie Jubinville
Financial System Analyst
Future Electronics Montreal
514-694-7710 ext.2059
Date: Mon, 16 Aug 1999 13:33:43 +0100
From: "Mark Smith" mark.smith@pdg-consulting.com
Subject: RE: FA SQL- urgent!!!!!
Melanie,
The tables you need are FA_ADDITIONS (contains all assets) with FA_BOOKS
(contains Cost etc.)
Relationship is one FA_ADDITIONS is represented in one or many FA_BOOKS,
(use the ASSET_ID to make the link). The ASSET_TYPE on FA_ADDITIONS will
tell you whether it is capitalized. The DATE_PLACED_IN_SERVICE on FA_BOOKS
will help you to get only the assets purchased in 1998.
To get the location, you need to use the one-to-many relationship from
FA_BOOKS to FA_DISTRIBUTION_HISTORY and pick up the latest assignment (again
via the ASSET_ID), and then get the location name from FA_LOCATIONS (using
LOCATION_ID).
Hope this helps !
Regards
Mark Smith
PDG Consulting Ltd
5 St. Johns Lane
London
EC1M 4BH
Tel: 0171-250-4736
Fax: 0171-250-4737
http://www.pdg-consulting.com
Open call command errors - oerr 2122
Date: Tue, 17 Aug 1999 11:22:13 -0400
From: "Keith Johnson" keith.johnson@connectware.ca
To: "Oracle listserv" OraApps-L@cpa.qc.ca
Subject: Embedded dynamic SQL
I am trying to write a very *simple* query using
dynamic SQL in a Pro*C program. I am running
against Oracle Apps 10.7 and the program which I am
modifying is compiled with the DBMS=V6_CHAR
option. This is the first dynamic SQL used in
this program.
I am currently using a statement similar to:
"SELECT COL FROM TABLE" without a where clause or
any other extraneous info.
My PREPARE executes successfully, as does my
DECLARE CURSOR. when I perform the OPEN call,
I get the following error message:
SQL-02122: Invalid OPEN or PREPARE for this
database connection (errno -2122)
I can't find an explanation of this code and/or
message anywhere in my documentation. If I
execute the same statement as regular embedded
SQL, it works fine.
Can anyone offer some help to me?
Thanks,
Keith
--
Keith Johnson
Systems Analyst
Connectware Solutions - an Epic Data Company
Date: Tue, 17 Aug 1999 16:55:11 +0100
From: sebastien.herpin@clf-dexia.com
To: oraapps-l@cpa.qc.ca
Subject: =?iso-8859-1?Q?R=E9f._:_Embedded_dynamic_SQL?=
Here is the result from the command: 'oerr sql 2122':
// *Cause: An attempt was made to execute an OPEN or a PREPARE statement
// using a cursor that is currently open for another database
// connection and, therefore, cannot be used for this connection.
// *Action: Close the cursor to make it available for this connection or use
// a different cursor for this connection.
Hope it helps !
Date: Tue, 17 Aug 1999 12:45:00 -0400
From: "Keith Johnson" keith.johnson@connectware.ca
To: oraapps-l@cpa.qc.ca
Subject: =?iso-8859-1?Q?RE:_R=E9f._:_Embedded_dynamic_SQL?=
Would anyone offer an interpretation for the Cause below? (above) I tried renaming
the cursor, using the AT database option with the OPEN and still no luck.
Select Column delimited by comma
From: Vunnava, Aparna [SMTP:Aparna.Vunnava@lsil.com]
Sent: Wednesday, August 18, 1999 10:19 AM
To: Multiple recipients of list
Subject: SQL question
hi,
Is there a way in SQL where I can select a column delimited by a comma.
For e.g.:
SELECT emp_number
FROM employee;
I want all the employee numbers to be displayed as
100,200,300,400....
Thanks for your advise.
Aparna Vunnava
aparna.vunnava@lsil.com
From: Robertson, Cassandra F. [mailto:cfrobert@southernco.com]
Sent: Wednesday, August 18, 1999 8:51 AM
To: Multiple recipients of list
Subject: RE: SQL question
Aparna,
Try
select emp_number ||',' from employee;
Be Blessed!!!
Cassandra Robertson
Southern LINC - CIS Operations
(desk) 678-443-1619
(LINC) 770-550-0484 PRVT ID. 4484
* cfrobert@southernco.com
Date: Wed, 18 Aug 1999 11:09:47 -0600
From: "Vunnava, Aparna" Aparna.Vunnava@lsil.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SQL question
hi,
This will only concatenate a comma after every row returned. But what I need
is
to get one column that would have all the employees delimited by a comma.
Aparna Vunnava
aparna.vunnava@lsil.com
From: Bartoletti, Mike [mailto:BartolMi@rf.suny.edu]
Sent: Wednesday, August 18, 1999 8:10 AM
To: Multiple recipients of list
Subject: RE: SQL question
You will have to use PLSQL to do this.
Michael Bartoletti
Applications Development
Research Foundation of SUNY
ph:(518)434-7204
fx:(518)434-7211
mailto:bartolmi@rfsuny.org
Date: Wed, 18 Aug 1999 12:46:40 -0400
From: "Innamuri, Chakrapani" cinnamuri@btg.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SQL question
Use Array Processing in PL/SQL and it will be very easy.
Also you can use a shell script to do this.
-chakrapani
Date: Wed, 18 Aug 1999 13:20:14 -0400
From: "Bartoletti, Mike" BartolMi@rf.suny.edu
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SQL question
You are going to have to do something like;
declare
emp_concatenate varchar2;
cursor c1 is select emp_id from employee;
begin
for r1 in c1 loop
emp_concatenate := emp_concatenate||r1.emp_id||',';
end loop;
end;
Michael Bartoletti
Applications Development
Research Foundation of SUNY
ph:(518)434-7204
fx:(518)434-7211
mailto:bartolmi@rfsuny.org