Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Question Unanswered: Help with P/L sql table variables?!

    Hi,

    I am writing a stored procedure which needs to manipulate a set of records based on several complicated select statements.

    Previously, I wanted to store these values into temp tables but the decision has been made (not mine ) that we are not to include create temp table or create table syntax in our stored procedures.

    These records have varying types and I need to be able to update some of these values.

    I was thinking of using the pl/sql table variable but I am reading up on it and it states that I cannot execute "UPDATE" statements on pl/sql table variables. Can someone tell me if this is in fact true? I am using Oracle 9i.

    If this is true, are there any other alternatives which will allow me to store my records and conduct updates if necessary?

    Thank you in advance

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    1. where is this "set of records" coming from originally? table, external table, utl_file, forms block, user input, ....?

    2. where is the final destination of these records


    If the records originate in a table, you might be able to process them completely using sql joins (I know you said "complicated select statements", but I've seldom seen anything so complicated that it couldn't be done in one multi-table join and still perform well.

    If the records are going to be inserted into a table after the computations, perhaps you insert them to the final table, using default values (0, null, 01-jan-1900) for the calculated columns and then update that table. Other sessions won't see the rows until you commit, so there's little risk in this affecting other session's queries.

    As for temp tables - I agree that you should not be creating/dropping tables from within your procedure. But is there any consistency to the data types? Perhaps a table which never gets dropped, but has temporary (or transient) contents which could manipulate.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Jul 2004
    Posts
    53

    Red face

    Not sure if I used the terminology correctly here, I think I should have used the word recordsets.

    The recordsets are coming from several select statements with some computation.

    Here's an trimmed down pseudo example:

    --in the stored procedure
    if searchbycustomer = true

    select room, starttime, endtime
    from reservation
    where customer = 'Smith'

    if searchbyroom = true

    select room starttime, endtime
    from reservation
    where room = '123'

    They can search using both criteria (room and customer) or by one of each.

    Now there are about 15 other search criterias in my situation and some of them require some calculation.

    It is this 'Select room, starttime, endtime.." that I wanted to insert into a temporary table.

    There really is no need for me to permanently keep these values stored somewhere because in my scenario I am looking for all of the times that are blocked (meaning not available) based on all 15 search criteria and then I want to find the times that are available based on that. So I am trying to find a temporary holding spot for the blocked times in order to find out what is available...

    I do have the option of having a permanent table created for these scenarios but I am worried that multiple users are going to be doing updates that I need at the same time. (I have the update statement executing in a loop so each user can potentially be running mutiple updates on the same row).

    So the trick for me is to find some sort of way to temporarily store these values, manipulate them, without using temporary tables and not affecting other user's data...

    I was thinking about table variables because I'm coming from an MSSQL background and its table variables can be update and can hold different data types in multiple columns...

    I am hoping to find something similar or better in pl/sql...

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    If you use a temporary table, you should always an extra column to tie the records to the current session (sessionid works well for this). Then include this column in the inserts, updates, selects to guarantee that you only affect your own data.

    Have you tried UNION:

    select room, starttime, endtime
    from reservation
    where customer = 'Smith'
    and searchbycustomer = 'true' -- variable is now string, not boolean
    union all
    select room starttime, endtime
    from reservation
    where room = '123'
    and searchbyroom = 'true'
    union all
    ...
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Jul 2004
    Posts
    53
    Although, I wont be able to use a temporary table..I do have a table that I can use to store this information and I am definitely digging the idea of using a sessionid! That's a great idea...

    can the sessionid be a value that can be extracted from Oracle somewhere?

    Like is there a system table that stores this value that I can transfer into this table as a value?


    I tried the union all statements on some of my sql and it worked like a charm! Including the boolean in the select statement itself...wish I'd thought of it...

    Thanks Shoblock!

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Forget using a session_id as a key into a table, use a GTT (Global Temporary Table). What he wants to do is EXACTLY what it was designed for. Don't reinvent the wheel when you already have one.

    no create tables, no drop tables in the procedures... All's well with the world.
    Last edited by beilstwh; 10-08-04 at 16:48.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Forget using a session_id as a key into a table, use a GTT (Global Temporary Table). What he wants to do is EXACTLY what it was designed for. Don't reinvent the wheel when you already have one.

    no create tables, no drop tables in the procedures... All's well with the world.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    Yes, if your version of Oracle uses global temporary tables, then use them.

    If not (some people are still using v7, go figure) then you can use:
    userenv('sessionid') - this returns the audit sid (which joins to v$session to get the sid, but it's still unique per session).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Jul 2004
    Posts
    53
    I am using Oracle 9i.

    Would I still need to use the sessionid in this scenario since I will have mutiple users accessing the global temporary table?

    If I do have to use session id's with the temp table to distiguish between sessions, would it be better for performance if I use a static table or a GTT?

    Thanks!

  10. #10
    Join Date
    Apr 2004
    Posts
    246
    GTT works by magic! It takes care of everything for you. You can see only your data, and nobody else's, and when you exit (or commit/rollback depending on the GTT definition), your disappears. Can't beat magic.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  11. #11
    Join Date
    Jul 2004
    Posts
    53
    That's awesome....seems to fit everything I ask for...so alls I need to do is create the global temporary table ONCE and it stays there indefinitely..and the user can manipulate ONLY their data? Magic indeed....

  12. #12
    Join Date
    Sep 2004
    Posts
    16
    Hi,
    You could also use Oracle Collection types - like records, nested tables and arrays. They are only collection variables, and let you hold the value you fetch from the database, and provide methods by which you can access them by subscripts, and manipulate them.

    Following is just an example using records and nested tables:

    create or replace procedure vintable as
    --create a type to store only a few columns from emp table
    type rectyp is record(empno emp.empno%type, --
    sal emp.sal%type,
    deptno emp.deptno%type);

    --create a table where each subscript holds a record type
    type mytype is table of rectyp;
    mytbl mytype ; --declare variable of type table

    begin
    select empno, sal, deptno
    bulk collect into mytbl -- this makes the process much faster
    from emp;
    for j in mytbl.first..mytbl.last loop -- traverse all records
    mytbl(j).sal := mytbl(j).sal * 10; --manipulate a value in the record
    dbms_output.put_line(mytbl(j).empno||' '||mytbl(j).sal);
    end loop;

    /*delete all elements of the collection. Now, you can use the same collection to collect a fresh set of values for a different condition.
    */

    mtybl.delete;
    end;

    In your case,
    -You'll have to create a record type for (room, starttime, endtime),
    -A table to store the records. This is Not a database or a temporary table, but behaves like a tbale
    -In the query, select the records into the table for the search condition.
    -Loop through the collection and manipulate where required.
    -Delete the collection elements
    -Use the same collection again for a new search condition

    Hope this helps.
    You could even go through some material on:
    http://www.csee.umbc.edu/help/oracle...a67842/toc.htm


    --Vinita

  13. #13
    Join Date
    Jul 2004
    Posts
    53
    Thanks Vinita...I'll try that out tonight and let you know how it works!

Posting Permissions

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