Hello All,
I am new to CA Service catalog and PAM technology and facing following issue in one of my PAM process.
In CA PAM 12.9.
I want to pass the system date to the INSERT statement using input parameter in the format of dd-MMM-yy hh:mm:ss AM/PM (for e.g. 28-Nov-17 02:01:28 PM).
I am using now() function to get the system date, this gives me the date & time in dd/mm/yyy hh:mm:ss AM/PM format (for e.g. 28/11/2017 02:07:53 PM). I am converting this date into desired format by using formatDate().
for e.g. formatDate(now(),'dd-MMM-yy hh:mm:ss a')
The query I am using to insert the values is as follow:
INSERT INTO table_name (col1,col2,col3,col4) select '?','?','?','?' from dual where not exists(select 'x' from table_name where col1 = '?')
Here, In my database table
col1, col2 and col4 are of type NVARCHAR and col3 is of timestamp(6)
My Input Parameters are :
Process.ID
Process.State
formatDate(now(),'dd-MMM-yy hh:mm:ss a')
Process.cust
Process.ID
After executing the query it gives me the error 'ORA-01858: a non-numeric character was found where a numeric was expected’
I try to modify my Insert statement by using to_timestamp() and to_date() functions of sql.
Following are the modification that I did into my Insert statement:
1.
INSERT INTO table_name (col1,col2,col3,col4) select '?','?',TO_TIMESTAMP('?',DD-MON-YY HH24:MI:SS')','?' from dual where not exists(select 'x' from table_name where col1 = '?')
2.
INSERT INTO table_name (col1,col2,col3,col4) select '?','?',to_date('?',DD-MON-YY HH24:MI:SS')','?' from dual where not exists(select 'x' from table_name where col1 = '?')
But the error still persist.
Any help is really appreciated.