SQL & PL/SQL :: Looping External Tables?

Jul 31, 2012

I am using an external table with numbers and joining the external table to fetch data and Insert into another table.I am altering the table within procedure to pass csv file name as parameter as It'll change each time. It's working fine but in case if my grouping id is having two gids, then Header from csv file is also inserted as row in to coupon_personlization_members.

I gave skip 1 in external table but for second GID, header is inserted as row.I don't want header to be inserted as row but header will be there on csv file.

Here is my procedure :
CREATE OR REPLACE
PROCEDURE proc_coupon_load(
p_grouping_id NUMBER,
p_file_name VARCHAR2)

[code]..

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: UNC In External Tables?

Aug 10, 2011

I made a directory and a external table, pointing to a file in the created directory. It appears that using unc path in my directory, won't work with my external table. Although the unc path points to the same server as where the database server is located.Is my assumption correct?

create or replace directory DIVA_POK as '\tvm011gm_diva_dir$ronnendbga_pok'

create table KOT_AOT(
AOT_IDENTIFICERENDE_SLEUTELvarchar2(14),
AOT_BEGINDATUMGELDIGHEIDdate,
AOT_EINDDATUMGELDIGHEIDdate,

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Creating External Tables

Jan 26, 2011

I have created a softlink to a data file orig_file.dat using the command ln -s orig_file.dat orig_file_link.dat

Now is it possible to create an external table using this linked file orig_file_link.dat

I am using this linked file to minimise the space usage because every month we get different data files and external table creation is done as a batch job copying the data file to another file whenever a new data file arrives. External table is created with the new file. so i need to check whether it is possible with the linked file instead of copying the file.

View 6 Replies View Related

SQL & PL/SQL :: External Tables (Oracle Loader)?

Jan 22, 2013

i am trying to create a external table.Table is getting created but there is no data.While checking the log file, its saying

KUP-04021: field formatting error for field CUST_CODE
KUP-04026: field too long for datatype
KUP-04101: record 1 rejected in file /data/mmi_mig_data/PCOM_CUSTOMER_1.csv
KUP-04021: field formatting error for field CUST_CODE
KUP-04026: field too long for datatype
KUP-04101: record 2 rejected in file /data/mmi_mig_data/PCOM_CUSTOMER_1.csv

But according to me in CSV file everything looks to be okay.External Table:

CREATE TABLE ET_PCOM_CUSTOMER(CUST_CODE VARCHAR2(12), CUST_TITLE VARCHAR2(12),
CUST_NAME VARCHAR2(240), CUST_MIDDLE_NAME VARCHAR2(240),
CUST_FIRST_NAME VARCHAR2(240), CUST_LAST_NAME VARCHAR2(240),
CUST_GENDER VARCHAR2(12), CUST_MARITAL_STS VARCHAR2(12),
CUST_NIC_NO VARCHAR2(60), CUST_ADDR_01 VARCHAR2(240),

[code]...

View 3 Replies View Related

Forms :: How To Use External Tables In Oracle 10g

Jun 6, 2011

How i use external tables as a form datablock in oracle oracle forms 10g an i use anyway external tables in a form datablock ?

View 3 Replies View Related

SQL & PL/SQL :: Can't Delete Files That Were Used On External Tables

May 11, 2011

I'm not sure if this should go on this topic.

Anyway, I've loaded 5 .csv files through an external table and after doing it I tried to delete them.

But this error comes "Cannot delete 'filename': It is being used by another person or program".

I closed Oracle Developer and tried again deleting them manually, and the result was the same.

Tried restarting and deleting one .csv and it worked, but when I open sql dev and tried deleting the other files couldn't do it.

The question is: files that were used on external tables can't be deleted if developer is working?

The thing is that I've created a Stored Procedure that delete the files and obviously can't work. So, I should delete every time I load a csv file after restarting the computer.

View 6 Replies View Related

SQL & PL/SQL :: How To Enable NOLOGGING In External Tables

Jun 16, 2011

How can I enable NOLOGGING in an External Table ?

My Table FT_PRICE_COST_MASTER is taking a long time(more than 1 hour) to excute inside the procedure. The statement is:

SELECT SUBSTR(m.sr_no,1,16) SR_NO, m.SKU_CODE, m.location_code, m.start_date, m.end_date, m.COST, m.tax, m.octroi, m.freight, m.cash_disc, m.scheme_disc, m.mrp, m.csp, m.rsp1_qty, m.rsp1_value, m.rsp2_qty, m.rsp2_value, m.rsp3_qty, m.rsp3_value, m.net_cost, m.final_cost, m.current_margin, m.pc_effect, m.created_by, m.creation_date, m.modified_by, m.modification_date, m.change_ind, m.tax_code,
[code]......

When I am recreating the External table with NOLOGGING keyword at the bottom of the synatx, it's showing "operation not supported". Else are there any other points that I should look to increase the execution time of the above statement ?

View 2 Replies View Related

Exporting Data From Tables To External Text File?

Apr 29, 2008

Actually what i am trying to do is to extract data form tables and place them in an external text file....i wrote the following code

FUNCTION

create or replace
FUNCTION dump_data ( p_query in varchar2,
p_separator in varchar2 ,

[Code].....

View 3 Replies View Related

Client Tools :: OWB File Loading Through External Tables

Mar 15, 2011

Is it possible to trim the file name while loading into OWB through external tables?

Like suppose I am trying to read a file which has a timestamp value appended in its name. In that case loading into external file would give an error.

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

Server Utilities :: Difference Between Sqlloader And External Tables?

Feb 9, 2011

I would like to know which of the above is faster for the same conditions.

i.e. If I am loading 1 million rows for the same conditions which will perform faster?

View 9 Replies View Related

Server Utilities :: Views Linked To External Tables

Mar 28, 2011

i just posted another topic where i heard about external table and i had a few questions concerning them. I thought it was best to create a new topic than to continue on the other one...

I noticed that to create an external table the CTL is like this:
CREATE TABLE emp_load (FIELDS description)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (RECORDS FIXED 62 FIELDS (employee_number CHAR(2),

[Code]...

1) This creates an external table, but, is it possible to Create a normal table in a CTL file? For physical tables, the table has to exist right?

2) if you create a view linked to 2 external tables and if the CSV files are updated each day, the external tables will be updated automatically, and the view will be updated as well?

3) Can't there be any synchronisation problems?

4) What happens if a select request (or someone requests on the view) while the CSV file is being updated?

5) Is there anyway you can protect the accesses from those tables/views when the CSVs are being updated?

