SQL & PL/SQL :: How To Join A Table Column Names With Data From Other Table

Jul 18, 2012

I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.

Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),

View 6 Replies


SQL & PL/SQL :: Possible To Print Table Data Without Knowing Column Names

Feb 3, 2012

What I am trying to do is print out page that displays all of the column titles and the data under them for a query given by the user. It is then going to be put into an excel spreadsheet.

I've done this before with Java, simply by using the getMetaData function, but I can't seem to find an alternative for PL/SQL. It seems at the very least I need to know the number of columns in a query, but that would defeat the purpose of this.

Is what I am trying to do even possible or is knowing the column names an absolute necessity when printing table data?

View 9 Replies View Related

PL/SQL :: How To Get Constraint Name / Table And Column Names

Sep 12, 2012

I am a junior DBA. I want to check how many columns are under PRIMARY KEY constraint.

I used dba_constraints view. Please find below details.

------------------------------ ------------------------------ - ----------------------------------------

View 6 Replies View Related

SQL & PL/SQL :: How To Pivot Table So That Rows Become Column Names

May 9, 2012

Oracle Database 10g Enterprise Edition Release - 64bit
PL/SQL Release - Production
TNS for Linux: Version - Production
NLSRTL Version - Production

See attachment for table creation and table data.

The table tbl_completed has two columns, id and completed. I have created a query that does provide me with the result set that I need, but I need to pivot the rows into columns. So instead of a result set to look like this:

select completed, count(completed) as theCount from tbl_completed group by completed;

Y 772
N 720

I need a result set to look like this:


The best solution that I have discovered is this, but I figuring their might be a better way.

(select 'THECOUNT' from dual) as COMPLETED,
(select count(completed) from tbl_completed where completed = 'Y') as Y,
(select count(completed) from tbl_completed where completed = 'N') as N
from dual;


View 3 Replies View Related

PL/SQL :: Search Of Table Names By Column Datatype

Apr 28, 2013

I have a requirement that i should list out all the table names which are all using timestamp datatype in a specified schema. Is there any way to find those table names by using any system tables.

View 1 Replies View Related

Fetching Table Names Based On Column Value

Mar 6, 2013

I have a schema in which i have 10000 tables and i want to fetch table names from the schema where org_id is not equal to 1,

Note: Here org_id is the column name .

Provide me sql code or procedure to achieve this.

View 2 Replies View Related

SQL & PL/SQL :: Predefined Function Names As Column Name In Database Table

Apr 9, 2012

Why Oracle allowing the Built-in function names as column name in data base table

Ex :

create table to_char
(to_number number(8) , to_char char(20) , to_date date ,substr varchar2(20))

View 3 Replies View Related

SQL & PL/SQL :: Logic To Find Table Names And Data In Oracle?

Feb 1, 2013

I have a schema DEF and I have a column_name CREATE_DATE.

I wanted to write a procedure which will give me list of tables whose CREATE_DATE data is prior to year 2009.

View 5 Replies View Related

Performance Tuning :: Retrieve Data From View Or Mention Table Names Directly?

Aug 11, 2011

base on performance it is better to retrieve data from view or mention the table names directly?

I have a select statement in from clause one of my table is view (which is having data collected from four tables) my question is whether performance of querry will be improved if i use directly all tables( four tables of a view) instead of a view

View 9 Replies View Related

SQL & PL/SQL :: Join 2 Tables And Get Data In Table 3

Mar 1, 2010

I have 2 tables as shown below. I have to join those tables and get data as in table 3. Condition is I have to get sum of scores for each student where category is 1 and active is Y.

A 10 1
A 10 2
B 10 1
B 20 2
C 10 1
D 20 1
J 30 1



query to show student name and sum of his score where category is 1 and active is Y. I am using Oracle 8i.

View 8 Replies View Related

Join Two Oracle Tables For Fast Fetch Of Data From Table?

Jul 2, 2012

join two oracle tables for fast fetch of data from table.

View 1 Replies View Related

PL/SQL :: Join Between Table With User Based Content And Base Table

Jul 30, 2012

I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).

i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).

Oracle version information
Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE     Production
TNS for IBM/AIX RISC System/6000: Version - Production
NLSRTL Version - Production

I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".

I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.

View 10 Replies View Related

