Unable To Use Count / Comparison Operations Within A Case?

Sep 25, 2008

When attempting to create the following table I recieve the error

count(m.mission_id) < 4

ERROR at line 9:
ORA-00905: missing keyword.

assist in order to resolve this error.

CREATE TABLE AM_agents
AS
(
SELECT
a.first_name || ' ' || a.last_name fullname,
'agent' person_type,

[code]...

View 4 Replies


ADVERTISEMENT

PL/SQL :: Unable To Run Spatial Operations Through DBLinks?

Aug 16, 2012

I am trying to run spatial operations through dblinks. See the example query below

select a.OGC_GEOMETRY.sdo_gtype from <table>@dblink a where sdo_nn(a.OGC_GEOMETRY,mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(0,0,null),null,null),'sdo_num_res=1')='TRUE'.

Query fails with the following error

ORA-13249:
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 9
*13249. 00000 - "%s"*
**Cause: An internal error was encountered in the extensible spatial index*
component. The text of the message is obtained from some
other server component.
**Action: Contact Oracle Support Services with the exact error text.*

Same Query runs fine in the original database but fails with dblinks. Is it possible to run spatial operations through dblink?

View 4 Replies View Related

PL/SQL :: How To Implement Count Case Based On 2 Criteria

Feb 14, 2013

i am looking to write a query that will count all NOT NULL queue values in a table that belongs to a certain group, but not sure how to write it. The following is what I have wrote, but just wanted to clarify how to implement count case based on 2 criterias.

select *
count(case when queue is not null then 1 end) over(partition by group) as queue_orders
from table_a;

For example if I have 10 jobs that belong to group 1, but I would like to count all group 1 jobs that have a queue value.

I have no data at the mo, just something that I am trying to start off.

View 10 Replies View Related

PL/SQL :: How To Use CASE And COUNT Statements In SELECT QUERY

Oct 13, 2012

I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,

SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN
0||#SDC||#PERIOD||#BATCH_ID
WHEN #SDC <10 AND #PERIOD <10 THEN
0||#SDC||0||#PERIOD||#BATCH_ID
WHEN #SDC >=10 AND #PERIOD <10 THEN
#SDC||0||#PERIOD||#BATCH_ID
ELSE
#SDC||#PERIOD||#BATCH_ID
END

View 11 Replies View Related

PL/SQL :: ORA-06592 / CASE Not Found While Executing CASE Statement

Oct 16, 2013

My DB version is

BANNER                                                       
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production                         
CORE 10.2.0.1.0 Production                                       
TNS for Linux: Version 10.2.0.1.0 - Production                 
NLSRTL Version 10.2.0.1.0 - Production      

I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592 

CASE UPPER(IC_TRANSACTION_NAME)
WHEN 'ALERT' THEN
SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A
WHERE A.ALERT_ID = IN_PARENT_NODE_ID;
INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,

[code]....

View 23 Replies View Related

SQL & PL/SQL :: DML Operations Within Function

Dec 16, 2011

I am creating an stored function which has to do some inserts in the meanwhile, and return after all the work has done, an UDT (2 or 3 columns of NUMBER datatype).

With this scenario I have an problem. The DML operations are not supported by and "SELECT * FROM Table(MyProc(args))". I have to use this "SELECT * FROM Table(MyProc(args))" because I need to pass the stored function results directly to an dataset.

Using a Stored Procedure it gives no errors, but the arguments must be passed like OUT params, and it is not what I want.

My question is:
Is there any other way to get a result (UDT) of an Stored Function (that makes Inserts) into a DataSet?

View 12 Replies View Related

SQL & PL/SQL :: Way To Know Last Committed DML Operations Over A Table?

Feb 11, 2011

Is there any way to know Last Commited DML Operations Over a table?

View 1 Replies View Related

SQL & PL/SQL :: Set Operations With Nested Tables?

Aug 20, 2010

In the example below I believe I have created a Nested Table of PL/SQL type and have tried various references to get the SET operation to work, line containing MEMBER OF. Taking the example below from the oracle documentation I have two questions.

