• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PL / SQL Error

 
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Guys,

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



 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So what error are you getting? TellTheDetails!
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And thanks for pointing out the error.
 
Rob Spoor
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic