Substitute characters within dollar with variable values and print

I am creating a header footer table, that will provide header and footer information to file
The header may contain a string with characters that need substitution.

Report format:
This report is ran on 10Apr2010, The report is run on 15Apr2010. Report is written on file my_report_file
data..data..data
data..data..data
data..data..data
Records written to file 120, report ran by fred

insert into header_footer values ('This report is ran on $sysdate$, The report is run on $p_report_dt$. Report is written on file $v_output_file_nme$, h);
insert into header_footer values ('Records written to file $v_rec_count$, report ran by $name','f');

--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 program should replace $sysdate$ with todays date in string
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, 'blah');
v_count:=v_count + 1;
END LOOP;

--write footer info into it
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 program should replace v_rec_count with records written
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('error occured');
END;
END LOOP;

select replace (replace (replace ('This report is ran on $sysdate$, The report is run on $as_of_dt$. Report is written on file $v_output_file_nme$', '$sysdate$', ltrim(sysdate)), '$as_of_dt$', ltrim('26-apr-2011')), '$v_output_file_nme$', ltrim('v_output_file_nme'))
from header_footer

sdstuber:
LOOP
BEGIN
v_string := REPLACE(v_string, v_key, mystrings(v_key));
v_key := mystrings.NEXT(v_key); <--what does this do?, can tokens and tokens in a string appear in any order?
EXCEPTION
WHEN OTHERS
THEN
EXIT;
END;
END LOOP;

To put it another way: From your first question you mention adding 'dynamic' values to your report headers and footers.

What are examples of your 'dynamic' values you might have that would not also require you to change the procedure code?

There are only a few Oracle dynamic columns like sysdate that you might add. Anything else would need to be added to the code anyway so just add more to your array/collection when you add the new variables.

"You have static requirements, so use a static list."
My static requirements are all the known parameters inside the procedure and sysdate. that is all i can show in the report.
If the user wants something fancy like his $myname$, report will just print $myname$ unreplaced.

as i said, everything that is known to the program like parameters, and sysdate can be given as a header/footer. something fancy like $myname$, the program does not know the value, so the program does nothing

Also note, your querys to do lookups of the definitions of these tokens takes time and consumes resources. Even if the query is fast.
Direct assignment without a query is faster

Again, I'm not going to try to help you do it the wrong way.

If you can demonstrate something that is actually dynamic then I will help
but trying to build a table to store static assignments is more complicated, slower and in this most recent example incorrect as well.

If you're trying to avoid "hardcoding" - DON'T!!!
That's not what you're doing, in fact, to make what you are attempting work would require a ton of hardcoded rules to handle the special case parsing

I was attempting to avoid hardcoding, due to which i created a script to extract tokens from a string $sysdate$ and parse the token to get sysdate
and then do automatic replace of tokens $sysdate$ with parsed tokens sysdate
loop
until '$token' not found
replace(str,token,parsed_token)

end loop;

but as i can see the string we get by parsing tokens end up being a string itself instead of a sysdate, v_output_file_name getting replaced by
today's date or the name of the report file

If i need hardcoding then i believe i don't need any program, just a simple REPLACE with hardcoded tokens and values are enough.

"faster" - yes, said it before multiple times - "IF" your list of tokens is small and fixed like your examples thus far, then nested replace is the most efficient.

"simpler" - no, not really. You are intentionally formatting your syntax to appear to as an array of index/value pairs, which means it's intuitively an array based problem.
Not using an array, but just faking it instead is somewhat confusing, but in this case still fairly trivial. Maintenance wise, changing it does require a little more work than simply
appending/deleting an element to the array. but again, it's only a little more work. So, "almost" the same in terms of simplicity, but I have to give the array method the nod
if you're really going to compare

"bug free" - no, but it has nothing to do with the nested replaces, your example is still doing implicit conversions. And,going back to the simplicity argument.
You're less likely to have bugs introduced later on by you or others that have to maintain the code if manipulations if it's just a matter of adding/removing array elements
than adding/removing parameters and nested function calls.

that's what I thought, but it doesn't apply here. You have nothing dynamic, so there is nothing to be gained by trying to fake it.
And, since the values that are replacing tokens are parameters and variables; those are, by their vary nature, required to be hardcoded.
Just as the BEGIN in a procedure isn't negotiable code in pl/sql , you can only use variables by actually using them.

However, the array method does allow more versatility with less maintenance coding so that lends itself toward the less-hardcoding argument

>>> i wrote a program to parse tokens

yes, I saw the other question about that. But it doesn't apply here. At least not with the example usage and example substitutions shown

>>that the procedure does not need to be checked out, changed each time a new parameter is added into

I agree with everything sdstuber has said. It seems that for some reason you aren't really seeing the reason why we are really saying that your list is actually hard-coded.

Back up in http:#a35476716 you used an example to show the possible flexibility of adding a new report parameter, $myname$, but you never answered the question: If the users want to add '$myname$', what do you want your code to do with it?

Would you not have to add code to the procedure to process that parameter? Since you have to touch the procedure, it really isn't dynamic.

Featured Post

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Cursors in Oracle:
A cursor is used to process individual rows returned by database system for a query.
In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.