• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 771
  • Last Modified:

printing header footer to a file

I have created a table header_footer that contains header/footer information.

The procedure my_proc prints the header, content and footer to the file.

However, when i run the program, instead of printing the values the table's column values are printed directly
0
gram77
Asked:
gram77
  • 6
  • 4
  • 3
1 Solution
 
gram77Author Commented:
select * from header_footer;
--(static part)         --(substituted in proc)
text                              text value                                       text_type
run date is:             sysdate                              h
package name is:       p_package_name                  h
program name is:       p_procedure_name                  h
Record Count is:       v_count                              f
0
 
slightwv (䄆 Netminder) Commented:
Can you post your code?

You are likely treating the column names as strings not selecting the values
0
 
gram77Author Commented:
PROCEDURE my_proc (
   p_package_name          VARCHAR2,
   p_procedure_name         VARCHAR2
 )
IS
v_count NUMBER;

BEGIN
      --open file my_file      
     my_file :=  UTL_FILE.fopen ('my_dir', 'my_report_file', 'w', max_linesize => 10000);

      --write header info into it    
    FOR cur_header_footer in (SELECT * FROM header_footer WHERE upper(text_type)='H')
    LOOP
      BEGIN      
              UTL_FILE.put_line (my_file, cur_header_footer.text||' '||cur_header_footer.text_value);
             EXCEPTION
            WHEN OTHERS THEN
            dbms_output.put_line('error occured');
       END;
    END LOOP;  


      --write blah to file 10 times
    FOR i IN 1.. 10
      --write to file using utl_file.
        UTL_FILE.put_line (my_file, 'bhah');
      v_count:=v_count + 1;
    END LOOP;

      --write footer to file
    FOR cur_header_footer in (SELECT * FROM header_footer WHERE upper(text_type)='F')
    LOOP
      BEGIN      
              UTL_FILE.put_line (my_file, cur_header_footer.text||' '||cur_header_footer.text_value);
             EXCEPTION
            WHEN OTHERS THEN
            dbms_output.put_line('error occured');
       END;
    END LOOP;  

 
    UTL_FILE.fclose (my_file);
END;  
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
gram77Author Commented:
my_report_file's output:
run date is:  sysdate <--should be today's date substituted
package name is:  p_package_name <--should be xyz_pkg, the value passed to the procedure
program name is:  p_procedure_name <--should be xyz_proc, the value passed to the procedure
Record Count is:  v_count <--should be 10 records
0
 
slightwv (䄆 Netminder) Commented:
If you have literal values stored in the header/footer table, that's what you get.

If you want to do it this way, you'll need to select those values 'into' something using dynamic sql.

I'm not sure what you are trying to do the the header_footer table.

Why not just display them in the code itself.

If I get a few minutes, I'll try to come up with some working code.
0
 
gram77Author Commented:
a seperate table is created for header/footer to make code generic. anything can be a header/footer, user may choose to have record_count as header. other may choose it to be a footer. also some other user may want say day of the his name in header, so i created this table.

header
--data
footer
0
 
gram77Author Commented:

This is what i have done, but this is not a generic code.. it is messy..
I do not need so many if statements..

FOR cur_header_footer in (SELECT * FROM header_footer WHERE upper(text_type)='H')
LOOP
             IF cur_header_footer.text_value = 'p_run_instance_id' THEN
                          UTL_FILE.put_line (f_ext, cur_header_footer.text||' '||p_run_instance_id);
             ELSIF cur_header_footer.text_value = 'sysdate' THEN  
                          UTL_FILE.put_line (f_ext, cur_header_footer.text||' '||sysdate);
             ELSIF cur_header_footer.text_value = 'p_task_instance_id' THEN  
                          UTL_FILE.put_line (f_ext, cur_header_footer.text||' '||p_task_instance_id);                        
             ELSIF cur_header_footer.text_value = 'v_output_file_nme' THEN  
                          UTL_FILE.put_line (f_ext, cur_header_footer.text||' '||v_output_file_nme);                        
             ELSIF cur_header_footer.text_value = 'v_rec_count' THEN  
                          UTL_FILE.put_line (f_ext, cur_header_footer.text||' '||v_rec_count);
             END IF;  
END IF;
0
 
slightwv (䄆 Netminder) Commented:
I haven't forgotten about you.  I've been working on this for the last hour or so.

Unfortunately that is pretty much the same thing I end up with.

I've been trying to use dynamic SQL to pull this off but have yet to get a working combination.
0
 
gram77Author Commented:
thx for your efforts slightwv.
0
 
slightwv (䄆 Netminder) Commented:
I ran out of time for the day.  Sorry.

I just cannot come up with a generic solution.

Hopefully another Expert will be along soon and have some tricks I can't think of.

I'll pick it back up tomorrow if you don't have a solution.
0
 
sdstuberCommented:
based on your if/else chain it looks like your header/footer records are just keywords that you need to
parse and create values for.

where are those values coming from?

for instance 'v_rec_count'   you are attempting to append a v_rec_count variable but no such variable exists.

also,  how do your package and procedure parameters get used? I see no reference to them.


if you do want to do keyword parsing, you will need some sort of conditional logic that examines each value and does something with it.  It might not look like the if/else chain you have above but it will be similar logic.





0
 
sdstuberCommented:
I'm making the assumption that there is more to your header/footer data than your 4 examples.

Even if there are more than those 4, if  they are all single value keywords then it shouldn't be too hard to implement


that is,  one word like "sysdate" which produces one value like "2011-04-26 11:37:00"

side note - your sysdate value doesn't have an explicit conversion.  I recommend doing so
0
 
sdstuberCommented:
if my assumption is correct and you have simple string substitution

use an associative array indexed by varchar2

first, populate your array with all values you are interested in.

mystrings('sysdate') := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
mystrings('p_package_name') := p_package_name;
mystrings('v_count') := v_count;

then in your cursor dereference your array by the values you need


  --write footer to file
    FOR cur_header_footer in (SELECT * FROM header_footer WHERE upper(text_type)='F')
    LOOP
      BEGIN      
              UTL_FILE.put_line (my_file, cur_header_footer.text||' '||mystrings(cur_header_footer.text_value));
             EXCEPTION
            WHEN OTHERS THEN
            dbms_output.put_line('error occured');
       END;
    END LOOP;  
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now