SQL & PL/SQL :: Display Temporary Column Of Two Users

Sep 26, 2011

I have created a query that display the difference in the tables and column of two users. Some of the column in the new user have been added new which were not there in the old user and some columns from the old user still exits but their data_type is changed.

Now I want to select one more column in the select statement specifying whether the column is modified or has been added.

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'ABC' AND
TABLE_NAME NOT IN (SELECT table_name FROM all_tables where owner = 'ABC'
MINUS
SELECT table_name FROM all_tables where owner = 'XYZ' )
MINUS
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'XYZ'
[code]....

View 9 Replies


ADVERTISEMENT

Application Express :: Display List Of All Users On Page / Accessing Or Using Application

Mar 23, 2013

i want to display a list of all users on a page those currently accessing or using the application. How do i accomplish this requirement?

View 1 Replies View Related

PL/SQL :: Add A Column To Display?

Mar 21, 2013

I have a procedure in this i want add a column to display my procedure is

set serveroutput on;
create or replace PROCEDURE IdentifySparesInDSIGNALTables
AS
V_SQL VARCHAR2(1024);
cnt1 number;

[code]...

i wnat to add dlstation in this procedure to display output...

View 1 Replies View Related

How To Display Row Value Of Each Class As Column Value

May 27, 2008

How to display row value of each class as a column value

IDNAMECLASSHOURSSCHOOLNAME
1AllenMaths12school1
2SamMaths12school1
4PamMaths10school1
3SamComp10school1
9SmithComp15school1
7SmithPhy20school1
5PamPhy12school1

[code]....

I wanted output like below for School1

NamemathsCompPhyAccountsBusiness
Allen120000
Sam1210000
Pam10012150
Smith01520120
Adams000030

I wanted output like below for School2

NameCompAccountsBusiness
Anthony2000
Chris102020

I wanted output like below for School3

NamemathsAccounts
Angelo2015
Robert1515

how to write a single query, Also i may add /delete school/class anytime. Query should support mesans everything dynamically should get the data.Even stored procedure will work for me.

View 3 Replies View Related

SQL & PL/SQL :: Get Value From One Column But Display ID Different Fields

Oct 22, 2012

get members's home and work and cell phone number if it's avaiable. if not just disply null.

Table name : Test

member entity_id Phone_id phone_code Phone_num
abc 400175 200201 HME 49.6171.59501
abc 400175 200202 CEL 491.170.9174054
abc 400175 200203 WRK 49.6142.7.76675
def 521985 199991 HME 555-555-5555
ghi 345634 188881 HME 222-345-2345
ghi 345634 188882 CEL 222-456-6565

[Code]...

View 6 Replies View Related

Fetch Multiple Value From A Column And Display It As A Row

Apr 19, 2011

I need to fetch the data from a particular column and display it as a row of data.

For example,

Table

Col1 col2 col3
1 6 10
1 7 11
1 8 12
2 6 18
2 7 null
2 8 19

Data should be diplayed using col 2 as partition as below

col1 col3 col3 col3
1 10 11 12
2 18 null 19

Note: I have also referred to this link [URL]....

View 1 Replies View Related

Oracle - Display Another Column In Table?

Mar 28, 2010

As far as SQL is concerned, I am using oracle 9i and oracle sql *plus.

1.
This is a table 'spj'
SID PID JID QTY
---------- ---------- ---------- ----------
1 3 2 5
1 2 2 12
1 7 1 10
2 5 3 5

[code]...

I wish to count all the qty for each pid.This is what I have:
SQL> select pid,sum(qty) from spj group by pid;
PID SUM(QTY)
---------- ----------
1 5
2 12
3 29
4 10
5 5
6 6
7 10

and it seems to work fine. But I now I want to display another column in the table called 'pname' which is a primary key in another table 'p'. 'spj.pid' is the foreign key.

