Re: Forms : Basic questions

From: Thomas Heimann <heimann-edv_at_t-online.de>
Date: Thu, 04 Jan 2001 23:58:18 +0100
Message-ID: <3A55000A.75DF21E3_at_t-online.de>

"Jérôme PERRET" schrieb:

> Hi,
>
> I've got a basic questions concerning Forms :
>
> I want to execute an INSERT command on a table whose name is in a parameter.
> How can I ?
> I think a procedure that receive in parameter a CHAR value containing the
> SQL command (INSERT into ...) and execute that command will do the job.
> ex : THE_FAMOUS_PROCEDURE('INSERT into '||:PARAMETER.MY_PARAMETER||'
> values('Hello');');
> Does this procedure exist ?
>
> Jérôme

Try something like this one:

---

[Quoted] PROCEDURE dsql_exec (sql_string IN VARCHAR2,

                ret_value  out number,
                ret_count  out number)
                IS  connection_id EXEC_SQL.ConnType;
     cursor_number EXEC_SQL.CursType;
     ret           PLS_INTEGER;
     connection_string varchar2(20):=null;
     ret_temp   number(7);

BEGIN
  ret_count:=0;
 IF connection_string IS NULL THEN
  connection_id := EXEC_SQL.DEFAULT_CONNECTION;  ELSE
  connection_id :=EXEC_SQL.OPEN_CONNECTION(connection_string);  END IF;
 cursor_number :=EXEC_SQL.OPEN_CURSOR(connection_id);  EXEC_SQL.PARSE(connection_id, cursor_number, sql_string);  exec_sql.define_column(connection_id, cursor_number,1,ret_temp);  ret := EXEC_SQL.EXECUTE(connection_id, cursor_number);  while exec_sql.fetch_rows(connection_id, cursor_number)>0 loop   ret_count:=ret_count+1;
 end loop;
  exec_sql.column_value(connection_id, cursor_number,1,ret_temp);

  ret_value:=ret_temp;
 EXEC_SQL.CLOSE_CURSOR(connection_id, cursor_number);
 EXEC_SQL.CLOSE_CONNECTION(connection_id);

 EXCEPTION
  WHEN EXEC_SQL.PACKAGE_ERROR THEN
     message('ERROR (' || TO_CHAR(EXEC_SQL.LAST_ERROR_CODE (connection_id)) || '): ' || EXEC_SQL.LAST_ERROR_MESG(connection_id));

     IF EXEC_SQL.IS_CONNECTED(connection_id) THEN
        IF EXEC_SQL.IS_OPEN(connection_id,cursor_number) THEN
          EXEC_SQL.CLOSE_CURSOR(connection_id, cursor_number);
        END IF;
        EXEC_SQL.CLOSE_CONNECTION(connection_id);
     END IF;

 END;

• Open a new connection. If the connection string is empty, assume the user [Quoted] [Quoted] wants to use the primary Oracle Developer connection.
  • Open a cursor on the connection for executing the SQL statement.
  • Parse the SQL statement on the given connection.
  • And execute it. If the connection is Oracle, any DDL is done at parse time, [Quoted] [Quoted] but if the connection is a non-Oracle data source, this is not guaranteed. [Quoted]
  • Close the cursor.
  • And we are done with the connection. The connection_id we have may come from [Quoted] calling EXEC_SQL.OPEN_CONNECTION or EXEC_SQL.DEFAULT_CONNECTION. Regardless, we [Quoted] [Quoted] [Quoted] should call EXEC_SQL.CLOSE_CONNECTION. If the connection_id was obtained by [Quoted] EXEC_SQL.OPEN_CONNECTION, EXEC_SQL.CLOSE_CONNECTION will terminate that connection. If the connection_id was obtained by EXEC_SQL.DEFAULT_CONNECTION, [Quoted] [Quoted] EXEC_SQL.CLOSE_CONNECTION will NOT terminate that connection, but it frees up [Quoted] EXEC_SQL package specific resources. [Quoted]
  • This is the general error raised by the EXEC_SQL package, and denotes an [Quoted] unexpected error in one of the calls. It prints the error number and error message to standard out.

    ---

Received on Thu Jan 04 2001 - 23:58:18 CET