SQL & PL/SQL :: Create External Table Based On Record Identifier?

Oct 21, 2011

I have a csv file which is in this format -

qwerty SCHEMATIC FILE
; Version 2007.7.1
Project,qwerty Project,,1,,7,1.5,1.5,1,1,0,,0,1,0,0,0,0,3,1,1,0,1,0,0,0,0,2,3,1,0,1,0,0,0,0,3,3,1,1,1,0,0,0,0,0,0,0,0,
abc,25150-28407.dat,,102,192,42,12632256,1,1,102,192,42,1,12632256,0,6,1,0,32896,1,0,0,0,0,,,-1,-1,-1,-1,0,0,0,-1,-1,-1
xyz, , , 0, 102, 0, 0, 0, 0, 0, 0, 0, 0, 0, , , , , , , , , , , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
Product,00094416505678,19133,"24-36""X80"" FOLD DR VIA MEH",,4.5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,0,
Product,00094416502345,37154,"24-36""X80"" xyz WHITE",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,
Product,00094416501111,83120,"24-36""X80"" abc WH",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0

Can I create an external table for loading this file based on selection of fields driven by the record identifier which is the first field of each record, except for the firt two?

For e.g In the above file, for records which say 'abc' in it's first field, I want to load the 2,3,6,8 fields delimited by a comma (the values may as well be null in those fields) and for records which say 'Product' in their first fields, I want to load 5,8,9 and 10 fields from the same file. Basically, I want to know if we can use instr function while choosing the fields line by line based on a search criteria in the file. There will be less columns in my table than the number of fields in the csv file, so I guess I got to mention 'MISSING FIELD VALUES ARE NULL' option. There is another challenge too - I have to skip loading the first two lines from the file to the table.

I have written a big pl/sql proc for doing the same, using utl_file.get_line option, but it is still untested and I would be extremely happy to believe that this can be achieved by creating an external table too.

View 11 Replies


ADVERTISEMENT

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?

Is CREATE EXTERNAL TABLE included in CREATE TABLE?

View 3 Replies View Related

SQL & PL/SQL :: Create External Table

Aug 17, 2007

when i just execute

CREATE TABLE ext_schoolof
REJECT LIMIT UNLIMITED;

then it gets " select * from ext_schoolof " BUT when i use procedure, it creates external table but when I try to get " select * from ext_schoolof ", then I get errors

The error numbers are

ORA-29913
ORA-29400
KUP-00554
KUP-01008
KUP-01007
[code]....

View 14 Replies View Related

SQL & PL/SQL :: How To Create External Table In Oracle 8i

Jul 6, 2011

oracle version : Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

How we can create external table in oracle8i:

CREATE TABLE calls_external
(
call_id NUMBER,
emp_id NUMBER,
call_type VARCHAR2(12),
details VARCHAR2(25)
)
ORGANIZATION EXTERNAL

[code]....

Script not working in Oracle8i but working perfect in oracle10g.

View 19 Replies View Related

Server Utilities :: How To Create External Table

Aug 10, 2012

im trying to create an external table, and i load my data without no problem, and everything is fine, but i got some behavior with one column that i would like to know whats behind scenes, OK let's get the example:

[*] Sample Data
Line 1:333 1111111112009100000000000080000000013450.33
Line 2:11111111111220091016000000004.48
Line 3:222222222 220091016000000004.48
Line 4:(This is a blank line left)

And this is my External Table Create Query:

CREATE TABLE EXT_TABLE_TEMP
(COL_A VARCHAR2(11),
COL_B VARCHAR2(1),
COL_C DATE,
COL_D NUMBER(12,2))
ORGANIZATION EXTERNAL

[code]....

As you can see i can upload my table with no problem but i always get 3 lines counting last blank line if i try LOAD WHEN COL_A != BLANKS, i dont know if its a problem of the blank space left between fixed fields length, but if i do LOAD WHEN COL_B != BLANKS i get correct result 2 lines instead of 3, i want to know why (missing fields...) and (reject rows...) are not working...

Note: COL_A could be 9-11 length, if length its 9 then 2 spaces left before next one...

View 4 Replies View Related

Export/Import/SQL Loader :: Setting For External Table - Create File In Directory DIR-1?

Nov 20, 2012

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

I am new in external table so i have tried following cmd.

