how to invoke ALTER SESSION ... from Report 6i? 1

[navrsalemile]

i need to change default NLS language from a report using ALTER SESSION ... SQL statement but do not know how and where to put statement in the report. should i use DBMS_SESSION instead and how?

 

[nagornyi]

Look into SRW.DO_SQL procedure.

 

[navrsalemile]

Thanks! should I put srw.do_sql inside Program Unit? or external pl/sql library? or before report trigger invoking column function invoking program unit? can i directly call dbms_session from program unit and how?

many thanks,
mile

 

[nagornyi]

You may put it in any program unit, one of the report triggers would be better. AFAIK, yes, you can call dbms_session. Just type it as SQL text in the SRW.DO_SQL precdcure as you would do in SQL+.

 

[navrsalemile]

thanks, the text field on the report which was supposed to change after i changed session nls parameter did not change. it seems it gets its value (current date) from the operating system (Windows PC) but not sure. i want to change it so it gets its value from Oracle sysdate. i tried with
srw.set_field_date (0, sysdate);
in the format trigger but no success. i cannot change data source for this field in the property to be for example a column formula.
here are the properties of this field:
source: Current Date
source data type: Date
format: fmDay Month DD RRRR HH:MI AM
print object on: first page
base printing on: enclosing object
keep with anchoring object: No

thanks,
Mile

 

[nagornyi]

Try sourcing the field with formula column of type Date returning sysdate.

 

[navrsalemile]

i tried that with no success. i created a new program unit which returns date, compiled it, but the drop-down list in Source property does not show new program unit!? it contains some preexisting elements including column functions, but not the new one i just created

 

[nagornyi]

You don't need program unit, just formula column. Do this.
1. In the data model window, click on the formula column tool (that with "+" sign.
2. Click in any place on the data model free from other object.
3. If property window does no open automatically, double click on the new formula object to open its property palette.
4. Give name: CF_SYSDATE
5. Make Datatype: Date
6. Click PL/SQL Formula and between the "begin" and "end" type in the following line:
return sysdate;
7. Click Compile Button
8. Now, source your field (that should also be of Date type) with CF_SYSDATE.

 

[nagornyi]

You don't need program unit, just formula column. Do this.
1. In the data model window, click on the formula column tool (that with "+" sign.
2. Click in any place on the data model free from other object.
3. If property window does no open automatically, double click on the new formula object to open its property palette.
4. Give name: CF_SYSDATE
5. Make Datatype: Date
6. Click PL/SQL Formula and between the "begin" and "end" type in the following line:
return sysdate;
7. Click Compile Button
8. Now, source your field (that should also be of Date type) with CF_SYSDATE.

 

[nagornyi]

You don't need program unit, just formula column. Do this.
1. In the data model window, click on the formula column tool (that with "+" sign.
2. Click in any place on the data model free from other object.
3. If property window does no open automatically, double click on the new formula object to open its property palette.
4. Give name: CF_SYSDATE
5. Make Datatype: Date
6. Click PL/SQL Formula and between the "begin" and "end" type in the following line:
return sysdate;
7. Click Compile Button
8. Now, source your field (that should also be of Date type) with CF_SYSDATE.

 

[navrsalemile]

thanks for thorough explanation! I read about tool palette but did not know what is it (did not show up when I initially opened the report).
I only had to add to_char to the formula and chang type of column to char as it did not work by direct assignment of Date type (probably due to date being default format of OS date).

 

[nagornyi]

I am glad you solved the problem, but if you please.. specifically, what did not work?