A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
Using Dynamic SQL in Forms
In another of my articles I show you how to implement dynamic SQL in database procedures, here's how to do the same in Forms PL/SQL code.
The reason why you would want do to do this is because, like in database procedures, you can't use DDL such as DROP TABLE 'table_name' directly in PL/SQL with Forms. The way to do it is with the built-in EXEC_SQL package. The same thing can also be accomplished with the FORMS_DDL built-in but that only applies to the current database connection, with the EXEC_SQL package its possible to have database connections to multiple databases and also execute non-oracle database procedures.
Here's some sample code.
sql_statement varchar2(100) := 'TRUNCATE TABLE TESTTAB';
username := GET_APPLICATION_PROPERTY(USERNAME);
password := GET_APPLICATION_PROPERTY(PASSWORD);
conn_str := GET_APPLICATION_PROPERTY(CONNECT_STRING);
con_handle := EXEC_SQL.OPEN_CONNECTION(username,password,conn_str);
curs_number := EXEC_SQL.OPEN_CURSOR(con_handle);
ret_val := EXEC_SQL.EXECUTE(con_handle,curs_number);
when EXEC_SQL.PACKAGE_ERROR then
message('Database DDL error');