I've been delving further into the Apex world this week and trying to create a simple application with the ability to create a table in any remote database. This required the use of a clever use of Apex (well i thought it was clever after it took me hours to get the damn thing working) and the use of a new (well i say new but i think i just missed it appearing in an earlier version) plsql built in procedure.
What i wanted to acheive was to have a screen in Apex where a user could 'define' what they wanted the table to look like - i.e. input a table name and all the column definitions. Something similar to the inbuilt wizard inside Apex to create tables. To start with i had no idea how to do this and began trawling google to try and borrow someone elses solution. However there didn't seem to be anything which did what i wanted but i made use of bits and pieces if things i did find to be able to build what i wanted.
The first difficult thing was to even decide how the screen to define the table could even be created - what i wanted didn't seem to be mentioned anywhere and i initially tried to create a form based on a temporary table as a possible solution but this didn;t get very far and i got increasingly frustrated with it.
Further googling revealed that a 'simple' html table with some clever bits of javascript could be used as the table definer piece of code i needed. There are lots of example of how to do this - I've pasted what my code actually looked like below but it's a combination of other code i found.
There are 3 main bits to it - the initial html table definition (at the bottom)
Some javascript to add a row to the html table (function addRow)
Some javascript to delete a row from the table. (function deleteRow)
<HTML>
<HEAD>
<TITLE> Add/Remove dynamic rows in HTML table </TITLE>
<SCRIPT language="javascript">
function addRow(tableID) {
var table = document.getElementById(tableID);
var rowCount = table.rows.length;
var row = table.insertRow(rowCount);
var colCount = table.rows[0].cells.length;
for(var i=0; i<colCount; i++) {
var newcell = row.insertCell(i);
newcell.innerHTML = table.rows[1].cells[i].innerHTML;
//alert(newcell.childNodes[0].value);
switch(newcell.childNodes[0].type) {
case "text":
newcell.childNodes[0].value = "";
break;
case "checkbox":
newcell.childNodes[0].checked = false;
break;
case "select-one":
newcell.childNodes[0].selectedIndex = 0;
break;
}
}
}
function deleteRow(tableID) {
try {
var table = document.getElementById(tableID);
var rowCount = table.rows.length;
for(var i=0; i<rowCount; i++) {
var row = table.rows[i];
var chkbox = row.cells[0].childNodes[0];
if(null != chkbox && true == chkbox.checked) {
if(rowCount <= 1) {
alert("Cannot delete all the rows.");
break;
}
table.deleteRow(i);
rowCount--;
i--;
}
}
}catch(e) {
alert(e);
}
}
</SCRIPT>
</HEAD>
<BODY>
<INPUT type="button" value="Add Row" onclick="addRow('dataTable')" />
<INPUT type="button" value="Delete Row" onclick="deleteRow('dataTable')" />
<TABLE id="dataTable" width="350px" border="1">
<THEAD>
<TR> <TH></TH> <TH>Column Name</TH> <TH>Datatype</TH> <TH>Precision</TH> <TH>Scale</TH> <TH>Nullable</TH> </TR>
</THEAD>
<TR>
<TD><INPUT type="checkbox" name="chkbox"/></TD>
<TD><INPUT type="text" name="f01"/></TD>
<TD>
<SELECT name="f02">
<OPTION value="varchar2">varchar2</OPTION>
<OPTION value="int">int</OPTION>
</SELECT>
</TD>
<TD><INPUT type="number" name="f03" value=255></TD>
<TD><INPUT type="number" name="f04"></TD>
<TD><INPUT type="checkbox" name="f05" ></TD>
</TR>
</TABLE>
</BODY>
</HTML>
I was surprised in the end how 'simple' the code was to generate a table to which rows could be added and removed.
So step 1 complete - the table can be defined but nothing can actually be done with the text entered into the screen.
The screenshot below shows what the html table looks like, there are a couple of extra fields and a button above it where the table_name and schema can be entered along with a simple submit button labelled "create table" which actually does nothing......
The next part of the puzzle proved exceptionally difficult to figure out and I nearly gave up before having a breakthrough.....
The key part to this is that Apex expects html table elements when submitted to have a particular name, if they are called anythng other than what it is looking for they are just ignored (at least that was how it behaved for me).
The fields have to be named f01,f02...... etc as this is what Apex is looking for - anything else such as 'fred' or 'bob' are 'invisible' to it.
So in my html i specify those as the 'names' of the fields - see example here:
<TD><INPUT type="number" name="f03" value=255></TD>
<TD><INPUT type="number" name="f04"></TD>
Now when the page is submitted those values are present and can be processed using simple plsql array logic - the code snippet below shows me processing the submitted values and turning that into an long string with the create table command in it:
declare
v_sql varchar2(4000);
begin
--wwv_flow.debug(apex_application.g_request);
v_sql := 'CREATE TABLE '||:ENV_PICKER||'.'||:TNAME||' (';
FOR i IN 1.. APEX_APPLICATION.G_F01.COUNT LOOP
IF APEX_APPLICATION.G_F02(i) = 'varchar2'
THEN v_sql := v_sql||APEX_APPLICATION.G_F01(i)||' '||APEX_APPLICATION.G_F02(i)||'('||APEX_APPLICATION.G_F03(i)||') ';
ELSIF
APEX_APPLICATION.G_F02(i) = 'int'
THEN
v_sql := v_sql||APEX_APPLICATION.G_F01(i)||' '||APEX_APPLICATION.G_F02(i)||' ';
END IF;
IF i = APEX_APPLICATION.G_F01.COUNT
THEN null;
ELSE
v_sql := v_sql||',';
END IF;
END LOOP;
v_sql := v_sql||')';
wwv_flow.debug(v_sql);
:SQL_COMMAND :=v_sql;
end;
At this point i have a global variable called :SQL_COMMAND which contains the SQL statement to create the table - i can view the value of this in the debug window of Apex by the call to the debug function just above the variable assignment.
So we are nearly there - now we just have to take this SQL and somehow execute in a database at the end of a database link. This looked problematic originally and i found a couple of workarounds to do this with jobs and dbms_sql neither of which were very nice. I then discovered the DBMS_UTILITY.EXEC_DDL_STATEMENT proc which was exactly what i needed.
I then just needed to define that this action ran on page submit after the stage where the SQL is constructed - the plsql to do this is shown below.
declare
v_database varchar2(10);
v_sql varchar2(4000);
v_grant_sql varchar2(4000);
v_grant_command varchar2(4000);
v_actual_command varchar2(4000);
v_schema varchar2(4000);
begin
select name into v_database
from endur_go go,em_endur_env_tracking t
where t.schema_name=go.env
and upper(environment_type)='DEV'
and env=:ENV_PICKER;
v_actual_command:= :SQL_COMMAND;
v_grant_command:= 'GRANT ALL ON '||:ENV_PICKER||'.'||:TNAME||' TO OLF_USER';
v_sql:='begin dbms_utility.exec_ddl_statement@'||v_database||'('||''''||v_actual_command||''''||');end;';
v_grant_sql:='begin dbms_utility.exec_ddl_statement@'||v_database||'('||''''||v_grant_command||''''||');end;';
wwv_flow.debug(v_sql);
wwv_flow.debug(v_grant_sql);
execute immediate v_sql;
execute immediate v_grant_sql;
end;
So there you have it, a simple screen that can create tables in any database.....
I'm not sure this actual example with be of practical use to many people but i think the concept with the f01 fields is very useful - i didn't find this well expalined anywhere i looked.
Of course there may be built in ways of doing the above better in Apex - I am kind of making it up as i go along and may be missing the obvious
Hope this is useful for someone!
Comments
Post a Comment