create directory dir_1 as 'E:ora_dirt' ;
grant read, write on directory dir_1 to HR;
select * from all_directories;
create table emp_ext
(emp_id number,
emp_name varchar2(30)

[code]...

since I am not able to see DIR_1 in E: drive due to which i havnt created  'emp.dat' file and on executing select on external table i m geting expected error *"ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04043: table column not found in external source: EMP_ID"*

how to create that file in directory "DIR_1" .

View 2 Replies View Related

SQL & PL/SQL :: External Table Query (compare Number Records In File With External Table)

Jan 23, 2013

I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.

I have attached the code that creates the external table and populates it.

View 5 Replies View Related

SQL & PL/SQL :: How To Record / Nested Table Type Based On Row Returned By Week

May 10, 2011

Is there a way we could define a record or a nestedtable with a type based on weak refursor i.e

TYPE RC IS REF CURSOR;
C2 RC;
Type t is table of c2%rowtype;
Following is some more explanation of what I am trying to do.

I have a table T with column A and B. Column A is a primary key with number 1,2,3,4,5,6, Column B has diffrent sql stmts stored. i.e 'Select * from emp', Select count(1) from dept' and so on. So table will look like

1 Select * from emp
2 Select count(1) from dept

Now I want to select statements stored in table T one by one and execute them by using cursor. Problem arises as i need to fetch the cursor into some variable but the outcome of each statment is diffrent and oracle does not allow to use cursorname%rowtype for a weak ref cursor.

View 3 Replies View Related

PL/SQL :: How To Split Column Data Based On Identifier

Oct 3, 2012

I am using oracle database.

I am having data in this format in my column 1234~2345~3456~4567.

I need a query to split the data in the column based on the identifier '~',so that i can pick out the value after the second occurrence of the identifier.

View 3 Replies View Related

Forms :: Create Two Datablocks Based On One Table In Oracle?

Jun 2, 2011

can we create two datablocks(header&line) based on one table in oracle forms.any way to create?

View 7 Replies View Related

PL/SQL :: Selecting Single Record From Multiple Record Based On Date?

Aug 26, 2013

I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name  

Null Type  ------ ---- ------------ ID_P        NUMBER       NAME_P      VARCHAR2(12) DATE_P      TIMESTAMP(6) Records---------------------1 loosi     22-AUG-13 01.27.48.000000000 PM1 nammi  26-AUG-13 01.28.10.000000000 PM2 kk        22-AUG-13 01.28.26.000000000 PM2 thej      26-AUG-13 01.28.42.000000000 PM 

now i have to select below 2 rows how can write select qurie for this?

1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej  26-AUG-13 01.28.42.000000000 PM

View 4 Replies View Related

WebService - How To Create Record Type And PL/SQL Table

Oct 7, 2010

I am working on a webservice call from ORacle.I have a button on my form application called verify.Wheni click on verify button , a pl.sql procedure should be invoked and that procedure will call .net webserive to validate the address , the result from the webserivce will be in xml.I have to extract the xml into some variables and return these varibles to Forms application..I am plannig to use pl/sql table to store the result from web service call.

here are the output values:

Customer_Id varchar2(20),
ErrorCode varchar2(30),
ErrorDesc varchar2(3000),
Fcount number,
FErrorCode -- this is array,-- if fcount >1 then these values will be repeted.
FErrorDesc -- this is array,
FStatusCode -- this is array,
FStatusDesc -- this is array,
Street varchar2(3000),
Street2 varchar2(3000),
Suite varchar2(20),
City varchar2(20),
State varchar2(2),
Zip_Code varchar2(10)

create record type and pl/sql table for these.I want to return pl/sql table as a out parameter to the form.

View 1 Replies View Related

Application Express :: How To Create Radio Groups Dynamically Based On Records In A Table

Nov 29, 2012

below requirement..

We have certain records like SQL, PL/SQL, Reports, Forms, OAF etc in a table. We wanted to capture rating for each of these criteria. So we want a form to be displayed dynamically..

SQL 1 2 3 4 (1,2,3,4 represent radio buttons)
PL/SQL 1 2 3 4
Reports 1 2 3 4
Forms 1 2 3 4
OAF 1 2 3 4

If we add another row, XMLP in that table additional radio button should be displayed automatically..

View 4 Replies View Related

Create Trigger So That Whenever Record In Employee Table Deleted?

Dec 7, 2009

I'm trying to create a trigger so that whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. It compiles but with warnings. Here's what I've got.

CREATE TABLE EmployeeArchive
(EmployeeID Int, FirstName Char, LastName Char,
EMail Char, PhoneNumber Int, HireDate Date, JobID Char, Salary Int,
Commission Int, ManagerID Int, DepartmentID Char);

[Code]....

View 11 Replies View Related

Forms ::create History Record For Each Record Whether Updated Or Not

Sep 13, 2011

I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.

No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.

Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.

View 4 Replies View Related

SQL & PL/SQL :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies View Related

PL/SQL :: Create Multiple External Tables From Same Flat File?

Nov 28, 2012

using oracle 10g currently create many external tables like so..

CREATE TABLE "XT_UNITS"
(

"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)
[code]....

is there any way I can use 1 flat file (csv) to populate many external tables ?

View 4 Replies View Related

SQL & PL/SQL :: Out Ref Cursor Based On Record Set

Sep 24, 2010

I have this record set coming from two tables.

cursor c1 is select cust_id from tab1 where login=in_login
union
select cust_id from tab2 where login_id = in_login;

in second step; I need to process and filter these ids based on some conditions.The final filtered record set I need to return as Out cursor;

How can I use the processed data to built the output ref cursor ?

View 4 Replies View Related

SQL & PL/SQL :: Count Number Of Record Based On Some Condition

Jun 28, 2013

i have two tables one as test_master and other one limit master.

test master table structure

ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure

limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --

ct_id and limit_id is match column used for mapping.

now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.

how to achieve the same using sql/oracle query.

View 7 Replies View Related

SQL & PL/SQL :: Bulk Insert Based On Record Count

Jun 19, 2012

Is there any defined record count range for the following ways of bulk insert :

INSERT INTO ABCTEMP SELECT * FROM DEFTEMP;

OR

through a cursor, bulk fetch and bulk insert under a loop.

View 6 Replies View Related

Overload PL / SQL Procedure Based On Different Record Type With Same Structure

Apr 8, 2004

I have two cursors like

cursor_A IS select * from table_a where condition_A;
cursor_B IS select * from table_a where condition_B;
record_A is a recorded of cursor_A%ROWTYPE
record_B is a recorded of cursor_B%ROWTYPE

I define a procedure like pro_A(record_in cursor_A%ROWTYPE) can I overload this procedure by defining pro_B(record_in cursor_B%ROWTYPE)?

If I can't, Can I call pro_A by passing record_B as the parameter to it?

View 3 Replies View Related

Forms :: Fetch Single Record From Multiple Records Based On Condition

Jan 27, 2012

I have made a travel booking system which comprises of 3 forms

1)Travel Booking form
2)Reservation Form
3)Cancellation Form

