​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
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.


 

DECLARE

 

    username varchar2(20);

    password varchar2(20);

    conn_str varchar2(20);

    con_status boolean;

    con_handle EXEC_SQL.CONNTYPE;

    cur_number EXEC_SQL.CURSTYPE;

    sql_statement varchar2(100) := 'TRUNCATE TABLE TESTTAB';

    ret_val number;

 

BEGIN

 

    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);

    EXEC_SQL.PARSE(con_handle,curs_number,sql_statment);

    ret_val := EXEC_SQL.EXECUTE(con_handle,curs_number);

    EXEC_SQL.CLOSE_CONNECTION(con_handle);

 

    exception

    when EXEC_SQL.PACKAGE_ERROR then

    message('Database DDL error');

    raise form_trigger_failure;

 

END;