Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00922 missing or invalid option (Oracle 11G)
ORA-00922 missing or invalid option [message #479596] Mon, 18 October 2010 08:55 Go to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
I am creating a stored procedure to create a table during run time using 3 passed in parameters to build the table name. Oracle is giving me the ORA-00922 missing or invalid operation error message on the EXECUTE IMMEDIATE statement of my stored procedure. I am new to writing stored procedures in Oracle and would appreciate any help that can be provided. I am pasting a copy of the stored procedures. I have also changed the names of the field names to be generic so I can post the code in this forum.

CREATE OR REPLACE PROCEDURE createTable(PARAMETER1 in string, PARAMETER2 in string, PARAMETER3 in string) IS

TABLE_NAME NVARCHAR2(50);
QUERY_STRING LONG;
BEGIN
TABLE_NAME := PARAMETER1 || '_' || PARAMETER2 || '_' ||PARAMETER3;
QUERY_STRING := 'CREATE TABLE USER.' ||TABLE_NAME||
'(FIELD1 NUMBER(10) NOT NULL,
FIELD2 NUMBER(10),
FIELD3 NVARCHAR2(50) NOT NULL,
FIELD4 NUMBER(10,0) DEFAULT (0) NOT NULL,
FIELD5 NUMBER(10,0) NOT NULL,
FIELD6 NCLOB NOT NULL,
FIELD7 DATE NOT NULL,
FIELD8 NUMBER(1) DEFAULT (0) NOT NULL),
CONSTRAINT PK_'||PARAMETER1||'_'||PARAMETER2||' PRIMARY KEY (FIELD2),
CONSTRAINT CHK_'||PARAMETER1||'_'||PARAMETER2||'_PROCESSED CHECK (PROCESSED IN (0,1))';
EXECUTE IMMEDIATE QUERY_STRING;

