Home » SQL & PL/SQL » SQL & PL/SQL » alter session fails (oracle 10g,win xp)
alter session fails [message #320878] Fri, 16 May 2008 10:00 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
1 create or replace procedure delete_id(e_no number)
2 as
3 begin
4 alter session enable parallel dml;
5 delete from emp_test where empno=e_no;
6* end;
SQL> /

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.46
SQL> show error
Errors for PROCEDURE DELETE_ID:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PLS-00103: Encountered the symbol "ALTER" when expecting one of
the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe


I need to alter the session for parallel dml operation,but in the procedure i face such error.How can we correct this?
Re: alter session fails [message #320879 is a reply to message #320878] Fri, 16 May 2008 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>How can we correct this?
EXECUTE IMMEDIATE
Re: alter session fails [message #320895 is a reply to message #320879] Fri, 16 May 2008 11:23 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
SQL> create or replace procedure delete_id(e_no number)
2 as
3 var varchar2(100);
4 begin
5 var:='alter session enable parallel dml;';
6 execute immediate var;
7 delete from emp_test where empno=e_no;
8 end;
9 /

Procedure created.

Elapsed: 00:00:00.01
SQL> exec delete_id(7934);
BEGIN delete_id(7934); END;

*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "SCOTT.DELETE_ID", line 6
ORA-06512: at line 1


getting the above error??
Re: alter session fails [message #320896 is a reply to message #320895] Fri, 16 May 2008 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
";" is not part of SQL.

Regards
Michel
Re: alter session fails [message #320897 is a reply to message #320878] Fri, 16 May 2008 11:39 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
when i remove the ; i get compilation error,

how can the systax be corrected?
Re: alter session fails [message #320898 is a reply to message #320878] Fri, 16 May 2008 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Why do you REFUSE to use CUT & PASTE as specified in Posting Guidelines above?

You're On Your Own (YOYO)!
Re: alter session fails [message #320902 is a reply to message #320898] Fri, 16 May 2008 12:02 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
SQL> create or replace procedure delete_id(e_no number)
2 as
3 var varchar2(100);
4 begin
5 var:='alter session enable parallel dml;'
6 execute immediate 'var';
7 delete from emp_test where empno=e_no;
8 end;
9 /

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.06
SQL> show error
Errors for PROCEDURE DELETE_ID:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PLS-00103: Encountered the symbol "EXECUTE" when expecting one of
the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
Re: alter session fails [message #320908 is a reply to message #320902] Fri, 16 May 2008 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
";" is not part of SQL but it is part of PL/SQL.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: alter session fails [message #320909 is a reply to message #320878] Fri, 16 May 2008 12:27 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Your code contains more semicolons. You removed the wrong one.
If you do not want to try remove all semicolons one-by-one, there is a chance to get the right direction from the documentation, found eg. online on http://tahiti.oracle.com/.

As your problem concerns EXECUTE IMMEDIATE statement, you will find the desired info when searching for this term.
Previous Topic: alter tablespace test_tbs read only hangs
Next Topic: Display avilable reco
Goto Forum:
  


Current Time: Thu Dec 08 02:03:33 CST 2016

Total time taken to generate the page: 0.11877 seconds