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

[Mark_Lurie]

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	-1	System settings restricted by the administrator, using the System Default Dedicated Sort/Join Memory size of 1023MB.
Information	-1	Running at a Low Priority.
Information	3	Executing PreSQL: "DROP TABLE "ODS_ENSURA_POLICY""
Error	3	Executing 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	3	Error running PreSQL on "NoTable": ORA-00907: missing right parenthesis
Information	-1	Completed 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.

[MarqueeCrew · Accepted solution]

@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

[VianneyM]

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

[Mark_Lurie]

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