SQL & PL/SQL :: Data Transformation / Remodel Some Data For Processing

Feb 6, 2011

I am trying to remodel some data for processing, as unfortunately the original data structure is unsuitable.

My requirements are to recreate the data as a view which can then be query'd directly by the client, and therefore would like to encapsulate all the required transformation within one SQL query.Here is a sample of the table structure i'm faced with:

SQL>
SQL> l
1 create table insane
2 (
3 dept number not null,
4 firstname varchar2(30),
5 surname varchar2(30),
6 city varchar2(30),
[code]....

So, i'd like to be able to turn this on it's head and come out with data that looks something like this (which, for the purpose of this example, is fabricated manually):

SQL> select * from sane
2 /

DEPT_ID PERSON_ID FIRSTNAME
---------- ---------- ------------------------------
SURNAME CITY
------------------------------ ------------------------------
1 30 Peabo
[code]....

View 11 Replies


ADVERTISEMENT

Quick Data Transformation

Jun 10, 2013

if mytable.col1 is not blank, then copy mytable.col1 to mytable.col2 BUT, it needs reformatting during the copy. Both columns are varchar2 col1 being 15 wide and col2 being 20 wide.

I need to transform from 'PACKAGEabcde001' to 'PACKAGEabcde 00001'

so yes, those 5 extra characters are indeed 3 spaces and 2 extra zero's.

View 1 Replies View Related

PL/SQL :: Data Transformation Query

Jun 10, 2013

newish to Oracle from an and am trying to do the following...  if mytable.col1 is not blank, then copy mytable.col1 to mytable.col2 BUT, it needs reformatting during the copy.  Both columns are varchar2 col1 being 15 wide and col2 being 20 wide.  I need to transform from 'PACKAGEabcde001' to 'PACKAGEabcde 00001'  so yes, those 5 extra characters are indeed 3 spaces and 2 extra zero's. 

View 2 Replies View Related

Data Guard :: Failure In Processing System Parameters

Apr 15, 2013

While configuring Data Guard as I have inserted following parameters in SPFILEPRMDB.ora (PRMDB is Primary DB).

*.db_unique_name='PRMDB'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMDB,STLDB)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:oracleproduct10.2.0flash_recovery_areaPRMDBARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRMDB'
*.LOG_ARCHIVE_DEST_2='SERVICE=STLDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STLDB'

[code]....

when I SQL> shutdown immediate; and SQL> Startup;

Oracle gives me following Error

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:ORACLEPRODUCT10.2.0DB_1DATABASE
INITPRMDB.ORA'

When I Restored old spfileprmdb.ora without these parameters then it Starts Up Normally.

View 1 Replies View Related

Data Guard :: Standby Database - Failure In Processing System Parameters

Dec 6, 2010

I am trying to create standby database on second machine ie. not on my machine. For this i have made added the required parameters in the pfile of the primary database but when I am trying to startup the database using the new pfile I am receiving some errors.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='E:oracleproduct10.2.0db_1databaseinitden1.ora'
LRM-00101: unknown parameter name 'L0G_ARCHIVE_DEST_2'
ORA-01078: failure in processing system parameters
SQL>

Below are the parameters of my pfile:

*.core_dump_dest='E:oracleproduct10.2.0/admin/den/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='den'
*.db_recovery_file_dest='E:oracleproduct10.2.0/flash_recovery_area'

[code]....

View 10 Replies View Related

Export/Import/SQL Loader :: Sequential Data File Record Processing?

Oct 1, 2013

If I use the conventional path will SQL*Loader process a data file sequentially from top to bottom?  I have a file comprised of header and detail records with no value found in the detail records that can be used to relate to the header records.  The only option is to derive a header value via a sequence (nextval) and then populate the detail records with the same value pulled from the same sequence (currval).  But for this to work SQL*Loader must process the file in the exact same sequence that the data has been written to the data file.  I've read through the 11g Oracle® Database Utilities SQL*Loader sections looking for proof that this is what will happen but haven't found this information and I don't want to assume that SQL*Loader will always process the data file records sequentially. 

View 9 Replies View Related

Spatial :: Parameters For 2D Affine Transformation

Jul 13, 2012

I have the following parameters for 2D affine transformation:

Two translation:
tx=2754.3661 and ty = -237.0731
and rotation in radians: -.001016214498072193185827329

Is this specification correct:

