PL/SQL :: Load Data From Index By Table To Table

Jun 27, 2013

We need to load data from index by table to table.Below code is working fine. 

declare
query varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;
begin

[Code]....

But data from source table and target table is dynamic.Ex:In above code, emp(source) and target table is emp_b are static. But for our scenario is depends on the source table , target would change as below.If source is emp then target is emp_bIf source is emp1 then target is emp_b1 ............ 

create or replace procedure p(source in varchar2, target in varchar2)
as
query varchar2(200);
source varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;

[Code]....

Its throwing. How to implement this scenario .

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Load Data From Table A To B While Looking Up Value From C?

May 20, 2011

I am very much new to this vast world of Pl/SQL. Recently I have moved to pl/sql domain. I want to write a package to move data from Table A to Table B by looking up the table C. Bellow is skeleton of my package.

Table name: source_a
columns: X1,X2,X3,X4,X5
Target table name: target_b
columns:Y1,Y2,Y3,Y4,Y5
Lookup Table : lookup_c
columns : Z1,Z2,Z3,Z4,Z5

1) I have to load data from source_a to target_b.
2) If value of column X1(source_a) matched with value of column Z1( lookup_c) then only we will process the recordsmeans those records will be the valid records. Rest of the records will be dropped.
3) From the valid records If X2 != Z2 then call a procedure pk_rec.generate_Y2(X2) to generate the value of Y2
{pk_rec.generate_Y2 already existing no need to create) else take the records form Z2 and use it to load the y2.
4) From the valid records If X3 != Z3 then call a procedure pk_rec.generate_Y3(X3) to generate the value of Y3
{pk_rec.generate_Y3 already existing no need to create) else take the records form Z3 and use it to load the y3.
5) X4,X5 are directly loaded into Y4,Y5.

View 3 Replies View Related

SQL & PL/SQL :: Load Data Into Empty Table?

Jul 25, 2011

There is one table with data in ORCL1 database.I have created the table using create table statement in ORCL2 database.Now i want to insert only the data into table.

I know one method ... drop the table i created using drop table statement and then create the table with data using export/import.

Is there any other way we can load data into empty table?

View 29 Replies View Related

SQL & PL/SQL :: How To Load The Data From View To Table

Feb 27, 2012

How can i load the data into a new table from view,when ever scheduler runs in the night, the data gets loaded in to a view and data coming from different tables and i should load that data every day and i dont want previous data again.The data should be loaded along with view .

View 6 Replies View Related

SQL & PL/SQL :: Cursors To Load Data Into A Table?

Oct 3, 2011

DB version: Oracle DB 10g Enterprise Edition 10.2.0.4.0

I have the following four tables:

tab_main- which lists main projects
tab_sub_main - which lists sub projects
tab_budget - amounts per projects/subprojects
tab_total - I want to load the data here

The table script with data is attached.

I want to load data into tab_total fields for prj_type= 'J' as follows:

1. accn_no from tab_main table.

2. fy from tab_budget table

3. fy_total_amt which is the sum(amt) from tab_budget table by accn_no and fy

4. all_FY_amt which is the sum(amt) from tab_budget table by accn_no

5. all the audit fields- date/user inserted/updated will come tab_budget table

how to create this procedure with cursors.

CREATE OR REPLACE PROCEDURE LOAD_DATA_INTO_TAB_TOTAL_PROC
IS
CURSOR C IS
select distinct m.accn_no, a.control_no,m.prj_type,
b.fy, b.amt, b.user_created, b.date_created, b.user_last_mod, b.date_last_mod
from tab_main m,
tab_sub_main a,

[code]....

CREATE TABLE tab_main
(
ACCN_NO NUMBER(7) NOT NULL,
PRJ_TYPE VARCHAR2(1 BYTE) NOT NULL
)
/
Insert into TAB_MAIN
(ACCN_NO, PRJ_TYPE)

[code]....

View 34 Replies View Related

PL/SQL :: Load Data Into A Table From LDR File?

Aug 6, 2012

how can I load data into a table from *.ldr* file? How exactly I can use such files to run in loader?

View 16 Replies View Related

Utility To Load Data Into Database Table?

Aug 24, 2011

I wanted to know the best utility in oracle to load data in crores from excel sheets in the database temporary tables in a minimum time.

Is sqlldr the best utility to use in this scenario or to use the parallel and append hint in the insert statment.

how much time the sqlldr and above mentioned hints take to load 10 crore data in the database table.

View 2 Replies View Related

SQL & PL/SQL :: Code To Load Data From Text To Table

