SQL & PL/SQL :: Implementing Unpivot In Oracle 11g
Sep 27, 2011
implementing UNPIVOT in..I am using Oracle 11g Release 11.2.0.1.0
OS: windows
Table Structure
CREATE TABLE "EMPLOYEE_ROLE"
("EMPLOYEE_ID" NUMBER(10,0),
"MANAGER_1" VARCHAR2(10 BYTE),
"MANAGER_2" VARCHAR2(10 BYTE),
"MANAGER_3" VARCHAR2(10 BYTE),
"MANAGER_4" VARCHAR2(10 BYTE),
[code]....
EMPLOYEE_ID MANAGER_1 MANAGER_2 MANAGER_3 MANAGER_4 MANAGER_5 ROLE_1 ROLE_2 ROLE_3 ROLE_4 ROLE_5 1345 John Mike Ram Kumar null DBA Sql Dev PLSQL Admin null
1 rows selected
I want a output like this
EMPLOYEE_ID MANAGER ROLE1
1345 John DBA
1345 Mike SQL DEV
1345 Ram PLSQL
1345 Kumar Admin
*I have tried with UNPIVOT *
SELECT
EMPLOYEE_ID
,MANAGER
FROM
EMPLOYEE_ROLE
UNPIVOT( MANAGER
FOR col1
in (MANAGER_1,
MANAGER_2,
MANAGER_3,
MANAGER_4,
MANAGER_5
))
i am able to get manager and role in two different queries
EMPLOYEE_ID MANAGER
1345 John
1345 Mike
1345 Ram
1345 Kumar
4 rows selected
SELECT
EMPLOYEE_ID
,ROLE1
FROM
EMPLOYEE_ROLE
UNPIVOT( ROLE1
FOR col1
in (ROLE_1,
ROLE_2,
ROLE_3,
ROLE_4,
ROLE_5
))
[code]....
Is it possible to add two unpivots in a single UNPIVOT query.
View 5 Replies
ADVERTISEMENT
Sep 26, 2013
I have question regarding data masking. Currently we are using Oracle 11g database , we are thinking of implementing data-masking feature in 11g. Is this can be done by adding any package to it.
View 4 Replies
View Related
Nov 23, 2010
Questions about implementing ARCHIVELOG mode in RAC environment.
Oracle 11gR1 RAC + ASM database. I'd like to implement ARCHIVELOG mode. Each node will have its own archive log.
Does it make sense to place archivelogs on ASM (shared storage)? Or the best practice would be to have some aside "BACKUP" server to place and keep archivelogs from all the RAC nodes there?
Additional question is about archive logs space required estimation. If it's correct to know estimate archive logs space required to get the daily avg number of switch logs
CODEselect round(avg(n_of_switch_logs), 3) avg_num_of_switch_logs
from (select trunc(first_time), count(*) n_of_switch_logs
from gv$log_history
group by trunc(first_time))
and multiply it on size of single redolog?
View 2 Replies
View Related
Dec 11, 2012
Cant find any artice on what is better to use unpivot or union all. I can achive same results using either but cant decide what would be better. For example a table has columns:
Name 1, Phone1, Name 2, Phone 2. The result I want would be:
Name 1, Phone 1
Name 2, Phone 2.
View 5 Replies
View Related
Oct 16, 2012
I want to be able to generate unpivot for 1 row of data dynamically.what i am trying to do is based on this linkI just need to be able to pass the table name in format : SCHEMA_NAME.TABLE_NAME and the query should unpivot the data for 1 row. (Will always be 1 row, just need to transpose the column names and the values for 1 row of data)
I made the below query so far. But I am getting an error and can't figure out a way to fix it.pass any table name from your database in the define step
define TAB_NAME='SCOTT.EMPLOYEE'
WITH sel_col AS
(
SELECT DECODE (data_type,
[code]...
ERROR at line 33:
ORA-00904: : invalid identifier
Its the last line of the code. The same seems to work in the select part but not in the IN part.
P.S: I cannot do PL/SQL - as i simply dont have access.
View 9 Replies
View Related
Mar 20, 2013
I am not sure if we can do this as i tried many solution for pivoting/unpivot My data looks like from table when i do Select * from table_name
col1 col2 col3 col4 col5 col6 col7 col8
20130320_0938 C11 1416 98 93 30 30 32
20130320_0938 C22 26 92 73 50 50 65
20130320_0938 C33 86 95 81 63 63 76
I want the data to look like
[code]...
View 5 Replies
View Related
May 15, 2013
explain the difference in the VALUE column when DESCRIPTION = 'dept' when the query is run with and without the order by clause.
SELECT * FROM
(
SELECT * FROM emp
--ORDER BY job
)UNPIVOT (VALUE FOR DESCRIPTION IN (sal AS 'salary', deptno AS 'dept'));
View 12 Replies
View Related
Nov 15, 2012
am running an oracle 11gR2 Database.
I have a table of the structure..
CREATE TABLE "RP_RESOLUTION_MASTER"
( "RM_ID" NUMBER,
"SR_ID" NUMBER,
"REQUEST_STATUS" VARCHAR2(200 BYTE),
[code]....
But I get an error
ORA-01790: expression must have same datatype as corresponding expression
01790. 00000 - "expression must have same datatype as corresponding expression" *Cause:
*Action: Error at Line: 3 Column: 51
View 4 Replies
View Related
Aug 28, 2012
We are planning to use an Application Express application for intranet and internet users. We want to implement a DMZ configuration for this application.
Is there any good guide to implement it?, we want to use Oracle Http Server to access Oracle Application Express.
View 2 Replies
View Related
May 8, 2012
we are implementing case insensitive seaarch in our small 10g database...We would be modifying the following parameters.
nls_comp=LINGUISTIC ;
nls_sort=binary_ci;
We want to use the existing indexes and as I understand we will need indexes created in following manner
create index <index_name> on <table_name>(nlssort( <column_name>, 'NLS_SORT=BINARY_CI'));
1) Is there any mechanism with which we can convert existing index to above type? (without drop and recreate)One of the issue I can see is related to unique values as 'ABC', 'Abc' and 'abc' would be treated same
2) Apart from that is there any issue, added maintenance for such index?
View 1 Replies
View Related
Dec 11, 2012
How to configure Oracle EM with newly created Oracle Instance on Oracle 10g DB,which is Single Instance DB but not RAC ,when I start the Oracle EM it is starting the default DB which created during Oracle Server Installation.
View 0 Replies
View Related
May 17, 2011
But i do a lot of work-arounds to make sure the installation does not make corrupt my OracleAgent,OracleService and OracleDataGatherer. If i just let the installation do its thing i have problems with my libraries, and can't start anything.
The errors are : Procedure entry point 'BlaBla' could not be located in the Dynamic Link library 'AnyName'.
I notice that i have 2 versions of the libraries in OracleHomeBIN (one version 8, one version 11). The programs that start the OracleAgent,Service and DataGatherer call the old libraries but expect to find values that can be found only in new libraries.
View 1 Replies
View Related
Jun 13, 2012
I have a requirement to put together a Oracle SQL template to create re-usable DDL/DML Scripts for Oracle databases.Only the Oracle DBA will be running the scripts so permissions is not an issue.
The workflow for any DDL is as follows:-
1) New Table
a. Check if the table exists from the system/admin views.
b. If table exists then give message "Table Exists"
c. If table does not exist then execute DDL code
2) Add Column
a. Check if Column exists for a given table from system/admin views
b. If column exists in the specified table,
b1. backup table.
b2. alter table to make changes to the column
b3. verify data or execute dml script convert from backup to the new change.
c. If Column does not exist
c1. backup table
c2. alter table to add column
c3. execute dml to populate column with default value.
The DML scripts are for populating base tables with data required for business operations.
3) Add new row
a. check if row exists by comparing old values of each column with new values to be added for the new record.
b. If exists, give message row exists
c. If not exists, add new record.
4) Update existing record (We have createtime columns in these tables so changes can be tracked)
a. check if row exists using primary key.
b. If exists,
b1. deactivate the record using the "active" column of the table
b2. Add new record with the changes required.
c. If does not exist, add new record with the changes required.
View 17 Replies
View Related
Aug 8, 2012
We would be moving oracle 11g unix sun solaries to oracle 11g Linux readhat OS. what would be the disadvantage and what are the item needs to be verified. Basically advantage of oracle 11g Linux readhat OS.
View 2 Replies
View Related
Jun 27, 2013
I would like to update an XML element without using the function APPENDCHILDXML or INSERTCHILDXML because they are not available in Oracle 10GR1.
In my database, Oracle XDB is not installed.
The following query fail with the following error : ORA-00904: "INSERTCHILDXML" : identificateur non valide
update scl_profile
set profile_data =
insertChildXML(profile_data,'/exportImportMarcheCriteria','colonnesExport',
XMLType('<colonnesExport>ENTETE_GESTIONNAIRES_AUTORISES</colonnesExport>'))
where profile_xmltype =
'fr.mipih.marches.marche.criteres.ExportImportMarcheCriteria'
and profile_type =
'eMagh2.MRGS.AccesMarche.ListeMarche.Export.OptionsExportImport';
[code]........
If i try to use the package DBMS_XMLDOM, i have the following error :
ORA-06550: Ligne 3, colonne 11 :
PLS-00201: l'identificateur 'DBMS_XMLDOM.DOMDOCUMENT' doit etre declare
ORA-06550: Ligne 3, colonne 11 :
PL/SQL: Item ignored
I think it's because ORACLE XDB component is not installed in my database.
View 1 Replies
View Related
Aug 7, 2012
This time i am downloading the Oracle 11g and then the Oracle APEX...(they are not in one package...right ? i mean they both have to download separately)i really new in oracle / PL/SQL. I'm not a programmer but i interest about database programming.For now i still use Ms. Access to develop an aplication and i really suprised i found this Oracle APEX because it an RAD tool too same as Access but more powerfull i think.
View 2 Replies
View Related
Mar 7, 2007
I am trying to connect to Oracle from another server that does not have any Oracle applications on it.
I tried Perl, PHP, Java but each require parts of Oracle installed.
View 2 Replies
View Related
Jul 8, 2011
I am working on an application which is on Oracle forms and Reports 6i.It connects to a database which is Oracle 9i. My client wants to upgrade the Database to Oracle 10gR2 or Oracle 11g. I searched in many forums and also saw in Interoperability table which says it is not compatible. But some users are using it fine.
View 1 Replies
View Related
Feb 4, 2012
Can I directly upgrade Oracle 9.2.0.1.0 From RHEL-4 platform to Oracle 11g Rel 2 on RHEL -5 platform?I have the export dump and cold backup of Oracle 9.2.0.1.0 database.
View 11 Replies
View Related
Dec 3, 2012
I am getting "ORA-03114: not connected to ORACLE" while installing "Oracle Database 11GR2" on "Oracle RAC 11R2" cluster setup. I am getting this error at the time of "Oracle Database Configuration Assistant" configuration reaches till 39% on HP-UX 11.31 IA64 platform.
View 25 Replies
View Related
Jul 12, 2013
I have problems to log in to Oracle?What name should I use for log in?
View 1 Replies
View Related
May 29, 2008
I want to manage database (create table ,insert data ,delete table etc ) by using tool in oracle 10 g similar to a OEM in oracle 9 i in order to manage the database simply and not using SQL command
I used to open OEM in browser but I don't know to manage my database method by using OEM . In a screen I find many function for example Host CPU Active session etc but I can't find function are about create database create table etc . And this is my question
How I did open OEM in order to manage database (create table ,insert data ,delete table etc )?
View 1 Replies
View Related
Jun 20, 2013
Is there anyway to know rowid in oracle10g when i know block number?
View 7 Replies
View Related
Jan 29, 2013
, does oracle 11g XE 64 bit with database and client for Windows 8pro is available?
View 2 Replies
View Related
Nov 23, 2006
While installing oracle 9i on windows XP. Oracle database configuration agent fails.
And it throws following error message.
java.lang.NoClassDefFoundError: oracle/sysman/emSDK/common/nls/MessageBundle
at oracle.sysman.assistants.dbca.backend.InitParamAttributes.loadAllInitParams(Compiled Code)
at oracle.sysman.assistants.dbca.backend.InitParamAttributes.initialize(InitParamAttributes.java:313)
at oracle.sysman.assistants.dbca.backend.InitParamAttributes.<init>(InitParamAttributes.java:255)
[code]........
View 3 Replies
View Related
May 9, 2011
this is just a generic question. If a table has multiple Indexes on it say a table of 20 columns where 8 columns have indexes on it (each columns out of the 8 has an index on it and out of which three columns contain unique indexes on it).
If i run a query on this table where only two columns are specified in the where clause are the remaining 6 indexes on the table scanned during query execution.
View 1 Replies
View Related
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
View Related
Jul 3, 2010
I am trying to upgrade database from 9.2.0.7 to 10.2.0 in my test server. Here are the steps i am planning to do.
1. Export user data of production, Import in test db(ABC) using Destroy=Y Fromuser,touser parameter( this step just to refresh old test data)
2. Install 10g db, Use DBUA Utility of 10g to start upgrade>select 9i database which i want to upgrade
3. follow DBUA instruction ..create datafile paths, init.ora file, tnsnames,listener... same as the existing setup i have of 9i(so i can make replica of 9i in 10g)
question: I have another database(xyz) in test server its also in 9i, I don't want to upgrade that for now. so do i need to use one listener /tnasnames file of 10g and include setting of 9i in that 10g file ?
How to manage init,tnasnames,listener files setting in 2 db versions . Is there anything else i need to do to perform upgrade 9i-10g ? or its all ?
I will install just 10g database will import full export of 9i db. if i can do full import in 10g or should i just go for fromuser, tuser option to import? i believe for that i need to create datafiles, user,grant in new 10g db. I am not sure which option is best to perform 10g upgrade?
View 2 Replies
View Related
Apr 19, 2012
I've been told by management this weekend I will be required to perform a complete patch of our Oracle 11g database.
Problem is I really have never installed / patched Oracle. I'm able to access pretty much any database here and interact with it from a SQL stand point so since I'm the only person in my entire office who knows SQL, they decided I can become their full blow DBA.
Currently the database is running a unpatched version:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Once I download the latest patch (11.2.0.2.0) files from Oracle & perform a complete backup using RMAN:
RMAN> backup full as compressed backupset database plus archivelog;
How exactly does one properly patch / update Oracle 11g to the latest version?
View 6 Replies
View Related
Apr 5, 2010
What is the best GUI application i can used to access oracle database, run SQL query and etc in window 7 ?.
I have installed toad for oracle, but i do not know how to configure it and connect to oracle 10g.
View 2 Replies
View Related
