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


ADVERTISEMENT

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

Accessing Something From Table Not Joined To Others

Jun 17, 2008

I have a schema whereby a table is not joined with other tables.

the info on that table can be gotten manually (by doing a query) and then using that info in another query. so is there a way of getting info from that table?

View 8 Replies View Related

SQL & PL/SQL :: Accessing XML Values From A Table?

Jan 29, 2013

i have a table which has 2 columns.1st column has userId and the other contains an xml data as a link.on clicking that link a new file opens containing the data in xml format.

<fields>
<field key="Public Email">piyush@chand.com</field>
<field key="Location">bangalore</field>
<field key="Website" />
<field key="Birthday">0001-01-01 00:00:00</field>
<field key="Gender">Male</field>
<field key="Language">English</field>
</fields>

i need to access location of a particular userId.How can i do that?

View 6 Replies View Related

SQL & PL/SQL :: Accessing A Table Contains Array In Remote DB?

Jan 30, 2009

How to access (create Synonym and Materialized View) a Table contains an Array of type object which is on Remote Database connecting through DBLink.

View 27 Replies View Related

SQL & PL/SQL :: Accessing V$mystat Gives Table Or View Does Not Exist

Jul 1, 2010

I am using the sid of v$mystat to create a unique filename in my pl/sql procedure.

I have granted access to v$mystat to the user that is accessing it from system user as:

SQL>GRANT SELECT ON V_$MYSTAT TO ar;
Grant succeeded.

SQL> commit;

Commit complete.

now when i login as user ar and do a select on v$mystat it works fine:

SQL> select sid from v$mystat WHERE ROWNUM = 1;

SID
----------
290

However, when i do the same from my PL/SQL procedure it throws an error saying :

SQL> @FILECREATE
53 /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION FILECREATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/8 PL/SQL: SQL Statement ignored
22/35 PL/SQL: ORA-00942: table or view does not exist

My PL/SQL function can be found as an attachment.

View 11 Replies View Related

Replication :: Block Corruption While Accessing Some Table

Mar 16, 2011

The database is running in archivelog mode and we have a standby with Maximum performance.There is no RMAN backup..We have noticed there is block corruption while accessing some tables.Now i would like to know are the corrupted blocks also replicated to the physical standby? Is there a way to recover the data from these corrupted blocks without shutting down the database ?

View 1 Replies View Related

SQL & PL/SQL :: Insert Statement In Trigger Accessing And A Select From A Table

Jun 6, 2010

In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.

But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------

IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------

In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .

SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID

how can I rewrite my query to the above value from the select into my insert statement..?

View 24 Replies View Related

SQL & PL/SQL :: Writing Procedure In User 2 Schema Accessing Table Product - Not Compiled?

Dec 17, 2010

I have got 2 users as user1 and user2.I have used the following statements from user 'user1':

create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;

In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.

create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;

[code]...

why I cannot access that table from procedure?

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

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

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 :: 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

SQL & PL/SQL :: External Table Error

Jan 28, 2011

I have an external table. The table gets created successfully. Once the table is created when I try to access it, I get the following error :

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /cmpnt/dev/test/ADE_TEMP_4992.log

The default directory is valid and does not have any issues. The IP address of my DB server and the server from which I am connecting to the DB are different. Is this is the issue ? However , all SQL queries are working fine except this one.

View 1 Replies View Related

SQL & PL/SQL :: External Table Creation

May 23, 2011

i had small query on external tables. i had (.csv) file outside the database. In this file, one column will be added monthly. i need to create the external table dynamically by adding the column for every month.

How the procedure can be created for this requirement.

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

External Table Concept

Jun 22, 2009

Provide material for External table concept .which is like exactly as SQL * Loader concept.

What are the basic difference between External table concept and SQL * Loader concept.

View 2 Replies View Related

SQL & PL/SQL :: External Table Syntax Error

Jul 5, 2012

SQL>

create table oldemp8
(
fname varchar2(30),
salary number(6),
job_id varchar2(20)
)
[code].......

Table created.

SQL> select * from oldemp8;
select * from oldemp8
*
ERROR at line 1:
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 "(": expecting one of: "comma, defaultif,
nullif, )"
KUP-01007: at line 7 column 16
ORA-06512: at "SYS.ORACLE_LOADER", line 19

SQL>

what is the syntax error in the above command. I place the notepad file properly.i create external table before many time but cant find any this type of error.

os windows xp 2000. oracle 10g (10.2.0.1.0)

View 7 Replies View Related

SQL & PL/SQL :: Specify Condition While Creating External Table

Feb 10, 2011

While creating external table how can I specify a particular decode condition for a date field that comes in as '2099-99-99' i want to change it to '2099-01-0001', how i can translate it

I already have this in the access parameters..

Incoming_DATE CHAR(20) DATE_FORMAT DATE MASK "YYYY/MM/DD"

View 2 Replies View Related

SQL & PL/SQL :: External Table And Blank Strings

Sep 23, 2011

I am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.

CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';

