Problem solve Get help with specific problems with your technologies, process and projects.

What is recursive SQL?

What is recursive SQL?

What is recursive SQL?

When you issue a SQL statement, the database must determine many things before it can even start executing that SQL statement. If you query a table, do you have permissions to access that table? Does that table even exist? Which tablespace holds that table? Oracle needs answers to these questions and more.

One of the things that Oracle is really good at is processing SQL statements. So if Oracle needs to find out if a table exists, Oracle will issue a SQL statement against the Data Dictionary to answer that question. This is similar to you querying DBA_TABLES to determine if the table exists. All of the SQL that Oracle issues behind the scenes on your behalf is called "recursive SQL." Some of the recursive SQL statements will require other questions to be asked, which spawns a new level of recursive SQL.

If Oracle finds a problem with your SQL statement, like the table does not exist, then Oracle will raise an error (ORA-00942). If Oracle does not find any problem, then your SQL statement will be executed. Executing your SQL statement may require additional recursive SQL statements.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.