SDO_UTIL.TO_WKTGEOMETRY(SDO_UTIL.AffineTransforms (
geometry =>myGeometry,
translation => 'TRUE', tx => 2754.3661, ty => -237.0731, tz => 0.0,
scaling => 'FALSE', psc1 => NULL, sx => 0.0, sy => 0.0, sz => 0.0,
rotation => 'TRUE',
p1 => MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(0, 0, NULL), NULL, NULL), line1 => NULL,
angle => -.001016214498072193185827329,
dir => -1,
shearing => 'FALSE', shxy => 0.0, shyx => 0.0, shxz => 0.0, shzx => 0.0, shyz => 0.0, shzy => 0.0,
reflection => 'FALSE', pref => NULL, lineR => NULL, dirR => -1, planeR => 'FALSE', n => NULL, bigD => NULL
))

BTW, in PostGIS it works correctly: ST_Affine (myGeometry, cos(rotation), sin(rotation), -sin(rotation), cos(rotation), tx, ty));

View 6 Replies View Related

SQL & PL/SQL :: Handling Circular Data In Oracle / Get Disjoint Sets Of Data Connected By 2 Values

Sep 29, 2011

I have this table :

column1 column2
--------- ---------
value1 value2
value1 value3
value2 value4
value3 value7
value7 value1
value8 value9

What I was trying to retrieve is something like that:

Quote:
output_column
---------------
value1, value2, value3, value4, value7
value8, value9

I don´t care about the order of the values in the row. In other words, I want to get disjoint sets of data connected by any of both values.Every pair in the input table is unique.

I have seen in the web that it is possible to do using connect by and hierarchical retrieving but I've been trying to make a lot of combinationts and I can reproduce the output.

View 2 Replies View Related

Any Data Compression Wizards That Automatically Suggest Level At Which Data Should Be Compressed

Jun 29, 2011

whether Oracle has any capability of automatically checking which lossless compression algorithm it should apply by analyzing a data stream on data load? Does Oracle have any compression advisors/wizards that would make recommendations as to type and level of compression?

View 3 Replies View Related

Store Data In CLOD Data Type - How To Create A Unique Index

May 20, 2013

We have been recommended to store data in CLOD data type.

Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.

We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?

View 2 Replies View Related

Data Guard :: Unable To Get Data Of Primary In Standby Database (dataguard)

Jan 16, 2013

i have configured physical standby in my local system, to check logshipping i created a table at primary db, wen i tried to check in standby, it says table does not exist..below are primary & standby alert entries..

Primary alert log

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.98)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STAND)(SERVER=dedicat ed)(CID=(PROGRAM=d:oracle11gappadministratorproduct11.1.0db_1inORACLE.EXE)(HOST=A960M)(USER=SYSTEM))(SERVER=dedicated)))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Remove Data Recently Loaded But Not Disturbing Existing Original Data?

Jun 24, 2010

I have few tables in Oracle 9i/10g , and they already have data in them. I am trying to migrate the data coming from various source systems into these Oracle tables. There is a chance that after loading I might get some unwanted data into these tables.

How do I remove just that data which I have loaded recently, and do not disturb the original data it already has.

Need to backup those tables and reload the data back if there is any problem, but I am looking at a different approach. I just don't want to change the existing system, as lot of users use the system.

View 8 Replies View Related

Server Utilities :: Transferring Changed Data From Database A To B By Data Pump?

Apr 1, 2011

I have database A (Working in Live environment) and Database B copy of Database (Not live) I have Restored whole database (A) RMAN backup file on Database (B) Previous week now i don't want to change anything in any schema and want to import only updated and new records in the table in Database B

There are around 20 schema If for example i have everything in new database B all required database objects like Procedure,functions, packages with indexes in all tables and data in tables, i just want to add new data and updated data.

IF i do following in source database

expdp directory=dpump_dir dumpfile=table_data.dmp content=data_only schemas=ACCMAIN,HRMAIN,..... include=TABLE

AND Import in destination database B, will it add new data and update existing one in table and not touch the table structure and indexes.

View 5 Replies View Related

Data Guard :: Changing Dbname / Sid / Data / Control File Locations?

Nov 5, 2010

I want to change dbname,sid,data/control file locations in operational dataguard setup i plan to follow as below

1)shutdown primary and standby (stop managed recovery)

2)change db name in init.ora of primary and standby change database name control file location

3)create control file for primary from trace(script) make changes for db name and file locations

4)mount and open primary database

5)create standby control file

6) transfer standby control file to standby

7) mount stand by database and start manage recovery

If this steps will error free do i need to follow any thing additional to this or what is other best way for this or its not possible at all

View 1 Replies View Related

SQL & PL/SQL :: Delete Previously Stored Data And Enter Only Current Data In A Table?

Jul 24, 2012

how to insert data in a table by deleting previous entered data and only inserting current data like:

CREATE TABLE test
(
name VARCHAR2(20),
id NUMBER
)
INSERT INTO test VALUES ('aaa',5500);

[code]....

