[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Substitute characters within dollar with variable values and print

Posted on 2011-04-26
64
Medium Priority
?
401 Views
Last Modified: 2013-12-07
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
0
Comment
Question by:gram77
  • 27
  • 24
  • 13
64 Comments
 

Author Comment

by:gram77
ID: 35467881
sample program:

CREATE TABLE HEADER_FOOTER
(
  TEXT        VARCHAR2(4000 BYTE),
  TEXT_TYPE   CHAR(1 BYTE)
)


ALTER TABLE HEADER_FOOTER ADD ( CONSTRAINT PK_BDE_HEADER_FOOTER PRIMARY KEY (TEXT_VALUE));


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');

commit;
0
 

Author Comment

by:gram77
ID: 35467898

create procedure write_report(p_report_dt varchar2,
                        v_output_file_nme varchar2,
                        v_report_run_by varchar2)
is
v_count number;
begin

   --open file my_file      
     my_file :=  UTL_FILE.fopen ('my_dir', v_output_file_nme , '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 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;  

end;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35467908
You cannot use a simple replace?

replace(text,'$sysdate$',to_char(sysdate,'DDMonYYYY'))
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35467927
ah, a design change from your other question.
http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_26976146.html


The replace should work but I'm not sure if you can do everything dynamic like you want in the other question.
0
 

Author Comment

by:gram77
ID: 35468001
replace(text,'$sysdate$',to_char(sysdate,'DDMonYYYY'))

This is a good idea but the string can contain the tokens in any order as the client wishes, but all tokens will be available in the program.

so the program has to first find out tokens, and replace these tokens with known variables in the program and print.

slightwv, this is a design change and this is a priority now than the previous post..
http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_26976146.html
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35468252
Getting the 'tokens' is a simple regexp loop.

I still think you'll need to manually do the 'replace' with hard-coded values.
drop table tab1 purge;
create table tab1(col1 varchar2(150));

insert into tab1 values('This report is ran on $sysdate$, The report is run on $p_report_dt$. Report is written on file $v_output_file_nme$');
commit;


declare
	var_count number := 1;
	var_name varchar2(20);
	inString varchar2(150);
begin
	select col1 into inString from tab1;

		while 1=1 loop
		begin
			var_name := regexp_substr(inString,'\$[a-z_]+\$',1,var_count);
			if var_name is null then exit; end if;
			dbms_output.put_line('Got: ' || var_name);
			var_count := var_count + 1;
		end;
		end loop;
end;
/

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35468291
looks like my assumption in your previos question was correct and you have simple string substitution

so, same idea here  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

the difference here than the original is that you'll need to iterate through the
array for each index doing a replace on each of them.


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35468311
I don't think regexp parsing of the strings is necessary

just iterate through array of known substitutions and replace.

if the keyword isn't in the string the replace will do nothing.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35468421
>>if the keyword isn't in the string the replace will do nothing.

good point.
0
 

Author Comment

by:gram77
ID: 35468426
sdstuber:
can you please give a working example..
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35468428
here's a simple example of what I mean
DECLARE
    TYPE replace_t IS TABLE OF VARCHAR2(100)
                          INDEX BY VARCHAR2(20);

    mystrings   replace_t;

    v_key       VARCHAR2(20);

    v_string    VARCHAR2(32767);
BEGIN
    mystrings('$sysdate$')         := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
    mystrings('$p_package_name$')  := 'TEST_PACKAGE';
    mystrings('$v_count$')         := 20;


    FOR x IN (SELECT 'replace $sysdate$ in this string with the system date' str FROM DUAL
              UNION ALL
              SELECT 'replace $p_package_name$ in this tring with my test package name' str FROM DUAL
              UNION ALL
              SELECT 'replace $v_count$ and $sysdate$ in this string with twenty and current time respectively' str FROM DUAL)
    LOOP
        v_string  := x.str;
        v_key     := mystrings.FIRST;

        LOOP
            BEGIN
                v_string  := REPLACE(v_string, v_key, mystrings(v_key));
                v_key     := mystrings.NEXT(v_key);
            EXCEPTION
                WHEN OTHERS
                THEN
                    EXIT;
            END;
        END LOOP;

        DBMS_OUTPUT.put_line(v_string);
    END LOOP;
END;

Open in new window

0
 

Author Comment

by:gram77
ID: 35468477
will this do:
select replace (replace (replace (text
    , '$sysdate$', ltrim(sysdate))
    , '$p_report_dt$', ltrim(sysdate))
    , '$v_output_file_nme$', ltrim(sysdate)
    )
from header_footer
where text_type='h';
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35468492
you tell us

run it, does it do what you want?

it'll be more efficient and if you have a small number of keywords it's probably best.

I suggested the array because I didn't know how many substitutions you might want and it's flexible and easy to add or subtract them as needed
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35468494
When using sysdate, you should force the format you want with to_char but that looks like the basics.

I do like the array suggested by sdstuber.  That should make maintaining the code easier.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35468506
I assume the LTRIM(sysdate) was just for illustration.

in reality you'll want to do explicit to_char conversions with masks
and outputfilename probably won't be a date
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35468538
you could wrap all of the array looping into a function that does all of the substiutions for you.



DECLARE
    TYPE replace_t IS TABLE OF VARCHAR2(100)
                          INDEX BY VARCHAR2(20);

    mystrings   replace_t;

    v_key       VARCHAR2(20);


    FUNCTION do_substitutions(p_string IN OUT VARCHAR2, p_array IN replace_t)
        RETURN VARCHAR2
    IS
        v_string   VARCHAR2(32767);
    BEGIN
        v_string  := p_string;
        v_key     := p_array.FIRST;

        LOOP
            BEGIN
                v_string  := REPLACE(v_string, v_key, p_array(v_key));
                v_key     := p_array.NEXT(v_key);
            EXCEPTION
                WHEN OTHERS
                THEN
                    EXIT;
            END;
        END LOOP;

        RETURN v_string;
    END;
BEGIN
    mystrings('$sysdate$')         := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
    mystrings('$p_package_name$')  := 'TEST_PACKAGE';
    mystrings('$v_count$')         := 20;


    FOR x
        IN (SELECT 'replace $sysdate$ in this string with the system date' str FROM DUAL
            UNION ALL
            SELECT 'replace $p_package_name$ in this tring with my test package name' str FROM DUAL
            UNION ALL
            SELECT 'replace $v_count$ and $sysdate$ in this string with twenty and current time respectively'
                       str
              FROM DUAL)
    LOOP
        DBMS_OUTPUT.put_line(do_substitutions(x.str, mystrings));
    END LOOP;
END;

Open in new window

0
 

Author Comment

by:gram77
ID: 35468548
I believe this seems to be simplest implementation:
select replace (replace (replace (text, '$sysdate$', ltrim(sysdate)), '$p_report_dt$', ltrim(sysdate)), '$v_output_file_nme$', ltrim(sysdate))
into v_str
from header_footer
where text_type='h';
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35468560
actually, in my example,  since mystrings is declared outside the scope of the do_substitutions function, it's not necessary to pass it as a parameter
DECLARE
    TYPE replace_t IS TABLE OF VARCHAR2(100)
                          INDEX BY VARCHAR2(20);

    mystrings   replace_t;

    v_key       VARCHAR2(20);


    FUNCTION do_substitutions(p_string IN OUT VARCHAR2)
        RETURN VARCHAR2
    IS
        v_string   VARCHAR2(32767);
    BEGIN
        v_string  := p_string;
        v_key     := mystrings.FIRST;

        LOOP
            BEGIN
                v_string  := REPLACE(v_string, v_key, mystrings(v_key));
                v_key     := mystrings.NEXT(v_key);
            EXCEPTION
                WHEN OTHERS
                THEN
                    EXIT;
            END;
        END LOOP;

        RETURN v_string;
    END;
BEGIN
    mystrings('$sysdate$')         := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
    mystrings('$p_package_name$')  := 'TEST_PACKAGE';
    mystrings('$v_count$')         := 20;


    FOR x
        IN (SELECT 'replace $sysdate$ in this string with the system date' str FROM DUAL
            UNION ALL
            SELECT 'replace $p_package_name$ in this tring with my test package name' str FROM DUAL
            UNION ALL
            SELECT 'replace $v_count$ and $sysdate$ in this string with twenty and current time respectively'
                       str
              FROM DUAL)
    LOOP
        DBMS_OUTPUT.put_line(do_substitutions(x.str));
    END LOOP;
END;

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35468572
Simplest maybe (don't forget TO_CHAR)  but harder to maintain.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35468575
yes,  if you have a tiny, fixed set of replacments,  then go with a hardcoded set of nested replace

if you want/need more versatility, then go with the array method
using the do_substitutions function makes the final code quite compact as well
0
 

Author Comment

by:gram77
ID: 35468576
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35468596
not quite.


 ltrim('v_output_file_nme'))
to use the variable:
 ltrim(v_output_file_nme))
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35468609
don't use

ltrim(sysdate)

use

to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

use whatever format you want,  if you're worried about extra spaces, then use "fm" in the format mask
0
 

Author Comment

by:gram77
ID: 35468994
This worked for me
 v_footer := REPLACE(REPLACE(REPLACE(REPLACE(cur_header_footer.text,
                                                                      '$sysdate$',sysdate),
                                                                      '$as_of_dt$',p_as_of_dt),
                                                                      '$v_output_file_nme$',v_output_file_nme),
                                                                      '$v_rec_count$',v_rec_count);
0
 

Author Comment

by:gram77
ID: 35469019
sdstuber:
sorry, i didn't really understand your program
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35469034
>>sorry, i didn't really understand your program

When you get a little time I would suggest you take another look at it.

It will same you some headaches when you go to tweak/add/change the values.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35469043
what don't you understand?  I'll be happy to explain any of the parts.
It's essentially just 3 things,  and one of them you already have in your code
so really, only 2 new pieces

first - fill your array with values.  

second - read your cursor (my cursor is a fixed list, you would use your real query)

in that cursor loop search for your strings and replace them.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35469173
when you formatted your replace statement in value pairs
your thinking was already heading toward a string-based array


'$sysdate$',sysdate),
'$as_of_dt$',p_as_of_dt),
'$v_output_file_nme$',v_output_file_nme),
'$v_rec_count$',v_rec_count);

