|
 |
| Recent blog posts: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Sunday, 13 August 2006
|
| what is the SQL equivalent of 'continue' and 'break' in C ? Guest 23:40:59 |
| | what is the SQL equivalent of 'continue' and 'break' in C ?
like can I do this...
for counter in 1..10
if(something something)
( if (something)
( if(something)
then continue;
//Rest of the for loop
end loop;
Would it start the next iteration without processing the rest of the
loop ?
thanks
- Ardy
|
| |  12 answers | Add comment |
|
| Specifying the CLOBs in SQLLOADER control files VenkataRamesh Kommoju 23:24:01 |
| | hi,
i have a column of length 4211 from the DB2 which is already loaded
into the datafile in EBCDIC format.
I am loading the data into the oracle tables using the charcter set
clause from EBCDIC to ASCII.
when i specify a column with CLOB datatype i am getting a syntax error.
Please let me know how to load a colunm with CLOB data type in the
oracle databaase tabel.
thanks
Ramesh
|
| | 5 answers | Add comment |
|
| Measuring wait time for openning a new connection Eyal Yurman 22:16:03 |
| | Hi,
I have an application (perl, using DBI) which opens many connections to
the database.
Will I see the effect of the connections on the wait events?
If not, can I measure this effect using the listener?
Thanks,
Eyal Yurman.
|
| | 3 answer | Add comment |
|
| how to download 8i Peter 22:13:06 |
| | Hello DBAs,
Please tell me from where I can download Oracle 8i. I need that version
but I didn't find it on Oracle.com. I need this to do upgradion. Please
inform......................
|
| | 4 answer | Add comment |
|
| Simple SQL update Randy Harris 21:11:16 |
| | I'm trying to do an update in Oracle 9i that seems like it should be
very simple but I can't figure out the syntax. The update needs to use
a different formula Dependant on the current value of the attribute.
Simplified:
UPDATE MyTable Set F1 = (F1 > 1) * 2 + 1 ;
This returns a missing right parenthesis error.
As does
Select (2>1) FROM dual ;
How do I put the comparison into the calculation?
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
|
| | 2 answer | Add comment |
|
| Data export from oracle 10G to SqlServer 2000 Aamir Majeed 09:27:29 |
| | I need to transfer data on daily basis from an oracle 10g database
running on Linux to the sql Server 2000 database.For that I need first
to get my query output from oracle into flat file and then bulkinsert
that file into sqlserver 2000.
I just read about Oracle Data Pump which could be used for Bulk Export
.I need to know how could I use data pump to get data out from oracle.
|
| | 7 answers | Add comment |
|
| My Introduction Faisal 08:30:00 |
| | Hi,
I am Faisal. I have six years of experience playing with oracle
database. I wnt to learn from you people and also want to share my
knowledge.
Regards,
|
| | 7 answers | Add comment |
|
| How to force two entities to point to the same lookup value Giraffe 04:32:53 |
| | Hi all,
Please bear with me I've tried to make my question as terse as possible,
whilst not appearing confusing (hopefully I've not failed in this regard).
I'm trying to store a Chart configuration in an Oracle database
(although I'm hoping that my choice of database will not lead to a
solution specific to it, since so far my database is not platform, or
database specific).
One of these chart entities has two or more axes (one x and at least one
y) related to it, and each axis relates to zero or more plot entities
(which store plot information such as plot type, line or columns/bars
etc). these plot entities relate to a specific item of data that can be
plotted (and relates to a Unit table).
So far I have
relations-> Chart (1-3) Axis (0-many) Plot (Many-1) DataItem
(I'm assuming I've got this relationship right)
How can I now insert a relationship with a Unit entity so that I can
ensure that Axis and Plot can refer to any unit in the unit relation *so
long as its the same Unit*. I'd like to enforce at a relational level a
limitation so that any number of plots can be configured for a specific
axis so long as they are all of the same unit. For instance income and
outgoings can both be related to an axis since they share the same unit
of currency. But weight and income cannot be plots that relate to one
axis since there are two units here and an axis can refer to only one unit.
It's easy to enforce this in my application but I was hoping to somehow
put in some kind of constraint at the database level to stop people
setting up charts with an axis that allows income, weight, and time to
all be plotted on the same axis.
Perhaps someone has stored a chart configuration in a database before
and can offer me some insight.
Many thanks,
Giraffe.
|
| | 8 answers | Add comment |
|
| Limit UPDATE statements to affect only one row Bernard 03:21:01 |
| | How can I limit UPDATE statements to affect only one row on a table and
if the update attempts to update more than one row an exception should
be raised. So to make it cleared it should not affect only the first
row in a multi row update, the where clause should restrict the update
to update only one row. I have no control over the SQL submitted and
therefore can not alter the SQL statement before it is submitted.
Example:SQL> --This should workSQL> Update emp set sal=0 where empno=7396;
1 row updatedSQL> commit;
--This should NOT workSQL> Update emp set sal=0; ORA-20001: You tried to update more than one row, please refrain from
his.
I tried to use the SQL%ROWCOUNT in a statement level update trigger one
the SAL column but seems like the attribute does truly one apply to
implicit cursors managed by PL/SQL
Thanks in advance
Bernard van Niekerk
|
| | 21 answer | Add comment |
|
| Desperately need an SQL query for the following problem Dave 00:13:49 |
| |
I don't write a lot of SQL queries but have been able to do most of the
work with what little I know. This one, however, has me stumpped.
Here's my problem:
I have records in a table that contain a start date and an end date.
I need to select records from this table that fall within a date range.
The date range could cover a single,multiple or no rows depending on
the
range being queried. Example:
My Table:
Row StartDate EndDate
1 1-Jan-2001 15-Jan-2001
2 16-Jan-2001 2-Feb-2001
3 3-Feb-2001 15-Feb-2001
4 16-Feb-2001 5-Mar-2001
5 6-Mar-2001 15-Mar-2001
Here are some possible queries:
Query StartDate EndDate ExpectedRow(s)
1 1-Jan-2001 22-Jan-2001 1,2
2 17-Jan-2001 25-Jan-2001 2
3 17-Jan-2001 8-Mar-2001 2,3,4,5
4 17-Mar-2001 1-Apr-2001 5
5 1-Apr-2001 20-Apr-2001 nothing
I've been trying a lot of different selects but nothing is working.
Can anyone help?
Dave.
|
| | 5 answers | Add comment |
Saturday, 12 August 2006
|
| "create or replace" for dbms_job Dn.Perl@Gmail.Com 23:27:55 |
| |
My aa.sql file contains code on the lines of ...
--------------------
create or replace procedure proc_aa as
begin -- {
statements
end -- }
/
VARIABLE v_JobNum NUMBER
BEGIN
DBMS_JOB.SUBMIT v_JobNum, 'proc_aa ;', trunc(sysdate) + 6/24,
'trunc(sysdate + (1/(24)),''HH'')');
END;
/
PRINT v_JobNum
COMMIT;
--------------------
I run : @aa.sql and start a dbms_job .
Then I modify the procedure a bit, and run @aa.sql again.
This starts one more dbms_job which runs the procedure
on the hour every hour. Is there any simple way of running
a 'create or replace dmbs_job' like construct, or do I have
to do it the slightly harder way of finding out the job number
first for the old version of the procedure, and then deleting it.
Query # 2 : Correct me if I am wrong but the dbms_job has
an air of 'DDL' about it; why should it be necessary to commit
the creation or deletion of a dbms_job?
Query # 3 : Which book (preferably O'Reilly's) or online
documentation is the best
way of finding out what parameters dbms_job.submit takes
or the meaning of the fields which make up 'dba_jobs' view
and suchlike matters?
Thanks in advance.
|
| | 2 answer | Add comment |
|
| Database Load or Usage calculation Guest 23:15:09 |
| |
I want to get some opinions on what would be a good way to calculate or
maybe ballpark estimate the LOAD or CRITICALITY or USAGE etc of an
Oracle database.
This is more from the point of view of an SLA.
So lets say we signup for supporting a database with x amount of
resources and then subsequently the number of USERS increase or
maybe that remains the same but their USAGE increases ie they submit
more SQL QUERIES to the database. Or maybe the users share connections
using a SHARED / WEB / APPLICATION account and just the number of
SESSIONS keeps increasing or such or maybe its just the DATA VOLUME
or DISK SPACE but that is also not the only factor if the data volume
stays same due to archival but many APPS are sharing the database or
such ...
What might be a good way to say that - OK last year X was the total
usage of the database and this year Y is the total usage and so the
support cost increases by Y-X coefficient.....
Thoughts ??
|
| | 4 answer | Add comment |
|
| Amount of tablespaces in 10g Guest 20:30:53 |
| | How big is the biggest Oracle 10g database?
a)
One tablespace is limited to 128 terabyte of data blocks (in Oracle
10g), both for smallfile and bigfile tablespaces. Is there a limitation
for the amount of tablespaces?
b)
At which size I have to consider performance issues?
Thanx in advance for your answers.
NitelyJoy
|
| | 6 answers | Add comment |
|
| SQL script to add Oracle userВґs responsibilities Guest 18:33:57 |
| | IВґm looking for a SQL script to add Oracle userВґs responsibilities,
bypassing Oracle forms.
Is there any way ?
IВґm using:
Oracle Applications : 11.5.4
Oracle Forms Version : 6.0.8.25.2
Application Object Library : 11.5.0
Thanks.
|
| | 4 answer | Add comment |
|
| Oracle on Windows Uri Dimant 17:49:22 |
| | Hello
Oracle 10g on Windows XP SP2 Pro
I have just intalled Oracle 10g and as I understood it created a database
ORCL by default
My question is how can I get the table's structure, make a query within
Oracle for Windows Assistance ( like a Enterprise Manager in SQL Server)
I mean I have connected to the database ,however could not see the
tables/views.
Another question is , I created a new database by using DAC and it created
three services on my computer , so if I have 1000 databases , will I see
1000*3 =3000 services? How can I delete/remove the new created database?
Thanks
|
| | 30 answers | Add comment |
|
| Need assistance for tuning Giridhar 15:06:50 |
| | we have a query which takes 31 seconds when called through JDBC.
The same sql statement, when executed from sqlplus takes only 1 second.
When we took help of our dba to find out why it takes 31 seconds in
production,
our dba found that one table is undergoing full table scan when called
from JDBC.
This was also found from V$SQL_PLAN.
However, explain plan shows index access and we get the output in just
1 second.
How can we find out what is the reason for this discrepancy?
Thanks.
|
| | 2 answer | Add comment |
|
| Export to PDF Matthias Hoys 13:46:33 |
| | Hi,
Are there any built-in packages in Oracle 10g Release 2 to export (generate)
data as pdf ? Or is my only option to load Java classes into the db (for
example iText) ?
Thanks
Matthias
|
| | 9 answers | Add comment |
|
| Search Replace String function Anil G 08:12:37 |
| | Does oracle provides search replace string functionality?
e.g. lets say i have data into column as '(A + B + C) - (D + E)'
I would like to have following : (
NVL(SUM(DECODE(b.lookup_code,'A',a.q_qty,0)),0) +
NVL(SUM(DECODE(b.lookup_code,'B',a.q_qty,0)),0) +
NVL(SUM(DECODE(b.lookup_code,'C',a.q_qty,0)),0)
) -
(
NVL(SUM(DECODE(b.lookup_code,'D',a.q_qty,0)),0) +
NVL(SUM(DECODE(b.lookup_code,'E',a.q_qty,0)),0)
)
In this case only thing needs to be done is search replace all values
with
NVL(SUM(DECODE(b.lookup_code,??,a.q_qty,0)),0)
|
| | 8 answers | Add comment |
|
| Outer join query problem Harnek Manj 07:11:02 |
| | Hey Everybody,
I am trying to run the following query on oracle 10g & I endup with an
error
ORA-01799: a column may not be outer-joined to a subquery
select s2.portfolio, parvalue pv, nvl(r.rate,0) rate from std s2
join rates r on s2.fltindex = r.indicator
and r.datefld = (select max(datefld) from rates r2 where r2.datefld <=
'11-August-2006' and r2.indicator = s2.fltindex)
where s2.type in (select type from stdtype where calctype <>
'LineOfCredit')
and nvl(s2.rate,0) = 0
and s2.datemature > '11-August-2006'
and s2.datesettle <='11-August-2006'
and upper(s2.status) = 'DONE'
but this same query works fine on Oracle 9i.
Is there any body who can tell me that what's problem can be. The issue
is that I can change the query but it should be compatible to SQL
Server also.
Thanks
Harnek
|
| | 4 answer | Add comment |
|
| Disk space usage issue related to Oracle 10g RAC with OCFS2 and ASM Kphanik38 04:12:31 |
| | I've an issue related to Oracle 10g RAC.
SETUP:
I've 2 node cluster each being Dell 2850 Server with RHEL 4.0
I've EMC CX300 SAN storage with following partitions
/orasoft 10 Gb OCFS2 File system
/oracrs 2 Gb OCFS2 File system
/orabackup 100 Gb OCFS2 File system
The datafiles are on ASM which is not directly visible in OS.
I've common Oracle Home installed in /orasoft/db_1 which is shared by
both nodes in cluster.
ISSUE:
I've faced an issue recently related to EMC storage.
The /orasoft partition displays 1.4 Gb space available using df
command.
With both nodes sharing the common Oracle Home (/orasoft/db_1), when
ever I try to touch a file I get an error as No Space left on device.
I'm unable to start any service with the same reason.
Is this setup correct ??
Can anyone help me with this storage issue ??
|
| | 3 answer | Add comment |
|
| ora_600 Souri Bozorgmehri 04:05:38 |
| | I have a big table in RAC 10g. After having hardware problem and
fixing it, when I want to populate this table, I get ora_600 error. I
have opened a ticket with Oracle Technet and they asked me to create
another table and see if I can populate that table. I did, but I did
not get any error message from the new table. It does not looks like
that I have block corruption either. I am stuck and don't know what
the problem can be. Any suggestion???
|
| | 2 answer | Add comment |
|
| Oracle database not starting up after reboot on RedHat ES4 Linux ! Shahid Bhatti 03:59:06 |
| | Hi,
I have Oracle database enterprise edition 9.2.0.4 running on RedHat
Linux ES release 4. Although the installation went fine and after the
installation I was able to see the common processes like ora_pmon
started in system, but when I reboot system the database does not
comeup.
I am using dbstart command via a /etc/init.d/oracle file. This is
linked to /etc/rc5.d and /etc/rc3.d as well in the Linux server.
The amazing thing is that tnslistener is started automatically after
system reboot but not database. That is, when I check the status of
that via the command "lsnrctl status" it shows me the one database
instance that I am hoping to see correctly. But no ora_pmon etc
processes are there.
Even interesting thing is that when I manually run the command
"/etc/init.d/oracle start" by hand, it does start the database
perfectly fine. This is the same command that I am running via the
startup scripts in /etc/rc5.d and othe runlevels.
Please help me finding out why is the database not starting up
automatically? I have checked the
/u02/app/oracle/product/9.2.0/startup.log which just says reports that
Listener has started but after that it does not show anything about
database starting up.
Below I am pasting the chunk of /etc/init.d/oracle script that is
supposed to start the listener and database. Please have a look at it
and tell me what can I be doing wrong in this case?
#################################################
ORACLE=oracle1
export ORACLE_HOME PATH
#
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
#
case $1 in
'start')
echo "$0: starting up" >> $LOG
date >> $LOG
# Start Oracle Net
if [ -f $ORACLE_HOME/bin/tnslsnr ] ; then
echo "starting Oracle Net listener"
su - oracle1 -c "$ORACLE_HOME/bin/lsnrctl start" >> $LOG 2>&1 &
fi
echo "starting Oracle databases"
su - oracle1 -c "$ORACLE_HOME/bin/dbstart" >> $LOG 2>&1
;;
########################################################
Thanks in advance for your urgent help.
--Smb
|
| | 5 answers | Add comment |
|
| recovery aproach ... Guest 03:48:49 |
| | Hello,
Sorry about starting new thread but this question is about another
aproach.
- we have the latest datafiles about 1 hour long
- only problematic tablespace/datafile is, let`s say system01.dbf
Question is:
Is there any possibility to create new database and then replacing good
datafiles and then do some "magic"?
Is there any kind of "magic" step?
Sorry, I know it looks crazy but trying everythink.
Thanks for answers
Robert
|
| | 6 answers | Add comment |
|
|
