PL / SQL Error

[Joe Harry]

Guys,

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

create or replace procedure drop_object (objName VARCHAR )
IS
    objType all_objects.object_type%type;
    logStr VARCHAR2 ( 255 ) ;
BEGIN
    SELECT object_type INTO objType
    FROM all_objects
    WHERE object_name = upper ( objName );
    logStr := 'DROP '|| objType||' '||objName;
    IF objType = 'TABLE' THEN
        logStr := logStr||' cascade constraints';
    END IF;
    EXECUTE IMMEDIATE logStr; // throws PLS-00103 in this line???
EXCEPTION
    WHEN no_data_found THEN
    NULL;
END;

[Rob Spoor]

So what error are you getting? TellTheDetails!

[Joe Harry]

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:

procedure create_q31_core_technology 
IS
    logStr VARCHAR2(4000);
BEGIN
logStr := 'CREATE TABLE q31_core_technology (
      Technology_Id      int(11) NOT NULL,
      Technology_Name    varchar2(20) NOT NULL,
      Technology_Version varchar2(20) DEFAULT NULL,
      Create_User        varchar2(10) NOT NULL,
      Update_User        varchar2(10) NOT NULL,
      Create_Date        timestamp NOT NULL DEFAULT SYSDATE,
      Update_Date        timestamp NOT NULL DEFAULT SYSDATE)';
        -- drop table if exists
    --drop_object('q31_core_technology');
    
        -- create table
EXECUTE IMMEDIATE logStr;

        -- create constraints    

EXCEPTION
    WHEN no_data_found THEN
    NULL;
END create_q31_core_technology;

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

exec my_package.create_q31_core_technology where my_package is where the procedure above exists

[Error] Execution (1: 1): ORA-00907: Missing Right Paranthesis
ORA-06512: in "JCERT_ADMIN.PC_CREATE_TABLES", Line 77
ORA-06512: in Zeile 1

Where Line 77 is the EXECUTE IMMEDIATE logStr;

Any pointers?

[Paul Clapham]

"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]

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]

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]

And thanks for pointing out the error.

[Rob Spoor]

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]

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]

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:

procedure create_q31_core_technology 
IS
    logStr1 VARCHAR2(4000);
    logStr2 VARCHAR2(4000);
    logStr3 VARCHAR2(4000);
    logStr4 VARCHAR2(4000);
BEGIN
logStr1 := 'CREATE TABLE q31_core_technology (Technology_Id number(11) NOT NULL,';
logStr2 := ' Technology_Name varchar2(20) NOT NULL, Technology_Version varchar2(20) DEFAULT NULL,';
logStr3 := ' Create_User varchar2(10) NOT NULL, Update_User varchar2(10) NOT NULL,';
logStr4 := ' Create_Date timestamp NOT NULL DEFAULT SYSDATE,Update_Date timestamp NOT NULL DEFAULT SYSDATE)';
        -- drop table if exists
    drop_object('q31_core_technology');
    
        -- create table
        EXECUTE IMMEDIATE logStr1 || logStr2 || logStr3 || logStr4;

        -- create constraints    

EXCEPTION
    WHEN no_data_found THEN
    NULL;
END create_q31_core_technology;

Facing the problem with the missing right parantheses. I'm sure that all my logStr's are well within the range.

[Martin Vajsar]

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.