6) Is it possible to create an index on these sort of tables?

7) Is it possible to index a view?

8) Are external tables visible on a tool like sql developper?

View 11 Replies View Related

Export/Import/SQL Loader :: External Tables Loading Multiple Files From Directory One By One

Oct 4, 2013

the following situation, I have a directory named /dat/global/stock/  inside this i will get files named differently for example below.abcdef.112dfgrt.2......

 Here i want to load this file one by one into the external tables and generate one more file based on some enrichment.

Step 1. Have to take first file and to load into the ext table.
Step 2. Enrichment
Step 3.File generation. 

Now here i am facing a problem that in that particular directory i usually get 1000 files so i need to get file one by one and to put in one more directory. how can i get file one by one and generate file by using oracle loader 

View 4 Replies View Related

SQL & PL/SQL :: To Get Rid Of Looping

Aug 17, 2010

The below sql is executed by reading the flat file row by row which is having more than 2 million rows.

SELECT CUST_SEQ_R, BANK_BRANCH_C INTO v_CustNo, v_BrnachCode
FROM TABLE1
WHERE ACCOUNT_NO = v_Acc_No
AND VIST_NO = v_VistnO
AND CUST_JOIN_DATE = to_Date(v_Cust_Date,'yyyymmdd');

The procedure opens the file, reads one record, parses it, then it executes few select statements then it loops back to the top where it reads one more record and runs through each activity again. How to read batch of records rather than one record at a time. Is something like creating staging table or by using bulk collect.

v_Acc_No,v_VistnO,v_Cust_Date are variables that read from below file.

test data:
BNC192363A1291197OQ6HA0H05557004559020080528
BNC194566A1280615VT8FA0H05917004016020080530
BNC192588A1263301VB2LE0G11065005707020080519
BNC178369A1295822MM4XB0G11820006839020080512

View 9 Replies View Related

SQL & PL/SQL :: Looping Through A Ref Cursor

Feb 16, 2008

I have a procedure which has an out value of a ref cursor (made up of different columns from different tables). I would like to call that procedure from another procedure and loop through each row.

I know how to call my stored procedure but I don't know how to loop through it.

View 5 Replies View Related

SQL & PL/SQL :: Straight Insert Instead Of Looping

Sep 24, 2012

I have a table with these rows,

Name No
one1
two2

Without using plsql looping constructs, how to achieve this in a simple sql statement?