1) As I understand it I should be able to use SET operations on Nested tables of PL/SQL types, (I am not using the CREATE OR REPLACE DDL statement prior to the DECLARE block.).
Is this correct?
2) I am assuming that I have to reference the record, can I reference by its type / row instance or can I only retrieve the record like a Cursor Fetch solution, (which would defeat the purpose.).

Is this a SQL to PL/SQL <> PL/SQL to SQL problem?

download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm

Example 5-24 Comparing Nested Tables with Set Operator
SET serveroutput ON
DECLARE
answer BOOLEAN;

[code]...

View 1 Replies View Related

PL/SQL :: Updating Column Value If Any DML Operations Done

Apr 12, 2013

I created a table with a column "id" and values for this column is attached a sequence. And now i need, if any value deleted from the table the column "id" will need to be sequence.

ex:

id name
-- -------
1 xxxx
2 yyyy
3 zzzzz
4 pppp
5 rrrrrr

if i delete

delete from test where id=4;

then automatically.. "id" column values will again in sequence... like this

id name
-- -------1 xxxxx
2 yyyyy
3 zzzzzz
4 rrrrrr

note: in the above if i delete the id=4 from the table again it will have be in sequence and if i inserted the again it has to take the next value continue to sequence....

ex : insert to test values(seq_name.nextval,'tttt');

id name
-- -------
1 xxxxx
.
.
.
4 rrrrr
5 ttttt

View 5 Replies View Related

Performance Tuning :: Looking For More Than Last 16 Operations For Session

May 25, 2010

Doing some data conversion at the moment and using V$SESSION_LONGOPS to predict when the current task will be finished so I can run the next one.

V$SESSION_LONGOPS seems to have only the last 16 long operations for the session. Older operations are automatically purged. My bigger tables have 32 partitions, so after the first 16 are processed, I cannot tell which partition I am up to.

Googling "old longops" and "longops history" didn't work, nor did the same searches on this site. The Oracle Reference manual section on V$SESSION_LONGOPS did not mention that older entries are purged.

View 3 Replies View Related

PL/SQL :: ORA-03127 - No New Operations Allowed Until Active Ends?

May 15, 2013

I'm a software developer, not an Oracle DBA

Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.

View 7 Replies View Related

Compare Non Numeric Data Using Arithmetic Operations?

Feb 22, 2013

Is there anyway to compare the non numeric data in a table to a numeric number.Want to run a query like

SELECT rank_id
FROM   mas_rank
WHERE  rank_code > 26 Rank_code contains numeric as well as some non numeric data

View 8 Replies View Related

SQL & PL/SQL :: Arithmetic Operations On Tabular Data Having Null Values

Sep 14, 2011

I am fetching data in the following set

NameWeight_LWWeight_TWChange
A56 56 0
B34 NULL -34
CNULL 77 77

Here Change=(Weight_TW -Weight_LW)

I should get values as 0, -34 and 77. But I don't get this as all operations with null gives null. These are fetched data and don't exist in the form shown. I have to use these row values of "Change" further. Is there any way of obtaining these results?

View 6 Replies View Related

Comparison Rows In Two Different Database

Oct 13, 2011

Currently I am working on conversion project. We want to verify the both applications updated database values are same or not.

if you run same transaction in both application values are updated in the database. I want compare both database tables, its updated same values in table or any mismatch in the table, is there any tools available right now to compare the two rows values in same tables.

View 2 Replies View Related

SQL & PL/SQL :: Time Comparison Alone Like Less / Greater Than?

Dec 15, 2010