Oct 14, 2012

What is the best way to load the data from text file to the table in PL/SQL .How can i write a program for that ?

-data is separated by ',' in text file for each columns in table

View 19 Replies View Related

PL/SQL :: SQL LOADER Script - Load Some Data Into Another Table?

Aug 2, 2012

have loaded some data into table 'A' by using sqlloader.

Structure of A will be like

bill_id, bill_amount, bill_date
     1     1000     2-1-12
     2     2000     3-2-12

Now my query is i have to load some data into another table 'B', with bill_id as one of the column but i will be not having this column in my csv file.

Structure of B should be like

     bill_no, bill_id, bill_desc
     101     1     abcd
     102     2     defg

my csv file have only 'bill_no' and 'bill_desc' data. How can i include bill_id values from A?I am using Oracle 10g.

View 3 Replies View Related

Forms :: Select A File And Load CSV Data Into A Table?

Mar 16, 2010

I am new to oracle designer, forms. The requirement is to select a csv file in a form ,read the file and load selected columns from a csv file into a table.

I am using CLIENT_TEXT_IO. I want to know how to extract the data from selected columns from csv file and insert into a table if the lenth of the columns are of variable length.

Another condition is that if there are duplicate rows based on orderid then take the maximum order seq nbr.Do I need to use
temp table for this logic?

View 1 Replies View Related

SQL & PL/SQL :: How To Load Oracle Table Data Into EXCEL Sheet

Jan 3, 2011

how to load oracle table data into EXCEL Sheet .

View 5 Replies View Related

Server Utilities :: Load Data Into A Table Using SQL Loader

Feb 7, 2013

I'm trying to load data into a table using SQL Loader but getting a failure error below.

Log File
========

SQL*Loader: Release 11.2.0.2.0 - Production on Wed Feb 6 23:54:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: /opt/Infor/Outbound_Marketing/7.2.2/EM/metadata/trans.ldr
Data File: /opt/Infor/Outbound_Marketing/7.2.2/EM/logs/trans.log
Bad File: trans.bad
Discard File: none specified
[code]....

View 13 Replies View Related

SQL & PL/SQL :: Remove Last Comma End Of String And Load Clob Data Into Table

Aug 29, 2012

To remove the last comma end of string and load the Clob data into table. create table test(name clob)

View 2 Replies View Related

SQL & PL/SQL :: Procedure To Load Data In Oracle Table Into Excel File

Dec 29, 2010

send me the procedure for loading the data in an oracle table into an excel file.

View 5 Replies View Related

Oracle 11g Express Edition - Load Huge Data Into Table

Nov 6, 2012

I am using oracle 11g Express Edition, I have a file of .csv forma, Which has a data of size 500MB which needs to be uploaded into oracle table.

Which would be the best method to upload the data into table. Data is employee ticket history which is of huge data.

How to do the mass upload of data into oracle table.

View 3 Replies View Related

Server Utilities :: Truncate Table And Load It With Data Present In File

Jul 17, 2010

My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.

View 6 Replies View Related

PL/SQL :: Load Data From Flat File To Target Table With Scheduling In Loader

Sep 7, 2013

I have requirement as follows.  I need to load the data to the target table on every Saturday. My source file consists of data of several sates. For every week i have to load one particular state data to target table.  If first week I loaded AP data, then second week on Saturday karnatak, etc.

Provide code also how can i schedule the data load with every Saturday  with different state column values automatically.

View 2 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Server Utilities :: How To Write Control File To Load Data Into Revenue Table

Aug 16, 2011

I have a table revenue

create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)

and i have data in a file like below

Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678

i want to load it into the table in the following way.

Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
How to write control file to load this data into the above revenue table.

View 2 Replies View Related

SQL & PL/SQL :: Use Of Index For Searching Data In Table

Mar 5, 2010

i want to know the use of index for searching data in table...tell answer to my query with table example....

View 2 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.

View 11 Replies View Related

SQL & PL/SQL :: Column Of Table With Data Type Varchar2 - Corresponding Index

Jan 10, 2013

I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column

like '%abc%'
like 'abc%'
like '&abc'

Will be the corresponding index will be for those cases?

View 3 Replies View Related

Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?

Feb 7, 2012

We deleted millions of records from a table.

1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.

2.Will re org table and index improve the database performance ?

View 7 Replies View Related

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.

View 6 Replies View Related

SQL & PL/SQL :: Load Table Using A External Table?

Sep 5, 2010

