PL/SQL :: Query To Identify Delimiter From Data?

Sep 2, 2012

I am trying to write a code to identify the delimiter in the file ( which is in the form of table(id, raw) in system)then I take this delimiter and pass it as a parameter to SP which perform cleaning of this file(table) and creates another clean table.

The problem I am facing is until now the file was coming with one fixed (TAB) delimiter, but now it has come with different (SPACE), now here I want to develop the code to identify the delimiter place it in a variable an pass this as parameter to cleaning SP.

--here i want to develop code to identify delimiter from hosts_equiv file which has data as below

select * from hosts_equiv where left(raw,1) not in ('#','*') and isnull(raw,'')<>''

Raw                  id
----------------        ---
hiper USER1        1
hiper2 USER1      2 
APX    user2       3

Need to identify delimiter between e.g. hiper USER1  and pass it as a parameter to the raw_parse sp

declare Tab  varchar(10)
set Tab = char(9)
exec raw_Parse
'hosts_equiv',   -- From table ( entire file content is stored as table with Id record no sequence generated)
'CleanedHosts_Equiv', -- To table name, when passed it will clean the from table and places the cleaned data in to_table

[code]....

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Query To Find Count Of Delimiter In Column Val?

Jun 29, 2010

Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.

Table:

CREATE TABLE N_B_S_1
(
NUM NUMBER,
VAL VARCHAR2(20)
)

Data:

INSERT INTO N_B_S_1 VALUES(1,'A,B,C');
INSERT INTO N_B_S_1 VALUES(2,'D');
INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK');
INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV');
INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM');
COMMIT;

Required output:

num, count of delimiters
===== ===========
1 2
2 1
3 4
4 2
5 4

View 5 Replies View Related

SQL & PL/SQL :: Query Identify A Change

Mar 21, 2011

Whenever there is a change in the c_flag, we need to identify the change and do some processing. How do I identify the change from either 1 to 0 or 0 to 1. The change can be either way. The point of importance is to identify the change.

CREATE TABLE tab1 ( id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
Start_Date DATE,
id_num NUMBER(10),
c_flag varchar2(2));
[code]....

View 2 Replies View Related

SQL & PL/SQL :: How To Identify Client Fired Query Still Connected To Database

Dec 21, 2011

I need to get a way to find, The client fired the SQL Query is still connected to the database or not. For e.g.

I fire an SQL query using SQL Developer, Query approximately takes 5 mins to complete. As soon as I fire the query SQL devloper hangs and I close it using task manager.

Is there any way I can identify such session where SQL developer fired the query is terminated but the query fired is still running.

I have to identify such Orphan session and terminate it.

View 3 Replies View Related

Query To Identify Invalid Objects In Database Per Schema

Sep 29, 2012

I need a job to get executed for every 1hour.Like i need a query to identify invalid objects in database per schema ,invalid type and this is a job to run every hour.How to schedule it.I only know that dba_jobs have the info.

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

ORA-01157 - Identify / Lock Data File 255?

Aug 30, 2010

I'm using SAP ECC6.0, Oracle 10G, HPUX B11.23.Recently I had performed database restoration from my backup tape. However, during the process of bringing up the Oracle and SAP database, I observed that there could be some archive logs went missing. I had tried to check them from my backup tape, but could not find it.

In short, now my SAP database is up and running, but I'm having another problem when I executed a "CheckDB" job in DB13. The job is unable to complete successfully

DB13 job log:
30.08.2010 19:44:40 Job started
30.08.2010 19:44:40 Step 001 started (program RSDBAJOB, variant &0000000000061, user ID BASIS)
30.08.2010 19:44:40 Execute logical command BRCONNECT On host drqaecc
30.08.2010 19:44:40 Parameters: -u / -jid CHECK20100830194440 -c -f check

[code]...

View 6 Replies View Related

Server Administration :: ORA-01157 / Cannot Identify / Lock Data File 201

Aug 12, 2013

Our server got crash in server1 and we recovered the same DB to server2 using RMAN back up. But while running our scheduler for email alert we got the error message as ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: 'D:ORADATAKFDBTEMP01.DBF' Our temp file is already in E folder.

View 4 Replies View Related

Server Utilities :: Data Pump Import - How To Identify Dump File Tablespaces

Jul 5, 2012

I'm rying to import schema's from a dump file that came from a different environment.

What I have is:

1. dump file
2. log file of the export

I'm trying to import the file(containing three schemas) with remap_schemas, and it fails, gives a lot of ORA-00959: tablespace 'string' does not exist.

Now, I've read in OTN:

[URL]

that what you need to do in that case is to use the REMAP_TABLESPACE option,to redirect the objects to a different tablespace.

I don't see a name of the tablespace I'm getting the error for in the export log.I don't know if I have more tablespaces I have to redirect with REMAP_TABLESPACE.

I don't want to perform this 3 times, have an error, by that find out what's the next tablespace needing redirection and only then starting over...

How can I know from the dump file and the log file,what is the tablespace names i need for the redirection to my names? Or its just that the tablespace giving me the error is the only one in the dump file?

View 3 Replies View Related

