28774

Explain FOR in oracle

Question:

I am making a test. I have all tests in rows, so my rows looks like this;

ID | TEST ---------------------------------- 1 | 'select sysdate from dual' 2 | 'select sysdatesss from dual'

Now I read it row by row and I need to test it with EXPLAIN PLAN FOR

so the for the first row it would be

EXPLAIN PLAN FOR select sysdate from dual

but I have problem converting the TEST field. Right now I use;

EXPLAIN PLAN FOR testing.TEST

but it does not work.

Any ideas?

Answer1:

A SQL statement is a string, but you have to use dynamic SQL to convert a SQL statement that is stored as a string.

FOR i IN (SELECT t.test FROM TESTING t) LOOP EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR '|| i.test ||''; END LOOP;

Reference:

<ul><li><a href="http://www.databasejournal.com/features/oracle/article.php/2109681/EXECUTE-IMMEDIATE-option-for-Dynamic-SQL-and-PLSQL.htm" rel="nofollow">EXECUTE IMMEDIATE</a></li> </ul>

Answer2:

EXEC IMMEDIATE 'EXPLAIN PLAN FOR ' || TESTING.TEST

Recommend

  • How to Count Rows from two Tables with one Command in MySQL
  • Javascript variables for Get http
  • Fail SonarQube quality gate when coverage decreases
  • Refactoring phase of the TDD Traffic Light - how to get this right?
  • Gradle test fails with NullPointerException
  • How do I run my Specs with the previous version of Rspec?
  • PostgreSQL 9.1 timezones
  • Is Android's ARGB_8888 Bitmap internal format always RGBA?
  • Does argparse support multiple exclusive arguments?
  • How to start server for Selenium grid Java Maven setup
  • C++/CLI Thread synchronization including managed and unmanaged code
  • Refactoring advice: maps to POJOs
  • Passing information to server-side function in a Google Docs Add On
  • Is is safe to use HSQLDB for production? (JBoss AS5.1)
  • @tailrec why does this method not compile with 'contains a recursive call not in tail position&
  • MAVEN : Run Multiple Maven Project using Maven Test
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • SQL Query - Table Joining Problems
  • Git describe fails to return most recent annotated tag
  • android google indoor map
  • Implementing “partial void” in VB
  • Convert Type Decimal to Hex (string) in .NET 3.5
  • What's the purpose of QString?
  • Zurb Foundation _global.scss meta styles for js?
  • How do I exclude a dependency in provided scope when running in Maven test scope?
  • Asynchronous UI Testing in Xcode With Swift
  • TFS: Get latest causes slow project reloading
  • angularjs unit test when to use $rootScope.$new()
  • Join two tables and save into third-sql
  • How to model a transition system with SPIN
  • ORA-29908: missing primary invocation for ancillary operator
  • Return words with double consecutive letters
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • SQL merge duplicate rows and join values that are different
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file
  • Unable to use reactive element in my shiny app
  • Converting MP3 duration time