2. Another query I can't perform needs the table s
SID SNAME STATUS CITY
---------- -------------------- -------------------- -------------------
1 s1 y asansol
2 s2 y durgapur
3 s3 y durgapur
4 s4 y asansol
5 s5 y kolkata
6 s6 y asansol
7 s7 y tarakeshwar

the question says:

Quote: List supplier names(snames) who supply at least all parts supplied by supplier(sname) S2.

How may I achieve it.

View 5 Replies View Related

Display Result Column 10610 In Second Row?

Mar 28, 2012

QUOTE (thiyagusham @ Mar 27 2012, 11:40 PM) SQL> select * from samp;

NAME DOJ QUAL EMPID
---------- --------------- ---------- -----
sam 21-mar-2012 mca
sony 03-jan-2000 mba 10610
maya 21-mar-2012 m.arch
sonna 18-mar-2012 mis

[code]...

i want to display in result column 10610 in second row , because emp sony having id "10610 "should i use sub-query ? without sub-query how can i display ?

View 4 Replies View Related

SQL & PL/SQL :: Display Conditional Column Values

Apr 23, 2013

I'm using the Oracle Emp,Dept tables as my sample. I want to display certain table column values based on some criteria. If met, display those values otherwise display other column values

For example:

So when dept.deptno=10, I want to display these 2 columns values
1. dept.deptno
2. dept.dname

otherwise, display these 2 columns values
1. dept.loc
2. null

Can this be done with one case, decode or "other" type of structure going thru the table one time??

SELECT emp.empno,
emp.ename,
CASE
WHEN dept.deptno = 10 THEN
to_char(dept.deptno)
[code].......

View 6 Replies View Related

SQL & PL/SQL :: Display Output For Null Column

Jul 24, 2012

I am having following table, and below are the out put and desired output.

CREATE TABLE tbl1
(
mon VARCHAR2(10)
, grp VARCHAR2(50)
, visits NUMBER
, redirect VARCHAR(50)
)
;

[Code]....

Query:

SELECT
mon
, grp
, SUM(visits)
FROM
tbl1
WHERE
redirect IS NOT NULL
GROUP BY mon, grp
;

Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13

Desired Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13
May-12 Red 0
May-12 Orange 0

How can this be acheived?

View 1 Replies View Related

To Display Third Column With Full Description

Mar 31, 2009

My Query is :

select id,descr,application from release

the above query fetches records as
1 testing D
2 upload H

i have to display my third column with full description which is stored in another table as select descr from app where code = application; I have to display this descr in the above result set instead of D,H.

View 5 Replies View Related

SQL & PL/SQL :: Display Values In A Column Instead Of Rows

Mar 10, 2011

Consider the following (example)table.,

TABLE_A
------------------------------
ID DEPT CRS
------------------------------
1 CS CS_100
2 SCIENCE SCI_150
3 MATH MATH_400
4 HISTORY HIS_110

[Code]...

To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,

--------------------------------------------
NO DEPT CRS
--------------------------------------------
1 MATH MATH_400, MATH_550, MATH_230
--------------------------------------------

instead of.,
--------------------------
NO DEPT CRS
---------------------------
1 MATH MATH_400
2 MATH MATH_550
3 MATH MATH_230
---------------------------

View 2 Replies View Related

PL/SQL :: Display Top 3 Sales Column Wise

Dec 21, 2012

I have two tables as below

AREA (area_code, area_name)
MONTHLY_SALES (area_code, isbn, book_name, qty, sales_amount)

I want to display the output of top 3 sales columnwise as below.

AREA_NAME  Top 1st (ISBN) Top 1st (BOOK_NAME)  Top 1st (QTY)     Top 2nd (ISBN)     Top 2nd (BOOK_NAME) Top 2nd (QTY)     Top 3rd (ISBN)  Top 3rd (BOOK_NAME)     Top 3rd (QTY)

View 5 Replies View Related