View 5 Replies View Related

Forms :: Looping Records With Checkbox Value

Jun 17, 2013

I am Using the Check Box in the Detailed Block & I Have Assigned values 'Y' and 'N' when Checked & Unchecked If there are 3 Values Retrieved in the Detailed Block, Among that 2 Values are Checked the Cursor is Directly going the 2nd Value and Displaying the 2nd Value 2 Times rather than Displaying the 1st Value.

Below Attached is the Code Which i am Using.

GO_BLOCK ('ITEM_RESULT');
L_ITEM_CODE := :ITEM_RESULT.ITEM_CODE;--71720;--
first_record;
loop
message (' item id is '|| L_ITEM_CODE || '-' || :ITEM_RESULT.CHECKBOX);
if L_ITEM_CODE is not null and :ITEM_RESULT.CHECKBOX = 'Y' then
begin
[code]....

View 6 Replies View Related

Application Express :: Looping Checkboxes

Jun 15, 2012

How can I loop through unchecked checkboxes using this construction: FOR i IN 1 .. apex_application.g_f01.COUNT (its for checked)

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

Forms :: Looping Multiple Records In Blocks

Jul 31, 2012

I have two blocks, both are multi record block. 1st block is control block and second is database block. Both the blocks have same fields(Example: Location,Location_name,Location_Type). In the first block(Control Block) I have check box. My goal is when I check the checkbox and click on add button all the records which are selected in first block should go to second block.

View 3 Replies View Related

SQL & PL/SQL :: Improve Procedure Which Is Looping Through Query To Make Inserts

Oct 10, 2013

I am trying to improve a procedure which is looping through a query to make inserts.

FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
[code]....

View 12 Replies View Related

JDeveloper, Java & XML :: Looping And Querying Data From XML Clob

Oct 14, 2013

Our company database is Oracle based and we use SQL Developer to pull out needed data.Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.

select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
And Wtr_Req_Xml Like ('%CSM%')
Order By Wtr_Receive_Date Desc)
where rownum = 1;
[code]....

This query is only able to pull the first value in the list.How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.

This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.

View 28 Replies View Related

Application Express :: Looping Through Row Selector Exponentially Duplicating

Jul 15, 2013

Using Apex 4.2

 BackgroundCreated a Tabular Form based on a view. Removed all the default multi-row updates and deletions, buttons and processes.I am using a tabular form because of the fact that I can gain access to a row selector.The row data itself is all read only so in effect this is a report with a row selector.This is because I can't find a way to select a row an interactive report, well I can and did but this was a work around. ProblemI have created a button to process the selector to run the below code:

begin
kdm_adm_utils.log ('Page', 'XKDM: WF: Current Development', 'Selected Check Boxes:'||wwv_flow.g_f01(1)) ;
kdm_adm_utils.log ('Page', 'XKDM: WF: Current Development', 'Selected Check Boxes:'||wwv_flow.g_f02(1)) ;
   for i in 1..wwv_flow.g_f01.count
   loop
   kdm_adm_utils.log ('Page', 'XKDM: WF: Current Development', 'i:'||i) ;
      kdm_adm_utils.log ('Page', 'XKDM: WF: Current Development', 'WF ID:'|| wwv_flow.g_f02 (wwv_flow.g_f01.count)) ;
   end loop;
end;

I just wanted to see what would be output. For one row this works fantastically and I can find the ID based on the rowWhen I select more than one row the loop run's through for the number of rows but then also runs for the number of rows selected.So if I selected threee rows this above procedure would run three times, each times looping three times.I have set the execution scope to: For Created and Modified Rows in the button condition that appears.RequiredI only want to loop through the selected rows once per button clickORKnow which row I am currently looping through. 

View 3 Replies View Related

SQL & PL/SQL :: View Created With Compilation Errors / ORA-01775 - Looping Chain Of Synonyms

Jun 21, 2011

I created a view in DB2 .I copied the code of view from DB3 and created in DB1. The view got created but with compilation errors.

SQL>
SQL> CREATE OR REPLACE FORCE VIEW bala.cee_efacts_pie_order_v (cenumber,
2 installationid,
3 areanumber,
4 clli,
5 servicedate,
6 forecasttype,
7 jobid,
8 shippriority,
[code]....

I checked the status of view in user_objects , it was invalid.How to make it valid ?

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

Server Utilities :: Append Tables Content To Existing Tables?

Nov 9, 2010

problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.

we have found something called table_exist_action=append in impdp.

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