I was created a SQL Script on Oracle APEX. That SQL will be maintained and updated, but we don't want to change the batch file that has already been created.

Therefore, I want to make the SQL executable using the name of the SQL created in Oracle APEX. (For example, first get the SQL body from the name, and then execute that SQL the second time.) Is it possible to use SQLPlus to achieve this?

(Maybe the information of the Script I created is in a table somewhere in the DB?)

CodePudding user response：

The SQL of the sql scripts can be queried. There is a apex dictionary view APEX_WORKSPACE_SQL_SCRIPTS that has the script name and the actual script. So what you could do is create a pl/sql procedure that takes the script and runs it using "execute immediate". Invoke that pl/sql procedure via your sql script in windows. A couple of notes:

• The script is stored in a column of datatype BLOB so you'll have to do a conversion to clob
• EXECUTE IMMEDIATE will not give you the normal output you'll see in your script (x rows inserted, table created, etc,...). You'll have to write your own code if you want to get similar output.