Re: Forms : Basic questions
Date: Fri, 5 Jan 2001 09:43:07 +0100
Message-ID: <9341g0$4fs$1_at_wanadoo.fr>
[Quoted] Thanks a lot, it work with my select commands, but when trying to pass
INSERT sql command, I get an ORA-01007 error : "variable not in select
list".
DELETE fails too.
Do you have an idea ?
Thomas Heimann <heimann-edv_at_t-online.de> a écrit dans le message :
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:
>
> --------------------------------------------------------------------------
>
> 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
> 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,
> but if the connection is a non-Oracle data source, this is not guaranteed.
> -- Close the cursor.
>
> -- And we are done with the connection. The connection_id we have may come
from
> calling EXEC_SQL.OPEN_CONNECTION or EXEC_SQL.DEFAULT_CONNECTION.
Regardless, we
> should call EXEC_SQL.CLOSE_CONNECTION. If the connection_id was obtained
by
> EXEC_SQL.OPEN_CONNECTION, EXEC_SQL.CLOSE_CONNECTION will terminate that
> connection. If the connection_id was obtained by
EXEC_SQL.DEFAULT_CONNECTION,
> EXEC_SQL.CLOSE_CONNECTION will NOT terminate that connection, but it frees
up
> EXEC_SQL package specific resources.
> -- This is the general error raised by the EXEC_SQL package, and denotes
an
> unexpected error in one of the calls. It prints the error number and error
> message to standard out.
>
> --------------------------------------------------------------------------
>
>
>
>
Received on Fri Jan 05 2001 - 09:43:07 CET