Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 6th, 2006, 05:10 AM
Registered User
 
Join Date: Jul 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default What does this piece of code do?

Hi,
Could any one please let me inform what does the following piece of code do step by step?
What do SQL instr function and substr function do in the following piece of code?
This piece of code belongs to a procedure whish is used for getting date on forms screen for a particular file on O/S

Thanks

Laldeen

Piece of code

BEGIN
FOR b IN 1..20 LOOP
UTL_FILE.GET_LINE (fp,v_line);
IF SUBSTR(v_line,1,3) = '***'
AND :DETAIL.FILE_DATE IS NULL THEN
:DETAIL.FILE_DATE := substr(v_line,1,100);
END IF;
END LOOP;


IF :DETAIL.FILE_DATE IS NULL THEN
:DETAIL.FILE_DATE := v_temp_line;
END IF;
v_temp_line := TO_CHAR(SYSDATE,'YYYY');
IF instr(:DETAIL.FILE_DATE,v_temp_line) >0 THEN
:DETAIL.FILE_DATE := SUBSTR(:DETAIL.FILE_DATE,
instr(:DETAIL.FILE_DATE,v_temp_line),16);
v_temp_date := TO_DATE(:DETAIL.FILE_DATE,'YYYY-MM-DD HH24:MI');
v_temp_line := TO_CHAR(v_temp_date,'DD Mon YYYY HH24:MI');
:DETAIL.FILE_DATE := v_temp_line;
END IF;
UTL_FILE.FCLOSE(fp);
IF v_temp_date > (:header.sysdate - :header.filter_days) THEN
get_file_size;


 
Old July 11th, 2006, 06:44 AM
Registered User
 
Join Date: Jul 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to TomFalc
Default

substr means substring - it extracts a short string from a longer one
instr means in string - it returns the position a string starts in another string

BEGIN
Start of code block

FOR b IN 1..20 LOOP
Loop incrementing b from 1 to 20

UTL_FILE.GET_LINE (fp,v_line);
Read a line from the file pointed to by fp into v_line

IF SUBSTR(v_line,1,3) = '***'
If the first three characters are asterixs...

AND :DETAIL.FILE_DATE IS NULL THEN
and this field is null...

:DETAIL.FILE_DATE := substr(v_line,1,100);
set the field to the first 100 characters of v_line

END IF;
END LOOP;


IF :DETAIL.FILE_DATE IS NULL THEN
:DETAIL.FILE_DATE := v_temp_line;
END IF;
v_temp_line := TO_CHAR(SYSDATE,'YYYY');
set v_temp_line to the current year (2006)

IF instr(:DETAIL.FILE_DATE,v_temp_line) >0 THEN
If the file_date contains the current year

:DETAIL.FILE_DATE := SUBSTR(:DETAIL.FILE_DATE,instr(:DETAIL.FILE_DATE,v _temp_line),16);
set file_date to 16 characters from the position of the current year
(If file_date = 'this is junk 2006/10/04 10:24 shakespeare', it will become
'2006/10/04 10:24')

v_temp_date := TO_DATE(:DETAIL.FILE_DATE,'YYYY-MM-DD HH24:MI');
Convert string to date

v_temp_line := TO_CHAR(v_temp_date,'DD Mon YYYY HH24:MI');
convert date to new string format (10-04-2006 10:24 -> 10 Apr 20006 10:24)

:DETAIL.FILE_DATE := v_temp_line;
END IF;
UTL_FILE.FCLOSE(fp);
closes the file

IF v_temp_date > (:header.sysdate - :header.filter_days) THEN
get_file_size;

I hope this helps - but try technet.oracle.com for complete documentation of Oracle.

Regards,

Tom





Similar Threads
Thread Thread Starter Forum Replies Last Post
understand a piece of code in assembler rbulus BOOK: Professional Assembly Language 1 January 10th, 2010 07:45 AM
Question about a piece of code chobo2 C# 2008 aka C# 3.0 8 November 30th, 2008 11:53 AM
How to delete a piece of code Charlie05 C# 2005 2 August 3rd, 2008 11:32 AM
This piece of code is giving an error sourik C# 2 July 15th, 2006 09:18 AM
putting piece of code into a string .... elladi Classic ASP Basics 0 December 23rd, 2004 12:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.