| Alter statement issue in PL/SQL block [message #620649] |
Mon, 04 August 2014 17:27  |
 |
gorants
Messages: 85
Registered: May 2014
Location: ATL
|
Member |
|
|
Hello All,
How you doing. I have below code to execute. I need to set alter session other wise my insert statment is not executing. with below code i get errors
DECLARE
V_PID NUMBER := 0;
BEGIN
FOR CNTR in 1..2
LOOP
select TRAN_ID_SEQ.nextval into V_PID from dual;
BEGIN
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Insert into TRAN ((to_timestamp(sysdate - 90 ,'DD-MON-YYYY HH24:MI:SS'),to_timestamp(sysdate - 90 ,'DD-MON-YYYY HH24:MI:SS'),'HR001','0643XGR',null,1,V_PID,1,1194106,0,null,('HIRDER'|| LPAD(V_PID, 8, 0)),1,null);
IF CNTR = 2
THEN
COMMIT;
END IF;
END;
END LOOP;
END;
/
Exit
Errors :
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
*
ERROR at line 10:
ORA-06550: line 10, column 1:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
Can someone let me know how can i resolve this error
appreciate your help
[EDITED by LF: fixed topic title typo]
[Updated on: Tue, 05 August 2014 12:12] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Alter statment issue in PL/SQL block [message #620655 is a reply to message #620654] |
Mon, 04 August 2014 18:49  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
gorants wrote on Mon, 04 August 2014 16:46Sorry i have tried using execute update before posting here, but I didnt post all tried attempts here.
Not sure if i have missed something i will reverify .. Thanks for your patience and help
EXECUTE UPDATE?
Excuse me & forgive me, but I don't believe you previously tried EXECUTE IMMEDIATE.
[Updated on: Mon, 04 August 2014 18:50] Report message to a moderator |
|
|
|
| Re: Alter statment issue in PL/SQL block [message #620656 is a reply to message #620655] |
Mon, 04 August 2014 18:56 |
|
gorants
Messages: 85
Registered: May 2014
Location: ATL
|
Member |
|
|
Trust me i tried like this but i got SP2-0552: Bind variable "MI" not declared. error
DECLARE
V_PID NUMBER := 0;
BEGIN
[color=green]EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'';[/color]
FOR CNTR in 1..2
LOOP
select PROD_TRKG_TRAN_ID_SEQ.nextval into V_PID from dual;
BEGIN
..
..
END[Updated on: Mon, 04 August 2014 18:58] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Alter statment issue in PL/SQL block [message #620661 is a reply to message #620658] |
Mon, 04 August 2014 21:21 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
gorants wrote on Mon, 04 August 2014 20:09Great thanks for your help
Funny thing is you don't need that ALTER at all. TO_TIMESTAMP first parameter is string while you are passing date. As a result oracle implicitly converts date sysdate - 90 to string. That's why you have to use ALTER to set default date format. You could simply use
to_timestamp(to_char(sysdate - 90,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
But correct way would be using CAST:
SQL> select to_timestamp(to_char(sysdate - 90,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') x,
2 cast(sysdate - 90 as timestamp(9)) y
3 from dual
4 /
X
---------------------------------------------------------------------------
Y
---------------------------------------------------------------------------
06-MAY-14 10.16.58.000000000 PM
06-MAY-14 10.16.58.000000000 PM
SQL>
And since columns are declared as timestamp there is a good chance you need better than sysdate precision provided by systimestamp:
SQL> select systimestamp - interval '90' day x
2 from dual
3 /
X
------------------------------------------------
06-MAY-14 10.16.59.406000000 PM -04:00
SQL>
SY.
[Updated on: Mon, 04 August 2014 21:26] Report message to a moderator |
|
|
|
| Re: Alter statment issue in PL/SQL block [message #620723 is a reply to message #620661] |
Tue, 05 August 2014 10:33 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
as the other people said, get rid of the alter and simply
Insert into TRAN ((systimestamp - 90 ,systimestamp - 90 ,'HR001','0643XGR',null,1,V_PID,1,1194106,0,null,('HIRDER'|| LPAD(V_PID, 8, 0)),1,null);
|
|
|
|
|
|
| Re: Alter statment issue in PL/SQL block [message #620728 is a reply to message #620725] |
Tue, 05 August 2014 11:09 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Yes, I made a typo. Nice catch. It should have been
Insert into TRAN values(systimestamp - 90 ,systimestamp - 90 ,'HR001','0643XGR',null,1,V_PID,1,1194106,0,null,('HIRDER'|| LPAD(V_PID, 8, 0)),1,null);
|
|
|
|
| Re: Alter statment issue in PL/SQL block [message #620765 is a reply to message #620728] |
Tue, 05 August 2014 15:15 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Well, systimestamp - 90 is worse than sysdate - 90. There is no such thing as timestamp arithmetic, so systimestamp - 90 causes implicit timestamp to date conversion then date arithemitic is applied to subtract 90 and then result is converted back to timestamp since it looks like target table column type is timestamp. So there is (conversion wise) a difference subtracting number from timestamp versus subtracting interval.
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