the only thing "special" I added was the function that iterates through the array
regardless of the number of values instead of hardcoding a fixed number of nested replace calls.

one thing you might need to be careful of is cascading replacements that create corrupt output.  both the nested replace and the array iteration are susceptible to it.

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35469209
By cascading replacments I mean something like this...

"Dinger Will Robinson!"

replace "i" with "a"
replace "ban" with "xxxx uh oh xxxx"

so,  the cascade happens when the first replace creates a candidate for the second replace that didnt' exist before

1st replace: "Dinger Will Robinson!"  =>  "Danger Wall Robanson!"
2nd replace: "Danger Wall Robanson!" => "Danger Wall Roxxxx uh oh xxxxson!"

using encapsulated keywords should help, provided your ecapsulating character "$" isn't used anywhere except for encapsulation and will never appear in a replacement value

0
 

Author Comment

by:gram77
ID: 35469259
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;


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35469526
since the array is indexed by strings and not numbers  you can't simply say index+1

mystrings.NEXT(v_key)  returns the next key after v_key  

They'll be in alphabetical order as determined by your NLS language and sort options.

The order of tokens in the strings is irrelevant as is the order of replacements, with the possible exception of the cascading mentioned above
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35469544
similar functionality could be achieved with 2 arrays
one of tokens and the other with values.
each indexes 1-N.

