Column Separator - set as TAB
Date: Mon, 23 Aug 1999 14:13:18 -0400
From: "Innamuri, Chakrapani" cinnamuri@btg.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: How to set COLSEP as "TAB"
Hi,
Does anybody know how to set TAB as column separator in SQL with "set
colsep" ?
I want to generate a SPOOL file with COLSEP as "TAB".
Otherwise , please let me know what is ASCII equivalent to TAB.
Thanks,
-chakrapani
Date: Mon, 23 Aug 1999 14:44:56 -0400
From: Ramesh Gade ramesh.gade@Yale.Edu
To: oraapps-l@cpa.qc.ca
Subject: Re: How to set COLSEP as "TAB"
It is: CHR(9)
Date: Mon, 23 Aug 1999 13:42:34 -0500
From: "Kanakam, Venkat (c)" vkanakam@Carlson.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: How to set COLSEP as "TAB"
Hi,
How about specifying the equivalent number of spaces for a TAB. Say, if
you want 5 spaces as tab , specify
Set colsep " " ( 5 spaces here).
SQL*PLUS automatically appends the required number of spaces for a column
value to match the maximum/formatted column value. So, basically more spaces
as column separator may give a TAB effect.
BTW, The ASCII value for TAB is 009, but I think we can not specify this in
set colsep...; Please post if you could.
Another solution may be a query like
SELECT col1||chr(9)||chr(9)||...||col2.. FROM table ;
HTH
Venkat Kanakam
Consultant
CIBER, Inc.
Date: Mon, 23 Aug 1999 14:44:08 -0400
From: "Innamuri, Chakrapani" cinnamuri@btg.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: How to set COLSEP as "TAB"
Thanks for the reply.
I already solved the problem by typing --- set colsep " "
I presses TAB instead of pressing spacebar 5 times.
This solved my problem.
We should not take 5 spaces. In some of the editors we cannot see TABs in
that
case. Also you cannot open it in an Excel Spreadsheet .
Also this is problematic if some columns are having 5 spaces in the middle
or
at the end.
Cheers.
-chakrapani
Output in tabular manner
Date: Mon, 23 Aug 1999 23:28:50 GMT
From: "Jaayanti Vishwanathan" vijaay@hotmail.com
To: oraapps-l@cpa.qc.ca
Subject: SQL Question ...URGENT
Hi All,
Any help would be highly appreciated.
I am having table having following columns :
Refund_id number
description varchar2
amount number.
There will be two records for each refund_id where description for first
record is 'Refund' and for other will be 'Interest'.
For above two records , I want to pull this data in following format :
refund_id refund_amt (for desc 'Refund') interest_amt(for 'Interest')
========= ============================== ============================
I am doing this using PL/SQL but is it possible directly in SQL without
using PL/SQL ?
Thanks in advance.
Date: Mon, 23 Aug 1999 19:37:12 -0400
From: "Annamaneni, Hanumantha" Hanumantha.Annamaneni@alcoa.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SQL Question ...URGENT
SELECT tab1.refund_id,
tab1.amount,
tab2.amount
FROM table_name tab1,
table_name tab2
WHERE tab1.refund_id=tab2.refund_id (+)
AND tab1.description = 'Refund'
AND tab2.description (+) = 'Interest'
Hanumanth
Date: Thu, 26 Aug 1999 14:42:29 PDT
From: "prasad surisetty" surisettyp@hotmail.com
Subject: RE: SQL Question ...URGENT
Why do we need th eouter join,slows doen the performence?
Date: Thu, 26 Aug 1999 19:13:38 -0400
From: "Annamaneni, Hanumantha" Hanumantha.Annamaneni@alcoa.com
Subject: RE: SQL Question ...URGENT
We need an outer join for the case where there is no record in the table for
'Interest' for a given 'Refund'. If you do not use the outer join your
report will not show the 'Refund' record.
Hanumanth
Date: Mon, 23 Aug 1999 19:41:47 -0400
From: "Bartoletti, Mike" BartolMi@rf.suny.edu
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SQL Question ...URGENT
use a decode statement like this
select refund_id, decode(description, 'Refund', amount, 0) refund_amt,
decode (description, 'Interest', amount, 0) interest_amount
from table_name
where blah = blah
Hope this helps
Michael Bartoletti
Applications Development
Research Foundation of SUNY
ph:(518)434-7204
fx:(518)434-7211
mailto:bartolmi@rfsuny.org
Name and address of employee and his contacts - how to get
Date: Tue, 24 Aug 1999 12:37:17 +0400
From: IjlalR@mashreqbank.com
To: oraapps-l@cpa.qc.ca
Subject: GEN: SQL
Hi,
I have a question which may be too basic for this forum but here goes:
I would like to create a view which gives me the name and address of an
employee plus the names and addresses of his/her contacts. The employee name
can be selected directly from PER_PEOPLE_F using the Person_Id. My problem
is that I need to get the Contact_Relationship_Id from
PER_CONTACT_RELATIONSHIPS before I can get the contact names from
PER_PEOPLE_F. How can this be achieved using 1 view? The end result that I
am looking for is 1 view that can be used to get this info.
Any suggestions/ideas would be appreciated,
Ijlal
LONG Raw Column - how to read
From: Krishna Kopalle [mailto:krishtabu@yahoo.com]
Sent: Friday, August 27, 1999 7:36 AM
Subject: how to read LONG RAW column
Hi All, I have to read data in a long raw column &
perform some string manipulation on it.
Actually Long Raw is used for sounds,bitmaps etc,.
but here we are storing character data.
I need to read this data & perform string manipulation.
All i can read is the Hexcode ofthe actual data.how do i retrieve the
correct data.
tried using UTL_RAW but of noavail..
Somebody please help.
Thanks
Date: Fri, 27 Aug 1999 12:15:03 -0400
From: "Innamuri, Chakrapani" cinnamuri@btg.com
Subject: RE: how to read LONG RAW column
Try this.
SQL set long 20000
-chakrapani
Date: Fri, 27 Aug 1999 11:56:46 -0700 (PDT)
From: Krishna Kopalle krishtabu@yahoo.com
Subject: RE: how to read LONG RAW column
we cannot select a long raw column at sql prompt.
it returns an 'Inconsistent datatypes' message.
data is stored in bits in a long raw column & when we
try to retireve the same it returns in hexode.
i want to look at my actual data & perform string manipulation.
i can view the data in the field from an application front end form or
using a tool like SQL Navigator.
I hope iam clear now.
Thanks.
Krishna Kopalle.
Mutation - of Tables at Detail level
Date: Mon, 26 Jul 1999 17:35:27 -0500
From: "Veach, L. Vincent" VeachLV@bvsg.com
To: "'oraapps-dba@cpa.qc.ca'" oraapps-dba@cpa.qc.ca
Subject: Mutating Tables!! AGHHHHHHHHHHHHHH!!!!!!!!!!
I need to understand how the mutating table error occurs at a detail level.
I am trying to validate the entered invoice number to be unique within a
vendor.
But no matter how I try to exclude the current row, I still get a mutating
table error.
Within the Before update trigger on each row the following code is trying to
count the
number of rows that have the invoice number (x_invoice_num) and vendor id
(x_vendor_id)
but is NOT the current row that fired the trigger ( invoice_id !=
x_invoice_id).
SELECT count(1), min(org_id)
INTO dummy_a, dup_org_id
FROM ap.ap_invoices_all
WHERE invoice_num = x_invoice_num
AND vendor_id = x_vendor_id
AND ((x_invoice_id IS NULL) OR (invoice_id != x_invoice_id));
If I get any number in dummy_a that is greater than zero then I know I have
a duplicate
invoice number.
I have tried using ROWID not equal to x_rowid where x_rowid is the trigger
row; but this still
gives me a mutating table error. Why? Is it the way my where clause is
formed?
Could it be that I am doing a full table scan because of the (x_invoice_id
IS NULL)?
I have it working with an INSTEAD OF trigger but my supervisor does not want
to use that
type of trigger since I would have to copy the update that the form is doing
within the trigger
and that form is a Oracle Apps form which could change in the next release.
Also the trigger
has been identified with some performance issues we think may be due
directly to it being
a INSTEAD OF trigger as apposed to a BEFORE UPDATE.
I am on release 11.03 of Oracle Applications, working with the AP module.
The database is 8.04 and its on a AIX machine.
Vincent Veach
Date: Wed, 28 Jul 1999 10:08:30 -0400
Date: Thu, 29 Jul 1999 07:30:05 -0500 Extracts from Oracle Tables
At 06:50 AM 4/22/99 -0400, William Gaston wrote:
Date: Thu, 22 Apr 1999 09:24:19 -0400
From: oraapps-l@cpa.qc.ca [mailto:oraapps-l@cpa.qc.ca] On Behalf Of
CFowler@littonapd.com
From: Jason Babicky [mailto:jasonb@aromat.com]
Date: Fri, 23 Apr 1999 10:00:44 -0700 Cursor for Inserts taking long time
Date: Wed, 08 Sep 1999 14:37:50 -0400
Date: Fri, 10 Sep 1999 22:48:07 MYT-8 Array - passing between procedures with Null value
Date: Mon, 13 Sep 1999 10:15:02 +1000
Date: Mon, 13 Sep 1999 09:01:22 -0700 Message - print from Pl/SQL procedure
Date: Tue, 14 Sep 1999 10:01:50 -0700 (PDT)
Date: Tue, 14 Sep 1999 12:14:29 -0500
Date: Tue, 14 Sep 1999 10:15:34 -0700
Date: Tue, 14 Sep 1999 10:26:40 PDT
Date: Tue, 14 Sep 1999 13:28:49 -0400 Date Format change
From: Innamuri, Chakrapani [SMTP:cinnamuri@btg.com]
Date: Tue, 14 Sep 1999 17:28:48 GMT
Date: Tue, 14 Sep 1999 12:29:46 -0500
Date: Tue, 14 Sep 1999 13:29:48 -0400
Date: Tue, 14 Sep 1999 13:46:29 -0400
Date: Tue, 14 Sep 1999 14:06:19 -0400 Compute count not working in SQL*PLUS report
Date: Fri, 1 Oct 1999 12:08:53 -0400
Date: Fri, 15 Oct 1999 09:20:42 -0700
From: "Mark W. Farnham" mwf@rsiz.com
To: "'oraapps-dba@cpa.qc.ca'"
Subject: RE: Mutating Tables!! AGHHHHHHHHHHHHHH!!!!!!!!!!
I'm curious why you don't just add a unique index on vendor_id and
invoice_num.
From: "Veach, L. Vincent" VeachLV@bvsg.com
To: "'oraapps-dba@cpa.qc.ca'"
Subject: RE: Mutating Tables!! AGHHHHHHHHHHHHHH!!!!!!!!!!
Our DBA suggested the same thing. But when we tried we found 600+ duplicate
invoice/vendors
in the database already. Just a little too much to correct by hand even if
were possible.
But I did find a solution in an Oracle PL/SQL book for solving this type of
problem.
The book suggested that the trigger be split into two triggers, a before
each row trigger and
an after statement trigger. The before each row trigger will save row
information into a PL/SQL
public package table and the after statement trigger will reference the
public package table for validation.
Since the after statement trigger is after the update but before any commit,
you don't suffer the mutating table
error when trying to read the same table and you can raise an application
error that will/could prevent
the commit of invalid data.
Good Morning...
We are going to extract data from Oracle tables and create a flat file
output. Different record types will be created (4 different types)
based on the data in each record. Has anyone done this? What is the
best method? Any help would be greatly appreciated.
Bill Gaston
Information Resources, Inc.
Chicago, IL
william.gaston@infores.com
From: Sateesh Reddy sreddy@camail2.harvard.edu
Subject: Re: Oracle Extracts
Hi Bill
Best way to extract data from oracle is write a pl/sql and use UTL_FILE
package.
this way you will have full control on your program. I have done many
extracts using this method.
In one of my program it creates 7 different files. But you need to have
oracle 7.3 or higher verson of database to use UTL_FILE package.
Good Luck
Sateesh Reddy
Harvard University
Boston.
Sent: Friday, April 23, 1999 6:00 AM
Subject: RE: Oracle Extracts
Hi,
Where can I find documentation that will tell me about the different
packages (i.e. UTL_FILE), etc???
Thanks in advance...
Candace
Sent: Friday, April 23, 1999 7:37 AM
Subject: RE: Oracle Extracts
Oreilly has a fairly decent book "Oracle Built In Packages" that
you might try...
Jason Babicky, Programmer / Analyst
Matsushita Electronic Materials, Inc.
From: CFowler@littonapd.com
Subject: RE: Oracle Extracts
Jason,
Thank you very much for the information.
Candace
From: Ramesh Gade ramesh.gade@Yale.Edu
To: oraapps-l@cpa.qc.ca
Subject: PL/SQL question
Hi all,
1) I have a simple procedure which has a cursor that selects all from
a remote oracle database view(about 90,000 records), opens the cursor and
inserts this data into my local database table. This procedure for some
reason is taking 5 hrs.
2) If I do 'Insert into my_local_table select all from
my_remote_view' this takes 6 min.
Could anybody tell me why the first method is taking longer than the
second method.
Is it something to do with resource usage on the remote SGA while
using the explicit cursor in the first method ?
Is there a work around for this.
Thanks in advance,
Ramesh.
From: philip@aleytys.pc.my
To: Ramesh Gade ramesh.gade@Yale.Edu, oraapps-l@cpa.qc.ca
Subject: Re: PL/SQL question
Using a cursor to pull data from a remote database link has significant
overheads. Your insert method is faster but the fastest method is to use
the SQL copy command to copy the data from a remote database. Since you
are using a PL/SQL cursor I presume you need to do some data manipulation
during the data transfer. What I did when I pulled over my GL journals
from our 9.4 financials to our new 10.7 box was to SQLCopy over into a
local temporary table (lets call it CUS_GL_INTERFACE) and then use PL/SQL
to do some post processing on it.
Philip
From: Jason Ekamper Jason.Ekamper@poports.com.au
To: "'OraApps-L@cpa.qc.ca'" OraApps-L@cpa.qc.ca
Subject: OAS PL/SQL Question
Hiya all. Question regarding passing an array between procedures. The plsql
procedure checks all check-boxes on the current form, and then passes all
the selected values (checked check-boxes) to an array for an action to be
performed on it. The problem occurs when the user selects no values. The
array passes 'null' values to the next procedure, and the program crashes.
Assigning a default value to one of the checkboxes is not an option, as the
procedure deals with deletion of records from the database.
Any help would be greatly appreciated.
Thanks
Jason Ekamper
Jasek@bigpond.com
From: "Orth, Tom (torth)" torth@sequent.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: OAS PL/SQL Question
Use a PL/SQL table, or in oracle 8 they are referred to as collections.
They
are
essentially "sparse" arrays on steroids. Sparse meaning they can have have
null values.
From: Ajay Alur ajayall@yahoo.com
To: oraapps-l@cpa.qc.ca
Subject: How do you print messages from a PL/SQL proc
I have built a interface package and I want to display messages either
in the log or the out file when I run my interface. Are any standard
packages available?
TIA
AJ
From: "Logan, Ernie" Ernie_Logan@bmc.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: How do you print messages from a PL/SQL proc
If you are on Apps 11.0 or above, FND_FILE is available for that purpose.
From: "Krishna, Shashi (shashi)" shashi@sequent.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: How do you print messages from a PL/SQL proc
You can use FND_FILE package to print the message in log or output file.
fnd_file.put_line(fnd_file.log, 'TESTING ');
Thanks
Shashi
From: "Satish Hakim" satish_hakim@hotmail.com
To: oraapps-l@cpa.qc.ca
Subject: RE: How do you print messages from a PL/SQL proc
I guess it is DBMS_OUTPUT package which we use to display message in log
file. You would be using it as :
DBMS_OUTPUT.PUT_LINE('Your message');
I hope this helps
Satish Hakim
From: Dinesh Makked dmakked@deltadentalva.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: How do you print messages from a PL/SQL proc
Use the standard UTL_file package of Oracle to create log/out files.....
Sent: Tuesday, September 14, 1999 10:18 AM
To: Multiple recipients of list
Subject: SQL question..... ( may be very simple ...)
Hi all,
Excuse me if you feel this is very simple question.
I have a DFF field which is storing the values of DATES as,
for ex:"1999/09/13 00:00:00".
How do I get the value "RRMMDD" value here....
Thanks in Advance,
-chakrapani
From: "bharathi Yarlagadda"
To: oraapps-l@cpa.qc.ca
Subject: Re: SQL question..... ( may be very simple ...)
Chakrapani,
Why are you worried about the exercise 'RRMMDD' etc.
Just use
substr('1999/09/10 00:00:00',3,2) ||
substr('1999/09/10 00:00:00',6,2) ||
substr('1999/09/10 00:00:00',9,2) if you are sure that the field
always shows the details in the pattern you mentioned.
This will reduce burden.... Am I correct ???
- Bharathi Yarlagadda
From: "Kanakam, Venkat (c)" vkanakam@Carlson.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SQL question..... ( may be very simple ...)
TO_CHAR(TO_DATE('1999/09/13 00:00:00','YYYY/MM/DD HH24:MI:SS'),'RRMMDD')
HTH
Venkat Kanakam
Consultant
CIBER, Inc.
From: "Joe Conroy" jconroy@MAIL.NYSED.GOV
To: oraapps-l@cpa.qc.ca
Subject: Re: SQL question..... ( may be very simple ...)
Try select trunc(to_char(sysdate, 'RRMMDD')) from dual
which give you something like 990914=20
From: "Joe Conroy" jconroy@MAIL.NYSED.GOV
To: oraapps-l@cpa.qc.ca
Subject: Re: SQL question..... ( may be very simple ...)
actually you want to do something like this:
to_char(trunc(sysdate), 'RRMMDD')
replacing sysdate with your DATE column.
From: "Innamuri, Chakrapani" cinnamuri@btg.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SQL question..... ( may be very simple ...)
Venkat and Bharathi,
You both are right. I did blunder by using "HH" instead of "HH24"....
Hence the question.
As Bharathi mentioned, I can also use that way....
Thanks to you both.
-chakrapani
From: Melanie Jubinville Melanie.Jubinville@future.ca
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: SQL*PLUS Report - Compute Count ????
Hi,
I am trying to run this SQL*PLUS report and my COMPUTE count will
not work. Can anybody tell me what I am doing wrong?
Thanks
set termout on
set heading on
ttitle on
btitle on
clear columns
clear breaks
clear computes
ttitle left 'Future Electronics' -
center 'Daily Rates Monitoring Report' -
right 'Report Date: ' &1 skip 1 -
center 'Date: ' &2 -
right 'Page: ' format 9,999 sql.pno skip 3
btitle left ' ' skip 1 -
center 'Confidential and Proprietary' -
right 'FECGLRAT.sql'
COLUMN sobk heading 'Set of Book' format a40 trunc
COLUMN ccrr heading 'From Currency Code' format a20 trunc
COLUMN ccrt heading 'Conversion Rate' format 999,999.99999999999
BREAK on sobk skip 1 on report
COMPUTE count of ccrr on sobk on report
SELECT sob.name sobk,
gdr.from_currency_code ccrr,
gdr.conversion_rate ccrt
FROM gl_daily_conversion_rates gdr,
gl_sets_of_books sob
WHERE (gdr.set_of_books_id = sob.set_of_books_id(+))
AND trunc(gdr.creation_date) = '&2'
ORDER BY sob.name
/
From: "Bagavathi Rajan K" rajankb@wipsys.soft.net
To: oraapps-l@cpa.qc.ca
Subject: Re: SQL*PLUS Report - Compute Count ????
Hi,
Break your compute statement into two pieces.
COMPUTE count of ccrr on report
COMPUTE count of ccrr on sobk
Regards,
K.Bagavathi Rajan.