SQL & PL/SQL :: Loading Past And Future Month Data From Different Source
Jul 31, 2013
I have an requirement to load past and future data from different source table to one tgt table.Say now we are in July
Past Data
Apr May Jun
Future Data
Aug Sep Oct
I HAVE actual sales for past month and present month which is in Table A & expected sales in table B.for every month i have to load 7 months data TARGRT TABLE..
In future the requirement may change to 6 months also. in that case the procedure has to load past 6 month + current month + 6month future so totally 13 month should be loaded.
View 3 Replies
ADVERTISEMENT
Jul 21, 2010
Source and Target db version : 10.2.0.4.0
Source Os :Cent OS 5.4 and Target OS:Sun OS 5.10
We are loading data from source DB to target DB using dblink.Source db is having 15.4 crore records of number and varchar2 data types.
Using the logic as follows.
insert into table1 as select * from table1@dblinkname[/email];
DB link is working.If I give "select * from scott.REPORT@DBLINK[/email]" in target db alone i could retrieve records.
Actual query:
Create Or replace procedure test_abcd as
begin
Insert into test select *
from scott.REPORT@DBLINK[/email]
dbms_output.put_line('Hello world');
end;
When i give the above query in sqlplus ,it is hanging. When i see the wait events i could find
"Wait Event: SQL*Net more data from dblink"
How to get the above things working.? when checked with network team they says there is no issue in the network. Do we need to modify any database/network level parameter settings.there is no firewall between source and target db.
View 9 Replies
View Related
Aug 13, 2010
Past 13 MonthEnds from a input date
(i.e.: 6/13/2010 is supplied, then query should be able to retrieve 5/31/2010, 4/30/2010, 3/31/2010, 2/26/2010, 1/29/2010, 12/31/2009?.....5/29/2009)
If the month end date falls on a weekend (Sat or Sun), then it should have the prior Friday's date.
View 18 Replies
View Related
Nov 16, 2013
i have an application for a medical laboratory, i insert patients every day , what i want is toget the number of patients for the past month ?2- for the past week ?3- the past day , and so on.
View 11 Replies
View Related
Dec 17, 2011
I need to get the past 12 months from the current month
for example
input:
march2010
output:
apr2009
may2009
june2009
july2009
augest2009
september2009
october2009
november2009
december2009
january2010
february2010
View 23 Replies
View Related
Jun 4, 2013
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
View 2 Replies
View Related
Mar 26, 2011
I am trying to set a item to system date + 18 years. What type of formula or where to put such a thing. i tried putting- select add_months(sysdate,216) from dual; into the calculation-formula in property palette with no success.
View 1 Replies
View Related
Dec 19, 2012
I'm trying to add edmx file in my project for first time. I want to choose the oracle provider ODP.net but cannot find Oracle in the data source list. I have oracle 11g installed , odp and odt installed and can access it from the solution as well. I saw the Oracle listed under data source when I tried to connect the solution to the database through server explorer. The solution is connected to Oracle database through ODP.
View 8 Replies
View Related
Mar 15, 2012
I have a horrible problem with EBS (actually, all problems with EBS are horrible) and I think I am stuck because of my ignorance of Forms. if I use terms that are not correct in the Forms world. The form consists of a number of named "blocks" and each block consists of a number of named "fields", not all of which are visible. I need to find the source of the data values in one of these fields. I have searched every table for a column of that name, also all the views and stored PL/SQL that I think might be relevant, but I can no find no mention of a column or variable with the same name as the field. The name does not get a hit in the online EBS tech ref manual, and only two ancient and irrelevant hits in MOS.
My question is: What are the possible sources of data for a field in a form? Have I missed any?
View 14 Replies
View Related
Jul 19, 2013
I'm new to Oracle Forms 10g.
When I create a data block based on a table using Data Block Wizard, the block's Query Data Source Columns property is automatically populated with column definition entries corresponding to the columns of the base table.
I tried making changes to these entries, for example by changing the data types to wrong data types or even deleting them, and I found that those changes had no effect on the block at all. The form was still working as I wanted.
What is exactly the role of the block's Query Data Source Columns property.
The F1 key help says "The Query Data Source Columns property is valid only when the Query Data Source Type property is set to Table, Sub-query, or Procedure". So, explain in each context of Query Data Source Type.
View 2 Replies
View Related
Apr 29, 2013
i have 2 tables in two different sources.
I have loaded data from source to destination.
some rows are missed while loading.
i want to know the missing rows
View 11 Replies
View Related
Mar 14, 2012
I have 1M Records coming from an External Data source as a Flat File (using ETL). Now I need only Yesterday's data only to load in my Database Table.
this can be done using Bulk Load and Filter.
write the CODE.
Second Part:-
Hint: if I need to update only those records been updated Say the Address1 field is updated. So this records need to update in my Master Customer Table.
If I have many fields in table and any records that are modified (coming to me from External Datasource as a Flat file) how to identify and update that record in my Master Customer Table?
View 5 Replies
View Related
Jan 11, 2011
I want to replicate single table data/modification from source db to destination DB.procedure or steps how to create replication between source and dest db for single table.
View 18 Replies
View Related
Jul 15, 2013
I created a standby database yesterday and everything is working correctly. I need to make some tweaks, however, and one of those is the directory that the standby database expects the archivelogs to be.
I found that the archivelogs were being shipped to $ORACLE_HOME/dbs and named arch*.arc. Interestingly a log switch ships the archivelog minus the "arch" at the front and all archivelogs now do not have that format. I moved the archivelogs to the correct location and renamed them. I have been able to set the correct location using standby_archive_dest and can see that the archivelogs are shipped to there.
Also, v$archived_log shows the correct path and filename. The problem I have is that when I come to apply the archivelogs it still seems to think that they should be in $ORACLE_HOME/dbs and named with "arch" at the front. what parameter I need to change to tell oracle the correct path and filename to use when applying the archivelogs?
View 9 Replies
View Related
Apr 10, 2012
We are getting the below error frequently from the application while doing insertion/dataloading to a table. The mentioned error is in the Primary key index
Error: 'ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state'.
I set the value SKIP_UNUSABLE_INDEXES = TRUE using the command 'ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE' to avoid this. Again we are getting the same error and Every time Iam rebuilding('alter index INDEX_NAME rebuild') the index and doing the DML Operation.
View 22 Replies
View Related
Jul 18, 2012
We have requirement such that whenever stored procedure is executed, their resultant records has to be stored in excel file ( Just like an reports ).No third party tool or reporting tools are used.
is there any option in oracle (Stored procedure or built in packages ) which can create excel file with the resultant records.
View 5 Replies
View Related
Jan 22, 2013
I have a requirement like, I received .dat files and placed in my local directory and there is a process/method where this data which is there in .dat file will insert into my oracle external tables?Any link of the example with clear steps?
View 9 Replies
View Related
Jul 3, 2013
I was trying to load data from XML files to an Oracle database table.I followed these below steps to load that file data into a table. Created XML_DIR1 as oracle directory where i have kept all XML files.
Create table import_rpt_xml of xmltypexmltype store as binary xml; insert into import_rpt_xmlvalues (xmltype (bfilename('XML_DIR1','I-Yamanouchi-20040525-501.xml'),nls_charset_id('AL32UTF8')));
This insert shows below error: Error starting at line 80 in command:insert into import_rpt_xmlvalues(xmltype(bfilename('XML_DIR1', 'I-Yamanouchi-20040525-501.SGM'), nls_charset_id('AL32UTF8')))
Error report:SQL Error: ORA-31061: XDB error: XML event errorORA-19202: Error occurred in XML processingIn line 69 of orastream:LPX-00217: invalid character 142 (U+008E) I tried to look into my XML and got that it has some Japanese characters in it.
this to deal with japanese characters in XML. I don't want to miss those characters. My databse NLS_CHARACTERSET is 'AL32UTF8'.
My sample XML file looks like this.
<ichicsr lang="ja">
<ichicsrmessageheader>
<messagetype>ichicsr</messagetype>
<messageformatversion>2.1</messageformatversion>
<messageformatrelease>2.0</messageformatrelease>
<messagenumb>US-Yamanouchi-W2004050033-4</messagenumb>
<messagesenderidentifier>Yamanouchi</messagesenderidentifier>
<messagereceiveridentifier>PMDA</messagereceiveridentifier>
[Code]...
and so on.
View 4 Replies
View Related
Aug 11, 2010
i loaded the data from csv file. and i used FIELDS TERMINATED BY x'09' values.but the end of the value having white space within it..
ZIP_CODE
"33004[] like box
"33004[]"
"33004[]"
"33004[]"
"33004[]"
how can i remove the white space when i load the data.
View 1 Replies
View Related
Aug 19, 2010
Is it Possible doing SQL LOAD into Varray table having two inner objects.
find the structure of the table and its types for your reference
CREATE OR REPLACE TYPE OB_TEST_INFO AS OBJECT (
AGE NUMBER ( 3 ),
NAME VARCHAR2 ( 14 )
);
/
CREATE OR REPLACE TYPE OB_TEST_INFO_VARRAY AS VARRAY( 400 ) OF OB_TEST_INFO ;
/
CREATE OR REPLACE TYPE OB_TEST_MAINTENANCE AS OBJECT (
BREAKOUT_TYPE NUMBER ( 1 ),
EXISTING_STRIPS_FLAG NUMBER ( 1 ),
OB_TEST_INFO OB_TEST_INFO_VARRAY
);
/
[Code]....
View 12 Replies
View Related
Oct 4, 2013
i am trying to load data into a table in a remote database schema, and my files are residing on another remote Server, Server having the files does not have a DB installed, i just need to know that if its possible or not..
View 2 Replies
View Related
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
Sep 5, 2010
How to load data to another schema's table through Sql*loader.
I tried the below things in the control file.
Load data
"
Into table scott.emp
View 14 Replies
View Related
Oct 15, 2010
I have a problem with loading data using SQL loader.I have such control file
LOAD DATA
INFILE 'D:oracleFIRMAS.txt'
INSERT INTO TABLE FIRMAS
FIELDS TERMINATED BY ","
(FIRMAS_REG_NR char,FIRMAS_NOS char,ID_PRODUCT char)
and data file "firmas.txt"
444557562, Avotoni SIA, 1332
445575627, Avotoni SIA, 1332
444557562, Avotoni SIA, 1332
When I try to execut this command
sqlldr userid=system/a1331 control=d:oraclefirmas.ctrl an error occured (sql*loader-350) what should I do?
View 16 Replies
View Related
Oct 29, 2010
We completed creating a replicate of dB_01 to dB_02 (housed in a single DEV server). But dB_02 had only table structures (no records). What would be our fastest option (tools, commands, etc.) to load more than a thousand new records for each of the 20 tables of dB_02?
View 7 Replies
View Related
Jun 8, 2012
I am using Oracle XE (11g) with APEX 4.1.1.0023 and Glassfish for the APEX Listener.I created a Data Upload set of pages and things worked great. I then exported the whole application and imported it into a new environment that was the same except the schema name was different. It was a different owner. I then tested the data upload in the new schema/environment and could not get the data loading to recognize the table. Upon comparison of the Shared Components between the two environments I discovered that the imported application in the new environment was still looking for the original schema name. The name is not editable via the Shared Components page. I had to recreate the pages and have it create a new Data Loading object before things worked again.
View 0 Replies
View Related
Apr 12, 2012
Iam using Oracle client 11g(32 bit) and Oracle database 11g on Unix server.
I have the created the following procedure to load XML file located in TEST_XML oracle directory( which is mapped to
DB/bulk01/REFLIB/REFDV/LOAD_XML) on the server.
Create or replace procedure TEST_LOAD_XML IS
dest_clob clob;
src_clob BFILE := BFILENAME('TEST_XML', 'arenas.xml');
dst_offset NUMBER := 1;
src_offset NUMBER := 1;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning NUMBER;
[Code]....
when I execute the above procedure I get ORA-22288 file or LOB operation FILEOPEN failed No such file or directory error.
Looks like it did not find my oracle directory 'TEST_XML'
do I have to do anything like mapping? if so since my database is on unix server how do I map?
View 4 Replies
View Related
Aug 31, 2011
I'm not sure if this is so much a SQL Loader problem as it is a database understanding problem, but here it is. I am having trouble loading data into a table (using SQL Loader) due to the fact that I am trying to load data row by row, into corresponding columns.
TestFile.csv
testvalue1, 123445
testvalue2, test
testvalue3, 455321
testvalue4, 65742
testvalue5, 5719
So, using the above data, I am trying to load the value for 'testvalue1' into a column defined as 'testvalue1'; the value for 'testvalue2' into a column defined as 'testvalue2' and so on. From my understanding, SQL loader loads by column not by row, so I am not even sure if this is possible.
View 2 Replies
View Related
Sep 11, 2012
I have data something like this.
Employee_id date_loggedin
123 09/10/2012
134 09/03/2012
111 09/02/2012
123 08/27/2012
134 08/01/2012
123 07/06/2012
111 05/11/2012
123 07/04/2012
123 07/03/2012
123 03/15/2012
123 01/11/2012
The desired output for input of employee_id=123 and dates between 01/01/2012 and 09/30/2012
Month login_count
JAN_2012 1
FEB_2012 0
MAR_2012 1
APR_2012 0
MAY_2012 0
JUN_2012 0
JUL_2012 3
AUG_2012 1
SEP_2012 1
View 2 Replies
View Related
Mar 8, 2011
I need to get data from a table in which dates is equal from previous month. The dates in this table has a formula DD-MMM-YY (CRE_DTTM is the name for date column).
I've already achieve getting data from the previous month by using this formula:
(to_char(CRE_DTTM,'MON')) = UPPER(to_char(add_months(trunc(sysdate,'MONTH'),-1), 'Mon'))
My problem now is what if the current month is for example JAN 2011.. I need to get the data from DEC 2010. How can I query the previous year in this case?
View 10 Replies
View Related