Then, instead of looping on string indexes you do a simple FOR loop

that loop might look something like this.

FOR i in 1..my_tokens.count LOOP
    replace(v_string,my_tokens(i),my_values(i));
END LOOP;

note, this is not "better", just different and it would consume slightly more memory as well
0
 

Author Comment

by:gram77
ID: 35474782
I have created a program that generates a list of tokens $sysdate$ and stripped tokens sysdate, and builds a string.

how do i tell oracle that sysdate is actually a keyword to be repalced with todays date
and v_output_file_nme is a parameter passed into the program
0
 

Author Comment

by:gram77
ID: 35474793
specs:
header_footer table:

text                                                                              text type
abc $sysdate$, def $as_of_dt$. ghi $v_output_file_nme$                h
Records written to file $v_rec_count$                                        f
0
 

Author Comment

by:gram77
ID: 35474795
set serveroutput on
declare
v_str VARCHAR2(4000);
v_token VARCHAR2(4000);
v_parsed_token VARCHAR2(4000);
v_header_footer VARCHAR2(4000);
i number :=1;
cnt number :=0;
TYPE typ_header_footer IS RECORD (
     token   VARCHAR2 (40),
     parsed_token   VARCHAR2 (40)    
      );
TYPE tbl_typ IS TABLE OF typ_header_footer
INDEX BY BINARY_INTEGER;

