Re: Problem with create temporary table in PL/SQL

From: Andy Hardy <aph_at_ahardy.demon.co.uk>
Date: Fri, 21 May 1999 01:28:31 +0100
Message-ID: <MyDbGBAviKR3EwOZ@ahardy.demon.co.uk>

In article <7i15sp$or1_at_netnews.hinet.net>, nick <nickcool_at_ms17.hinet.net> writes
>Hello.
>
>How to create temporary table in PL/SQL ?
>

For Oracle 7.x, you'll need to use dbms_sql for statements where the table name varies. I use the following procedure to do this - you just pass in the piece of sql you want executing in p_ddl.

PROCEDURE ddl(p_ddl IN VARCHAR2, p_message OUT VARCHAR2, p_return_code OUT NUMBER)
IS

        v_cursor_id INTEGER;
        v_dummy INTEGER;
BEGIN
        v_cursor_id := dbms_sql.open_cursor;
        dbms_sql.parse(v_cursor_id, p_ddl, dbms_sql.v7);
        v_dummy := dbms_sql.execute(v_cursor_id);
        dbms_sql.close_cursor(v_cursor_id);
        p_message := 'DDL OK';
        p_return_code := 1;
EXCEPTION
        WHEN OTHERS THEN
                p_message := substr(SQLERRM(SQLCODE), 1, 80);
                p_return_code := 0;

END ddl;

Andy
>Thanks in advance,
>Nick
>
>
>

--
Andy Hardy. PGP key available on request

---

Received on Thu May 20 1999 - 19:28:31 CDT