I am trying to load my table using a external table. I have data in .DAT file and I have created a directory and its path and everything is complied and created properly. But problem is, when I say "insert into my table select from external table". It's inserting all null values into my table. Instead of inserting data from my .DAT file its inserting all null values,

View 10 Replies View Related

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

SQL & PL/SQL :: Full Table Scan On Index Table

Feb 3, 2012

when i am Executing the following statement

SELECT DISTINCT EXPOSURE_REF FROM KBNAS.VW_EXPOSUREDETS_FOR_CCYREVAL
WHERE EXPOSURE_CURRENCY='THB' AND BASE_TXN_CCY='USD' AND BRANCH_CODE='7000'
AND (REVAL_STATUS='O') AND CONV_RATE<>'62' AND (EXPOSURE_AMOUNT<>0)
UNION
SELECT DISTINCT ED.EXPOSURE_REF FROM KBNAS.EXPOSURE_DETAILS ED,
[code].....

I have attached DDL for table EXPOSURE_DETAIL(PARTITION),LEDGERCARD,LEDGERCARDDETAILS, DDL for INDEX on those tables and DDL for Views..

Issue: we have created the Indexes but when we check the explain plain .. full table scan is going on..I have attached the explain plan ..

View 11 Replies View Related

Load XML To SQL Table Using Loader

Feb 11, 2013

The problem I'm having is my XML tree doesn't contain all possible elements. In this example the second entry doesn't contain <age> - only the first entry will be added to the database

All columns are varchar2 datatype

XML:

CODE<rowset>
<row>
<name>Name</name>
<age>Age</age>
<city>City</city>
</row>
[code].......

View 3 Replies View Related

PL/SQL :: How To Load XML File Into Table

Jun 13, 2013

I've been working on Oracle for many years but fot the first time I was asked to load a XML file into a table.As an example, I've found this on the web, but it doesn't work. the file acct.xml is this:

 <?xml version="1.0"?><ACCOUNT_HEADER_ACK><HEADER><STATUS_CODE>100</STATUS_CODE><STATUS_REMARKS>check</STATUS_REMARKS></HEADER><DETAILS><DETAIL><SEGMENT_NUMBER>2</SEGMENT_NUMBER><REMARKS>rp polytechnic</REMARKS></DETAIL><DETAIL><SEGMENT_NUMBER>3</SEGMENT_NUMBER><REMARKS>rp polytechnic administration</REMARKS></DETAIL><DETAIL><SEGMENT_NUMBER>4</SEGMENT_NUMBER><REMARKS>rp polytechnic finance</REMARKS>[code]......

 For the two tags HEADER and DETAILS I have the table: 

create table xxrp_acct_details(status_code number,status_remarks varchar2(100),segment_number number,remarks varchar2(100)); 

before I've created a create directory test_dir as 'c:esterno'; --

where I have my acct.xml and after, can you give me a script for loading data by using XMLTABLE? I've tried this but it doesn't work: 

DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x1.status_code,         x1.status_remarks,        x2.segment_number,         x2.remarks from xmltable(  '/ACCOUNT_HEADER_ACK/HEADER'  [code]......

This should allow me to get something like this: select * from xxrp_acct_details; Statuscode status remarks segement remarks --------------- -------------------- ------------- ------------ 100 check 2 rp polytechnic100 check 3 rp polytechnic administration100 check 4 rp polytechnic finance100 check 5 rp polytechnic logistics500 process exception 20 base polytechnic500 process exception 30500 process exception 40 base polytechnic finance500 process exception 50 base polytechnic logistics but I get:

 Error report:ORA-06550: line 19, column 11:PL/SQL: ORA-00932: inconsistent datatypes: expected - got NUMBERORA-06550: line 4, column 2:PL/SQL: SQL Statement ignored06550. 00000 -  "line %s, column %s:

%s"*Cause:    Usually a PL/SQL compilation error. and if I try to change the script without using the column HEADER_NO o keep track of the header rank inside the document: 

DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x1.status_code,         x1.status_remarks,        x2.segment_number,         x2.remarks from xmltable(  '/ACCOUNT_HEADER_ACK/HEADER'  [code]......

I get this message: Error report:ORA-19114: error during parsing the XQuery expression:  ORA-06550: line 1, column 13:PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignoredORA-06512: at line 419114. 00000 -  "error during parsing the XQuery expression: %s"*Cause:   

An error occurred during the parsing of the XQuery expression.*Action:   Check the detailed error message for the possible causes. My oracle version is 10gR2 Express Edition

View 3 Replies View Related







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