ARRAY    tbl_typ;
begin
  FOR cursor_header_footer IN (SELECT text,text_type
                                FROM header_footer
                                WHERE upper(text_type) = 'H')
  LOOP                              
 
        LOOP
          EXIT WHEN instr(cursor_header_footer.text,'$',1,i) = 0;

        --extracts token $sysdate$      
          SELECT substr(cursor_header_footer.text,
          instr(cursor_header_footer.text,'$',1,i),
          (to_number(instr(cursor_header_footer.text,'$',1,i+1)+1)-
          to_number(instr(cursor_header_footer.text,'$',1,i)))) str
          INTO v_token
          FROM dual;

        --strips $ from $sysdate$ becomes sysdate
          SELECT substr(v_token,to_number(instr(v_token,'$'))+1,to_number(instr(v_token,'$',-1))-2)
          INTO v_parsed_token
          FROM dual;

          cnt := cnt + 1;      

        --build array of token and parsed_token      
          ARRAY (cnt).token := v_token;
          ARRAY (cnt).parsed_token := v_parsed_token;
         
          i := i + 2;
        END LOOP;
     
      FOR j in 1 .. cnt
      LOOP
                  --searches text for token and replaces with parsed_token
           IF j = 1 THEN
             v_header_footer := REPLACE(cursor_header_footer.text, ARRAY (j).token, ARRAY (j).parsed_token);
             ELSE
             v_header_footer := REPLACE(v_header_footer, ARRAY (j).token, ARRAY (j).parsed_token);            
             END IF;
      END LOOP;  
 cnt := 0;    
 END LOOP;
  dbms_output.put_line('last: '||v_header_footer); <--the result is a string    
end;
0
 

Author Comment

by:gram77
ID: 35474801
program output:
This report is ran on sysdate, The report is run on as_of_dt. Report is written on file v_output_file_nme
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35474887
I'm not sure what your question is.
what is the token/parsed_token thing supposed to be?

are you asking how to populate the array?  
If so, most of it will be a static list like I showed and with an array like I showed

mystrings('$sysdate$')         := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
mystrings('$p_package_name$')  := 'TEST_PACKAGE';
mystrings('$v_count$')         := 20;

if you have tokens that can be looked up via data in a table then you could do something like this...

select token_name, token_value
into v_name, v_value
from your_table
where ....;

mystrings(v_name) := v_value;

or put it in a cursor loop if you can derive multiple token values from a table.

note, this a separate step from the actual replacing.
And you won't do any token extraction from the target strings.
You simply replace them.






0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35474939
So we have come full circle back to the problem you have in your initial question?

http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_26976146.html


From your original question, I assume you still want them assigned dynamically without hard-coding the value pairs in the procedure itself?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35474947
if you are trying to use a numeric array with records instead of string based indexing

then it's the same idea, you create your token/value pairs.
I'm not sure about the intent of your record field names.
I'm treating "parsed_token" as "value"  that may or may not be what you intended

ARRAY(1).token := '$sysdate$';
ARRAY(1).parsed_token  := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
ARRAY(2).token := '$p_package_name$';
ARRAY(2).parsed_token  := 'TEST_PACKAGE';
ARRAY(3).token := '$v_count$';
ARRAY(3).parsed_tokeh := 20;

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35475007
the assignment doesn't have to be a static list.  But, given the examples displayed thus far,  they should be.

assigning $sysdate$  the value of SYSDATE should be a static assignment
assigning $package_name$  the value of a parameter called p_package should be a static assignment

if you're looking for a way to dynamically create tokens as well as dynamically define what the values of those tokens will be,  you will have to use dynamic SQL

BUT... from what you've shown so far,  there is no reason to pursue that. It'll only be slower, more complicated and begs for SQL injection attacks
0
 

Author Comment

by:gram77
ID: 35475096
"if you're looking for a way to dynamically create tokens as well as dynamically define what the values of those tokens will be,  you will have to use
dynamic SQL"

can you please give an example of how this will work in my program
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35475212
>>> can you please give an example

no, not really.

you don't have anything that is actually dynamic.

I guess you could do something silly like


create table my_tokens (token_name varchar2(20), token_value varchar2(100));

insert into my_tokens values ('$sysdate$','select sysdate from dual');
commit;

then in your code you could loop through my_tokens

ARRAY(i).token_name := x.token_name
execute immediate x.token_value  INTO ARRAY(i).token_value;

but again,  this is a bad idea because you don't really want dynamic tokens




0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35475236
the above example is to illustrate how it "could" be done and hopefully is self-evident that it is not how it "should" be done.

if you want to pursue it anyway, good luck; but I won't help more along those lines unless you can demonstrate that there is actually a good reason to implement such a thing.

You have static requirements, so use a static list.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35475267
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35475438

the fact that you were considering  replace(replace(replace(.....)))

as an option should reinforce that you don't need (or want) dynamic tokens
0
 

Author Comment

by:gram77
ID: 35476602
"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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35476623
Where will you get the value to be used for '$myname$'?

Given your current path, you will have to hard-code that value in the procedure anyway, therefore you still have static values.
0
 

