Query In 10g Oracle

Apr 18, 2008

I am facing problem with a select query in oracle 10g database from vb.net.It was working for oracle 9. The select statement I have written is as follows

Str=" select UCC.table_name, UCC.constraint_name, UCC.column_name, UCC.position, UC.constraint_type " & "from USER_CONS_COLUMNS UCC,USER_CONSTRAINTS UC " & "where (UCC.constraint_name = UC.constraint_name) " & "and UC.constraint_type = 'P' " & "and UCC.table_name = " & " '" & TableName & "'"

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

Jun 19, 2012

I have the following four tables with the following structures Table A

ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE

Table B

ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE

when these two tables were joined like the following:

Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)

Now i have to join the next table C with table B

Table C structure is

ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer

I have the common column such as ColD2 and colB4 to get the Col D3

how do i join the existing query + join between table b and table c?

View 4 Replies View Related

Forms :: F11(Query Mode) And Execute Query In Oracle?

Jul 17, 2011

how to achieve F11(Query mode) and Execute Query in Oracle Forms?

View 1 Replies View Related

Query In SQL Plus (Oracle)

Feb 24, 2009

"Determine what departments are in each of the 4 regions. That is, what are the names of the departments that reside in each of the regions." So basically it wants me to list the departments in each region.

This is what the tables look like that it could possibly be drawn from...

Regions
region_id
region_name

Departments
department_id
department_name
manager_id
location_id

Locations
location_id
street_address
postal_code
city
state_province
country_id

Countries
country_id
country_name
region_id

View 3 Replies View Related

Oracle Query And PHP?

Sep 13, 2010

but I am facing the following problem:In my php code, I do several queries that work, but when I use 2 '%'s in the same 'WHERE' clause string, it doesn't work.

$query = "select smth from A where smthelse='%blabla%'";
$sth_query = OCIParse($connect, $query);
OCIExecute($sth_query);
...

View 4 Replies View Related

Pivot Query In Oracle

Jan 24, 2011

I have a requirement to write a single sql query where i can generate the pivot report. Found some of the examples in Google search. But here we are hard coding the values if it is limited like month in this example.

i want to write similar query to represent the amount based on product type , i have around 200 types of products. I can't write case/ decode statement those many times.

query which will produce the output in pivot format , dynamically depending the number of values.

select Product,
sum(case when Month=�Jan� then Amount else 0 end) Jan,
sum(case when Month=�Feb� then Amount else 0 end) Feb,
sum(case when Month=�Mar� then Amount else 0 end) Mar
from Sales
group by Product

View 3 Replies View Related

SQL & PL/SQL :: Oracle 9i - How To Use Grouping Query

May 31, 2012

Am getting an output like this in oracle9i,

S.No Column1 Column2 Column3 DateCol
1 A B C 10/2001
2 A B C 03/2001
3 B B C 02/2001
4 B B C 01/2001
5 A B C 03/2000

But My real scenario is , i need to populate the output in below structure

S.No Column1 Column2 Column3 DateCol

1 A B C 10/2001
A B C 03/2001
2 B B C 02/2001
B B C 01/2001
3 A B C 03/2000

I dont know how to form the query , to retrieve the structure..

View 2 Replies View Related

SQL & PL/SQL :: Oracle Select Query?

Jul 22, 2010

