| alter session fails [message #320878] |
Fri, 16 May 2008 10:00  |
swas_fly
Messages: 150
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  |
anacedent
Messages: 6201
Registered: July 2005
Location: surf meets turf in 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 |
swas_fly
Messages: 150
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 |
Michel Cadot
Messages: 17653
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
";" 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 |
swas_fly
Messages: 150
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 |
anacedent
Messages: 6201
Registered: July 2005
Location: surf meets turf in 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 |
swas_fly
Messages: 150
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 |
Michel Cadot
Messages: 17653
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
";" 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 |
flyboy
Messages: 519
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.
|
|
|
]
Members
Search
Help
Register
Login
Home
