Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Loading data using In DB: ORA-00907: missing right parenthesis

Mark_Lurie
8 - Asteroid

Running from either the Server or locally, when I run my workflow that is querying an Oracle db and writing to another Oracle table the process is successful for one month's of data.

 

However, when I take off the date filter and try to run it for everything I get the following error message:

 

Information
-1System settings restricted by the administrator, using the System Default Dedicated Sort/Join Memory size of 1023MB.
Information
-1Running at a Low Priority.
Information
3Executing PreSQL: "DROP TABLE "ODS_ENSURA_POLICY""
Error
3Executing PreSQL: "CREATE TABLE "ODS_ENSURA_POLICY" AS WITH "Tool2_c82d" AS (select cd.yearmo_num , c.custref , c.custref||c.custcheckdigit cust..." : ORA-00907: missing right parenthesis
Error
3Error running PreSQL on "NoTable": ORA-00907: missing right parenthesis
Information
-1Completed Event #1: After Run: Email to mark.lurie@homeserveusa.com

 

Is there something that I can do?

 

I get the same error if I manually truncate the table and try to do an Append, too.

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

@Mark_Lurie ,

 

It looks like your SQL is being truncated (my guess).  Please try (if possible) to simplify your in-db work.  You can use a dynamic output tool to capture the full SQL statements being passed.  There is a possibility that the driver has a linesize limit that you are exceeding.  Maybe the configuration limits the amount of SQL and you've exceeded that limit.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
VianneyM
Alteryx
Alteryx

Hi @Mark_Lurie,

 

The error that you are receiving comes directly from Oracle. 

 

There are some reasons why this can happen, as mentioned here: https://www.tekstream.com/resource-center/ora-00907-missing-right-parenthesis/

 

I recommend to have a look into your pre-sql statement, this might have a syntax problem

 

Error ORA-00907 can commonly occur in commands such as CREATE TABLE, CREATE CLUSTER, and INSERT, which all require an itemized list enclosed in parentheses. It can also occur within subqueries such as WHERE clauses, UPDATE table SET column = (SELECT…) statements.

In the following example, the missing right parenthesis after “20” would throw error ORA-00907:

CREATE TABLE employee

(

employee_name VARCHAR(20 NOT NULL,

employee_phone VARCHAR(10) NOT NULL,

PRIMARY KEY(employee_name)

)

This error also occurs often in cases in which quotation marks are improperly used. If you are using single quotation marks in phrases that are enclosed by other single quotation marks, you must add another single quotation by the inner single quotation. In other words, you cannot use 4 single quotation marks together (‘___’___’___’). It must be written as ‘___”___”___’.

 

Are you using in-db tools? or you are just connecting to the DB?

 

Best,

Vianney

Best,
Vianney
Mark_Lurie
8 - Asteroid

Mark,

 

Using that Dynamic Output In-DB tool did the trick!

 

Looking at the SQL statement, it appears that Alteryx wraps parenthesis around the SQL Statement but at the end of mine, I had a commented the last line out, eg

--WHERE CURRENT_MONTH_FLAG = 1 ) 

When Alteryx tried to execute the SQL Statement it couldn't find the ending parenthesis and thus the process abended.

 

Workflow has be been running 10 minutes now, way longer than before, so I am hopeful that this is going to work.

 

Thanks again!

 

Mark

 

Labels