Oracle Forms

Hi all,

[Edit; I'm using Oracle Forms from the 10g Developer Suite]

Depending where and how I'm using SYSDATE, it returns the date in various formats

The Goal: Have SYSDATE always return in the format of 8/16/11 10.38.15.000000 AM

I think this is MM/DD/YYYY HH24:MI:SS based off the following (Under the assumption that .000000 comes after SS due to system defaults even if FF isn't declared)

^^^ Updated, I have literally gone insane and don't even know what I want the form to do anymore after all these dates/formats/timestamps/errors


These are a number of scripts I ran on the server just to gather some information and see what happened;

-----
- select * from NLS_DATABASE_PARAMETERS
- returns
- DD-MON-RR (for NLS_DATE_FORMAT)
- DD-MON-RR HH.MI.SSXFF AM (for NLS_TIMESTAMP_FORMAT)
-----
- select SYSDATE from dual;
- returns
- 16-AUG-11
-----
- select TO_CHAR(SYSDATE, 'DD-MON-RR HH24:MI:SS') from dual;
- returns:
- 16-AUG-11 14:53:52 (What I want, under the assumption that the .000000 will be added automatically)
-----




This part of the code is used to read from a table that has a default_value column. All other entries in this column are normal text strings, but when the entry reads User or Sysdate, I want to populate that actual data (as opposed to the word User/Sysdate). User works fine, however Sysdate is giving me trouble.

	
if Upper(p_default_value_txt) = 'USER' then
     Copy(User, :System.Cursor_Item);
			
elsif Upper(p_default_value_txt) = 'SYSDATE' then 														   
     Copy(SYSDATE, :System.Cursor_Item);	
			
else Copy(p_default_value_txt, :System.Cursor_Item);
		
end if;	

Using plain SYSDATE populates the field with "16-AUG-0011 00:00:00"

I've tried a number of other things, the closest I've got is using

Copy(TO_CHAR(SYSDATE, 'DD-MON-RR HH24:MI:SS'), :System.Cursor_Item);

Which returns "16-AUG-0011 15:43:22" ...still with the extra two 0's in the year section

I've also tried without TO_CHAR, replacing TO_CHAR with TO_DATE, and other formats like 'DD-MON-YY', nothing giving the exact desired format.


Another issue that's bothering me is when when I press submit, the form is what has been put into the field and use it to build a SQL that will be run on the server (In this case the timestamp is a SET condition on an UPDATE)

I see the following errors when I try to submit;

ORA-00904: "AUG": invalid identifier
ORA-06512: at "ANF.AF_EXECUTE_DYNAMIC_SQL", line 11

I would be surprised if the increased year formatting is causing the "AUG" error, which makes me think I might have another problem besides the formatting. The second error links to an external function which runs the SQL, however there is no issue on any other SQL I run without a timestamp field. The code for that function is as follows

create or replace
FUNCTION     af_execute_dynamic_sql
       (p_sql_statement IN VARCHAR2)
   RETURN NUMBER
IS

   L_row_count NUMBER := 0;

BEGIN

   execute immediate p_sql_statement;
   L_row_count := SQL%ROWCOUNT;
   
   RETURN L_row_count;
END;

Lastly, I use SYSDATE elsewhere in my code and it does exactly what I want it to;

	IF L_alert = ALERT_BUTTON1 THEN
    	     INSERT into IT_SQL_LOG (EXEC_SQL_TXT, ROWS_AFFCTD_NUM, USER_ID, CHANGE_DT) Values (:global.final_sql, L_rowcount, User, SYSDATE);
      	     commit;
	ELSE
    	     rollback;
	END IF;

When I go into IT_SQL_LOG table, the CHANGE_DT column has entries with the following (correct) formatting;
16-AUG-11 10.38.15.000000 AM


So somehow when I use 'SYSDATE' to populate a field and then build a SQL on the fly with a parameter taken from that field...I 1) don't get the desired format and 2) am getting an invalid identifier from my month...

My next thought was to make an external function that gets called right as the form loads to do an alter session no nls_date_format or whatnot to try and force SYSDATE to the desired formatting...or to set_item_property initial_value to $$DATETIME$$...Just something I read. I'm not sure external DDL functions are necessary or if they're the best solution though, I'm quite new to PL/SQL coding so I'm sure there's something I'm missing.


Thanks in advance for any input, sorry for the drawn out response...I'm starting to go crazy with all these different dates and formats;

Travis

Edited by: TravC on Aug 16, 2011 1:19 PM

Edited by: TravC on Aug 17, 2011 10:08 AM

Edited by: TravC on Aug 17, 2011 10:09 AM