Select
to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a1,
to_char(to_date('10-02-2006 01:30:00 PM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a2,
Case
when to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') >

[code]...

from the above query i was expecting value '2' but its returning '1'. As I am using TO_CHAR its trying to compare characters. Is there a way, to compare times alone like less than, greater than?

View 3 Replies View Related

SQL & PL/SQL :: Comparison Function And Sql Statement

Apr 3, 2011

When we create sql and some pieces of sql are implemented as oracle function. and we run this sql only once every day. I read article that function after first run located in cache. This part of cache ( with function ), is it really consume one oracle resources? Or it will be erased after while.

View 3 Replies View Related

Performance Tuning :: Parallel Operations Not Executing With Expected Degree

Apr 2, 2012

I am executing a sql statement which is doing FTS in parallel mode The server has 8 cpus and threads_per_cpu is 2

The v$sql shows PX_SERVERS_EXECUTIONS as 8

select PX_SERVERS_EXECUTIONS, sql_text from v$sql where sql_id='0q0nk5117yth2'
8, select /*+ full(a) parallel(a)....

however the px_sessions shows 17 sessions (16 parallel session + 1 parent session (where sid = qcsid) Now in px_sessions, these 16 parallel session are divided in 2 server sets 1 and 2 and values for degree and required degree are 8 and 16 respectively

However, all the time, only 8 sessions which belong to server set = 1, were active though its state was waiting with event "PX Deq Credit: send blkd"

The other session which belong to server set = 2 were never active and always had waint event ='PX Deq: Execution Msg'

what could be the reason that 16 parallel session could not be started though I am the only person using the server, there aren't any batch jobs, dbms_jobs,even archivelogs (not a prod system)?

Note that paralel_max_servers setting is 16

Another issue being the duing start of the query approximately 100-115 blocks were read for the query (checked from longops) however after 60-70% blocks are read the number of blocks read / seconds falls down to 10-20 blocks / second across all parallel sessions.

View 2 Replies View Related

Performance Tuning :: NESTED LOOPS JOIN And Distributed Operations?

Oct 30, 2012

I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).

What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?

I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).

SELECT A.POLICY ,
F.MIN_MEMBER_ID,
MIN(A.EFF_DATE) EFF_DATE,
A.EXP_DATE ,
G.DESCRIPTION PROGRAM_NAME,

[code]...

View 5 Replies View Related

Server Utilities :: Data Migration - Remote Operations Not Permitted

May 10, 2011

I am trying to insert data in one of the tables called as tstcntr_mstr in ibpslive instance by ibpslive user.

My source tables are on ncfiiidv instance.

Query is as follows:

insert into tstcntr_mstr
(select * from tstcntr_mstr@dlink_ncfmdv)

Error that I get is remote operations not permitted on object tables or user-defined type columns.

Table tstcntr_mstr@dlink_ncfmdv contains types.

the migration of the data.

View 20 Replies View Related

Automatic Storage Management :: Re-Balance Operations Run In Serial Or Parallel

Nov 27, 2012

Background: We are migrating a lot of databases from one SAN appliance to another. We are doing this by adding new disks from the new SAN appliance to the existing disk groups, re balancing, removing the old disks from the disk groups, and then re balancing again.

Question: If I execute two ALTER commands with the same power on 2 or more separate disk groups, do both operations start executing right away? Or do they queue up and execute one after another?

I ask because we would like to queue up several re-balances so we don't have DBAs watching status bars all day.

View 7 Replies View Related

SQL & PL/SQL :: Date Comparison - Two Columns In Database

Jul 27, 2010

I have two columns in database

INPUTDATE DDMMYYYY
and
OUTPUTDATE YYYYMMDD

I want to compare both the columns because of format of columns i am getting problem to compare.

View 2 Replies View Related

SQL & PL/SQL :: Pivot Table Comparison Between Months

Jun 28, 2010

SQL> select * from query;

CUSTO SWITCH DATE_X METRIC COUNT_X SEQUENCE_NO FILE
------- ------- --------- ------- ---------- ----------- ----
JCI S1 28-JUN-10 PORT1 10 4 TNB
JCI S1 28-JUN-10 PORT2 4 4 TNB
JCI S1 28-JUN-10 PORT3 8 4 TNB
JCI S2 28-JUN-10 PORT1 1 2 TNB
JCI S2 28-JUN-10 PORT2 5 2 TNB
JCI S3 28-JUN-10 PORT1 6 8 TNB
JCI S5 28-JUN-10 PORT2 2 4 TNB

------->s5 only in the current month-it was not in the previous month data collection
-----------------------------------------------------------------------------
JCI S1 28-MAY-10 PORT1 10 3 TNB
JCI S1 28-MAY-10 PORT3 5 3 TNB
JCI S2 28-MAY-10 PORT1 4 1 TNB
JCI S2 28-MAY-10 PORT2 2 1 TNB
JCI S2 28-MAY-10 PORT3 8 1 TNB
JCI S3 28-MAY-10 PORT2 7 7 TNB
JCI S3 28-MAY-10 PORT3 5 7 TNB
JCI S4 28-MAY-10 PORT1 2 10 TNB
------->s4 in the previous month data

15 rows selected.

1 SELECT A.CUSTO,A.SWITCH,
2 A.PORT1-B.PORT1,
3 A.PORT2-B.PORT2,
4 A.PORT3-B.PORT3
5 FROM (SELECT
6 A.CUSTO

[Code] ......

CUSTO SWITCH A.PORT1-B.PORT1 A.PORT2-B.PORT2 A.PORT3-B.PORT3
------- ------- --------------- --------------- ---------------
JCI S1 0 4 3
JCI S2 -3 3 -8
JCI S3 6 -7 -5

How to include the Switch S4 and S5 in the output. how implemented FULL OUTER JOIN

View 13 Replies View Related

SQL & PL/SQL :: Multilevel Collection Comparison Using Sets?

Sep 1, 2010

I need to populate a table based on the results of comparing sets of data. I decided to do this using MULTISET EXCEPT, but having created the structure, do not know whether it is actually possible, and if so, what syntax to use.

I have created:

CREATE OR REPLACE
TYPE NUMBER_TBL IS TABLE OF NUMBER;
/

CREATE OR REPLACE
TYPE PACKAGE_OPTION_GROUP_OBJ AS OBJECT( ID NUMBER, benefits NUMBER_TBL )
/

CREATE OR REPLACE
TYPE PACKAGE_OPTION_GROUP_TBL AS table of PACKAGE_OPTION_GROUP_OBJ
/

I have populated the following:

selectedTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();

defaultTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();

groupTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();

comparisonTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();

Once populated they contain the following data:
***selectedTable***
GROUP_ID : 121
BENEFITS : 161
---------------------------
GROUP_ID : 123
BENEFITS : 165
BENEFITS : 167
---------------------------

***defaultTable***
GROUP_ID : 121
BENEFITS : 161
---------------------------
GROUP_ID : 122
BENEFITS : 162
BENEFITS : 163
---------------------------

***groupTable***
GROUP_ID : 121
BENEFITS : 161
---------------------------
GROUP_ID : 123
BENEFITS : 165
BENEFITS : 166
---------------------------

I first need to compare the ID field only of selectedTable with defaultTable to find where they differ, something like

comparisonTable := selectedTable MULTISET EXCEPT(ID) defaultTable

I am expecting the result to be something like:
***comparisonTable***
GROUP_ID : 123
BENEFITS : 165
BENEFITS : 167
---------------------------

However, I don't know if this is possible and if so, what the syntax would be.

At a later stage, I will need to compare the benefits between selectedTable and groupTable, where the GroupID's match, which is why I have the tables structured in this way.

View 6 Replies View Related

Client Tools :: SPA Comparison Report

Jul 11, 2013

I ran SPA for SQL workload of around 94 SQLs.In comparison report that is generated by SPA, there is a metrics "Top 94 SQL Sorted by Absolute Value of Change Impact on the Workload".

In this metrics there is a column "Impact on Workload". This column hold a value in percentage.how this is calculated by SPA. What formula is used by SPA to calculate "Impact on Workload".

View 2 Replies View Related

SQL & PL/SQL :: Range Comparison - Low And High Pair

Mar 11, 2011

I am trying to compare the ranges of low pair and high pair,if they are within the range then source_conn_id should remain same,else it should be updated to null,which i had written it in else block.How can I implement the IF block and what to write in that block so that source_conn_id can remain the same.

SQL> CREATE OR REPLACE PROCEDURE fp_complements_src(p_id varchar2,ftr_con_id varchar2)
2 AS
3 BEGIN
4 FOR i IN(SELECT SOURCE_CONN_ID,LOW_PAIR,HIGH_PAIR FROM COMP_TEMP1 WHERE SOURCE_CONN_ID=ftr_con_id)

[Code]....

View 2 Replies View Related

Performance Comparison Between Oracle Sun Sparc 10 And IBM P7

Jul 5, 2012

Provide details about performance comparison between Oracle Sun Sparc 10 and IBM P7 server running oracle 11gR2 for OLTP system.

I know that licensing wise Sun will be much cheaper but i want to know other aspects such as performance, storage, transaction per second. Basically on a nut shell which server to buy for our OLTP system.

View 1 Replies View Related

Performance Comparison TDE Versus Plain Tablespace

Dec 9, 2008

Environment Setup

Oracle Server 11g on HP-UX
Oracle Client on Windows

I am using swingbench tool to generate load on DB and using OLTP like benchmark i am comparing the performance of plain data and encrypted data.

I have created two different database. one for tde and other for plain. I have populated same number of rows in both databases. Then i start running the benchmark and i use SAR to collect disk I/O's, VSAR to CPU usage.

From the sar report it seems that,

Oracle plain has faster transactions, it uses minimum CPU. But when look in tot the Reads/Writes TDE has lower than the plain.

If TDE needs to encrypt the data to store in the disks it should occupy more space than the plain data. Then the I/O should be more in TDE..

Note: Bcz the DB parameters are same, number of rows in the tables are same. File system and its block size are same. I will run the swingbench seperately for both the databases.

I am attaching the excel sheet for sar results. Let me know if you need more information

View 7 Replies View Related

Comparison Between Toad And SQL Developer To Compare Schemas

Apr 17, 2013

My primary objective was to compare objects in schemas in two different databases and find out the differences, Execute DDL's in the database where objects are missing and syn schemas in two different databases.

So I need to compare schemas in databases. Which tool will be user friendly to make a comparison of database objects existing in schemas in two different databases.

I'd like to see if I can get a list of pro and cons between Toad and SQL Developer for comparing schemas pros and cons. How to make a comparison. I have some idea on using TOAD but was not familiar with SQL Developer.

Below is my requirement:-

Connect to Source
Connect to Target
Compare schemas with different object types
Find out differences
Generate DDL's for the missing objects or for the objects in difference report
Run them in missing instace(Source/Target)
Make sure both are in sync.

View 2 Replies View Related

Forms :: Date Comparison In Default_where Clause

May 25, 2011

Could it be that it's impossible to change the date format in the default_where clause?

The table column PROPOSAL_END in the database that I want to compare with, is in Format DD.MM.YYYY.

I tried:

set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char(to_date(sydate,'DD.MM.YYYY')));
set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char([-- A date item with the intial value $$date$$ the output is in Fomat DD.MM.YYYY by default --]));
set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char(to_date([-- A date item with the intial value $$date$$ the output is in Fomat DD.MM.YYYY by default --],'DD.MM.YYYY')));

It all does dot matter. Every time the generated select-statement shows the format DD-MMM-YY. How can I change that?

View 6 Replies View Related

PL/SQL :: Comparison In Trees (development And Production Objects)

Apr 17, 2013

I have a below requirement to compare the development and production objects.if any association_type or association_role are not exists in production then i need to return a message like "the Type Object found in Development,but not Production"

Below is the tree structure

development
ProcessingSite(Association type1)
TreatingSite(role1)
MoodedActivity(role2)
MaterialName(role3)

production
ProcessingSite(Association type1)
TreatingSite(role1)
MaterialName(role2)

Processing Site is an association_type and it is having 3 association_roles. we can observe same association_type in the production, but Mooded Activity(association_role) is not available. in this case we need to return "Type Object found in Development,but not Production".

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved