Quick Tip: Stripping out your Java/Python from your SQL

Have some SQL in your Java or Python app that needs some work?

try (PreparedStatement p = c.prepareStatement("select sum(salary), department_id \n" + "from employees \n" +
"where salary > 2500 \n" + "group by department_id"))

All of those nasty string concatenations and carriage returns and the string assignment itself…

I just want to see the SQL in my SQLDev 🙁

Embed/Expose…in this case we can tell that’s Java code and we’ll automatically EXPOSE the SQL for you.

The Movie

Did you say Python?

Yes, yes I did.

Let’s say I have this code:

CREATE TABLE emps
    AS
        SELECT employee_id,
               first_name,
               last_name,
               hire_date,
               job_id,
               salary,
               department_id
          FROM employees;

If I invoke the same right-click –

Which will give me –

var sql = "CREATE TABLE emps\n"+
"    AS\n"+
"        SELECT employee_id,\n"+
"               first_name,\n"+
"               last_name,\n"+
"               hire_date,\n"+
"               job_id,\n"+
"               salary,\n"+
"               department_id\n"+
"          FROM employees;";

And if I right-click and do the Embed/Expose thing again…it’ll strip out the string concats, the carriage returns and the var sql bits.

And PL/SQL?

Yes, and PL/SQL.

I can get this code generated (or removed):

SQL VARCHAR2(4000) := 'CREATE TABLE emps '|| CHR(10)  || 
'    AS '|| CHR(10)  || 
'        SELECT employee_id, '|| CHR(10)  || 
'               first_name, '|| CHR(10)  || 
'               last_name, '|| CHR(10)  || 
'               hire_date, '|| CHR(10)  || 
'               job_id, '|| CHR(10)  || 
'               salary, '|| CHR(10)  || 
'               department_id '|| CHR(10)  || 
'          FROM employees;';