• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PL / SQL Error

 
Joe Harry
Ranch Hand
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Guys,

I'm trying the following script on 11g Oracle to drop a table if exists:



 
Rob Spoor
Sheriff
Pie
Posts: 20610
63
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So what error are you getting? TellTheDetails!
 
Joe Harry
Ranch Hand
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have mentioned the error in my code above. Anyways, I got rid of that error. Now facing another one and I really see no reason as to why this should happen.

Here is my procedure:



When I try to execute the above procedure, I get the following error



Where Line 77 is the EXECUTE IMMEDIATE logStr;

Any pointers?
 
Paul Clapham
Sheriff
Posts: 21322
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Missing Right Paranthesis" is pretty clear, I think (even though it's misspelled -- not very professional on the part of the database maker). And if you took that string starting with "CREATE TABLE" and assigned it to a variable of length 256, then it would be truncated and hence there would be a missing right parenthesis.
 
Joe Harry
Ranch Hand
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fixed the above problem as well. It was perhaps due to the wrong data type for the id. It should be number instead of an int.
 
Joe Harry
Ranch Hand
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:"Missing Right Paranthesis" is pretty clear, I think (even though it's misspelled -- not very professional on the part of the database maker). And if you took that string starting with "CREATE TABLE" and assigned it to a variable of length 256, then it would be truncated and hence there would be a missing right parenthesis.


Forgive the database maker. The spelling mistake was from me. Actually the error message was in German and I misspelled Parantheses.
 
Joe Harry
Ranch Hand
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And thanks for pointing out the error.
 
Rob Spoor
Sheriff
Pie
Posts: 20610
63
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe Harry wrote:I have mentioned the error in my code above.

Sorry, I missed it. People don't expect error messages inside code blocks.
 
Joe Harry
Ranch Hand
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rob Spoor wrote:
Joe Harry wrote:I have mentioned the error in my code above.

Sorry, I missed it. People don't expect error messages inside code blocks.


No problem. I'm happy that I could resolve it. Good to learn it by doing.
 
Joe Harry
Ranch Hand
Posts: 10124
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:"Missing Right Paranthesis" is pretty clear, I think (even though it's misspelled -- not very professional on the part of the database maker). And if you took that string starting with "CREATE TABLE" and assigned it to a variable of length 256, then it would be truncated and hence there would be a missing right parenthesis.


Guess I might need help again. I modified the script and to look like as below:


Facing the problem with the missing right parantheses. I'm sure that all my logStr's are well within the range.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I took the statement you're executing using execute immediate, reformatted it and run it in Sql plus. The error was indicated on the line containing DEFAULT SYSDATE. It seems that in Oracle the DEFAULT clause must precede any constraints defined on a column, and NOT NULL is a constraint. Change the declaration to TIMESTAMP DEFAULT SYSDATE NOT NULL.

Admittedly, the error message is rather cryptic. But taking the command out of the PL/SQL code and executing it separately helps resolving the issue.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic