Oracle Count Distinct-Values ORA-01762: vopdrv: view query block not in FROM

Greetings,I am experiencing consistent errors using SQL*Plus XQuery processor and the processor internal to the 11g database using XMLQUERY and XMLTABLE using count and distinct-values functions. In other cases where I have encountered bugs in these processors I have mostly been able to work around ...

importing oracle.sql.BLOB through SQL Loader

Currently the system we have creates .sql files and executes them. This takes a long time, so we're attempting to change to using SQL Loader. The one problem I'm having that I can't seem to fix is finding out how to imitate the behavior of this SQL statement through SQL Loader:INSERT INTO KRSB_QRTZ_ ...

Oracle how to select distinct values from a pl/sql table

How to select distinct values froma pl/sql table and insert into another pl/sql table ...

Oracle Need of SQL query in selecting distinct values from two tables

I need a query for selecting distinct values from two tables with one condition.for eg:there are two tables a & b.in table a there are values like age,***,name,empno and in table b valuses are such as age,salary,DOJ,empno.here what i need is with the help of empno as unique field,i need to selec ...

Oracle Insert distinct values with sequences

We have a table t with columns c1 and c2. C1 is primary key created with a sequence. We need to populate this table with distinct values from another table 'master' at the same time have the sequence generate the key with a select statement. insert into t1 (c2) select distinct(m1) from masteris ther ...

Oracle Formatting values on SQL statements

Does anyone know of the possibily of formatting values on a sql Statement? I know that on Sql*Plus, for example, I can use "Column <column name> format <format>" but having in mind that this is not possible on PL/SQL developer or in Oracle SQL Developer, I would like to do the formatting ...

Oracle Notification of XMLType message using PL/SQL callback

Is it possible to receive a notification when an XMLType message is enqueued onto a queue of payload XMLType using a PL/SQL stored procedure in Oracle 10.2.0.2? I cannot get this to work. If I make my queue of payload type Object with one attribute of XMLType, then the notification works fine. I can ...

Oracle SQL Trace and Bind Values

I need to capture bind values in SQL Trace. I am running Oracle 9.2.0.8. I will be running my queries from an application, not SQL Plus. I can use a after log on trigger to turn on trace. Has anyone done this with success? Are there any other ways to do this at the server level? ...

Oracle distinct-values and collections

I need to have the average of some element grouped by an ID, but I don't seem to get it right. The problem probably lies in that the average needs to be taken over an element in a collection of 600 documents, instead of 600 elements in one document.In XQuery I can come up with something like:for $c ...

Oracle Distinct in PL/SQL TABLE

I am getting PL/SQL Table as an input to my program. Now before starting up on any logic in the program I want to find the distinct values in one of the column of PL/SQL Table. How can I do that.Suppose PL/SQL Table is like this+++++++++++++++TYPE XXDUMMYRec IS RECORD(Authorization_Number Varchar2)X ...

Oracle Null values consideration while quering distinct values.

