Home » SQL & PL/SQL » SQL & PL/SQL » Alter statement issue in PL/SQL block (oracle 10g)
Alter statement issue in PL/SQL block [message #620649] Mon, 04 August 2014 17:27 Go to next message
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 #620650 is a reply to message #620649] Mon, 04 August 2014 17:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You can't do DDL (ALTER) inside PL/SQL directly.
You must (ab)use EXECUTE IMMEDIATE, but you must first properly handle the issue regarding single quote marks.
Re: Alter statment issue in PL/SQL block [message #620652 is a reply to message #620650] Mon, 04 August 2014 18:22 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thanks BS.

Sorry i didnt get you on "but you must first properly handle the issue regarding single quote marks". Would be great if you give some code snippet example how to handle in procedure.
Re: Alter statment issue in PL/SQL block [message #620653 is a reply to message #620652] Mon, 04 August 2014 18:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
unable to to use SEARCH or GOOGLE yourself?

http://www.oracle.com/pls/db121/search?remark=quick_search&word=execute+immediate

when all else fails Read The Fine Manual

why is ALTER statement inside the LOOP? ALTER only needs to be done once before the loop starts.

Be sure to post corrected procedure here after it works without error.

[Updated on: Mon, 04 August 2014 18:40]

Report message to a moderator

Re: Alter statment issue in PL/SQL block [message #620654 is a reply to message #620653] Mon, 04 August 2014 18:46 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Sorry 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
Re: Alter statment issue in PL/SQL block [message #620655 is a reply to message #620654] Mon, 04 August 2014 18:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gorants wrote on Mon, 04 August 2014 16:46
Sorry 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 Go to previous messageGo to next message
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 #620657 is a reply to message #620656] Mon, 04 August 2014 19:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Sorry i didnt get you on "but you must first properly handle the issue regarding single quote marks".

  1  declare
  2  begin
  3  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-YYYY HH24:MI:SS''';
  4* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> 


Re: Alter statment issue in PL/SQL block [message #620658 is a reply to message #620657] Mon, 04 August 2014 19:09 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Great thanks for your help
Re: Alter statment issue in PL/SQL block [message #620659 is a reply to message #620657] Mon, 04 August 2014 19:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Then print the variable before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.
Re: Alter statment issue in PL/SQL block [message #620661 is a reply to message #620658] Mon, 04 August 2014 21:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
gorants wrote on Mon, 04 August 2014 20:09
Great 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 Go to previous messageGo to next message
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 #620725 is a reply to message #620723] Tue, 05 August 2014 10:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Bill, guess you missed "values" keyword. Or did I miss something?
Re: Alter statment issue in PL/SQL block [message #620728 is a reply to message #620725] Tue, 05 August 2014 11:09 Go to previous messageGo to next message
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 Go to previous message
Solomon Yakobson
Messages: 3269
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.
Previous Topic: time slots of 1 hour within a fixed working hours
Next Topic: How to make logic
Goto Forum:
  


Current Time: Thu Mar 28 18:44:08 CDT 2024