Search code examples
javascriptoracle-apex

how to run SQL script in js in orcale apex

i want to run sql script inside Execute when Page Loads in orcale apex

example to explain what i want

var result = "SELECT column_name FROM table_name WHERE condition;";

console.log(result);
Solution

  • That's a Page Load dynamic action (DA).

    One option to do what you asked for is to set two steps for that DA. Here's how:

    • the first step is to create action whose type is "Execute server-side code". As it is PL/SQL, its SELECT requires an INTO clause. Into what? A hidden page item (I named it P2_RESULT).

      Also, you should pay attention to possible NO_DATA_FOUND or TOO_MANY_ROWS errors, but - let's presume that query really returns one and one only row. I'm just counting employees:

      select count(*) into :P2_RESULT from emp;

      Set "Items to return" property to P2_RESULT.

    • the second step is "Execute JavaScript code":

      console.log($v("P2_RESULT"));

    Run the page; when executed, it results in

    enter image description here

    (i.e. there are 13 employees in that table).