Suppose in any column of a table, we have null values. and we want to query distinct values using following two queries, results leads different conclusions.Query 1. select distinct <Field> from <table>; -- This query considers null value in result set.Query 2: select count(distinct < ...

Oracle Distinct values with group by

I would like to know if there is a way to get the result I want with a query. I'm on Oracle 10g.Here is a simplified example:SELECT A, SUM(DISTINCT B+C) FROM( SELECT 1 AS A, 2 AS B, 3 AS C FROM DUAL UNION ALL SELECT 1 AS A, 2 AS B, 3 AS C FROM DUAL UNION ALL SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL U ...

Oracle SQL Query: How to pass list of values to the IN operator

I'm trying to pass a list of values to the WHERE IN <list>I dont want to use dynamic cursors (REF CURSOR). Is this possible using Oracle SQL?Please let me know.My program:DECLARE...list_of_ids := '10, 20, 30';...SELECT MAX(sal) INTO max_sal FROM employee WHERE emp_id IN (list_of_ids);...END;/ ...

Oracle Replace Repeating rows with blank, and show only distinct values

I have a SQL query which is as follows:SELECT DISTICT Item_id, item_details, category,groupFROM my_itemsWHERE......The results are as follows:Item_id item_details category Group001 keyboard PC-1 Grp1001 keyboard PC-2 Grp1003 monitor PC-1 Grp 1003 monitor PC-2 Grp 2 What i actually want is that to ha ...

Distinct not working in Oracle SQL* Plus

I just created a script to query unique records in the Oracle SQL Developer. The results when run in Developer are unique but when i tried the script in SQL*Plus, it gives duplicate records. I tried the same script in TOAD and it did give unique records. I am using DISTINCT.Has anyone come across th ...

Oracle How to select distinct values from all the columns in a table

I ve got a table of 6 columns. I need to query the distinct values from all the 6 columns. Kindly help me in this regard.Thanks in Advance,With Warm ...

Oracle Did I misuse 'DISTINCT' in this SQL statement?

I have a table TI_ORDER only contains following data: O_ID E_CODE M_SEQ B_SEQ VARCHAR NUMBER NUMBER NUMBER CE013 1 1 1 CE013 1 2 1 CE013 1 3 1 CE013 1 4 1 CE013 1 5 1 CE013 1 6 1 CE013 1 6 2 CE013 1 7 1 CE013 1 8 1 CE013 1 8 2 CE013 1 9 1 CE013 1 10 1 CE013 1 10 2 CE013 2 1 1 CE013 2 2 1 CE013 2 3 1 ...

Oracle distinct values

I have a table xx it contains lot of junk data .How to select distinct values from that table date_wid sr_type Lob Queue20030106 complaint unspec unspec20030107 complaint unspec unspec20030108 fault unspec unspec20030109 complaint unspec unspec20030110 cleared unspec unspec ...

Oracle how to create a table with distinct values ?

I want to create a new table based on an existing table distinct values only, how can I avoid to create the duplicate values ?e.g.table1field1 field20001 ABD0001 ABD0002 DCF0002 DCFnew tablefield1 field20001 ABD0002 DCF ...

Oracle get distinct values from plsql array

I have declared a variable as below in plsql proc.type t_itemid is table of varchar2(10);inserted set of items in to this using a programnow i want distinct values from that array how can i get it. ...

Oracle get distinct values from a coma seperated string variable

I am getting an comma seperated string as in parameter, now i want to get the distinct values from that string and will use those values to pass it to a query.any help ...

Oracle How to pull columns with distinct values only from the two tables?

Does anybody know the trick to pull columns from two table with distinct values only?e.g. if tbl1 and tbl2 have column col1, col2, col3, col4 ..., with some common and distinct values, how can I pull only those columns that has distinct values? ...

Oracle XQuery: Retrieve Distinct Values

The following query is returning me duplicate rows. How can we retrieve the distinct values? Can we use Distinct somewhere in this query? Please help me.SELECT XMLQuery('<Update>{ for $demo in (ora:view("TableA")),$demo_audit in ora:view("TableA_AUDIT")let $demo_id := $demo/ROW/ID/text(),$demo ...

Oracle New version and distinct-values

Does the new version include improvements to speed up the distinct-values function?As a review from ancient history (July), I need to look through thousands of small documents, each with one or more category tags, and generate a list of used categories. This took a very long time to do, and it was d ...

Oracle Query to profile all distinct values in multiple columns

I would like a query that would return all distinct values in several specified columns. NOT distinct COMBINATIONS of the columns. For example, if the table is:A , B , C1 , DOG , BLUE2 , DOG , BROWN1 , CAT , YELLOWThe query returns:A , B , C1 , DOG , BLUE2 , CAT , BROWNnull , null , YELLOWDoes that ...

Oracle Counting distinct values

I have a fairly simple question. I wolud like to get all distinct values from a column and another column which counts all occurancies of the value in the table.Sample data:MitchJohnMichaelMitchMichaelJosephMichaelResult:Michael | 3Mitch | 2John | 1Joseph | 1 ...

Oracle Distinct values in each column

HiI have a table with three columns and I would like to display distinct values in each column. Could any one please help me to get the output. col1 col2 col3==== ==== ====a 1 x a 2 ya 3 za 4 ub 5 vb 6 xb 7 xb 9 xb 10 yb 11 yb 12 yb 13 yb 14 xb 15 yb 16 zb 17 ub 18 vb 19 xb 20 xc 21 xc 22 yc 23 yc 2 ...

Oracle How to select distinct values from collection?

How do I act on each distinct value in an Associative Array?Sub-questions are: how do I reference the array column (distinct(*) is not correct)? and how do I reference values in the loop (y.value?)?DECLARETYPE siteArray IS TABLE OF VARCHAR2(30)INDEX BY BINARY_INTEGER; newSites siteArray;BEGIN/* popu ...

Oracle function to return distinct values from a list

i want your help in a function which can return distinct values that passes to it. in my scenario i want to to select all the distinct items from a list, they are not stored in a table i have to pass them all at onceexample0989.726332, 1234.567432, 3453.736379, 3453.736379, 0989.726332, 3453.736379, ...

Oracle Generate 5 distinct values of x for every distinct value of y

I have a table with two columns.create table test (x number, y number);I need 5 distinct values of y to be created for every distinct value of x . It should look likeselect * from test;   X Y ---------- ---------- 1 733 1 734 1 735 1 736 1 737 2 876 2 877 2 878 2 879 2 880 3 1120 . . . ...

Oracle TO INSERT DISTINCT VALUES..

I AM TRYING TO INSERT DISTINCT VALUES..I AM GETTING AN ERROR OF ORA-00936,I AM USING THE CODE BELOW ..PLEASE CORRECT ME...CREATE TABLE xyz2 ( AA NUMBER(5),BB NUMBER(5),cc CHARACTER(5),DD CHARACTER(5) PRIMARY KEY,EE NUMBER(10), ID NUMBER(5),SLNO VARCHAR2(5)); INSERT INTO xyz2 VALUES (AA,BB,CC,DD,EE)S ...

Oracle need Help-Distinct Values

Please help me for below situation.I need to get a distinct values from a column in a table(table has millions of rows).i tried with select distinct year from t1;and select year from t1 group by year;Both queries are working slowly.Query is taking 20-30 minutes to process. I need to know just distin ...

Oracle unable to update xmltype column through dynamic sql statement

I am encountering an error message while updating a xmltype column using dynamic sql statement. I am using dynamic sql here as the table is not placed in the same schema from where the plsql procedure is invoked. The schema name is passed to the procedure as an argument. I am using below pseudo code ...

Oracle Xquery to return distinct values in xml

I have a requirement, where in i have to get distinct values from an xml file.EX: Input<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Main xmlns="http://www.abc.org.com/FormDetails"><abcd first="1" second="2"/><Fruits><fruit name="banana" color="green"><frui ...

Oracle SQL scipt - errors

I'm not sql expert. I have one subject about databases and oracle sql. I would like to create a database but oracle sql showed me these errors ... this is my sql scripts .../*Created 15.5.2009Modified 1.7.2009ProjectModelCompanyAuthorVersionDatabase Oracle 10g*/Drop table "VYROBCE" CASCADE CONSTRAIN ...

Converting DB2 SQL to Oracle SQL

I am currently converting functions, views, and queries from IBM DB2 SQL to Oracle SQL. I ran into a few problems that I am not sure how to solve:PROBLEM 1:In the following code, the parameters p_er_id, p_entity_id, and p_dsrc_acct_id are not recognized although the syntax for creating the function ...

Error : "com.ibm.db2.jcc.a.be cannot be cast to oracle.sql.BLOB"

I created a store procedure with one input and one output parameter. Output parameter is BLOB.I am calling this procedre by java and inserting the value of that into Oracle table. When i am runing this progarme i am getting the following error."com.ibm.db2.jcc.a.be cannot be cast to oracle.sql.BLOB" ...

Oracle sql dump date formatting options...

Suraj Neupane said: I am dumping SQL for some reports.DB is Oracle 9i and I'm sending date prompt from Cognos to the SQL.The prompt is from the calendar and it sends date as yyyy-mm-dd instead of Oracles dd/mon/yy format.Some field in oracle are string value converted to date such as to_date(oracle_ ...

Problem in loggin to ORACLE SQL*PLUS

I have downloaded Oracle 8.0.6.0.0 from this site and installed it. I have not been able to logon to Oracle SQL*Plus. I have tried to log as scott/tiger and used all possible values for the host string but nothing seems to work. The summary of the host string used and the error messages is:Username: ...

Using 'IN' in the Where Clause & good Oracle SQL query tuning books

We are trying to run a statement like the following:select <list>from table1 a, table2 bwhere a.field1 IN ( <list> ) anda.field2 IN ( <list> ) anda.field3 IN ( <list> ) anda.field4 IN ( <list> ) anda.field5 IN ( <list> ) anda.datefield > <date passed in> ...

Formula field: display joined string of distinct values from column in detail section

I'm wondering if this is possible to achieve in Visual Studio Crystal Reports using a formula field or a SQL expression field.I have an Invoice with Invoice Lines. Invoice Lines is my sub report. I want to concatenate all of the distinct values from a column in the sub report and display them in the ...

Distinct values

I am a developing an application wich reads the values of Sql Server 2000 that come from readings sensors, now the problem is that they want two different reports to the ones that already have, one summarized and another detailed but, I have the problem in the summarized doing the sentence Sql,what ...

Oracle SQL Exceptions - not sure what they mean

I'm getting SQL exceptions when I try to login to my application (the login page shows up fine but then I get Internal Errors from Tomcat when I try to login as a user) - I'm including a stack trace. I'm not sure what the cause of the exceptions is....does it sound like I'm running out of database c ...

SQL PRO AND ORACLE SQL URGENT HELP

Can any one help me out here withe these questions: Pro C Qusetions:1. What are the SQLCA and SQLDA ?2. What is a mutating table ?3. What are indicator variables and how are they used ?4. What are the components of the Oracle VARCHAR type and how should such a structure be loaded into a C Char type ...

oracle sql STRUCT query

all. I have a simple program running in JDeveloper using JDBC for retrieving SDO_Geometry values from various spatial tables. The program works fine but when I run the following SQL statement to retrieve the geometry column from a table states it is returning the sdo_geometry values in the following ...

distinct values from multi tables

all i am new to sql,iam having a requirement where i need to have a distinct value from the result lets say example,contacts tableid name --- -------- 1 john2 jason3 markaddress tableid address conid-- ------ -----1 newlane1 12 newlane2 23 newlane3 34 newlane4 15 newlane5 16 newlane6 2city tableid c ...

Oracle SQL

Oracle SQL : How do we loop inside a PROMPT and ACCEPT. We have to keep on prompting the user until the user enters the correct values. ...

Oracle SQL - how to add two duplicate columns returning only one?

im having heaps of trouble nutting out this sql problem with duplicate rows. I can delete duplicate rows or just select distinct values to avoid them, but here is my problem...I have a table called StockItems(prodID, WhouseID, Qty). the first column is the id of a product. The second is the warehous ...

loader constraints violated when linking oracle/sql/OPAQUE class

Platform: Linux RHAS 3.0RDBMS version: 10.1.0.4What libraries, besides libxdb.so should be present in LD_LIBRARY_PATH for OPAQUE to function?I have a java application built on XDB access ... it works fine as stand alone access, but when accessed through OC4J I get the following error:java.lang.Linka ...

Oracle SQL multiple-choice question

Below is a multiple choice question for preparing candidates for the examIntroduction to Oracle SQL (1z0-007)Evaluate this SELECT statement:SELECT employee_id, nameFROM employeeWHERE employee_id NOT IN ( SELECT employee_id FROM employeeWHERE department_id = 30AND job = 'CLERK'); What would happen if ...