No question at this time
DBA Top 10
1 B. Vroman 15700
2 M. Cadot 10200
3 T. Boles 8350
4 J. Schnackenberg 8300
5 A. Kavsek 7800
6 M. Hidayathullah ... 2500
7 P. Wisse 1200
8 G. Lambregts 1100
9 T. P 1000
10 B. Derous 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48277
Total active users1612
Act. users last 24h4
Act. users last hour0
Registered user hits last week185
Registered user hits last month836
Go up

Fetching Recording of between Dates
Next thread: Issue connecting Oracle 11g to SQL Server 2012 — ORA-12523: TNS:listener could not find instance app
Prev thread: checkpoint and SCN

Message Score Author Date
Hello Experts, I am trying to fetch the records...... rahul singh Apr 24, 2017, 09:21
Hi Rahul What are you trying to achieve with th...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Jan Schnackenberg Apr 24, 2017, 09:51
Hi, Below is my requirement : 1. I want to f...... rahul singh Apr 24, 2017, 10:52
Hello Rahul,   <<<I>Pls suggest me th...... Bruno Vroman Apr 24, 2017, 11:10
Hi, I need to automate the process as i need to...... rahul singh Apr 24, 2017, 11:20
Hello Rahul, about 1 million rows a day and you...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Bruno Vroman Apr 24, 2017, 12:30
Hi Rahul 1) Then use parameters to your procedu...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Jan Schnackenberg Apr 24, 2017, 15:07
Thanks Bruno and Jan ..I will try to search it th...... rahul singh Apr 25, 2017, 06:15
Dear Rahul, please check below links, it may he...... Mirza Hidayathullah Baig Apr 25, 2017, 06:56

Follow up by mail Click here


Subject: Fetching Recording of between Dates
Author: rahul singh, India
Date: Apr 24, 2017, 09:21, 545 days ago
Os info: Windows
Oracle info: Oracle 11.2.0.1.0
Message: Hello Experts,

I am trying to fetch the records from Data Base between the two dates.

Requirement : Fetching the data on date basis and import in Excel date-wise.

I wrote a below procedure but not able to print all the required columns.

The output of below process is only the first column (Accounting):

PROMPT CREATE OR REPLACE PROCEDURE record_fetch
CREATE OR REPLACE PROCEDURE record_fetch
IS

test_start_date Number;
test_end_date Number;
data_id NUMBER;
ACCOUNTING NUMBER;
RECORD DATE;
EQUIPMENT NUMBER;


CURSOR C1 IS
SELECT cs_data_id, ACCOUNTING_ID, RECORD_DATE ,EQUIPMENT_ID from OP_ACCOUNTING_UNIT
WHERE To_Number(record_date) >=test_start_date AND To_Number(record_date) < test_end_date;

BEGIN
OPEN C1;

test_start_date := To_Number(to_char(TO_DATE('06/01/2014 01:10:00','MM/DD/YYYY HH24:MI:ss'), 'J'));
test_end_date := To_Number(to_char(TO_DATE('06/10/2015 01:10:00','MM/DD/YYYY HH24:MI:ss'), 'J'));

FOR curr_r IN test_start_date..test_end_date loop

FETCH C1 INTO data_id,ACCOUNTING,RECORD, EQUIPMENT;


dbms_output.put_line(data_id);dbms_output.put_line(curr_r);
dbms_output.put_line(ACCOUNTING );
dbms_output.put_line(RECORD );
dbms_output.put_line(EQUIPMENT );


EXIT WHEN c1%FOUND;

END loop;
close c1;
END;
/


Pls help thanks in advance.

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Fetching Recording of between Dates
Author: Jan Schnackenberg, Germany
Date: Apr 24, 2017, 09:51, 545 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Rahul

What are you trying to achieve with this?
Why are "test_start_date" and "test_end_date" not parameters to the procedure?
Why do you have a column "record_date" that seems to contain a string that represents the number of days since January 1, 4712 BC.?
Why do you open the cursor before setting the variables containing the minimum and maximum thresholds?
Why don't you use a %ROWTYPE variable?


And: Do you get any error message?

What do you mean with "The output of below process is only the first column (Accounting)"? What happens when the "record" and "equipment" lines should be written?

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Fetching Recording of between Dates
Author: rahul singh, India
Date: Apr 24, 2017, 10:52, 545 days ago
Message: Hi,

Below is my requirement :

1. I want to fetch the between two dates ..monthly and yearly

2. Data to be save date wise in excel sheet

3. The data contain few columns which I need to specify.

Pls suggest me the best option to do. I dont have much experience in plsql.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Fetching Recording of between Dates
Author: Bruno Vroman, Belgium
Date: Apr 24, 2017, 11:10, 545 days ago
Message: Hello Rahul,

  <<Pls suggest me the best option to do. I dont have much experience in plsql.>>

Maybe you don't need PL/SQL... What about something like
SELECT t.cs_data_id || ',' ||

TO_CHAR( t.accounting_id ) || ',' ||
t.record_date || ',' ||
t.equipment_id
FROM op_accounting_unit t
WHERE t.record_date BETWEEN TO_CHAR( DATE '2014-01-06', 'J' )
AND TO_CHAR( DATE '2015-10-06', 'J' )
ORDER BY t.cs_data_id
;
that you can spool in a ".csv" file? (I assume that there are no "," inside the data + some assumptions about data types, check and correct if necessary)

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Fetching Recording of between Dates
Author: rahul singh, India
Date: Apr 24, 2017, 11:20, 545 days ago
Message: Hi,

I need to automate the process as i need to fetch the monthly data and the number of records are two high its about 1 million a day, so i want to execute the procedure which can loop the dates and fetch the data date-wise.

Hope you understand the requirement.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Fetching Recording of between Dates
Author: Bruno Vroman, Belgium
Date: Apr 24, 2017, 12:30, 545 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hello Rahul,

about 1 million rows a day and you expect to use DBMS_OUTPUT to see monthly data on your screen? (and spool it?) Hmmmm hmmmm

You might investigate the use of a PL/SQL procedure making use of UTL_FILE to create and fill the files without any interaction with a screen.

Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Fetching Recording of between Dates
Author: Jan Schnackenberg, Germany
Date: Apr 24, 2017, 15:07, 545 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi Rahul

1) Then use parameters to your procedure to pass these dates. Do not hard-code them into the procedure. Not even as a workaround during development. The current code was completely broken, partly due to this.

2) Either do what Bruno told you, or try to find some code that already does what you want. A quick google search ("plsql export excel") showed me that there are such tools.

3) That's still no reason to do what you did. Spooling PL/SQL output will buffer all of the output until the PL/SQL block finishes. If you have millions of rows this will either crash or gobble up all of your RAM very fast. Use UTL_FILE or some specialized PL/SQL package.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Fetching Recording of between Dates
Author: rahul singh, India
Date: Apr 25, 2017, 06:15, 544 days ago
Message: Thanks Bruno and Jan ..I will try to search it the UTL package, Kindly provide me the links for my understanding if you have any.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Fetching Recording of between Dates
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Apr 25, 2017, 06:56, 544 days ago
Message: Dear Rahul,

please check below links, it may help you on UTL_FILE.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#BABGGEDF

http://psoug.org/snippet/UTL_FILE-file-write-to-file-example_538.htm

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here