Date format - surprising results
Date: Thu, 17 Jun 1999 13:00:29 -0500
From: "Kanakam, Venkat (c)" vkanakam@Carlson.com
Subject: Date format - surprising results
Hi ,
While trying to write a Reports 2.5 report which has to use the
number of weeks for a month in some calculations , I thought I can get from
Oracle with a query as follows and do something after that. The query gives
accurate results for some years , but for some years for december month ,
the cumulative weeks from year start gives as 1 .
SQL select to_char(last_day(to_date('12-2008','MM-YYYY')),'IW') from dual ;
TO
--
01
I can use WW instead of IW , but it gives me 53 weeks instead of 52.
Please suggest a workaround or correction.
Thanks
Venkat Kanakam
Consultant
CIBER, Inc.
Calling SQL from Apps
Date: Thu, 1 Jul 1999 15:03:28 -0400
From: louis_avrami@es.adp.com (Louis Avrami)
To: oraapps-dba@cpa.qc.ca
Subject: Error calling sqlplus from app
Hello all,
We're currently running Apps 10.7, SmartClient 16.0 on AIX 4.3.2.
We recently upgraded our RDBMS from 7.3.2.3 to 7.3.4.4. (the 16.1
upgrade is scheduled for next month)
Since the upgrade we have been experiencing a strange problem with
one of our custom SQL scripts that is executed through the
application. When it is executed through the app, it fails with the
following error messages:
APP-00969 Program was terminated by signal 11
APP-01126 Concurrent Manager got error running SQL*Plus for request
We still have a 7.3.2.3 test database, and when we run the SQL
script there, it works without any problems.
The SQL itself is simple, it's just querying some data into a flat
file. The first line of the script creates a spool file:
spool $GL_OUT/Report1.lst
select ....
The Report1.lst file does get created, as a zero-byte file. Right
after that, the program blows up and we get the signal 11.
If anyone has any insights into this or any similar problem, please
let me know.
Thanks,
Lou Avrami ( louis_avrami@es.adp.com )
Date: Sat, 3 Jul 1999 03:03:36 -0700 (PDT)
From: S F sf3010@yahoo.com
To: oraapps-dba@cpa.qc.ca
Cc: louis_avrami@es.adp.com
Subject: Re: Error calling sqlplus from app
Hi Louis,
We are running Oracle Apps 10.7 on HP-UX 11 on Oracle RDBMS 7.3.4.2.0.
While running some concurrent requests, we've encountered the same
error message:
APP-00969 Program was terminated by signal 11
By the way, these concurrent requests comprise of both standard and
some customized processes/reports.
I have been talking to Oracle Support for this but didn't get any help
on this one. However, I've checked on UNIX, that when a program exits
with signal 11 it means that there has been a privileged register trap
exception. That's the only thing I've been able to locate on this
error.
In case if you get any clues/ideas about this from Oracle or elsewhere,
please do email to me as well.
Best regards,
SF
sf3010@yahoo.com
Date: Mon, 05 Jul 1999 11:27:25 +0200
From: Ian Barbour ian.barbour@nvision.co.za
To: oraapps-dba@cpa.qc.ca
Subject: RE: Error calling sqlplus from app
Have you tried changing $GL_TOP/Report1.lst to /tmp/Report1.lst ?
--Ian
Date: Tue, 6 Jul 1999 07:08:30 -0500
From: "Vicha, Curt" vichac@Jostens.com
Subject: RE: Error calling sqlplus from app
Has maxdsiz Unix kernal parameter changed, if not what size is it?
maxdsiz is max memory for a process.... If 64mg or lower make it larger.
Tuning SQL Loop with 'IN' clause
Date: Fri, 02 Jul 1999 16:39:13 GMT
From: manu b manu024@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: some SQL challenge
Hi Friends,
does not look like a good time to ask question( everybody will be in
Holiday mood now) but can someone pls suggest on the following situation :
In a Loop (stored procedure) my developer is doing something like this:
select a,b,c from table_a where
e = :value1 and g = :value2
AND
d in (select z from table_b where x = :value)
table_b : Application table
Have just 2 columns z,x. It has around .5 million records. Will keep on
increasing.
There are three indexes on this table.One primary which is on (z,x) and
other two are non-unique indexes on the 2 columns separately.
table_a : Basically a temporary table so I can play around with its storage
parameter. Has around 25,000 records and not going to increase.
The above statment is physically coded at 4 places with different where
clauses for table_a but as far as the 'IN' clause is concerned which refers
table_b that is same.
Can someone suggest me a way to tune it . I dont want to remove the whole
statement from all four places but I want to tune the 'IN' part for sure(
like making a stored function if possible or any other idea)
thanks for any help
Date: Fri, 2 Jul 1999 12:56:02 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
To: "'oraapps-dba@cpa.qc.ca'" oraapps-dba@cpa.qc.ca
Subject: RE: some SQL challenge
Manu Instead of IN clause use exists clause which is much faster then IN
clause also it takes care of the NULL values too.
hope this helps.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Fri, 02 Jul 1999 19:41:44 GMT
From: manu b manu024@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: RE: some SQL challenge :once more
Thanks Bharat & Arvind
for the responses: One more small question
1) When I use exists then I get the following explain plan output :
SELECT STATEMENT
FILTER
TABLE ACCESS FULL table_a
INDEX UNIQUE SCAN table_b_PK
AND when I use Join then I get the following explain plan
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS FULL table_a
INDEX UNIQUE SCAN table_b_PK
May I know which one will be faster.
Also I think in my setup full table scan will not cause much harm as this
table(TMP_PAYROLL_REG) will be less than 1 mg and I can cache this. I dont
want to create indexes also as selectivity is not good. Do you agree with
the my observations..
any response will be greatly appreciated.
thanks
Compile SQL script of 2000 lines
Date: Wed, 14 Jul 1999 10:29:55 +0530
From: Kluz amn9c038@trishul.icil.co.in
To: "oraapps-l@cpa.qc.ca" oraapps-l@cpa.qc.ca
Subject: Compile SQL script of 2000 lines?????????????
Please tell me what is the maximum length of procedure that can be
compiled on an SQL prompt. I have a script which is 2000 line and
about 80k. How do I run it.
Please advise as soon as possible.
Regds
Kluz
Date: Wed, 14 Jul 1999 07:30:55 -0700
From: "Orth, Tom (torth)" torth@sequent.com mailto:torth@sequent.com
Subject: RE: Compile SQL script of 2000 lines?????????????
Is it not possible to modularize some of the code? Perhaps
making a package for util functions or something? If that's not
possible, did you try stripping out the comments?
Date: Thu, 15 Jul 1999 09:27:29 +0530
From: Kluz amn9c038@trishul.icil.co.in
Subject: Re: Compile SQL script of 2000 lines?????????????
Yup!!! Thank you very much.............
Its working now,............... but my intention is to
find out the longest code that can be compiled on
an SQL prompt.
Regds
Kluz
Date: Thu, 15 Jul 1999 22:58:47 +0530
From: "Orafin" orafin@sohm.soft.net
Subject: Re: Compile SQL script of 2000 lines?????????????
Hi,
The size of the pl/sql procedure is limited 64K. The coding Standards
Manual of Oracle Apps talks about this. I'm just reproducing it here for ur
reference.
Package Sizes
A clientside PL/SQL program unit’s source code and compiled code
together must be less than 64K. (A program unit is a package
specification or body or standalone procedure.) This implies that the
source code for a program unit cannot exceed 10K.
If a package exceeds the 10K limit, you can reduce the size of the
package by putting private variables and procedures in one or more
private packages. By standard, only the original package should
access variables and procedures in a private package. If an individual
procedure exceeds the size limit, you should separate the code into two
or more procedures.
When an Oracle Forms PL/SQL procedure exceeds the 64K limit,
Oracle Forms raises an error at generate time.
Server side packages and procedures do not have a size limit, but
when Oracle Forms refers to a server side package or procedure, it
creates a local stub, which does have a size limit. The size of a package
stub depends on the number of procedures in the package and the
number and types of arguments each procedure has. Keep the number
of procedures in a package less than 25 to avoid exceeding the 10K
limit.
HTH,
Kishore
Dynamic SQL
--- Kluz amn9c038@trishul.icil.co.in wrote:
I encounter this error when I execute the procedure
below, any inputs please mail ASAP, I am on oracle8:
SQLshow errors
Errors for PROCEDURE DPRO:
LINE/COL ERROR
-----------------------------------------------------------------
6/1 PL/SQL: Statement ignored
6/50 PLS-00226: package 'DBMS_SQL' used as
variable reference
The procedure.....
create or replace procedure dpro(sal in
emp.sal%type) as
cur number;
rows number;
begin
cur:=dbms_sql.open_cursor;
dbms_sql.parse(cur,'delete from emp where ename like
:x',dbms_sql);
dbms_sql.bind_variable(cur,':x',sal);
rows:=dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
exception
when others then
dbms_sql.close_cursor(cur);
end;
/
Regds
Kluz
Date: Wed, 14 Jul 1999 02:37:32 -0700 (PDT)
From: =?iso-8859-1?q?david=20solomon?= david_solomon_uk@yahoo.com
Subject: Re: Kluz:Dynamic SQL???????????????????
Hi,
You need to enclose :x between two quotes, i.e ':x'.
Cheers
David
Date: Wed, 14 Jul 1999 15:30:08 +0530
From: Kluz amn9c038@trishul.icil.co.in
Subject: Re: Kluz:Dynamic SQL???????????????????
I am sorry but it even gives error if I write:
select 1 from dual
Regds
Kluz
Date: Wed, 14 Jul 1999 14:17:37 +0400
From: mzaveri@eppco.co.ae
Subject: Re: Kluz:Dynamic SQL???????????????????
You need to write the version compatiblity variable in place of dbms_sql
you can write dbms_sql.native
Node's name from SQL
From: david solomon david_solomon_uk@yahoo.com
Subject: *** Node's name from SQL ***
Date: Wed, 14 Jul 1999 08:08:51 -0400 (EDT)
Hi,
Can you tell me how to get the node's name for an instance using
sqlplus without hosting out.
Cheers
David
Date: Wed, 14 Jul 1999 08:43:26 PDT
From: "DASARI SUBRAHMANYAM" srdasari@hotmail.com
Subject: Re: *** Node's name from SQL ***
How about this:
'select userenv('terminal') from dual;'
-dasari
Date: Wed, 14 Jul 1999 08:44:26 PDT
From: "DASARI SUBRAHMANYAM" srdasari@hotmail.com
Subject: Re: *** Node's name from SQL ***
I am sorry, I am mistaken for terminal id
Date: Wed, 14 Jul 1999 13:22:40 -0700 (PDT)
From: MOHAMMAD RAFIQ rafiq9857@yahoo.com
Subject: Re: *** Node's name from SQL ***
Please try this. It can be amended according to
your requirement.Run as system or equivalent/
select s.username "ORACLE user", s.osuser "OS user",
s.sid, s.serial# "Srl#", p.spid "Sys PID",
nvl(s.terminal, decode(s.machine,null,'Client',
substr(s.machine,1,instr(s.machine,'.')-1))) "Source",
s.status "Status", s.program "Program", s.logon_time
"Logged on"
from v$session s, v$process p
where s.paddr = p.addr
and s.username is not null -- filters out ORACLE
background processes
and s.status = 'ACTIVE'
order by s.machine, s.username;
Regards
Rafiq
Date: Thu, 15 Jul 1999 09:06:04 +0000 (GMT)
From: =?iso-8859-1?q?david=20solomon?= david_solomon_uk@yahoo.com
Subject: Re: *** Node's name from SQL ***
Mohammad,
Many thanks for the code. All what really I wanted was to have a simple
code to show the node's name and the active instances on it. If you you
have such a code that will be nice.
Cheers
David
Date: Thu, 15 Jul 1999 13:09:57 -0500
From: "Landa, Mark (c)" U25MIBG@Carlson.com
Subject: RE: *** Node's name from SQL ***
From sqlplus?
If what you are looking for is the Unix host name for the box the instance
you are currently logged into is running on you can try something like:
select substr( program, instr(program,'@',1,1)+1,
instr(program,'(',1,1)-instr(program,'@',1,1)-1 ) hostname
from v$session
where program like '%PMON%';
This should return the equivalent of using "uname -n" from the OS.
I can't think of any reason why a given instance would have information in
the database about any other active instances running on the same box.
Date: Fri, 16 Jul 1999 06:53:13 -0400 (EDT)
From: =?iso-8859-1?q?david=20solomon?= david_solomon_uk@yahoo.com
Subject: RE: *** Node's name from SQL ***
Hi,
This can happen when you are working on a clustered system.
When you view your systems processes you can see all available
databases on the instance; but if you login to sql session you would
see the database for your session.
Let us make it simple, What is needed is a sql code to show node's
name, instance name and all active databases on it. This should match
up with what you have on your system processes relating to all active
databases at the time.
Cheers
David
Date: Fri, 16 Jul 1999 14:14:18 -0700
From: Andy Rivenes arivenes@llnl.gov
Subject: Re: *** Node's name from SQL ***
Here's code I use to determine the node name:
-- Get node name
-- The original query turns out to be UNIX specific. On NT all background
-- processes run as threads of the ORACLE executable and therefore don't
-- contain the node or process name.
BEGIN
SELECT substr(program,8,INSTR(program,' ')-8)
INTO node_nam
FROM sys.v_$process
WHERE program LIKE '%(PMON)%';
EXCEPTION
WHEN NO_DATA_FOUND THEN
node_nam := 'NT';
END;
Andy Rivenes
arivenes@llnl.gov
Date: Wed, 21 Jul 1999 09:36:46 -0400
From: "Parker, Robert" Robert.Parker@dplinc.com
Subject: RE: *** Node's name from SQL ***
NT environment, try this.
select sid,user#,osuser,schemaname,machine,terminal,client_info from
v$session
Robert Parker
Date: Wed, 21 Jul 1999 11:17:27 -0400 (EDT)
From: =?iso-8859-1?q?david=20solomon?= david_solomon_uk@yahoo.com
Subject: RE: *** Node's name from SQL ***
Robert,
Many thanks for your notes.
Will you tell me, in your environment, will this code show you all
available databases on the system? The problem I have is that the code
shows me the name of the database I connected to only. If you check the
processes on the system then you can see all active processes for all
databases on it.
Regards
David
Date: Wed, 21 Jul 1999 14:30:00 -0400
From: "Parker, Robert" Robert.Parker@dplinc.com
Subject: RE: *** Node's name from SQL ***
David,
The code I sent will only show you the databases on the box you're
connected. For the same type of information on an Enterprise scale, take a
look at Oracle's Enterprise Manager Tool (Repository Manager). For NT server
process on NT servers within the company, take a look at Server Manager for
NT.
Robert
How to get Report names for each report group ?
Date: Thu, 15 Jul 1999 09:50:49 -0400
From: Melanie Jubinville Melanie.Jubinville@future.ca
Subject: SQL question*****
Hello,
I am trying to get all the report names for each report group that
we defined.
I know to link FND_REQUEST_GROUPS and FND_REQUEST_GROUP_UNITS but
from which table to I get the report name and description. Is it
FND_CONCURRENT_PROGRAMS ? If so, how do I link it to the other tables ???
Thanks
Melanie
Date: Thu, 15 Jul 1999 22:40:52 +0530
From: "Orafin" orafin@sohm.soft.net
Subject: Re: SQL question*****
Hi,
If u r referring to the reports we define in GL thru FSG, then we can get
the report names from
'NAME' column of 'RG_REPORTS_V'.
HTH,
Kishore
From: Melanie Jubinville [SMTP:Melanie.Jubinville@future.ca]
Sent: Thursday, July 15, 1999 12:22 PM
Subject: RE: SQL question*****
No, I am talking about the report names that you attach to a report group
in
sysadmin
Date: Thu, 15 Jul 1999 14:42:08 -0400
From: "Jagannathan, Ravi" RJagannathan@prcnet.com
Subject: RE: SQL question*****
FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID=FND_REQUEST_GROUP_UNITS.REQUES
T_UNIT_ID
Thanks
Ravi Jagannathan (MIS)
Tel : (305) 816 4831
Date: Thu, 15 Jul 1999 13:57:54 -0500
From: "Logan, Ernie" Ernie_Logan@bmc.com
Subject: RE: GEN: SQL question*****
select request_group_name
, concurrent_program_name
, user_concurrent_program_name
from fnd_concurrent_programs_vl p
,fnd_request_groups g
, fnd_request_group_units gu
where g.application_id = gu.application_id
and g.request_group_id = gu.request_group_id
and gu.unit_application_id = p.application_id
and gu.request_unit_id = p.concurrent_program_id
Displaying fields from query dynamically
Date: Thu, 15 Jul 1999 14:06:14 PDT
From: "" nmturakhia@hotmail.com
Subject: Displaying fields dynamicaly in forms 4.5
Dear oracle users,
I have a special requirement.
Assume col1 that I am selecting from table temp.
select col1 from temp;
Now if query fetches 9 records.
Each column I want to show like this.
1 2 3 4 5 6
7 8 9
If query fetches 15 records.
Each column I want to show like this.
1 2 3 4 5 6
7 8 9 10 11 12
13 14 15.
That means items displayed on screen chages based on output of
query. I can not define maximum number of fileds at design time.
Pls. suggest some solution
Thankx
Nishit
Date: Fri, 16 Jul 1999 11:57:35 -0400 (EDT)
From: Andy Schindler aschindl@yahoo.com
To: oraapps-l@cpa.qc.ca
Subject: Re: Dynamic creation of item (Forms 4.5)
Message-ID: 19990716155735.23461.rocketmail@web221.mail.yahoo.com
Create a temporary table with 6 columns.
In a pre-form trigger write a PLSQL to populate the
table along the lines of this pseudo code:
cnt = 1
begin
fetch
if cnt = 1 set col1 var; cnt += 1;
if cnt = 2 set col2 var
if cnt = 3 set col3 var
if cnt = 4 set col4 var
if cnt = 5 set col5 var
if cnt = 6
set col6;
insert into temp table
reset vars
cnt = 1
end
Develop form on temporary table.