SQL & PL/SQL :: Get Duplicate Values Using Delimiter

Sep 1, 2010

I have table with below values

SELECT * FROM dup_val

1
1
1
1
2
2
2
2
3

The result set should be like

1-1
1-2
1-3
1-4
2-1
2-1
2-2
2-3
3-1

the query get the above result set.

View 10 Replies View Related

Splitting A String Based On Delimiter?

Feb 19, 2008

I was wondering if there is an Oracle function available to split a string based on a delimiter character. For example, if I have a table consisting of:

HOST
-----
emerald.test.com
ruby.test.com
diamond.test.com

I would like to only return ('emerald', 'ruby', 'diamond') by getting all data leading up to the first '.' character.

View 1 Replies View Related

Server Utilities :: SQL Loader Comma Delimiter

Dec 22, 2011

I am using sqlloader for loading the data into database by using csv file.My csv file is delimited by comma in that i am having a column which is having the , and line feeds targeted to load into a long data type.for example as below

descri,dfdfdfd,dfdfdf,
sdfsdf,
dfsdfd,

i want to move this column data into a single table column.But due to because of delimited "," it is splitting into number of columns

View 6 Replies View Related

Reports & Discoverer :: Any Way To Pass Delimiter In Builder

Dec 15, 2010

I need to produce Excel spreadsheet from report builder.currently I am getting a format like,

ProductID;Count;CountUnique;
1001;12;16;
1002;11;6;
1004;16;666;
1006;60;86;

This format when opened in Excel does not group in separate columns resulting in all getting mixed up.I am looking a format like,

ProductID,Count,CountUnique,
1001,12,16,
1002,11,6,
1004,16,666,
1006,60,86,

This opens up clearly on excel. items like Product,Count are shown on separate columns along with the value.I am still looking into delimited formatting.

View 2 Replies View Related

Reports & Discoverer :: TAB Delimiter Report Is Not Functioning

Sep 16, 2006

I am developing a TAB delimited oracle reports(6i).In which I am doing the following

1. One query for both text and excel reports
2. two layouts, one for standard another for excel(I have parameter to select whihc format user want to see)
3. In excel layout I putting one fielde for each row. And assigning each row with a formul;a column
4. In formula column I am sending data for that row with tab delimited as below
return(:Name || chr(9) || :Age || chr(9) || :depat) ;
5. I have registered in Apps
6. When I run the report in apps the output comes with tab space for each column
7. But I save the same file and try to open in excel with delimiter as 'TAB' its not spreading across cell. It is just coming in a single cell

View 4 Replies View Related

SQL & PL/SQL :: Using Regexp_replace To Eliminate Duplicate String With Delimiter

Sep 11, 2013

I'm trying to eliminate duplicate string for more than 1 occurrences along with its delimiters, but couldn't get it working. Here is what I tried.

SQL> column str format a30
SQL> column replaced format a30
SQL> with x as
2 (select 'a#~#b#~#a#~#d' as str from dual union all
3 select 'a#~#b#~#c#~#a' as str from dual union all
4 select 'b#~#a#~#c#~#a' as str from dual)
select str,
regexp_replace(str, '[^a|#~#a]{2,}','',1,2) replaced
from x; 5 6 7

STR REPLACED
------------------------------ ------------------------------
a#~#b#~#a#~#d a#~#b#~#a#~#d
a#~#b#~#c#~#a a#~#b#~#c#~#a
b#~#a#~#c#~#a b#~#a#~#c#~#a

The output I need is
a#~#b#~#d
a#~#b#~#c
b#~#a#~#c

View 12 Replies View Related

Server Utilities :: Flat File Tab Delimiter Import Messed Up With SQL Loader

Jan 4, 2012

I am trying to import data from below content.

Flat File

PARENTCHILDALIAS
PLAN_PCOTDefaultPlanning Customer
1001_BTPCOTDefaultGeneral Planning Customer
2000_BTPCOTDefaultNational Account Planning Customer
3000_BTPCOTDefaultDistributor Planning Customer
3010_BTPCOTDefaultEducation Planning Customer
3020_BTPCOTDefaultResearch Planning Customer
OPT1_PCOTDefaultOption 1 Planning customer
OPT2_PCOTDefaultOption 2 Planning customer
OPT3_PCOTDefaultOption 3 Planning customer

The problem here is , When you try to import to a table which has same columns . I skipped the first line when loading . The issue here is the second field is getting split in to the two columns . for e.g. :- Default goes to Child and Remaining goes to the Alias.

infarct there is a tab at the end of the each line. How to set the Sql loader settings correctly so that I can populated the end column in CHILD column only.!!!!

OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'FlatFile.txt'
BADFILE ''FlatFile.bad'
DISCARDFILE ''FlatFile.dsc'

INTO TABLE "table"
FIELDS TERMINATED BY X'9'
OPTIONALLY ENCLOSED BY "''" TRAILING NULLCOLS
(PARENT,
CHILD,
ALIAS CONSTANT '')

View 5 Replies View Related

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

View 2 Replies View Related

SQL & PL/SQL :: Identify Table Row

Nov 14, 2012

