SQL & PL/SQL :: How To Extract CSV File

Jun 14, 2010

I have to extract a csv file running a sql file.

SQL>@d: estEndItem_Vio_Item_Material_Violations.sql;

This works on the sql prompt. I have to do the same using schedular now for which I want to do the same embedded in a procedure.

create or replace procedure test_csv
as
begin
@d: estEndItem_Vio_Item_Material_Violations.sql;
end;

How can I run the sql file in a procedure.

View 4 Replies


ADVERTISEMENT

Creating Extract File From DB?

Jun 22, 2010

I have data in multiple oracle tables. I have to create a extract flat file after applying some validation and business logic on it and store it in unix server with naming convention FF_RMS_SC_<<YYYYMMDDhhmm>>.txt.This job will be scheduled to run daily to create the flat file. I guess pl/sql and unix needs to be used.

View 1 Replies View Related

SQL & PL/SQL :: Creating Extract File From Oracle DB?

Jun 22, 2010

I have data in multiple oracle tables. I have to create a extract flat file after applying some validation and business logic on it and store it in unix server with naming convention FF_RMS_SC_<<YYYYMMDDhhmm>>.txt.This job will be scheduled to run daily to create the flat file.

View 1 Replies View Related

JDeveloper, Java & XML :: Extract Name And ID From File

Jul 23, 2012

Trying to extract name and ID from the below XML File using XMLTable but it is returning no data

XML File is - please check attachment

Query I am using:

select p."uid",p."uname" from warehouses w,
xmltable('/ArrayOfAnyType/anyType'
passing w.warehouse_spec
columns "uname" varchar2(100) PATH '/name',"uid" varchar2(100) path '/id'
)p where warehouse_id = 6;

View 24 Replies View Related

XML DB :: Parse XML File And Extract Data

Jul 22, 2013

I'd like to parse an XML file and get some data extracted as columns. Input file country.xml:

<?xml version="1.0" encoding="UTF-8"?>
<MAS Action="Insert">
<Country ObjectId="100000000000000009" VersionId="8"><Id>1</Id><NlTexts><Name Language="de">Land1</Name><Name Language="en">Country1</Name></NlTexts></Country>

[Code]....

View 2 Replies View Related

Extract Big Table To A Delimited File?

Mar 17, 2013

A big table of size more than 4 GB from 10g DB needed to be extracted/exported into a text file,the column delimiter is "&|" and row delimiter is "$#".I cannot do it from TOAD as it is hanging while extraction of big table.

View 9 Replies View Related

JDeveloper, Java & XML :: How To Extract Data Using XSD File

Feb 9, 2012

How to extract the data from XML using the xsd file. attached files.

Explanation: first check the EmailMessage tage from order_conf.xml compared with Email.xml(<xsd:element name="EmailMessage">) if exists then go to next node.
EmailMessage(exists tag in order xml file)
->next <ns1:emailNotificationype> this tag should be follow under the EmailMessage tag(<xsd:element ref="emailNotificationype">) in Email.xml
->next <ns1:orderNotification> -> check this tag in <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow under <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow these tages <xsd:element name="templateFormatInfo"> <xsd:element ref="templatecode"/>
<xsd:element ref="templateversion"/>

i have to extract the value in between tags.

templatecode -> ORDCONF
templateversion ->1.0

Finally i have to load the data into table.

View 17 Replies View Related

PL/SQL :: Extract Data From CSV File And Push Into Various Tables

Aug 20, 2013

I have a requirement. I will get .csv files . I need to extract the data from .csv file and push  into the various tables.

The illegal data will be rejected and sent back to the sender.I need to schedule this logic every weekend. I am on 10gR2 on Solaris.

View 7 Replies View Related

SQL & PL/SQL :: Extract Data From A Table Using UTL File Utilities

Sep 3, 2010

I have a requirement to extract the data from a table using the UTL file utilities.

My problem is, Say i have a table t1 with column C1,C2, C3, C4, C5. This table t1 gets loaded everyday. i need to pickup the data only that which has changed/inserted in the last load. How can i achieve this ? There is no timestamp in this table.

View 3 Replies View Related

SQL & PL/SQL :: Extract Data From Local Database Table To Text File?

Jun 27, 2013

i want to extract data from my local database table to text file using plsql

View 4 Replies View Related

Server Administration :: Oracle JDBC Classes12.jar File - Unable To Extract

Apr 21, 2010

Upgrading from 10.1.0.2 to 10.1.0.5. Enterprise Manager requires 'newest' version of Oracle JDBC drive.Downloaded what I believe to the correct file (classes12.jar). I'm unclear what to do with this, my readings have pointed me in the following direction:

1) copy to c:oracleproduct10.1.0db_1jre1.4.1in
2) extract

here is the problem...tried:

1) just clicking on it (nothing)

2) c:program filesjavajre1.6.0_03injavaw -jar classes12.jar
Error:
Failed to load Main-Class manfest atrribute from
c:oracleproduct10.1.0db_1jre1.4.1inclasses12.jar

Is my location correct, I've been hunting everywhere..making no progress.

View 4 Replies View Related

Client Tools :: Bash Script - Extract Fields And Make Inserts In SQL File

Aug 13, 2010

I have a Bash script that counts the rows of a csv file, extracts the fields and makes inserts in a sql file. Then it logs into SqlPlus and calls the insert file. The sql file looks like this:

WHENEVER SQLERROR EXIT
INSERT INTO SCHEMA.TABLE1 (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
[code]....

I rely on "WHENEVER SQLERROR EXIT" for things to go the right path. However sometimes because of the contents of the CVS files (which I can't control) some rows don't get inserted but SqlPlus doesn't see that as an error, doesn't exit and I end up with the wrong number of rows being informed in the second insert.Is there some kind of "if-then-else" construct in Sql? After all the inserts are made, do a "select count (*)" and compare that number to the one informed by the script. If they match, make the final insert and commit; else exit.

View 9 Replies View Related

SQL & PL/SQL :: How To Extract Data Between [ ]

Jun 8, 2012

I have a requirement where the data between [] or ][ has to be extracted from a string.

Here is my situation :

INPUT:
[abc] [def]-[ghi][jlk]

OUTPUT:
row_num field_name
1 abc
2 <blank_space>
3 def
4 -
5 ghi
6 null
7 jkl

View 5 Replies View Related

SQL & PL/SQL :: Extract One Schema's DDL

Apr 22, 2011

Is there any option available in DBMS_METADATA.GET_DDL in such a way that I can extract the script (user creation+grants)only for that particular schema?

View 5 Replies View Related

SQL & PL/SQL :: Extract DDL Of DBMS_JOB

Dec 20, 2011

How to extract DDL of DBMS_JOB in sqlplus ?

View 30 Replies View Related

PL/SQL :: Extract Value From Column Name

Nov 14, 2013

I'm stuck on 1 scenario

I have the following table: 

Create tabledrop table age_rate;
CREATE TABLE age_rate(age_0_4 NUMBER(4),age_5_20 NUMBER(4),age_21_34 NUMBER(4),age_35_44 NUMBER(4));
-------------------------------Insertion 
INSERT INTO age_rateSELECT 45, 50,  60,  90
FROM dual UNION ALLSELECT 45, 50, 60,  88
FROM dual UNION ALLSELECT 40, 50,  60,  90 FROM dual UNION ALLSELECT 5,  50, 60,   88
FROM dual ;  
-------------------------------Query on table
SELECT * FROM age_rate; Query Output age_0_4           age_5_20      age_21_34      age_35_44 45                          50                     60                     9045                          50                     60                     8840                          50                     60                     905                            50                     60                     88 Required outputRate     Min_age     Max_age

----The below rate is for age band 0_445   0           445            0           440            0           45              0           4

--The below rate is for age band 5_2050            5           2050            5           2050            5           2050            5           20

--The below rate is for age band 21_3460            21          3460            21          3460            21          3460            21          34

--The below rate is for age band 35_4490            35          4488            35          4490            35          4488            35          44   

Rules--I have all data in rows so each column in row create separate rows and add 2 columns automatically Min_age and Max_age and insert value on these column on basis of column name for example  if column name like age_0_4 then put 0 in min_age and put 4 in max_age means values for Min_age and Max_age extract from the basis of column name. I don't know if it is possible or not

View 6 Replies View Related

PL/SQL :: Query To Extract Changes

May 7, 2013

I am using Oracle database version 11.2.1 and would like to extract the level change and level start date where reason_code is 'PROMO' split by ID.

The test script is below:

create table test(
id number,
start_date date,
reason_code varchar2(10),
level number
)
insert into test values(001, '01-JAN-13', 'PROMO', 2);
[code]....

The expected output would be:

Fields - ID, old_level, old_level_start_date, new_level, new_level_start date

e.g.
001, 2, '08-MAR-13' , 3, '05-MAY-13'
002, 4, '13-APR-13' 5, 02-MAY-13'

View 2 Replies View Related

PL/SQL :: Extract Numbers Only

Sep 18, 2012

Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but mean i have to add . after numbers . as follow

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but this is not right

i want to extract numbers without DOT also.

View 6 Replies View Related

Extract All Employees Who Have 2 Functions

Feb 24, 2013

I have a table:

create table employee_function
(
id_emloyee number,
id_function number
);
with clients and their functions.

I want to extract all employes who has 2 functions (ex:id_function = 1 and id_function=2)

View 3 Replies View Related

Extract Hour From A Date?

Mar 29, 2004

how to extract hour part from a date?

suppose my date is like

29-mar-2004 09:20:34

i wanna get only hour from the above date-

View 3 Replies View Related

How To Extract The Data From DBF Files

Aug 13, 2010

I'm a SAP consultant working in SQL on NT platforms. This is the first conversion from Oracle that I have done. My client has provided us with a "Cold" backup of the Oracle dbase on a HD formatted in Unix, I have the partition mounted and I'm able to view the files. I have the ORDATA folder with all the .DBF files.

Q: How do I extract the data from the .DBF files. I need to export to something workable with SQL.

Original database was on Unix, I'm operating on Windows platform.

View 4 Replies View Related

Unable To Extract NULLS

Dec 20, 2011

Am trying to extract data from oracle onto flatfile(.txt), am using UTL_FILE but, NULLs in oracle tables are getting converted into space and if i try loading into table it is getting loaded as space,

View 1 Replies View Related

Extract DDL's Without Storage Parameters?

May 11, 2005

I need to extract DDL's without storage parameters. if i use the export and import using indexfile or if i try to extract using the DBMS_METADATA.GET_DDL package , in both ways my output is with the storage parameters

eg:
CREATE TABLE "SEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),

[Code].....

View 6 Replies View Related

Extract Index DDL From Database?

Apr 27, 2011

Is there a way, i can extract index DDL from my database?

View 1 Replies View Related

SQL & PL/SQL :: How To Extract Substring From A Set Of Strings

Jan 25, 2012

Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .

In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.

View 3 Replies View Related

SQL & PL/SQL :: How To Extract The Rights Of Users On ERP

Mar 2, 2013

I would like to extract the user rights from the ERP that I use with SQL/Oracle.

There are 3 databases. The first gives me all the users with the main key id_user. The second one gives me the actions with the main key id_action. The third one only gives me what the user can't do.

I would like to know all the actions that all users can do or not.

The result must be something like:

Action 1
User 1 Yes
User 2 No

Action 2
User 1 No
User 2 Yes
....

View 9 Replies View Related

SQL & PL/SQL :: Extract Date From String

Jan 8, 2013

I need to extract the First portion of date entered in remarks column , i have prepared a test as below.

CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));
INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');
INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');
[code].....