SQL & PL/SQL :: Query On Single Column To Display As Rows

Aug 5, 2010

I has a table of structure of varchar2 datatype.

NO
----------
1-2
3-4
5-6

desired output is:

SQL>1
2
3
4
5
6

The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.

I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.

View 10 Replies View Related

Client Tools :: How To Display The Column Heading

Aug 2, 2010

how to dispaly the column heading.

I tried to do the below.

03-AUG-10> set pages 0
03-AUG-10> set heading on
03-AUG-10> select count(*) numberofrows
2 from iceberg_mig_acnts_stage2 s2, tvp109workorder t109
3 where s2.no_account = t109.no_account

[Code]....

View 9 Replies View Related

PL/SQL :: How To Calculate Values And Display To Virtual Column

Mar 12, 2013

How I can build a query with conditions and calculations? E.g. I've got this table

Start          | End     |     Working Place     |     Mandatory
------------------------------------------------------------------------------------
01-JAN-13 | 11-JAN-13 |     Office           |          1
14-JAN-13 | 25-JAN-13 |     Home Office      |     0
04-MRZ-13| 15-MRZ-13 |     Office           |          0
11-FEB-13 | 22-FEB-13 |     Office           |          1

Now if column working place=Office and column mandatory=0
the new column "price" has to calculate: (End-Start)* $25.00
and if working place=Office and column mandatory=1
the "price" column has to calculate: (End-Start)* $20.60
else $0.00

I tried it with the case statement but I didn't know how to calculate my values and display it to the virtual column "price".

Something like
case
when Working_Place = 'Office' and Mandatory=1
then ...
else '0.00'
end as PRICE
?????

Or is it not possible?

View 5 Replies View Related

PL/SQL :: Only Display A Column Value One Time For A Record / Not A Static Value

Jan 7, 2013

I am trying to only display a column value one time for a record, not a static value. The value changes and there can be many values. I do not think grouping will work since the Date value is dynamic. I am using Oracle for Toad 10.5. There are 4 records with this test data.

select Date, Person, Language, Country
from TableATableA
Date            Person  Language   Country
01/25/2013       James   English
12/20/2012       James   English
US

[code]....

View 3 Replies View Related

Client Tools :: Column ROWID Doesn't Display?

Feb 13, 2012

the table is a view. But the column ROWID doesn't display in Toad. He just ignores that.

View 3 Replies View Related

Forms :: How To Display Table Column Heading On Oracle 6i

Jun 8, 2013

How to display table columns on oracle form?

View 7 Replies View Related

SQL & PL/SQL :: Query To Display Repeated Column Values As Null?

Dec 13, 2012

I have table with the values as below.

C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ

I dont want to print the repeated value(NAME) of C1 multiple times as below.

C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ

I could do it using the below query using union with the rownum.

select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3

Is there any other way of displaying using a single sql query.

View 17 Replies View Related

Forms :: Display Blob Column In A Form (not Image)

Oct 12, 2011

I have a blob column in one table. I need to show it in my form (6i). If i put an image file in the column, it works (because i show it in a Image item type) but if i put a text file in the blob column, it does n´t work.

View 4 Replies View Related

Forms :: Cannot Display Image From Blob Column In Stored Procedure

Oct 27, 2011

I have a table named PRODUCT (ID Number , ProductImage BLOB). I have inserted some records in. I want to create a form for user to view this table.

When I create Data Block, in Data Block Wizard, if I select option 'Table or View' to retrieve data, it's ok (Data type of item ProductImage is Image, when I run form and query, it display image normally).BUT, I want to retrieve data from stored procedure in a package. This is my package

PACKAGE my_pkg IS
TYPE my_rec IS RECORD
(
myID PRODUCT.ID%TYPE,
myImage PRODUCT.ProductImage%TYPE
);

[code].....

