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

[Quoted] [Quoted] 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. [Quoted]   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;

[Quoted]   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