Re: HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work

This is not really related to the original question, but I thought it was worth pointing out.

Yes, for Oracle 7, DBMS_SQL.PARSE of a DDL statement also performs the execute. However, it is a good idea (and safe) to always execute as well. I think in Oracle 8, a PARSE of a DDL statement may not do an execute(*). Also, if you are using DBMS_SQL's client-side extension, EXEC_SQL, against a potentially non Oracle database, you will want to do an Execute, as many Databases that are not Oracle only Execute when you EXECUTE.

Therefore, I wouldn't worry about the IF stmt_type != 'DDL' THEN statement. If the PARSE already executed the statement, the second execute will not do anything.

• Art Clarke

(*) I'm not 100% sure about this, but the EXECUTE does not hurt.

Steve Johnson (robertoc_at_fyiowa.infi.net) wrote:
> When executing the DBMS_SQL.PARSE command with a DDL statement it also
> performs the execute. I'm not sure what happens if you try to Execute
> it
> also. In my code I also OPEN, PARSE, EXECUTE if not a DDL and CLOSE
> each time I perform a call to DBMS_SQL. You might have to close your
> current cursor and open a new one for each SQL statement. I use a
> database procedure inside a package to do this and I pass a SQL
> statement
> to it. None of the statements passed to this code are DDL so I issue
> DBMS_SQL.EXECUTE.
 
> PROCEDURE exec_dbms_sql(sql_statement varchar2, stmt_type varchar2) IS
> cursor_handle INTEGER;
> tbl_updated INTEGER;
> BEGIN
> /* Create a cursor */
> cursor_handle := dbms_sql.open_cursor;
>
> /* Parse SQL statement.Executes if the SQL is DDL*/
> dbms_sql.parse(cursor_handle, sql_statement, dbms_sql.native);
>
> IF stmt_type != 'DDL' THEN
> tbl_updated := dbms_sql.execute(cursor_handle);
> commit;
> END IF;
 
> dbms_sql.close_cursor(cursor_handle);
> END exec_dbms_sql;
 

> I think that I'd also use views, if possible, instead of temporary
> tables. This way it wouldn't take up any space and you wouldn't have to
> drop them you could - Create or Replace view xxxxx as.
>
> Hope this helps
> Steve J.
  Received on Wed Jul 30 1997 - 00:00:00 CEST