Temporary tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Temporary tables

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Hi all,

    I have a question on temporary tables .
    Can I use a temporary table as an ordinary table within its scope, i.e., can I run DML statements on it ?? If yes, is the syntax same as that for ordinary tables?

    Any guidance in the right direction would be of much help.

    Shiva.

  2. #2
    Join Date
    Mar 2001
    Posts
    17

    Smile

    Yes

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Thanks for your response.
    If YES, then here is my issue.
    I need to create a temporary table, insert rows into it and then sort the records based on a particular field.
    I have simplified my procedure :
    Here is it:

    Declare
    cursor s_shiva is
    select distinct contact_type
    from shiva order by contact_type;

    contacttype varchar2(20);
    a varchar2(500);
    begin
    a := 'create global temporary table shiva(customer_cd varchar2(20) NULL,contact_type varchar2(20) NULL,seq_no integer) NULL';
    execute immediate a;
    insert into shiva(customer_cd,contact_type,seq_no) values ('1','cust',1);
    insert into shiva(customer_cd,contact_type,seq_no) values ('1','billing',1);
    insert into shiva(customer_cd,contact_type,seq_no) values ('1','noc',1);
    commit;

    open s_shiva;
    loop
    fetch s_shiva into contacttype;
    exit when s_shiva%notfound;
    dbms_output.put_line(contacttype);
    end loop;
    close s_shiva;

    end;

    But it gives me an error saying identifier 'Shiva' must be declared wherever I have used it. Is this not the right way of creation or is it that I should affix a qualifier or some kind to the temporary table name before using it.

    Any help or atleast a direction in which I can find some sample is appreciated.

    Shiva.

  4. #4
    Join Date
    Dec 2000
    Posts
    28

    Exclamation

    Hi,

    First, check what version of Oracle u are using. If it is not O8 then probably you should user DBMS_SQL package to create the table on the fly.

    Second, If you want to create a table using Dynamic SQL, you must have CREATE TABLE or CREATE ANY TABLE privileges granted directly to your schema. Remember that roles are disabled during PL/SQL compilation and execution.

    My guess would be that u must not have the necessary privileges to exeucte that DDL statement from this PL/SQL block.

    Let me know if it helps

    AD

  5. #5
    Join Date
    Mar 2001
    Posts
    17

    Smile

    You shoud write query like this....
    a := 'create global temporary table shiva(
    customer_cd varchar2(20) NULL,
    contact_type varchar2(20) NULL,
    seq_no integer NULL)';



  6. #6
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Thanks for pointing out that syntax mistake. I have changed it and still get the same error.
    I am running Oracle8i on Solaris and I do have all the required privileges. Can I use DBMS_SQL package to create temporary tables too??


    Shiva.

  7. #7
    Join Date
    Dec 2000
    Posts
    28

    Wink

    Okay. I spent sometime on this and finally got it. Since you are creating the table SHIVA on the fly your cursor cannot refer this table. Here is the solution :

    1. Create a table shiva1 and refer this in the cursor select.
    2. Now create the table shiva on the fly and insert the rows using dbms_sql (Please note that the insert also should be using dbms_sql )
    3. Now copy the data from shiva to shiva1. ( use dbms_sql and insert into the shiva1 table.
    4. open the cursor and display the value.

    Here is the final code which does the above mentioned steps (btw, it works. I have tested it )
    :
    Declare

    cid integer;

    cursor s_shiva is
    select distinct contact_type
    from shiva1
    order by contact_type;

    contacttype varchar2(20);
    a varchar2(500);
    fdbk binary_integer;

    begin

    cid := DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(cid, 'create table shiva(customer_cd varchar2(20) NULL,contact_type varchar2(20), seq_no number)', dbms_sql.native);
    fdbk := DBMS_SQL.EXECUTE(cid);

    dbms_sql.parse(cid, 'insert into shiva(
    customer_cd,
    contact_type,
    seq_no )
    values ('||''''||'1'||''''||','||''''||'cust'||''''||',1)', dbms_sql.native);

    fdbk := dbms_sql.execute(cid);

    dbms_sql.parse(cid, 'insert into shiva(
    customer_cd,
    contact_type,
    seq_no )
    values ('||''''||'2'||''''||','||''''||'two'||''''||',2)', dbms_sql.native);
    fdbk := dbms_sql.execute(cid);

    dbms_sql.parse(cid, 'insert into shiva(
    customer_cd,
    contact_type,
    seq_no )
    values ('||''''||'3'||''''||','||''''||'three'||''''||', 3)', dbms_sql.native);
    fdbk := dbms_sql.execute(cid);

    dbms_sql.parse(cid, 'insert into shiva1 select contact_type from shiva', dbms_sql.native);
    fdbk := dbms_sql.execute(cid);

    DBMS_SQL.CLOSE_CURSOR(cid);

    commit;

    open s_shiva;
    loop
    fetch s_shiva into contacttype;
    exit when s_shiva%notfound;
    dbms_output.put_line(contacttype);
    end loop;
    close s_shiva;

    EXCEPTION
    /* If an exception is raised, close cursor before exiting. */
    WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(cid);
    RAISE; -- reraise the exception
    end;
    /


    Please let me know if it helps.

    Thanks,
    AD



  8. #8
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Ad,

    Thanks for the time you have taken for the research.

    But my question is - I want to use temporary table and not an ordinary permanent table - the reason - I do not want to waste space, as it is not going to be used anywhere else, but in this one precedure to hold values.

    It would be great if you can guide me through creating and accessing a temporary table...

    Shiva.

  9. #9
    Join Date
    Dec 2000
    Posts
    28
    Hi,

    I am aware of temporary table in Oracle using PL/SQL table using CREATE TYPE only. I am new to CREATE GLOBAL TEMPORARY table statement. Is it in O8i ? Unfortunately i am still working with O8. Sorry i could not help.

    thanks.
    AD

  10. #10
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    I picked this 'Create global temporary table' statement up from one of the posts in this forum and it says it works in 8.1.6.
    I do not want to use PL/SQL tables as I would not be able to run a select query to it.

    I am not able to find information on this particular command in the web....Do you know of any link or site which would give me a guideline??

    Shiva.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width