CREATE TABLE ET_RPDMMA1_PEDI_MSTR (
GCN_SEQNO NUMBER(6),
PDM_MNAGE NUMBER(4),
PDM_MXAGE NUMBER(4),
PDM_MND NUMBER(18,6),
[code].......

This is an except of what's in the external text file. The full text file has been attached.

000011|0030|....|000000.000000| |000000.000000| |0002
000011|0365|....|000000.000000| |000000.000000| |0002
000011|0730|....|000000.000000| |000000.000000| |0002
^
blank spaces may be |
causing error----------

Here is the error message I am receiving. I believe this is caused by the blank fields in the data.

INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,....
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?

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

SQL & PL/SQL :: External Table Does Not Show All Records

Jun 24, 2010

I have an external table as follows:

CREATE TABLE EXT1
(
COL1 NVARCHAR2(2000),
COL2 CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY FILE_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
( "COL1" CHAR,
[code]....

From here I can count all records and the total number of records matching the file record count, but could not read the clob columns. it's not picking up the values. but if I take this out

( "COL1" CHAR,
"COL2" RAW
)

then I am missing the record counts.Also if you put all varchar and char, that also missing the count against the file.

View 6 Replies View Related

SQL & PL/SQL :: External Table And Spanish Characters

Sep 27, 2011

I am having difficulty importing Spanish text files into my Oracle 11g XE database. Below is my external table declaration code. The external file is attached.

CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/.../';

CREATE TABLE ET_SLBLWD0_SPNSH_DESC (
LBL_WARN VARCHAR(4),
LBL_TXTSNS NUMBER(2),
LBL_DESCS VARCHAR2(55),

[Code]...

All 406 rows seem to import fine, but do not display the Spanish accent characters correctly. Note the strange characters:

SQL> SELECT count(*) FROM ET_SLBLWD0_SPNSH_DESC;

COUNT(*)
----------
406

SQL> select * from et_slblwd0_spnsh_desc;

LBL_ LBL_TXTSNS LBL_DESCS L L L L
---- ---------- ------------------------------------------------------- - - - -
0001 1 Puede causar somnolencia. El alcohol puede intensificar
0001 2 este efecto. Tenga cuidado cuando conduzca veh¿los
0001 3 automotores u opere maquinaria peligrosa.
0002 1 Importante: Acabe todo este medicamento a menos que
0002 2 quien le escriba la receta le indique lo contrario.

[Code]...

I tried modifying my external table declaration by adding CHARACTERSET WE8ISO8859P1 and CHARACTERSET WE8MSWIN1252, but that just seems to cause the system to throw out ALL the rows containing Spanish characters:

COUNT(*) ---------- 197

SQL> select * from et_slblwd0_spnsh_desc;

LBL_ LBL_TXTSNS LBL_DESCS L L L L
---- ---------- ------------------------------------------------------- - - - -
0001 1 Puede causar somnolencia. El alcohol puede intensificar
0001 3 automotores u opere maquinaria peligrosa.
0002 1 Importante: Acabe todo este medicamento a menos que
0002 2 quien le escriba la receta le indique lo contrario.
0005 1 Debe tomar este medicamento con mucha agua.
0006 1 Puede descolorar la orina o las heces.

[Code]...

The log file specifies the following error on the bad rows:

error processing column LBL_DESCS in row xxx for datafile ...SLBLWD0_CRLF.TXT ORA-12899: value too large for column LBL_DESCS (actual: 55, maximum: 55)

What can I do to import these Spanish characters correctly?

View 3 Replies View Related

PL/SQL :: External Table To Read Different Encoding?

Apr 22, 2013

i'm having some issues with the External table. Currently my database encoding is AL32UTF8. If my external table wants to read the txt file with the ZHS16GBK, the value will not show correctly when the external table reads the file. Is there any ways to display the value in the txt file correctly without changing the encoding database and encoding in the txt file.

View 2 Replies View Related

PL/SQL :: External Table With Empty File

Oct 9, 2013

My db version: Oracle 11g I have an empty csv file.I created a external table for the empty csv file.When I run:select count(*) from externaltblname;It returns 1. It should return 0 right. In the definition, I specified "SKIP 1"But still it returns 1. When I use this external table to load into a target table. It loads a single row with null values.How to fix this.

View 2 Replies View Related

Server Utilities :: How To Get Proper Value In External Table

May 3, 2012

getting proper value from the file in external table.

How can I get the whole status in STATUS column like completed , Inprogress, incompleted.
Right now, if I gave position like (38:9) full status doesn't show. if I give (38:11) then '|1' is adding in status from the flat file.

BATCH_NO FILE_DATEEMP_ID COMPANY_ID TRANSACTIN_ID FILE_NAME STATUS DOC_NO
10000104252012100001***4252012**1:35:57***D100001***04252012***10:35:57***Diverified

[Code].....

View 3 Replies View Related

SQL & PL/SQL :: Loading Data Through Oracle External Table?

Sep 14, 2011

I am loading data using Oracle External Table.

I am faced with certain errors which I am unable to proceed with.

Forwarding the source code snippet of the script of the oracle procedure.

The Source Code

drop table nar_temp_xtern;
create table nar_temp_xtern
(
cost_centre varchar2(06),
description varchar2(80),
field3 varchar2(80),

[code]....

Errors received :

ERROR at line 1:
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 "badfile": expecting one of: "column, enclosed,

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Oracle External Table / Suppressing Characters

Nov 23, 2011

I have an oracle code that is using Oracle External table to load a file.The issue: the column within the files have NA within them which needs to be skipped. Mentioned the code below -

create table lric_arc_fac1_xt
(
cost_category varchar2(30),
component varchar2(30),
fac number(38,18),
lric number(38,18),
marginal number(38,18),
sacs number(38,18),
dlric number(38,18),
dsac number(38,18),
[code].....

View 6 Replies View Related

SQL & PL/SQL :: Skipping Of Records - Oracle External Table?

Sep 24, 2011

I have a flat file on an unix system.The file is required to be loaded through Oracle External Table.

Issue:
Not sure how to skip the first record when loading through Oracle External Table.How to suppress data while loading through External Table.

Requiremen syntax where in I can skip the first record. syntax for suppressing values in columns that are not required. How the same needs to be handled in case of Number datatype and Varchar2 datatype. Example - In case of Number can it be replaced with 0 and for datatype can be same be replaced with NULL.

View 9 Replies View Related







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