Is it possible to find the schema name,table name and DB name if we know a value in table ?

View 6 Replies View Related

SQL & PL/SQL :: Identify Duplicate Row

Feb 1, 2011

I have table named TEST with NO (NUMBER) and DOJ (DATE) columns with primary key.

Post inserting a millions of records to it, i am inserting a duplicate NO and DATE.

How oracle identify (mechanism used) the latest record as duplicate?.

View 4 Replies View Related

SQL & PL/SQL :: Identify Footprint Of ID On Database

Apr 15, 2010

I am looking for the ways to identify the Footprint of the My ID on the database, as it is used to run very heavy production reports.may include utilization of resources.

View 12 Replies View Related

Identify Cause Of Large UGA Consumption?

Apr 27, 2013

I'm trying to identify what PL/SQL or the exact code that's is causing massive UGA consumption. How can I identify it?

View 5 Replies View Related

Security :: How To Identify Patch No

Apr 12, 2013

Quote:Every patch or patch set also has a patch number to identify it

All patch and patch set releases carry version numbers.

For ex : Oracle 10g Release 10.2.0.1, then an available patch set might be 10.2.0.2 , .... , 10.2.4.0 , 10.2.5.0

My QUES is : How can i identify Patch no ? How it is differ from patchsets no ?..

View 27 Replies View Related

How To Identify And Update The Database Parameters

May 17, 2013

I have to check some parameter values for an OID tuning I do not have sys access, I have only schema user access

Now how do I see the values of SGA_TARGET, db_cache_size etc parameter

I manage to see these var in init.ora file under /dbs folder but there are 2-3 spfile also having containing these attributes, not sure which reflect the actual.

Also can I alter also these value via schema user or do I need SYS access?

View 17 Replies View Related

Identify Attributes For Customer / DVD And Rental Tables

Oct 29, 2008

wish to do a database on dvd rental in oracle following: for the ER diagram i have indetified the entities as : branch , emp, customer,dvd, rental...

1) set the one - many relationship in the above data
2) identify the attributes for customer,dvd and rental tables and thus normalise them as well.

View 2 Replies View Related

Server Administration :: Identify Tnsnames.ora File?

Jun 10, 2011

How to find out the database TNSNAMES.ora file if there are multiple .ora files with different names in Oracle_home/network/admin directory ?

View 6 Replies View Related

SQL & PL/SQL :: How To Identify Which User Has Changed Table Structure

Jun 21, 2012

a table structure is modified every now and then because of which the few packages get uncompiled. is there any way to monitor which user has changed table structure.

View 2 Replies View Related

Server Administration :: ORA-01157 / Cannot Identify Datafile 1

Jun 28, 2010

I have a database of branch A whose files are located in E: Drive. I want to download it in branch B.I placed all the files of Branch A in D: Drive of Branch B. When i start the database i was getting controlfle error. I made required corrections in initorcl.ora. Now when i start the Database,its mounted, but I am getting ORA-01157: Cannot identify datafile 1. I tried to rename the file, but the "alter database rename file1 to file2;" option is not working.

View 3 Replies View Related

SQL & PL/SQL :: How To Identify Specific Sequence Name Applied On A Table

Mar 1, 2011

How to identify the specific sequence name applied on a table.

View 4 Replies View Related

SQL & PL/SQL :: Identify Department VP In Employee Interface Table

Mar 12, 2010

I have employee interface table something like this.

emp_idemp_name Job_title supervisor_name
1AJ Engineer BJ
2CK Analyst ND
3BJ Manager TR
5TR VP IT JD
6ND S Manager MD
7MD VP Telecom SK
8SK VP Eng JR

I want to idenitfy the VP for each employee. The logic I have to apply is check for hte supervisor of each employee to see if the supervisor has designation starting with 'VP'. If no, I have check the supervisor of the supervisor and so on. I tried using a recursive query using connect_by_root but in the above example for employee ND it lists the VP as both MD and SK. I need it to show on MD who is the lower in the hierarchy.

I am a Java person but since my app uses the Oracle DB I am to do this task.

View 13 Replies View Related

Performance Tuning :: Identify Blocking Sessions

Feb 21, 2011

My statspack reports are sometimes showing figures such as this from the Foreground Wait Events section:

Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
PL/SQL lock timer 1,430 100 6,982 4883 0.0 18.0
enq: TM - contention 3 0 6,438 ###### 0.0 16.6
db file sequential read 310,385 0 1,765 6 3.5 4.6
TCP Socket (KGAS) 12,593 13 180 14 0.1 .5
db file scattered read 23,673 0 87 4 0.3 .2
log file sync 19,409 0 82 4 0.2 .2
ksfd: async disk IO 1,680 0 29 17 0.0 .1
[code].....

The TM lock that occurred 3 times appears to be disastrous.Historically, this could be caused by missing indexes on foreign key columns.

How can I be alerted when this event occurs, so that I can do some real-time investigation into the sessions and the SQL that hit it? I suppose I could schedule a job to query v$lock/dba_waiters/dba_blockers every few minutes, but is there a better way? Any standard edition scripts for this?

View 4 Replies View Related







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