I got two rows. now when I do insert statement I want to delete the previously stored data and only insert the current data like:

INSERT INTO test VALUES ('aaa',8);
INSERT INTO test VALUES ('aaa',9);

it must show aaa,8 and aaa,9 bt not the previous values.

NOTE: we can not do sth like: update set... where id = ... becoz the values are dynamic.

View 4 Replies View Related

ODP.NET :: Cannot Find Oracle Data Source In Entity Data Model Wizard

Dec 19, 2012

I'm trying to add edmx file in my project for first time. I want to choose the oracle provider ODP.net but cannot find Oracle in the data source list. I have oracle 11g installed , odp and odt installed and can access it from the solution as well. I saw the Oracle listed under data source when I tried to connect the solution to the database through server explorer. The solution is connected to Oracle database through ODP.

View 8 Replies View Related

Data Guard :: Buffer Busy Waits On UNDO Data In Active DG

Feb 24, 2013

Oracle Version: 11.1.0.7.0
Active Dataguard

Statspack has been configured for Active Dataguard on Primary database.We got an spike of Buffer busy waits for about 5 min in Active Dataguard, this was causing worse Application SQL's response time during this 5 min window.Below is what i got from statspack report for one hour

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:      18611 21-Feb-13 22:00:02      236       2.2
  End Snap:      18613 21-Feb-13 23:00:02      237       2.1
   Elapsed:               60.00 (mins)
[code]...

Why there could sudden spike of demand on UNDO data in Active Data Guard ?

View 2 Replies View Related

Forms :: First Form Clear Previous Data And Then Populate Data From Table

May 7, 2012

when i press when button pressed trigger, i want first the form will delete all the previous data and then populate the data from the table, that's why i used clear_block first, but this clear_code is not working here. my coding is given below

go_block('show');
clear_block(NO_VALIDATE);
declare
cursor c1 is select *
from qtr_demand order by 1;
begin
[code]..........

View 26 Replies View Related

Server Utilities :: Data Pump For Exporting And Importing Extremely Large Data Files

Sep 24, 2010

I am considering all of the capabilities and benefits of using Data Pump for exporting and importing extremely large data files. Would like to know if importing to tape is possible? If so, would the data be accessible if needed later?

View 4 Replies View Related

Performance Tuning :: Split Data Separated By Comma / Then Create Collection With Data Mapped From Other Columns

Sep 25, 2013

DB Used : Oracle 10g.

A table X : NUM, INST are column names

NUM ----- INST

1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1

-- Based upon requirement :

1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,

Eg:

For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298

3) Save these values into

A table Y : INST, NUM are column names.

INST NUM
23 1234,1298

1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.

2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..

View 4 Replies View Related

Precompilers, OCI & OCCI :: Data Lost After Adding Null Terminator For VARCHAR Data Received From Database

Dec 13, 2010

We are migrating a proc application as described below.

Old Env: UNIX
Old DB: Oracle 8i

New Env: Linux
New DB: Oracle 11g

New modules are successfully compiled in Linux environment. But we are facing issues in writing the output of VARCHAR datatype to a file.

find below the extract of code.
EXEC SQL BEGIN DECLARE SECTION;
varchar mcolmnvarchar[4];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE crs CURSOR FOR
SELECT NVL(colmn,' ') FROM table1

memset(mcolmnvarchar.arr,'�',4); //Was added for only Linux migration. Not present in unix env.

EXEC SQL FETCH c1 INTO :mcolmnvarchar;

cout << "Data at Stage one"<< mcolmnvarchar << endl;
mcolmnvarchar.arr[mcolmnvarchar.len]='�';
cout << "Data at Stage two"<< mcolmnvarchar << endl;
fprintf(fptr,"%-4s",mcolmnvarchar.arr);

Above code works absolutely fine in Unix env with Oracle 8i. But with Linux env & Oracle 11g it is not working. No compilation or run time errors. Data at Stage one prints the output of database properly. But after null terminator code, Data at Stage two statement prints without any value. Value is lost after null terminator code.

View 7 Replies View Related

Transfer Data To SQL Server 2008 / Arabic Data Showing

Jan 15, 2013

I am trying to transfer data from Oracle database table into another table resides in a SQL server 2008. A database link is already set, and data can be selected/queried using

select * from a_table@db_link

the thing is when trying to insert Arabic data from Oracle to a SQL server table

insert into a_table@db_link values(
'N''some data in Arabic'' ,
'11-oct-00',
'some data in English'
);
commit;

its inserted alright, but when we try to display the data it shows like this instead of displaying Arabic, the english data is alright, but also date show as garbage?!!

