Hey everyone, I'm trying to figure out how to use dynamic sql in Application Express. Specifically, I'm trying to write a query using substitution/bind variables. I don't know if you need a description of the application or not, but here is the query which will run in SQL Plus:
ACCEPT file_id PROMPT "Enter the file_id (a number, e.g. 3): "
-- Preliminary Query, to set table_name
COLUMN reference_id_col NEW_VALUE reference_id
COLUMN table_name_col NEW_VALUE table_name
SELECT f.reference_id AS reference_id_col
, t.reference_table AS table_name_col
FROM aa_files f
JOIN aa_type t ON f.file_type = t.type_id
WHERE f.file_id = &file_id
;
-- Main Query
SELECT *
FROM &table_name
WHERE reference_id = &reference_id
;
So there are three substitution variables, &reference_id, &file_id and &table_name. My basic setup is I have a table full of table names, so I query it to get one table name into a variable, then query that table for my data.
I'm working inside the SQL Commands window to try and figure out the right syntax to make this work in APEX. I'm fairly sure APEX doesn't allow the use of substitution variables, since they're a client side thing of SQL Plus right?
I'm still a student, so I'm not experienced with Oracle or Application Express, but from reading the APEX docs and googling, I figure that bind variables should be able to do this for me, but I'm not sure how. I've been looking for some examples or documenation about using dynamic SQL in APEX, but I haven't been able to find anything. I'm pretty sure I could do this using a PL/SQL block, but I would rather do it in pure SQL if possible.
So if anyone could help me out by either giving me an example of what syntax I would have to use to write a query that will run in Application Express, or could point me towards documentation about how to use dynamic sql in Application Express, it would be greatly appreciated!
If you need more information about my application, I can post my create table statements and some inserts of sample data, if that would be helpful.
Thank you!
Edited by: Username on 21-Jun-2010 7:34 AM