Home » SQL & PL/SQL » SQL & PL/SQL » Updating and Deleting from collections
Updating and Deleting from collections [message #214690] Wed, 17 January 2007 10:33 Go to next message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
Hi all,

I have PL\SQL Table definded as follow:

CREATE OR REPLACE TYPE EntryTableType AS OBJECT
(
	 TableID  NUMBER,
	 ColumnID NUMBER,
	 ColType  VARCHAR2(100),
	 Deleted  Number
);

CREATE OR REPLACE TYPE EntryTable_Temp_Table AS
   TABLE OF EntryTableType;


In the stored procedure I have a variable definded as:
tbl_Entries EntryTable_Temp_Table := new EntryTable_Temp_Table();


Now after inserting some rows in there I need to update field "Deleted" for every record in the PL\SQL table. I tried the following:
UPDATE TABLE(CAST(tbl_Entries AS EntryTable_Temp_Table))
SET Deleted = 1
WHERE <..... some subqueries.....>;

But I got an error comming back saying table or view does not exisit.
So the question is how can I update or remove rows in the tbl_Entries?

Thanks,
Paul
Re: Updating and Deleting from collections [message #214700 is a reply to message #214690] Wed, 17 January 2007 11:36 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I think you are misunderstanding what these types are. They are not tables. The TABLE(CAST) converts an in-memory array of types into a result set that can be queried like a table. However, it is not actually a table and can't be accessed by normal SQL methods like insert or update.

Why do you want to delete them ? As soon as the procedure terminates, the variables will all be deleted anyway. If you really have to delete them, then you just need to set the deleted flag to 'Y':

declare
  tbl_Entries EntryTable_Temp_Table := new EntryTable_Temp_Table();
  v_index number;
begin
  tbl_Entries.extend;
  tbl_Entries(1) := EntryTableType(null, null, null, null);
  tbl_Entries(1).TableID  := 5;
  tbl_Entries(1).ColumnID := 3;
  tbl_Entries(1).ColType  := 'XXX';
  tbl_Entries(1).Deleted  := 0;
  --
  -- Do some processing
  --
  tbl_Entries(1).Deleted  := 1;
  --
  -- or just delete the lot  
  --
  tbl_Entries.delete;
  --
  -- or just wait until the program exits, in which case tbl_Entries will be deleted from memory anyway
end;
Re: Updating and Deleting from collections [message #214702 is a reply to message #214690] Wed, 17 January 2007 12:04 Go to previous messageGo to next message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
Hi there,

The reason for updating the array is that I am using it as a temp table. And in a complex stored procedure I need to put some rows into this ""temp table"" at the beginning, do some processing to determine what other stuff I need to do later.

The "Deleted" field will determine rather I need to delete some rows from a real table after the processing. So it is vital for me to be able to update this flag for every entry I have put into the ""temp table"".

Regarding remove rows from them, it is in the similar line of argument. but so far I have failed to dig up anything from the Internet (have been google-ing for the past week Crying or Very Sad) that tell me either it is impossible or it is possible but not a lot of people has done it.

Re: Updating and Deleting from collections [message #214703 is a reply to message #214702] Wed, 17 January 2007 12:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Like Ctulhu said, your TABLE-type is not a table in the database-sense. It is an in-memory table, comparable to an array. You cannot use sql against an array in any language (any language I know of that is).
You might however define a global temporary table. That IS a database table, which contents are only visible to the session that inserted/updated them.
Re: Updating and Deleting from collections [message #214704 is a reply to message #214690] Wed, 17 January 2007 12:19 Go to previous messageGo to next message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
Thanks for all your answers. Maybe I can have the same effect without using the conventional SQL. I am thinking of looping through the elements in the array and try to so something clever with it.

Regarding Global Temp Table, I am sure that I am not allowed to use it anywhere in my work.

I wish there is some new features coming up to provide some easy way out when translating temp table in T-SQL into PL\SQL.
Re: Updating and Deleting from collections [message #214707 is a reply to message #214704] Wed, 17 January 2007 12:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
It is difficult to guess at exactly what your overall goal is. However, usually when people mention trying to convert T-SQL code to PL/SQL and mention a temp table, what they need is an inline view. Oracle is not as transactional as T-SQL and things that are done in multiple steps there are typically more efficiently done in one big SQL query in Oracle. For example, instead of (pseudocode):

create temp table
insert into temp table
update temp table
select from temp table

you would (pseudocode):

open ref_cursor for -- to return result set
select ... from -- substitute for update
(select ... from) -- inline view substitute for insert to temp table

Re: Updating and Deleting from collections [message #214711 is a reply to message #214690] Wed, 17 January 2007 12:46 Go to previous messageGo to next message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
Sorry I am just started with oracle, could you please tell me what is an Inline View and could you provide a simple example to illustrate Inline View?

I know how cursors works roughly and sometimes I have tried to eliminate some temp table usage in T-SQL (ie: some of the select top 1 .... looping in T-SQL) with nesting loops and cursors in PL/SQL. But not all of them are successful.

Re: Updating and Deleting from collections [message #214719 is a reply to message #214711] Wed, 17 January 2007 13:47 Go to previous message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
An inline view is a subquery in the from clause. You can select from it just as you would from a view or table or temporary table that had been created using the same query, but without creating any view or table. I have provided two very simple examples below. The first one uses a global temporary table and duplicates the usual t-sql method of insert, update, and select. The second one does not use a separate table, but instead opens a ref cursor that returns the result of one query, that uses an inline view in place of a temporary table. The example below is just to demonstrate the functionality. You actually would not even need an inline view or temp table in such a simple example, as it could all be done in one query. This is just to present the general method.

-- duplication of t-sql step by step method using temp table:
SCOTT@10gXE> CREATE GLOBAL TEMPORARY TABLE Entry_Table_Temp_Table
  2    (TableID   NUMBER,
  3  	ColumnID  NUMBER,
  4  	ColType   VARCHAR2(15),
  5  	Deleted   Number)
  6  ON COMMIT PRESERVE ROWS
  7  /

Table created.

SCOTT@10gXE> CREATE OR REPLACE PROCEDURE the_stored_procedure
  2  AS
  3  BEGIN
  4    INSERT INTO Entry_Table_Temp_Table
  5    SELECT empno, mgr, job, comm
  6    FROM   emp
  7    WHERE  deptno = 30;
  8  
  9    UPDATE Entry_Table_Temp_Table
 10    SET    deleted = 1
 11    WHERE  deleted > 0 OR deleted IS NULL;
 12  END the_stored_procedure;
 13  /

Procedure created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> EXECUTE the_stored_procedure

PL/SQL procedure successfully completed.

SCOTT@10gXE> SELECT * FROM Entry_Table_Temp_Table
  2  /

   TABLEID   COLUMNID COLTYPE            DELETED
---------- ---------- --------------- ----------
      7499       7698 SALESMAN                 1
      7521       7698 SALESMAN                 1
      7654       7698 SALESMAN                 1
      7698       7839 MANAGER                  1
      7844       7698 SALESMAN                 0
      7900       7698 CLERK                    1

6 rows selected.


-- more efficient way in pl/sql using inline view and ref cursor:
SCOTT@10gXE> CREATE OR REPLACE PROCEDURE a_better_way
  2    (p_ref OUT SYS_REFCURSOR)
  3  AS
  4  BEGIN
  5    OPEN p_ref FOR
  6    SELECT TableID, ColumnID, ColType,
  7  	      CASE WHEN Deleted > 0 OR Deleted IS NULL
  8  		   THEN 1
  9  		   ELSE Deleted
 10  	      END  Deleted
 11    FROM   -- the three lines below are an inline view:
 12  	      (SELECT empno AS TableID, mgr AS ColumnID, job AS ColType, comm AS Deleted
 13  	       FROM   emp
 14  	       WHERE  deptno = 30);
 15  END a_better_way;
 16  /

Procedure created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> VARIABLE g_ref REFCURSOR
SCOTT@10gXE> EXECUTE a_better_way (:g_ref)

PL/SQL procedure successfully completed.

SCOTT@10gXE> PRINT g_ref

   TABLEID   COLUMNID COLTYPE      DELETED
---------- ---------- --------- ----------
      7499       7698 SALESMAN           1
      7521       7698 SALESMAN           1
      7654       7698 SALESMAN           1
      7698       7839 MANAGER            1
      7844       7698 SALESMAN           0
      7900       7698 CLERK              1

6 rows selected.

SCOTT@10gXE> 

Previous Topic: sql report creation
Next Topic: SQL query
Goto Forum:
  


Current Time: Fri Dec 02 18:38:49 CST 2016

Total time taken to generate the page: 0.35802 seconds