Author Comment

by:gram77
ID: 35476700
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35476716
>>something fancy like $myname$, the program does not know the value, so the program does nothing

I'm not following.  If the users want to add '$myname$', what do you want your code to do with it?
0
 

Author Comment

by:gram77
ID: 35476783
sdstuber:
This example will work for sysdate, but what about parameters passed to the program say v_output_file_nme?

****************************
create table my_tokens (token_name varchar2(20), token_value varchar2(100));

insert into my_tokens values ('$sysdate$','select sysdate from dual');
commit;

then in your code you could loop through my_tokens

ARRAY(i).token_name := x.token_name
execute immediate x.token_value  INTO ARRAY(i).token_value;
0
 

Author Comment

by:gram77
ID: 35477032
is such a thing possible in pl/sql?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35477063
using parameters is easy

ARRAY(1).name := '$your_parameter_token$';
ARRAY(1).value := p_your_parameter;


your parameters are definitely not dynamic
because they must be compiled into the procedure declaration.  
0
 

Author Comment

by:gram77
ID: 35477121
create table my_tokens (token_name varchar2(20), token_value varchar2(100));

insert into my_tokens values ('$sysdate$','select sysdate from dual');
insert into my_tokens values ('$v_output_file_nme$','v_output_file_nme');

commit;

then in your code you could loop through my_tokens

ARRAY(i).token_name := x.token_name
execute immediate x.token_value  INTO ARRAY(i).token_value;
execute immediate x.token_value  INTO ARRAY(i).token_value;<-will this substiture the parameter value with my_report.27042011?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35477196
no, that will not work, the substitution will insert the name of the variable

but, again, there is no need to that, nor should you even want to try this.

v_output_file_nme isn't dynamic.  It's fixed.  
The variable is part of the source code of the procedure.

To try to make it "configurable" via a table is just adding complexity
it doesn't work
and, even if it did, what would be the point?

Just define the variable directly.

ARRAY(2).name := '$v_output_file_nme$';
ARRAY(2).value := v_output_file_nme;


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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35478464
gram77,

You've opened several questions along a related theme.
Clearly you're not getting the answer you want to see.

That doesn't mean you haven't gotten the answers you need though.

But, just to be sure.  Please post an example of a string and and a result that you believe the solutions above  won't solve.

0
 

Author Comment

by:gram77
ID: 35481311
sdstuber:
"If you're trying to avoid "hardcoding"  -  DON'T!!!"

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.


v_footer := REPLACE(REPLACE(REPLACE(REPLACE(cur_header_footer.text,
                                                                      '$sysdate$',sysdate),
                                                                      '$as_of_dt$',p_as_of_dt),
                                                                      '$v_output_file_nme$',v_output_file_nme),
                                                                      '$v_rec_count$',v_rec_count);

what i want to know is if my assumption is correct?
0
 

Author Comment

by:gram77
ID: 35481366
hy i wrote a program to parse tokens was so that the procedure does not need to be checked out, changed each time a new parameter is added into
the procedure that can be written on to the report.

but if one needs to "hardcoding":

then doing this is faster, simpler, and bug free
v_footer := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cur_header_footer.text,
                                                                      '$sysdate$',sysdate),
                                                                      '$as_of_dt$',p_as_of_dt),
                                                                      '$v_output_file_nme$',v_output_file_nme),
                                                                      '$v_rec_count$',v_rec_count),
                                                      '$MyNewParamAddedToProc$,p_MyNewParamAddedToProc);

Then this:
PROCEDURE write_report (p_pkg_nme varchar2,
                  p_MyNewParamAddedToProc varchar2); <--new parameter introduced to code
IS
DECLARE
      ..
BEGIN
    mystrings('$sysdate$')         := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
    mystrings('$p_package_name$')  := p_pkg_nme;
    mystrings('$v_count$')         := v_cnt;
    mystrings('$MyNewParamAddedToProc$') := p_MyNewParamAddedToProc; <--new parameter introduced to code

      ..
END;

do you agree?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35482600
"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.




0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35482628
>>> I was attempting to avoid hardcoding

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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35482816
>>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.
0
 

Author Comment

by:gram77
ID: 35484605
I see sense in what sdstuber is saying
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35484634
thanks,  I do try  :)
0
 

Author Comment

by:gram77
ID: 35496597
slightwv:
"If the users want to add '$myname$', what do you want your code to do with it? " --do nothing just print $myame$
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35497100
>>> just print $myname$


using the array method I described above, this is exactly the functionality you'll get
0

Featured Post

Configuration Guide and Best Practices

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question