How do I pass a table as parameter on Ajax callback

Question

I want to pass a table as parameter on an ajax callback procedure in Oracle APEX 5, because I need to make an SQL query on that table.

The SQL process is stored as shared component inside the Apex 5 application. Screenshot

My procedure is like this

(procedure name: THIS_PROCESS)

declare
    v_tablename varchar(128);--max table_name lenght
    v_ID number;

    v_somevar
BEGIN
    SELECT Columname, 
    INTO v_somevar
    FROM v_tablename
    WHERE ID = v_ID;

       --Do stuff           
END;

This code (FROM v_tablename) gives me a compilation error:

ORA-00942: table or view does not exist ORA-06550: line 9, column 5: PL/SQL: SQL Statement ignored

I'm a total newbie. I had been reading that I should call that procedure with this javascript:

apex.server.process ( "THIS_PROCESS", {
        x01: "TABLENAME",
        x02: "Row_ID",

        pageItems: "#P1_Item,#P2_Item"
  },{
        success: function( pData )
            // do something here
        }
  } );

I do not understand why I should pass x01 and x02 instead of v_tablename and v_ID

Do x01 and x02 automatically are assigned to v_tablename and v_ID?

Answer 1

Here's an example page process THIS_PROCESS of type "Ajax Callback". Note that you need Dynamic SQL to select from a table name that isn't hardcoded.

declare
    v_table varchar2(128) := apex_application.g_x01;
    v_id number := apex_application.g_x02;
    v_somevar varchar2(100);
    v_sql varchar2(4000);
begin
    -- validate v_table parameter to avoid sql injection. will throw exception if it fails
    select table_name into v_table from all_tables where table_name = v_table;

    v_sql := 'SELECT Columname
    FROM ' || v_table || '
    WHERE ID = :A1';
    execute immediate v_sql into v_somevar using v_id;
    -- do something with v_somevar
end;

Do be careful with this sort of thing - this design will allow a malicious user to write their own javascript function which can pass any table name that it likes to your procedure.