SQL & PL/SQL :: Query To Transpose Column Names To Row

Mar 9, 2011

Query : select * from scott.dept where deptno=10;

Result
DEPTNO |DNAME |LOCATION
-------|----------|----------
10 |ACCOUNTING|NEW YORK

But i want the above resultset as below,

COL1 COL2
----------------------
DEPTNO | 10
DNAME | ACCOUNTING
LOCATION | NEW YORK

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Query For Columns Without Using Column Names

Jun 5, 2013

I have limited permissions and am unable to create temp tables.So I would like to use a cursor to "create" a table of sorts then access/query it. But this "table"/cursor would have no column names so how do I refer to the columns? Is there a way to refer to a column by column number rather than column name in a query:

select column1 from tablename where column2 = 'abc'?

Is there a way in a query/update/insert to refer to a column by column number rather than column name?

declare
cursor c1 is
select 'abc', '8-Apr-2013', pk_id from EMPLOYEE where pk_id = '153'
UNION
select '1xyz', '4-10-2013', pk_id from EMPLOYEE where pk_id = '154'
c1_val number;

[code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Use Column Names Generated From Dynamic Query

Feb 8, 2013

I have written an SQL which will dynamically generate the Select statement with from and where clause in it. But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

For that i have used a ref cursor to open and insert the table.

In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

find below the sample

TYPE ref_csr IS REF CURSOR;
insert_csr ref_csr;
v_select VARCHAR2 (4000) := NULL;

[Code].....

View 5 Replies View Related

SQL & PL/SQL :: Query Result Into XLS File With Column Names?

Oct 6, 2012

I want to load query result into .xls file with column names by using plsql.

Like this i need to generate 4 files and zip it then send mail with attachement of that zip file.

Can we automate in PLSQL? If it is possible pls share the script.

Ex: select ename,eno,dept,deptno,sal,hiredate from employee;

ENAME ENO DEPT DEPTNO SAL HIREDATE like this we need to store the query result into .xls file.

View 1 Replies View Related

SQL & PL/SQL :: Use Records Returned By Query As Column Names In A Select?

Jul 3, 2011

is it possible to use the records returned by a query as column names in a select query.

select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%')
from v$database;
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

View 3 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

SQL & PL/SQL :: Transpose And Dynamic Column Depending On Result

Nov 4, 2013

I am having a table structure like below.

city amt1 tx_date
-----------------------------------------------------
Blr 10000 20050101
Delhi 25000 20050101
Blr 10000 20050102
Blr 2100 20050103
DELHI ...... 20050104
...... ....... ........

i have to place the data in following manner.

city 20050101 20050102 20050103 20050104 ...........etc
-------------------------------------------------------
Blr 10000 10000 2100
Delhi 25000 0 0

Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.

View 6 Replies View Related

PL/SQL :: Conditional Transpose Of Column Data Into Rows

Jul 26, 2013

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production versionI have following table - 
drop table t2;

create table t2(BATot,Ly_BATot,LLy_BATot,BScTot,Ly_BScTot,LLy_BScTot,BAMSTot,Ly_BAMSTot,LLy_BAMSTot) as select
5000,2000,12600,20000,45600,35000,45000,56000,65000 from dual ;

select null class, batot,ly_batot,lly_batot,bsctot,ly_bsctot,lly_bsctot,bamstot,ly_bamstot,lly_bamstot from t2; Simple DML I am using -
SELECT * FROM T2;

C      BATOT   LY_BATOT  LLY_BATOT     BSCTOT  LY_BSCTOT LLY_BSCTOT    BAMSTOT LY_BAMSTOT LLY_BAMSTO
- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        5000       2000      12600      20000      45600      35000      45000      56000       65000

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: Query To Transpose Quest_id And Ans_id Columns Into Rows For Matching Records

Nov 16, 2012

create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...

Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like

CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234

View 11 Replies View Related

SQL & PL/SQL :: Column Values To Column Names

May 20, 2011

I had a table( T1). Five columns exists in the table. The table data is like below

C1C2C3 C4C5
--- ----- ---- ----- ----
JAN FEB MAR APR MAY
One Two Three Four Five

I need the output like below one

JANFEBMARAPRMAY
---- ----- ----- ---- ----
OneTwoThreeFourFive

View 13 Replies View Related

How To Use Column Names In Array

Dec 2, 2012

I have a tmp table with 6 cols, and data to be inserted is from two other tables. My problem is there the cols of the table are

sid varchar2(10),
cob_dt varchar2(10),
deal_id varchar2(10),
new_val varchar2(10),
old_val varchar2(30),
amend_col v archar2(50),
i have return a proc
create or replace

[Code]...

View 1 Replies View Related

SQL & PL/SQL :: Column Names Are Changed

Jul 21, 2011

when we decribe one table structure, it shows the column name as below:

DUKEDMTS03DB01:SYS@TODS1> desc uet_rep.ECM_MAINTENANCE

Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NOT NULL VARCHAR2(15)
C2 VARCHAR2(254)
C3 NOT NULL NUMBER(15)
C4 VARCHAR2(254)
C5 NOT NULL VARCHAR2(254)
C6 NOT NULL NUMBER(15)
C7 NOT NULL NUMBER(15)
C8 NOT NULL VARCHAR2(254)

[Code]...

But, till yday it was showing the orignal column name..

View 3 Replies View Related

SQL & PL/SQL :: Using VArray For Known Column Names?

May 2, 2013

I am trying to build an array that contains known column names, 63 columns in all.The idea is to search backwards from 63 down to say 10 to find the first non-blank column.

Then using a loop with the known number of lines print from 1 to last non-blank column.The problem is that it only shows the column names and not the values of the columns.So I am getting 63 lines every time, instead of 20, 30, 40 or however how many fields actually have something.

Here is my
declare
to_num number;
field_name varchar2(15);
[code]....

View 9 Replies View Related

SQL & PL/SQL :: Return Column Names In Lowercase

Oct 11, 2011

I wonder if there is any way to return the columns of an select with its letters lowercase?

I have a piece of code that creates an script wich returns an SQL result to be confronted with some templates. My template have the column names in lowercase and because It is case sensitive the Uppercase returned by Oracle,

View 5 Replies View Related

Forms :: Dynamically Getting Column Names

Jul 20, 2010

i have list item populated with many table names from a schema. i have grid in oracle forms 10g and i want to fill the grid with at least four/more columns.I want to fire the list change trigger when each time any one table name is selected.

how can i find the columns names dynamically for filling the grid.

View 6 Replies View Related

SQL & PL/SQL :: Retrieving Column Names And Storing Them

Apr 2, 2010

I'm writing a procedure that takes a table name as a parameter and I would like to print out the column name with the supporting row entry for each row. I know the logic I'd like to use, but how do you query the metadata to return the column names and store them.

View 3 Replies View Related

SQL & PL/SQL :: Cursor To Fetch Column Names

Jul 25, 2013

I have the following piece of

CREATE OR REPLACE PROCEDURE COMP_RECORDS
IS
l_query VARCHAR2 (10000) := '';

CURSOR TBL1
IS
SELECT TABLE_NAME, COLUMN_NAME FROM COLS_TO_COMP WHERE TABLE_NAME='ACE_HIST';
TBL1_REC APP.COLS_TO_COMP%rowtype;

[Code]..

However I am getting an ORA-00923 exception with message as "FROM keyword not found where expected". know if I can/cannot use a cursor to fetch column names for a table?

View 9 Replies View Related

PL/SQL :: How To Use Column Names Generated From Dynamic SQL

Feb 8, 2013

I have a problem with Dynamic SQL.

I have written an SQL which will dynamically generate the Select statement with from and where clause in it.

But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

For that i have used a ref cursor to open and insert the table.

In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

find below the sample code:

TYPE ref_csr IS REF CURSOR;

insert_csr ref_csr;

v_select VARCHAR2 (4000) := NULL;
v_table VARCHAR2 (4000) := NULL;
v_where VARCHAR2 (4000) := NULL;
v_ins_tab VARCHAR2 (4000) := NULL;
v_insert VARCHAR2 (4000) := NULL;
v_ins_query VARCHAR2 (4000) := NULL;

[Code]...

How to fetch the column names here
|| ');';

EXECUTE IMMEDIATE v_ins_query;
END LOOP;

View 8 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.

SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints
2 where TABLE_NAME='DSET_PRODUCT_S';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------------------------------
SCOTT SYS_C10202456 C EMPLOYEE
SCOTT SYS_C234576 C DEPT

View 6 Replies View Related

PL/SQL :: Rename Only Two Names In Column Report_headings1?

Jul 30, 2012

I need to rename only two names in column report_headings1. But in order for me to see all other names I need to use all of them in decode function. Is there an easier way to write this Select Statement.

select days_start, days_to,
decode(report_heading1,'31 Or More days', 'Future', '00 Dispute/Pend', 'Dispute or Pend', '1 to 30 Days', '1 to 30 Days',
'31 to 60 Days', '31 to 60 Days','61 to 90 Days', '61 to 90 Days', '91 Or More Days', '91 Or More Days' ) report_heading1, report_heading2, type
from ar_aging_bucket_lines lines, ar_aging_buckets buckets
where lines.aging_bucket_id = buckets.aging_bucket_id;

View 5 Replies View Related

SQL & PL/SQL :: Dynamic Query For Swapping Names

Mar 25, 2011

writing dynamic sql query for swapping names:

the output shuld be like that

input output

a a
a b b a
a b c c b a
a b c d d c b a

i have writen it in static form by using instr,substr.But i m having difficulty in making it dynamic by using select statement.I have to make it for retrieving data from database.

View 13 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 10.2.0.5.0 - 64bit
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - 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;

COMPLETED THECOUNT
Y 772
N 720

I need a result set to look like this:

COMPLETED,Y,N
THECOUNT,772,720

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

select
(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;

COMPLETED,Y,N
THECOUNT,772,720

View 3 Replies View Related

SQL & PL/SQL :: Inserting Values For All Column Names In Trigger

Aug 10, 2010

I am working on convertion of triggers from SQL SERVER to Oracle. I got them converted using a tool. But not sure how to correct the below trigger which uses '*' in sql server.

Code in SQL SERVER :-

CREATE TRIGGER INSERT_AUDIT ON T_AUDIT
FOR INSERT AS
INSERT INTO T_AUDIT_LOG
SELECT 'INSERT','AFTER', CURRENT_TIMESTAMP, NULL,*
FROM INSERTED

Converted into Oracle:-

CREATE OR REPLACE TRIGGER INSERT_AUDIT
BEFORE INSERT
ON T_AUDIT
FOR EACH ROW
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER', SYSTIMESTAMP, NULL, * );

END;

how can I remove the *. I tried replacing the * with the rest column names prefixing with :NEW.

the last 3 columns when are c_data1,c_data2,c_data3 as :-
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER',SYSTIMESTAMP NULL, :NEW.c_data1,:NEW.c_data2,:NEW.c_data3);
But this gives error:-
PLS-00049: bad bind variable 'NEW.C_DATA1'
PLS-00049: bad bind variable 'NEW.C_DATA2'
PLS-00049: bad bind variable 'NEW.C_DATA3'

how can I convert this code.

View 7 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 :: 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 :: 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

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 :: Rows To Columns With Dynamic Allocation Column Names

Nov 16, 2012

I want the output from two tables with rows to columns and generate dynamic basing on the columns.

For example: Table A (a1) , Table B(a1,b1)

Data: A B
------ ----
a1 a1 b1
--- ---- ----
1 1 x
2 1 y
3 2 a
2 b
2 c

o/p: Columnname col_1 col_2 col_3
-----------------------------------------------
a1 b1_1 b1_2 b1_3
----- ---------------------
1 x y
2 a b c

Columns should be generated based on the second table second column.

View 18 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 2.111.7.20.

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";
[code].......

this is the oracle procedure:

PROCEDURE getValues(
rc IN OUT sys_refcursor
)
IS
BEGIN
OPEN rc FOR
SELECT Name, Value FROM MySchema.MyTable;
END;

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







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