SQL & PL/SQL :: Name Of Table Where All Table Names Stored

Oct 19, 2010

Just wanted to know What is the name of the table where all table names are stored?

View 2 Replies View Related

Windows :: Case Sensitive Column Names In Data Reader

Nov 14, 2010

it seems that oracle data reader on some client machine is case sensitive. We are using oracle 10g. 2 client machines connect to the same DB and run the same code - one works and another doesn't. they are both using odac

this is my .NET code (which works fine on every environment it was installed in the last 2 years):

using (OracleConnection sqlConn = new OracleConnection())
sqlConn.ConnectionString = connectionString;
using (OracleCommand sqlCmd = new OracleCommand())
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "getValues";

this is the oracle procedure:

PROCEDURE getValues(
rc IN OUT sys_refcursor
SELECT Name, Value FROM MySchema.MyTable;

The problem that we saw was that on one of the client machines dr["Name"] threw exception :

Error Source: Oracle.DataAccess.Client.OracleDataReader.GetOrdinal
Error Message: System.IndexOutOfRangeException: Unable to find specified column in result set
at Oracle.DataAccess.Client.OracleDataReader.GetOrdinal(String name)
at Oracle.DataAccess.Client.OracleDataReader.get_Item(String columnName)

We modified the code and used NAME instead of Name and it worked!! (dr["NAME"] instead of dr["Name"])

How can it be? from oracle documentation:
A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.

My question is what can cause such a behavior? Some ODAC/ODP definitions, .NET configuration?

View 4 Replies View Related

SQL & PL/SQL :: Find All Column Names In All 20 Tables That Have Same Names?

Jul 7, 2010

I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.

create table t1 (
col1 varchar2(10),
col2 varchar2(10));
create table t2 (
col1 varchar2(10),
col3 varchar2(10));
create table t3 (
col1 varchar2(10));

View 1 Replies View Related

Retrieving Max / Latest Date From A Table With A Join To One Another Table

Sep 19, 2011

I am having trouble retrieving the Max, latest date, from a table with a join to one another table and other fields from both.I was able to get the MAX service_date grouped by id. But once I tried to add more fields to the query and another table it won't work.

Here is what I have:

selectMAX(cs.service_date), cs.notes, cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd
from cs join wr on cs.applicant_id=wr.applicant_id
where wr.status_cd='AC'
group by cs.applicant_id

View 3 Replies View Related

Inserting Data In New Column Where Table Has Huge Data

May 26, 2013

I am trying to add a new column in a table and insert data from another column of same table.

alter table POSITION add INT_MK_DATA_ID number(10,0) null;

As there are huge number of records in the POSITION table ...its taking for ever to execute this query.

View 1 Replies View Related

Using Column Data As Table Name?

Nov 5, 2012

I have some tables. TABLE1 contains columns called query_id and a column called List_name TABLE2 holds a list of data and the TABLE_NAME = value in TABLE1 held under LIST_NAME TABLE3 contains column with Query_ID and other info.

Is it possible to grab the entry in TABLE1 under LIST_NAME as a TABLE_NAME in a data source?

View 1 Replies View Related

SQL & PL/SQL :: How To Insert All User Names Into A Table

Jan 1, 2012

How to Insert all user names into a table from sql for example when i use select * from all_users command it displays all user names i want that how we transfer these user names into a table.

View 11 Replies View Related

SQL & PL/SQL :: Take Sum Of Data From One Table But Three Different Condition With Three Column

Oct 5, 2010

Now am on a need to take sum of data from one table but three different condition with three column.I give example Lets consider I have a table called Sales in which it has three columns


There are 3 different flag for status A,B & C.I want display data in the following format in five columns.

Dept Status_A Status_B Status_C
1 30 50 20
1 10 60 30

total 40 110 50

But data is stored in the table in rows like
1 30 A
1 50 B
1 20 C like all rows.

For this I wrote union operation and sum.Am
creating a view for this with there columns dept,Sales_A,Sales_B & Sales C for sum of data.My view is

select dept,Sum(sales_amt),0,0 from sales where status='A' group by dept union
select dept,0,sum(sales_amt),0 from sales where status='B' group by dept union
select dept,0,0,sum(sales_amt) from sales where status='C' group by dept

Am assigning 0 for other two status of data because i want the data to be displayed on different columns based on row level condition.The view will return data like

Dept Sales_A Sales_B Sales_C
1 30 0 0
1 10 0 0
1 0 50 0
1 0 60 0
1 0 0 20
1 0 0 30

So finally am using sum function again and selection total sum of value for each status from this view.So by using union operation how can i display data from different condition of data into seperate columns.

View 2 Replies View Related

SQL & PL/SQL :: Query All Table Names Within Userid / Schema?

Feb 7, 2011

Is there a query that will return all the table names that are not populated with any data within a Userid/Schema?

View 2 Replies View Related

SQL & PL/SQL :: Sort Names In Table Without Using Group By Command

Jul 3, 2011

how to sort the names in table without using group by command?.

View 3 Replies View Related

SQL & PL/SQL :: Specific Data From Column Of Details Table

Jul 4, 2012

In details table i am having loc is column in which data is like this(Jones,180),(US,host name),(job, company),(id,0122)

output should be it should take only first value from ().

View 2 Replies View Related

SQL & PL/SQL :: Table With VARCAHAR2 Data Type Column

Jul 28, 2010

I have a table with VARCAHAR2 data type column, I am unable to update that column with the following data.

UPDATE we set sname='(([Emp].Job.Catageory
AND ([Employee].IDtype IN 'ABC'))'