I select option 'Stored Procedure' in Data Block Wizard, then select my stored procedure (my_pkg.Do_Query). Next I choose 2 columns from my stored procedure (ID, ProductImage). The wizard still applys data type of ProductImage as Image. I finish Data Block Wizard and Layout Wizard... run form, query and an error message display:

FRM-40364: The data type of item 'ProductImage' does not match the corresponding column in the stored procedure.

I try to change 'myImage PRODUCT.ProductImage%TYPE' -> 'myImage BLOB' in my package but still get that error.

View 4 Replies View Related

Reports & Discoverer :: Change Number Of Column Display According To The Condition

Apr 18, 2012

Recently I am facing a problem while working with Oracle reports 2.5.

My requirement is:
there is a report in which there are 5 columns right now. Now what i want is, whenever a condition will satisfy , a new column should be also display in that report otherwise it must be stay as it is.

Previously : a b c d
Now if a=1 : a b z c d
else
a b c d
where a,b,c,d,z are columns.

View 1 Replies View Related

Application Express :: How To Display Custom Report Column Heading

May 13, 2013

I am having report region with different columns, in that one column heading needs to be start with lower case like "iST Status" but always it is showing "IST Status".

View 5 Replies View Related

Client Tools :: Display Clob Column In Text File In Sqlplus

Mar 30, 2012

I have extracted data from table and write into one text via sqlplus utility in shell scripts. i got correct output. i am having two issues on the output file

1) Outfile file size is huge high compare then table segment data.

2) last column having extra space.

The output column is clob datatype. so i have added set long 50000 and set longchunksize 50000 parameter. after adding these only i got above issues. without two options, i am not getting this isssue but lines are wrapped.

#Set the scripts Path
SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts"
echo $SCRIPTS_PATH

[Code]....

View 2 Replies View Related

Client Tools :: Number Data Type Column Does Not Display As Entered?

Jun 28, 2010

Now i have a problem which i am facing for the first time. The problem is:

I have created a table test1 with two column of number data type and the column witdth is 25. Then enter the data but when i select the recrod it does not display the column data as it was entered.
SQL> create table test1
2 (
3 startno number(25),
4 endno number(25)
5 );

Table created.

SQL> insert into test1

[code]...

View 5 Replies View Related

Forms :: Display Checkbox Selected Records In One Column Of Form / When Push Button Is Pressed

Jul 2, 2011

how to display check box selected records in one column of form when push button is pressed.

View 1 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Temporary Tablespace Is Empty

May 10, 2011

While I was exporting ( schema level ) ,I am getting following errors :

vaughn$ exp parfile=qar808par.txt
Export: Release 11.1.0.7.0 - Production on Tue May 10 10:52:34 2011
Copyright © 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users .

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOXFIRE_TO_TSFDA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOXFIRE_TO_TSFDA
About to export TOXFIRE_TO_TSFDA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully

'Parfile' contents :

owner=TOXFIRE_TO_TSFDA
file=exp_TOXFIRE_TO_TSFDA_QAR808.dmp
consistent=y
direct=y
recordlength=65535
log=exp_TOXFIRE_TO_TSFDA_QAR808.log

What is the reason, and It is saying that temporary tablespace is empty ( temp tablespace have almost free space ) why it is saying like that? And users default temp tablespace is that tablespace only ( database have only on temp tablespace

View 1 Replies View Related

Global Temporary Tables

Jan 17, 2011

I have two database DB1 for EBS database and DB2 for Portal database. DB2 is always up.

DB1 uses some Global Temporary tables to write and store session level information.

I have Secondary database also for DB1. Whenever DB1 is down and its secondary database base is up, my requirement is to enable write operation to these Global Temporary Tables. Since secondary database we open Read-Only mode , I can't write to these GTTs.

DB2 is always up so I want to create the copies of these GTTs in DB2 portal database. Is there any harm on doing this.

Is there any harm storing session level information of DB1 database In DB2 database through DB-Link.

View 1 Replies View Related







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