--Required output is

LV_EMP_CODE LV_DESC
R0754 16/04/2012
R0667 14/07/2012
R0841 29/05/2012
R0854 29/05/2012
R0313 09/09/2012

View 15 Replies View Related

SQL & PL/SQL :: Extract String Between The Braces?

Sep 13, 2011

I have following column data

NZ(abc_mode)/ (NZ(bch_mode) + NZ(cdh_mode)

Now i need query to extract the string inside the braces NZ() with or without using regular expressions in SQL

the output should be

abc_mode
bch_mode
cdh_mode

View 5 Replies View Related

SQL & PL/SQL :: Extract Data Into Excel

Mar 21, 2012

Data that enter by user in database having " in the sentences
example:

column1
----------
"My Smurf Village

column2
--------
Farmvile

column3
--------
"Testing

My select statement is

Select column1,column2,column3 from table. Output in toad perfect but When I export into excel using pipe | as a separator, the output become

column1 in excel
----------------
My Smurf Village|Farmvile

column2 in excel
---------------
Testing

How I can code my program so that when extract to excel " is recognized as sentences. output are correct as in database?

View 5 Replies View Related

SQL & PL/SQL :: Extract Minute From Time?

Sep 7, 2010

1) Extaract time from date field

2) Extract minute form that time ..

View 13 Replies View Related







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