EXEC_SQL

Esempio di utilizzo del package EXEC_SQL per eseguire istruzioni SQL dinamiche all'interno di Oracle Forms o Reports.

PACKAGE connessione IS
 curr_user varchar2(20);
 curr_pass varchar2(20);
 curr_conn varchar2(20);
 sql_connect Exec_Sql.ConnType;
 procedure apri;
 procedure chiudi;
END;
 
PACKAGE BODY connessione IS
procedure apri is
 begin
    curr_user := Get_Application_Property(USERNAME); 
    curr_pass := Get_Application_Property(PASSWORD); 
    curr_conn := Get_Application_Property(CONNECT_STRING); 
    sql_connect := Exec_Sql.Open_Connection(curr_user, curr_pass, curr_conn);
 end;
procedure chiudi is 
 begin
    Exec_Sql.Close_Connection(sql_connect);
 end;
END;
 
FUNCTION n_righe RETURN pls_integer IS
   sql_cursor Exec_Sql.CursType;
   sql_command varchar2(4000);
   n_righe pls_integer;
   ret     pls_integer;
begin
   sql_cursor := Exec_Sql.Open_Cursor(connessione.sql_connect);
   sql_command := 'select count(*) from ...... where .......');
   Exec_Sql.Parse(connessione.sql_connect, sql_cursor, sql_command, Exec_Sql.V7);
   EXEC_SQL.DEFINE_COLUMN(connessione.sql_connect, sql_cursor, 1, n_righe);
   ret := Exec_Sql.Execute_and_fetch(connessione.sql_connect, sql_cursor);
   EXEC_SQL.column_value(connessione.sql_connect, sql_cursor, 1, n_righe);
   Exec_Sql.Close_Cursor(connessione.sql_connect, sql_cursor);
   return n_righe;
end;