Under one booking number i can add multiple users in which they can have there multiple travels.

Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.

What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.

For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.

View 9 Replies View Related

SQL & PL/SQL :: Insert All Records From External Table Into Export Table

Mar 25, 2013

following is the requirement

External Table
WKSHT_FILE_EXT
wksht_line
Export Table
Wksht_export
global_idvarchar2(10)
wksht_linevarchar2(250)
[code]....

Step 1.Insert all records from the external table into the export table. Truncate the export table first

Step 2.Read in a record from the export map table

Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table

Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line

Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.

first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET

WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE
SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1
BRANCH = 2B
EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28
[code]....

THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK

View 1 Replies View Related

XML DB :: Create XML Based On Xsd And Then Validate?

Jul 31, 2013

 I want to create an XML based on xsd and then  i want to validate the same xml with xsd ,

View 1 Replies View Related

SQL & PL/SQL :: XML External Table

May 12, 2011

I'm experiencing some problems when trying to import an 50Mb XML file to an Oracle database. In this XML file I do have several data from customers:

- Name
- Address
- Contacts

I also have several tables within the DB that would receive this information:

- Customer
- CustomerAddress
- CustomerContacts

The problem is that, with my XML Transformation and correspondent insertion onto the databse I'm having an huge problem of time expended. I'm having more than 3 hours to insert over 180.000 records on those tables. what can I do to accelerate the process?

View 1 Replies View Related

SQL & PL/SQL :: Create New Column Based On MIN DATE

Jun 26, 2012