SELECT DISTINCT PATIENT_ID , (
SELECT ROWNUM,APPOINTMENT_ID FROM
AppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2
AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID

[code]...

MY SELECT query failing actually i am trying to convert sql server select into oracle.My sql server query is like this.

SELECT DISTINCT PATIENT_ID , (
SELECT TOP 1 APPOINTMENT_ID FROM
EMRAppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2
AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID
ORDER BY PATIENT_ID, LAST_UPDATED_DATE ASC) AS FIRST_APPOINTMENT FROM
EMRAppointmentDetailsHistory ADH WHERE ADH.APPOINTMENT_STATUS_ID = 2
AND ADH.VISIT_TYPE_ID NOT IN (7)

View 14 Replies View Related

SQL & PL/SQL :: Union Query For Oracle

Feb 29, 2012

I am new to SQL Oracle programming and have a question on a union query. I am trying to get results (example) for the following:

Org # Full_date Run_date
5 2/20/2012 2/20/2012
5 2/21/2012 2/21/2012
5 2/22/2012 null (there is not a record for this)
6 2/20/2012 2/20/2012
6 2/21/2012 null
6 2/22/2012 2/22/2012
7 2/20/2012 2/20/2012
7 2/21/2012 2/21/2012
7 2/22/2012 2/22/2012

The dw_time table would have the listing of all dates, (Full_date) and the dw_capacity_daily table would have the run_date. Here is my

select * from (
select distinct a.Organization_Nbr, d.full_date
from CMBHS_DW.DW_ORGANIZATION a, cmbhs_DW.DW_Organization_Identifier b, cmbhs_DW.DW_Contract c, cmbhs_dw.dw_time d
where a.ORGANIZATION_NBR = b.ORGANIZATION_NBR
[code]....

View 7 Replies View Related

Pagewise Display In Oracle Query

Aug 14, 2010

the scott.emp table has 14 employee records. I want to display like this in a select query .

ename ename ename
-------- -------- --------
adams abc def
scott xyz pal
smith hij efg
------ -------- -------
page1 page2 page3

and another 5 records
ename ename
-------- --------
asd rty
afg uio
wrt no name
------- ---------
page4 page5

View 1 Replies View Related

SQL & PL/SQL :: Oracle Query For Days Output

Aug 5, 2010

I want to pull out the output only when Day is 1 and 2 and 3. I don't want if either Day is 1 or Day is 2 or Day is 3 is present.

Test Case

--Creat Table
create table dummy_name
(name varchar2(5) not null,
day number(3) not null);

--Insert Values
insert into dummy_name values ('A', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 3);
insert into dummy_name values ('B', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 1);
[code]...

View 8 Replies View Related

SQL & PL/SQL :: Oracle External File And UTF-8 Query

Dec 15, 2010

Encountering an issue with an Oracle external table. We get the following error when we load a particular file in this table:

ORA-12801: error signaled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

We have narrowed this down to a text field in the file that contains the following text (text obfuscated):

XXXXXXXX ł XXXXXXXXXXXXXXXXXXXXXXXXXXXXX

This is nominally 40 characters long, which matches the maximum field size, but it is being rejected because it appears the 'ł' character is causing Oracle to interpret the length as 41 characters instead. If I remove a X then the file loads without issue.

We tried this in a new schema we created where we added the same table and used the same file. There was no problem at all. The oracle database has the following settings:

NLS_CHARACTERSETUTF8
NLS_NCHAR_CHARACTERSETUTF8
NLS_LENGTH_SEMANTICSBYTE
NLS_LANGUAGEAMERICAN

The table is defined as follows:

CREATE TABLE XXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXX_INTERFACE
(
XXXXXXXXXXXXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXXXXX VARCHAR2(40 CHAR),
[code]........

We tried adding the following attributes but they did not seem to make any difference:

CHARACTERSET UTF8
STRING SIZES ARE IN CHARACTERS

View 6 Replies View Related

SQL & PL/SQL :: Oracle - Add Numbered Column Within Query Without OVER

Aug 26, 2010

I am running a query in Oracle that uses multiple joins. I want to return as a part of the select statement a column that contains the row number of the result.

Here is an example of my query;

SELECT *
FROM A_TABLE
INNER JOIN THEADM.A_TABLE_EXTENSION
ON THEADM.A_TABLE_EXTENSION.OBJID = A_TABLE.OBJID
INNER JOIN THEADM.A_TABLE_ENV
ON THEADM.A_TABLE_ENV.A_TABLE_ENV2A_TABLE = A_TABLE.OBJID
INNER JOIN THEADM.A_TABLE_LOG
ON THEADM.A_TABLE_LOG.A_TABLE_LOG2A_TABLE = A_TABLE.OBJID
WHERE CREATION_TIME > '01AUG10'

I want the resulting query (after the final inner join) to contain a column named 'Column' (or similar) that contains the number of the row.

I cannot re-sort the result set - there is no criteria by which to do this. (which is why I want the row number so badly... so that I can sort the result set however I want later and return to the original using this row number) The way that it is returned from the system is the way that I need it so using OVER ORDER BY won't work for me here. I also can only read the database, I cannot INSERT or add any tables.

View 11 Replies View Related

SQL & PL/SQL :: Query Result More Row Return In Oracle

Apr 3, 2013

I have a view base on this query returned more rows returned.

select *
From (
Select c_code ,
from_date,
c_range ,
[code].........

View 5 Replies View Related

SQL & PL/SQL :: Query To Find History In Oracle 11?

Jul 29, 2011

find the recent SQL history from Oracle 11. could the following SQL retrieve all SQL history happened in last 5 mins?

SELECT h.user_id,
s2.username,
h.sample_time,
s1.sql_text
FROM v$active_session_history h,
v$sqlarea s1,

[code]....

View 3 Replies View Related

Any Query Timeout Parameter In Oracle?

Oct 2, 2013

Is there any timeout for sql queries in oracle,  if so what is the timeout parameter.

View 3 Replies View Related

Use Oracle Hint To Use Index In Query Which Uses UNION?

Mar 31, 2011

I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.

Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.

The select statements inside the inline view returns huge number of row (like 50 million rows).

The whole query fails with time out.

How can I optimize this query further?

Is there a way to pass Oracle Hints so that Oracle uses indexes?

View 4 Replies View Related

Query On Formatting Data In A Specific Way In Oracle

Apr 25, 2010

I am currently doing a project where i need to write a stored procedure which will be doing the following-

i)it will retrieve multiple columns from multiple tables in a single database(through join) based on certain conditions
II)then it will store the entire data in a certain field(File_data) of staging table

inside file_data a header and a trailer will be present with the records.also the field values will be pipe separated and a new record will start in a new line.

So,the data inside the file_data of staging table will look like this-

H|v1000
transdate|ordnmb|deposite_amt|order_status....
12-nov-09|123456|23.8|C...
4-dec-07|234567|67.7|R...
..........
7-jan-04|567890|54.7|x.....
T|234(record count)

i did this formatting using java, but my project leader wants me to do the formatting using SP,and wants me to use staging table.

View 7 Replies View Related

Oracle - SQL Query For Number Of Days Since Last Backup

Oct 20, 2011

Is there any Oracle query I can run to determine the "number of days since the last backup"? SQL Server provides this data but I cant seem to find the equivalent for Oracle. Looks like there may be some information like this via RMAN tables and if so I want to create the simplest query possible to obtain that information.

View 3 Replies View Related

Translating Query From SQL Server To Oracle Syntax

Sep 10, 2009

Translate following SQL query from SQL Server syntax to Oracle syntax.

SELECT ID,
[LMT(MTR)] = MAX(case when TYPE = 'LMT' then VALUE end),
[AAD(KGM)] = MAX(case when TYPE = 'AAD' then VALUE end),
[VOL(MTQ)] = MAX(case when TYPE = 'VOL' then VALUE end)
FROM yourtable
GROUP BY ID

View 2 Replies View Related

Oracle SQL - Query A Large Library File?

Dec 1, 2008

I'm currently new to Oracle 10g SQL and I need to query a large library file.I need to find the different first keywords associated with references written by authors who have more than 10 references in the readings table.

View 4 Replies View Related

Oracle 11.2.0.3.0 - Select Query Taking Time

Jan 24, 2013

My oracle database version is 11.2.0.3.0 where i am having one schema in that schema i am having 3 same tables with same structure same data but with different name.

but problem is in first table when i perform select query it takes 5 sec, in another table it is taking 0 sec and in third table it is taking 10 sec.

View 1 Replies View Related

Forms :: Generate Query Result In Oracle 10g

Apr 3, 2013

Is there any way to Create Form in which i give a query and in result it return the query result in detail block in form of fields just like we return in toad or pl/sql developer.

View 3 Replies View Related

SQL & PL/SQL :: Oracle 10.2.0.4 On Solaris 10 - Recursive Query Using Hierarchy

Nov 9, 2011

We are on oracle 10.2.0.4 on solaris 10. My question is on a sql query. Is it possible to rewrite a query to avoid the connect by and prior constructs and use joins? For example i have the following query:

SELECT empno,
ename,
job,
mgr,
hiredate
FROM emp
START WITH empno in (7566,7698)
CONNECT BY PRIOR empno = mgr

How can it be rewritten using a two table join (self join)? I am not sure if it can be done and whether it is possible.

View 4 Replies View Related

SQL & PL/SQL :: Translating Update Query In Access To Oracle

Apr 13, 2011

This is my working query in ms access...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

This doesn't work in oracle, I Googled and read that update doesn't work with inner join in oracle.

View 2 Replies View Related

SQL & PL/SQL :: MS Access Query With Linked Tables To Oracle DB

Mar 2, 2011

The code was originally an MS Access Query with linked tables to the Oracle DB however trying to remove the need for access and have it drop directly into Excel using Recordset.

SELECT
SWPRO.CASE_INFORMATION.CASEDESC,
SWPRO.OUTSTANDING_MAIL.CASENUM,
CASE_DATA_1.FIELD_VALUE,
SWPRO.OUTSTANDING_MAIL.SENTDATE,
SWPRO.OUTSTANDING_MAIL.DEADDATE
FROM

[code]....

View 20 Replies View Related

SQL & PL/SQL :: Oracle Dynamic Query Returning To Out Sys_refcursor

Nov 8, 2011

I'm trying to build a dynamic sql inside an Oracle procedure, and return it as a SYS_REFCURSOR to ADO.NET using ODP.NET in Oracle 11R2. Below is my table:

CREATE TABLE "TFWDB_ENTIDADE"
(
"CD_ENTIDADE" NUMBER(10,0) NOT NULL,
"DC_ENTIDADE" VARCHAR2(100 BYTE) NOT NULL,
"NM_ENTIDADE" VARCHAR2(255 BYTE) NOT NULL,
"CD_TIPO" NUMBER(5,0) NOT NULL,
"ID_STATUS" CHAR(1 BYTE) DEFAULT ('1') NOT NULL,
CONSTRAINT "PKFWDB_ENTIDADE" PRIMARY KEY ("CD_ENTIDADE"),
CONSTRAINT "CHFWDB_ENTIDADE" CHECK (ID_STATUS IN ('0', '1'))
);

Below is the procedure I build to SELECT it:

create or replace procedure spfwdb_sl_entidade_1(
p_cd_entidade in number,
p_dc_entidade in varchar2,
p_nm_entidade in varchar2,
p_cd_tipo in number,
p_id_status in char,
[code]........

I'm calling this code from C#, and I get an empty Result Set. What am I doing wrong? Can I output the dynamic sys_refcursor that I created? Below is my C# code using ODP.NET:

string pConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASERVER)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORASID)));User Id=user;Password=password;";
OracleConnection oConn = new OracleConnection(pConnectionString);
oConn.Open();
string pNomeProc = "spfwdb_sl_entidade_1";
[code]........

View 7 Replies View Related

PL/SQL :: Oracle Query Export Into Excel File

May 29, 2013

How can i automatically export oracle query result into the excel file in oracle?

View 2 Replies View Related

Check Index Used In Oracle Select Query?

Mar 21, 2013

I have partitioned an oracle table and created Local Index for the Partitioned table .

Now i want to make sure that the Local index is being used when i perform select query on the Table partition.

How do i confirm that ? can i check the explain plan generted for the select query to confirm local index is being used ?

View 1 Replies View Related

Oracle 10g - Query Is Very Slow In Jdbc But Not In Other Tools

Jan 29, 2013

I'm using PreparedStatement to execute a query with some parameters. in sql developer, it just spends 12 seconds but in jdbc, it's around 140 seconds.

I disabled "_optim_peek_user_binds" parameter already but it didn't work. alter session set "_optim_peek_user_binds"=false;

View 1 Replies View Related







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