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