CREATE TABLE DAN_DATES
(ID VARCHAR2(12),
YEAR VARCHAR2(,
TERM VARCHAR2(,
START_DATE VARCHAR2(12))

INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120227','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120626','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110226','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110725','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2012','1201',to_date('20120227','YYYYMMDD'));

Want to take the Start_Date for that year, CREATE A NEW COLUMN and place that START_DATE (which is row 1 for the year (min)) in it. So for ID 1 TERM is 1201 and 1202 BUT we want the top start date (earliest start date) and CREATE a clumn (NEW_START_DATE) and place that date in there wherever year is 2012.

I want to get
IDYEARTERMSTART_DATEMIN_DATE
12012120127-Feb-1227-Feb-12
12012120126-Jun-1227-Feb-12
22011110126-Feb-1126-Feb-11
22011110125-Jul-1126-Feb-11
22011110126-Sep-1126-Feb-11
22012120227-Feb-1227-Feb-12

View 10 Replies View Related

MS Excel As External Table

Sep 6, 2013

I need to do some analysis and research to find if any of these 8000 tables I have in a spreadheet that are going to be spit off into a separate database are used by any of our PeopleSoft processes.

I'm assigned to Student Records identified by NTSR.

So for table AAP_ETHNIC_PMPT, I could do something like

SELECT *
FROM PSSQLTEXTDEFN
where SQLID like 'NTSR%'
AND upper(sqltext) like '%PS_AAP_ETHNIC_PMPT%';

But how can I automate this and search 8000 rows in column A2 of a spreadsheet? What other tables other than PSSQLTEXTDEFN or PSPROJECTITEM can I use to search for values of NTSR?

View 1 Replies View Related

SQL & PL/SQL :: Datafile For External Table?

Mar 9, 2010

Every day we have use different data file for an external table to read from.

filename like this "filenameddmmyyyy"

Every day, I have to change name of datafile in external table script which is not a good way.

I want the automated way for external table to read the latest datafile from specified directory.

Further, there is anyway that we could know that the latest data file is available for external table.

View 20 Replies View Related

SQL & PL/SQL :: Accessing Dmp Via External Table

Aug 5, 2011

when i am writing dump from external table, it is accessing records from dump.but when i am trying to access other dumps(create thru expdp) it is giving error.the logic i am following is mentioned below-

CREATE OR REPLACE DIRECTORY "DIR_GMS" AS 'D:Gopal_works est_env_files'

GRANT READ ON DIRECTORY dir_gms TO gopal;
GRANT WRITE ON DIRECTORY dir_gms TO gopal;

-- creating dump file in directory

CREATE TABLE emp_ext
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('emp_ext_dmp.dmp')
)
[code]......

i am able to see records.

New point:
-- taking export thru expdb
expdp hr/hr tables=EMPLOYEES directory=DIR_GMS dumpfile=HR_EMP.dmp logfile=expdpEMP.log
then i created one EXTERNAL TABLE TO access it.

CREATE TABLE emp_xt (
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('HR_EMP.DMP')
);

while accessing, it is giving error:

SELECT * FROM EMP_XT

ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-31619: invalid DUMP FILE "D:Gopal_works est_env_filesHR_EMP.DMP"
ORA-06512: AT "SYS.ORACLE_DATAPUMP", line 19

View 13 Replies View Related

SQL & PL/SQL :: External Table With Validation

Jun 25, 2010

I'm trying to create a external table column validation.

I have data like this

First column is Numeric, second is Non-Numeric

1,X
2,Y
3,Z

There are chances that I may have data as following
1,X
2A,Y
3,Z

My requirement is whenever I get non-numeric data in first column I want to pass 0 or NULL. So, I have created a external table with following script.

CREATE TABLE EXT_test
(
SL NUMBER (4),
BRANCH_NAME VARCHAR2 (100)
)
ORGANIZATION EXTERNAL
(
[code]........

Following is the code for function is numeric

function isnumeric( p_string in varchar2) return boolean as
l_number number;
begin
l_number := p_string;
return TRUE;
exception
when others then
return FALSE;
end;

It is not recognizing function IS NUMERIC. Is there any way, I can do this through External Table.

I'm getting error like

The following error has occurred:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "comma, date_format, defaultif, enclosed,
(, ltrim, lrtrim, ldrtrim, notrim, nullif, optionally, ), rtrim, terminated"
KUP-01008: the bad identifier was: isnumeric
KUP-01007: at line 9 column 16
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

View 1 Replies View Related







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