when trying to insert arabic data directly through SQL server its fine though.I suggested that we transfer data through ODBC to flat files like Acess and then to SQL server but the team rejected it since they're going to do it daily and the data is huge( we are talking more than 28000 records).

View 1 Replies View Related

Oracle Data Pump - Export Data From Schemas Or Tables

Oct 11, 2012

I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?

View 7 Replies View Related

Data Guard :: DATA FILES IN Storage Area Network (SAN)

Mar 29, 2011

I am working in a bank as an system consultant, i have a SAN Storage Area and oracle as below.

SAN 1

This interface includes the DATA FILES of the oracle tablespace

SAN 2

SAN1 Mirrors the DATA FILES of the oracle tablespace to SAN 2

1. Can i rely on real time data recovery from SAN2 ?
2. if SAN1 (Data Files are currupted) will the SAN2 Data Files will be currupted as well.
3. If the SAN2 is currupted then what Oracle Features can be used to have uncurrupted data.

View 5 Replies View Related

Data Guard :: ORA-01157 / Cannot Identify / Lock Data File 1

Jul 23, 2010

i did everything writen but when i do *SQL>alter database recover managed standby database disconnect from session;*

i go and look in the standby database AlertLog file ,and thats whats writen

*ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: غير قادر على فتح الملف
O/S-Error: (OS 3) The system cannot find the path specified.

[code]....

strange thing that it realises the primary database in drive F and it goes to it but i dont understand what could be the reason of this ,although im doing this command while primary database is shutdown!

View 2 Replies View Related

Data Guard :: ORA-01354 / Supplemental Log Data Must Be Added To Run This Command

May 2, 2012

I configure logical standby online .when I execute dbms_logstdby.buid,first

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

it was blcoked by other sesson,then i kill the holding session,but no work.then i cancel this step and execute it again . the error is

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
BEGIN DBMS_LOGSTDBY.BUILD; END;
*
ERROR at line 1:
ORA-01354: Supplemental log data must be added to run this command
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3669
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3755
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 370
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 157

[code]....

View 3 Replies View Related

Forms :: Data Have Been Displayed Normally / Tree Data Is Hidden With No Error

May 10, 2010

i'm use oracle form builder 6i when i use tree item to display data

the data have been displayed normally but at runtime when i resize the window the 'tree data' is hidden with no error.

View 1 Replies View Related

Data Guard :: Data Files Locations On Oracle 11gR2

Jun 15, 2012

I have set up a cross platform (Microsoft Windows IA (32-bit) -> Linux x86 64-bit) data guard and it worked fine.Then I did a switch over (which again worked) and found out the data is not getting replicated at all.. checked the data files available from the new primary database and found out they are in the windows format as below..

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:ORACLEAPPADMINISTRATORORADATAMFSSYSTEM01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSSYSAUX01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSUNDOTBS01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSUSERS01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSRMANRMAN_TS01.DBF

and physically they were created at '/home/app/oracle/product/11.2.0/db_1/dbs/' and as

D:ORACLEAPPADMINISTRATORORADATAMFSREDO02.LOG
D:ORACLEAPPADMINISTRATORORADATAMFSREDO03.LOG
D:ORACLEAPPADMINISTRATORORADATAMFSRMANRMAN_TS01.DBF

View 5 Replies View Related

Data Guard :: Unable To Get Data Of Primary In Standby Database

Jan 16, 2013

I configured dataguard in my local system.

1) scn differs wrt primary in standby (i checked, 1day difference), how to make scn same?

2)i created a table in primary, its not refelecting in standby, (below i ve pasted alertlog entries)

ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:oracle11gappadministratordiag
dbmsstandstand racestand_dbw0_6916.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:ORACLE11GAPPADMINISTRATORORADATASTANDSYSAUX01.DBF'

[code]....

3)wen i try to open standby database in read only mode gives below error..

ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'D:ORACLE11GAPPADMINISTRATORORADATASTANDSYSTEM01.DBF'

View 31 Replies View Related

Forms :: 6i - One Data Block Based On Table / Fetching Data

Nov 3, 2011

I am working on forms 6i. I have one data block based on table EMP.

Datablock-A: Empno, Ename, Sal

I have FIND Screen: Empno, Ename, FIND Button.

I can search the data through FIND Screen and populate data in Datablock A.

I am using below logic to search data through FIND Screen.

When-Button-Pressed(FIND Button): calling 'EXECUTE_QUERY'.

In Pre-query trigger of DatablockA:

copy(:FIND.EMPNO,'BLOCKA.EMPNO');
copy(:FIND.ENAME,'BLOCKA.ENAME');

It's working fine. But below case is failing.

Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.

But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.

why it's failing to pull records if i try to query data in first case only.

View 2 Replies View Related







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