I am getting the error "ORA-00933: SQL command not properly ended"

View 9 Replies View Related

PL/SQL :: Update Column Based On Sum Of Data From Another Table

Apr 17, 2013

We had two tables.

Table 1: matusetrans

ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13


Table 2: Inventory

ITEMNUM STORELOC lastyear currentyear
AM1324 AM1 need sum(quantity) here need sum(quantity)
AM1324 AM2 need sum(quantity) here need sum(quantity)

We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.

we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.

How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table

Edit/Delete Message

View 6 Replies View Related

SQL & PL/SQL :: Move Data From Varray Column To A Table

Mar 28, 2013

I have to move the data from a Varray column to a table.

Lets say I have created a Varray as described below,

create or replace TYPE "BT_TYPE" AS OBJECT (

create or replace TYPE "BT_VR" AS varray(20) of BT_TYPE

And I have a used this Varray as the column datatype in table

Create table BT_MASTER(

And this table contains data.

I want to move the data in the VARRAY column BT_DETAIL to another table. I have create a staging table BT_STG which contains a surrogate key column and the columns from the VARRAY. I am creating this staging table at run time.

Create Table BT_STG(

One way to create this staging table is to query the data dictionary views to get the VARRAY object's columns, datatyeps and create it.

Is there any other simpler way by which I could create a table similar to a VARRAY/Object?

Something similar to,

create table test as select * from BT_VR

View 4 Replies View Related

Security :: Encryption Table Column Data

Jul 19, 2012

I created a test table with 4 columns(id, first_name,last_name, salary-number ) and then alter table to encrypted salary column . everything is OK.

I insert values into test table. However, I still can see salary value by select SQL.

What is wrong?

my db is oracle 11.2.01 in 2008 SP window


View 7 Replies View Related

PL/SQL :: Randomize Data Stored In One Column Of A Table

Jul 12, 2012

I have a problem that I don't know how to solve it.here is it:

I have a table test1(intitule varchar,age number):

------------------------------ ----------
nom1 1
nom2 2
nom3 3
nom4 4
nom5 5
nom6 6
nom7 7
nom8 8
nom9 9
nom10 10

I want to disorder(randomize) column INTITULE without touching to 'age'.

View 28 Replies View Related

SQL & PL/SQL :: Hard Code Bad File Names For External Table

Apr 7, 2011

I have question on the following, that gets defined for the bad file and the log file

BADFILE 'bad_%a_%p.bad'
LOGFILE 'log_%a_%p.log'

What does the %a and % p indicate? Also if I wanted to get the value of %p and %a into a variable how would I do it? I want to be able to append %p and %a to the below variable, but unsure how to achieve it..

l_badfile := file_nm || '.bad' ;

View 3 Replies View Related

Client Tools :: Unable To View Table Names

Jun 28, 2012

i'm working on sql developer in that i'm able to access tables if particular table name is given..

i don't understand is there any grant role problem .

View 11 Replies View Related

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