END;
Re: ORA-00922 missing or invalid option [message #479598 is a reply to message #479596] Mon, 18 October 2010 08:58 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Standard method of debugging dynamic sql:
Assign the dynamic sql to a variable (which you have done).
Output the variable using dbms_output or some other method.
Paste the output into sqlplus and see if it looks valid.

That said, creating tables in oracle is almost always a bad idea. I'd rethink your approach if I was you.

EDIT: typo

[Updated on: Mon, 18 October 2010 09:02]

Report message to a moderator

Re: ORA-00922 missing or invalid option [message #479599 is a reply to message #479596] Mon, 18 October 2010 08:58 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You cannot perform DDL through EXECUTE IMMEDIATE, I think. Are you sure that you've searched for this and there was no result?

MHE
Re: ORA-00922 missing or invalid option [message #479601 is a reply to message #479599] Mon, 18 October 2010 09:01 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Maaher wrote on Mon, 18 October 2010 14:58
You cannot perform DDL through EXECUTE IMMEDIATE, I think.


Course you can, people wouldn't dig themselves into such epic holes of dynamic mess if you couldn't.

SQL> begin
  2  execute immediate 'create table bob (a number)';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select 1 from bob;

no rows selected

SQL> 

Re: ORA-00922 missing or invalid option [message #479602 is a reply to message #479601] Mon, 18 October 2010 09:09 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
Thank you for the quick reply. The reason that I'm creating a table using a stored procedure is that the product I work on needs to create tables at run time on the fly. We have three schemas that we use in oracle and there is a part of the product that allows the customer to create a new area to work in thus the need for run time table creation. We do inserts into meta data in one schema and create tables in the other two. We are currently using text files to parse scripts and execute in code. We wanted to put them into stored procedures so we could make everything transactional allowing for better handling of the new record creation if anything goes wrong. Currently if something goes wrong than we have an incomplete set up and clean up is not easy. I have done this in SQL server and it works perfectly. Can I ask how would you handle creating tables at run time if you don't recommend using stored procedures? As I indicated I am new to writing stored procedures and new to programming in general.

Thanks,

Kevin
Re: ORA-00922 missing or invalid option [message #479603 is a reply to message #479601] Mon, 18 October 2010 09:09 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Sorry, you're right, cookiemonster. I don't know where I got that idea.

MHE

[Updated on: Mon, 18 October 2010 09:09]

Report message to a moderator

Re: ORA-00922 missing or invalid option [message #479605 is a reply to message #479602] Mon, 18 October 2010 09:17 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Oracle is different from SQL Server. I know that people tend to create intermediate tables in SQL Server but in Oracle you don't. Your entire approach seems flawed to me.

Perhaps you're looking for a global temporary table? You are creating tables with the same fixed design. Why not use an existing table? Oracle can handle large tables, huge data sets, ... .

MHE
Re: ORA-00922 missing or invalid option [message #479607 is a reply to message #479603] Mon, 18 October 2010 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I have done this in SQL server and it works perfectly.
a KLUDGE solution that works in SQL Server does not mean it is a viable solution for Oracle.
"temp" tables are rarely required in Oracle.
Re: ORA-00922 missing or invalid option [message #479611 is a reply to message #479607] Mon, 18 October 2010 09:24 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
At this point the product I work on won't be changed in terms of how it's designed and built. We are not creating temp tables in the database as the tables are used by the customer to process data. They have to be created dynamically because of the nature of our application providing the mechanism to create different areas to work in and test what they want to do in the product. The customer provides the input to the names of the tables. Due to the nature of the product it's the way it has to be for now. I have seen replies about how much of a kludge it is and that I shouldn't do it in oracle that way. I have yet to hear an alternative solution from anybody as to how it should be handled. The product is quit mature and making large scale changes to the database at this point are going to be very difficult to do and to sell the the appropriate stake holders...so I'm stuck with what I've got...anybody been there?
Re: ORA-00922 missing or invalid option [message #479615 is a reply to message #479596] Mon, 18 October 2010 09:36 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Did you follow the very first cookiemonster's advice? Because, this is correct way of debugging dynamic SQL. You would get this result after replacing the parameters with some values:
SQL> CREATE TABLE USER.TABLE_NAME
  2  (FIELD1 NUMBER(10) NOT NULL,
  3  FIELD2 NUMBER(10),
  4  FIELD3 NVARCHAR2(50) NOT NULL,
  5  FIELD4 NUMBER(10,0) DEFAULT (0) NOT NULL,
  6  FIELD5 NUMBER(10,0) NOT NULL,
  7  FIELD6 NCLOB NOT NULL,
  8  FIELD7 DATE NOT NULL,
  9  FIELD8 NUMBER(1) DEFAULT (0) NOT NULL),
 10  CONSTRAINT PK_P1_P2 PRIMARY KEY (FIELD2),
 11  CONSTRAINT CHK_P1_PARAMETER2_PROCESSED CHECK (PROCESSED IN (0,1));
FIELD8 NUMBER(1) DEFAULT (0) NOT NULL),
                                      *
ERROR at line 9:
ORA-00922: missing or invalid option

Now change it to valid static statement, then make the same changes in the dynamic one. Next time, please, process these steps yourself.
Re: ORA-00922 missing or invalid option [message #479616 is a reply to message #479611] Mon, 18 October 2010 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Then print the SQL before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.
Re: ORA-00922 missing or invalid option [message #479618 is a reply to message #479611] Mon, 18 October 2010 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
I doubt you'll find much advise on how to do this since most of us avoid this situation like the plague.
However I appreciate the fact that getting permission to significantly alter a large scale old application can be epically difficult.
If you tell us what the tables are being used for we may be able to suggest a simple alternative but failing that you're just going to have to fix your code and carry on. I did tell you how to diagnose your problem in my first reply.
Re: ORA-00922 missing or invalid option [message #479620 is a reply to message #479611] Mon, 18 October 2010 09:43 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
I did read the first post and have been trying to add some output to my stored procedure with no luck. I am getting no output. When I use PLSQL debugger I can see the sql statement in a tool tip but that's it. I have been trying to get it into something so I can try and execute it myself but as I indicated in my first post I am new to working with oracle in this way and have very little experience in doing any of this. So it is taking me longer to figure it out than it would if I had been doing this a while which I haven't...again I keep hearing the negative feedback on what I'm doing but haven't heard of an alternative solution for what I have to work with. If you are going to put it down than offer an alternative...that's all I'm asking.
Re: ORA-00922 missing or invalid option [message #479622 is a reply to message #479620] Mon, 18 October 2010 09:46 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) What tool are you using to run this procedure?
2) How do you expect us to suggest an alternative when the only concrete requirement you have given us is dynamic tables?
Re: ORA-00922 missing or invalid option [message #479625 is a reply to message #479620] Mon, 18 October 2010 09:49 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
I certainly do not want to come across as an unappreciative person. I am frustrated as I've been given a task for which I'm new at and was told to use this approach. If I can find an alternative approach that would be great so I'll attempt to describe what we do without divulging too much information that's unique to our application. The application processes transactional data and stores certain pieces of information in a production history table. The customer can build events to look for in their data and we'll remember when it happens. Because the information is critical is is stored in it's own table and can get very large 100 of millions of records. We also provide a built in test environment that allows the customer to build new event detection algorithms and test storing history in the test environment. We don't want to pollute the production data so we create a new table to store the test history. The customer can create as many test environments as they want. The system also produces output which the customer uses to conduct their business. Again we don't want to pollute the production environment with the test data. Again I am just trying to find the right way to do this in the environment I'm given and am appreciative of all the advice I can get. Thanks...
Re: ORA-00922 missing or invalid option [message #479626 is a reply to message #479625] Mon, 18 October 2010 09:50 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
I am using PL/SQL to attempt to debug the procedure.
Re: ORA-00922 missing or invalid option [message #479628 is a reply to message #479626] Mon, 18 October 2010 09:53 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
PL/SQL is the name of a language not a tool.
Do you mean PL/SQL Developer?

As for your test environments - if they're in different databases (which they should be) then why does that require dynamic tables?
Re: ORA-00922 missing or invalid option [message #479635 is a reply to message #479626] Mon, 18 October 2010 09:59 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
Yes I left off the developer. I am using PL/SQL developer version 7.15
Re: ORA-00922 missing or invalid option [message #479636 is a reply to message #479615] Mon, 18 October 2010 10:01 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
The key of your solution (bad approach or not) lies here:
flyboy wrote on Mon, 18 October 2010 16:36
Did you follow the very first cookiemonster's advice? Because, this is correct way of debugging dynamic SQL. You would get this result after replacing the parameters with some values:
SQL> CREATE TABLE USER.TABLE_NAME
  2  (FIELD1 NUMBER(10) NOT NULL,
  3  FIELD2 NUMBER(10),
  4  FIELD3 NVARCHAR2(50) NOT NULL,
  5  FIELD4 NUMBER(10,0) DEFAULT (0) NOT NULL,
  6  FIELD5 NUMBER(10,0) NOT NULL,
  7  FIELD6 NCLOB NOT NULL,
  8  FIELD7 DATE NOT NULL,
  9  FIELD8 NUMBER(1) DEFAULT (0) NOT NULL),
 10  CONSTRAINT PK_P1_P2 PRIMARY KEY (FIELD2),
 11  CONSTRAINT CHK_P1_PARAMETER2_PROCESSED CHECK (PROCESSED IN (0,1));
FIELD8 NUMBER(1) DEFAULT (0) NOT NULL),
                                      *
ERROR at line 9:
ORA-00922: missing or invalid option

Now change it to valid static statement, then make the same changes in the dynamic one. Next time, please, process these steps yourself.

Your syntax is not correct. Move the right bracket to the end of the creation statement.

For the next version I suggest you really rethink your logic. This is so bad.
MHE
Re: ORA-00922 missing or invalid option [message #479638 is a reply to message #479636] Mon, 18 October 2010 10:06 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
To debug this in PL/SQL developer:
Add the following line of code to your procedure immediately before the execute immediate line:
dbms_output.put_line(QUERY_STRING);


Run the procedure in a sql window. See output in output tab.
Re: ORA-00922 missing or invalid option [message #479642 is a reply to message #479636] Mon, 18 October 2010 10:11 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
I have been attempting to follow the original advice to get the output of my statement since I first received his reply. This is my first attempt at writing a stored procedure like this and the first time I've had to debug a stored procedure. I had to educate myself how to use the recommendation he suggested as I had never done it before. When I use the PL/SQL developer tool to debug the stored procedure I get no output from my output statements and it hangs on the execute statement. When I just test the stored procedure I get no output as well. I must not have added the debugging messages correctly. I added the following line after the begin statement dbms_output.enable; and I added the following two debugging lines after I create the sql statement dbms_output.put_line('table name = ' ||TABLE_NAME); dbms_output.put_line('query = ' ||QUERY_STRING);
Re: ORA-00922 missing or invalid option [message #479647 is a reply to message #479642] Mon, 18 October 2010 10:21 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
I ran the stored procedure from a query window and the output tab was empty. I see that I have invalid sql but I can't see the sql for some reason. Do I need to do anything else to see the output on the screen? Did I enable it correctly?
Re: ORA-00922 missing or invalid option [message #479649 is a reply to message #479647] Mon, 18 October 2010 10:31 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
OK, one thing that might foil your plan anyway:

- You state that the purpose of the whole exercise of moving from scripts to PL/SQL is to make the test system creation "transactional".

That won't work with your approach, since all DDL statements result in explicit commit, so there is no way for DDL statement to behave "transactional"
Re: ORA-00922 missing or invalid option [message #479650 is a reply to message #479647] Mon, 18 October 2010 10:38 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
I did get the stored procedure to work it was a problem with the syntax as it was indicated to me by somebody earlier. Believe me when I say I was attempting to debug this on my own and was merely seeking advice and guidance from those that have done it before. I still never got the output to the screen like I wanted but I did get it working. Thank you all for your feedback...
Re: ORA-00922 missing or invalid option [message #479654 is a reply to message #479650] Mon, 18 October 2010 10:46 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't need to use dbms_output.enable in PL/SQL developer. There is a checkbox on the output tab called enabled. Which does what it says.
try running a block like this to see if you get the output:
BEGIN
dbms_output.put_line('output!');
END;
Re: ORA-00922 missing or invalid option [message #479655 is a reply to message #479650] Mon, 18 October 2010 10:48 Go to previous messageGo to next message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
I spoke with someone that recommended using the execute immediate approach in the stored procedure after I explained what it was that I was looking for. Perhaps I was misunderstood by her...in any case that will foil my plans if that's the way that command works which it sounds like it does. Is that the only way to use a stored procedure to create a dynamic table on the fly? (flawed design aside)
Re: ORA-00922 missing or invalid option [message #479657 is a reply to message #479655] Mon, 18 October 2010 10:55 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The way you create the table is not really important. The fact THAT you create a table makes a "transactional approach" impossible, since the creation of a table breaks any transactional approach.

You could create your own "transaction system" by

1) create new user (schema)
2) do stuff in that new schema

and then either have the new schema when everything works OK, or drop the user again when something goes wrong.

That way you would either have a "valid" new schema or no new schema at all.

I would probably still do that with scripts, though, not with execute immediate. Execute immediate in that case just adds a new level of complexity without having any real benefits.
Re: ORA-00922 missing or invalid option [message #479658 is a reply to message #479655] Mon, 18 October 2010 10:58 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes.

And lets be clear - it's not execute immediate that does a commit. It's create table. It is physically impossible to make a process in oracle that creates a set of tables in a transactional way, as each ddl command is it's own transaction.
That said I doubt that it really needs to be 'transactional' - you just need a process that creates and drops tables in the same managable, repeatable way. Stored procs are ideal for this.
Re: ORA-00922 missing or invalid option [message #479661 is a reply to message #479658] Mon, 18 October 2010 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Application objects (table, etc.) should be STATIC between application version releases.
There is NO legitimate need to CREATE & DROP objects to process application data.
Re: ORA-00922 missing or invalid option [message #479662 is a reply to message #479658] Mon, 18 October 2010 11:06 Go to previous message
kjrooney91
Messages: 12
Registered: October 2010
Junior Member
Got it...thanks for all your help. I learned a lot today about how Oracle works. I have been playing in SQL server and Oracle seems to work much differently so this is a learning process for me. Again thank you all for your feedback it is much appreciated...
Previous Topic: gettting error when using clob in execute immediate
Next Topic: fetch system date using function
Goto Forum:
  


Current Time: Thu Apr 18 20:44:07 CDT 2024