Home » SQL & PL/SQL » SQL & PL/SQL » using nested tables with CAST as normal table (merged 3)
icon4.gif  using nested tables with CAST as normal table (merged 3) [message #392357] Tue, 17 March 2009 11:53 Go to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

Hi, i need an table type of objects that will store some records from a table(ps_rc_case) in order to join it with other tables.

I have created the object type

CREATE OR REPLACE TYPE t_cases AS OBJECT (
					case_id number(15), 
				business_unit varchar2(15));

create or replace type t_cases_table as table of t_cases;


I have create a block in which i have populate manually the values into the object.
BLOCK1
declare 

cases_table t_cases_table :=
            t_cases_table (                                               
                            t_cases (220908,'CO0'),
                            t_cases (220909,'CO0'),
                            t_cases (220910,'CO0'),
                            t_cases (220911,'CO0'),
                            t_cases (220912,'CO0'),
                            t_cases (220913,'CO0'),
                            t_cases (220914,'CO0'),
                            t_cases (221005,'CO0'),
                            t_cases (221006,'CO0'),
                            t_cases (221056,'CO0'),
                            t_cases (221057,'CO0'),
                            t_cases (221062,'CO0'),
                            t_cases (221063,'CO0'));
begin 

insert into vio2 select a.case_id, a.business_unit 
from ps_rc_case a,  table(cast(cases_table as t_cases_table)) b
where b.case_id = a.case_id
and   b.business_unit = a.business_unit;

commit;                                   
end;                          

If i execute this the table vio2(case_id number, business_unit varchar2) gets populated correctly.

Now i tried to get the data in my object using bulk collect.

declare 

cases_table t_cases_table ;

begin 

select t_cases(case_id, business_unit)
bulk collect into cases_table 
from ps_rc_case where id_cust =789;

insert into vio2 select a.case_id, a.business_unit 
from ps_rc_case a,  table(cast(cases_table as t_cases_table)) b
where b.case_id = a.case_id
and   b.business_unit = a.business_unit;

commit;                                  
end;  

The problem is that i got all records in my object with bulk collect, but now the table Vio2 is no more populated.

Can somebody tell me why is not working with bulk collect, and why.
Can i do it in some other way to be fast, i'm trying to avoid cursors.

Many thanks
Re: using nested tables with CAST as normal table [message #392360 is a reply to message #392357] Tue, 17 March 2009 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
do not cross/multi-post
Re: using nested tables with CAST as normal table [message #392365 is a reply to message #392360] Tue, 17 March 2009 12:06 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

I'm sorry the browser gave an error and i didn't know if it got submitted
Re: using nested tables with CAST as normal table (merged 3) [message #392366 is a reply to message #392357] Tue, 17 March 2009 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>i need an table type of objects that will store some records from a table(ps_rc_case) in order to join it with other tables.
WHY? What is wrong with regular tables being joined?
Or is this just a classic homework exercise?
Re: using nested tables with CAST as normal table (merged 3) [message #392391 is a reply to message #392366] Tue, 17 March 2009 14:34 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

The reason why i need an object type, is because on a single session can be 2 or more operators, and because of this two people may execute the procedure on the same session, and i need transactions to be isolated.

I was thinking of using global temporary table, but is used per session, and is not helping me much.



Re: using nested tables with CAST as normal table (merged 3) [message #392392 is a reply to message #392357] Tue, 17 March 2009 14:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>two people may execute the procedure on the same session
Can NOT, will NOT, does NOT have 2 people on the same session!
Post reproducible proof for your (outrageous) assertion.


In my opinion, you are trying to solve a problem which does not exist.

> i need transactions to be isolated.
In Oracle a transaction is isolated.

A database transaction, by definition, must be atomic, consistent, isolated and durable.
Database practitioners often refer to these properties of database transactions using the acronym ACID.

[Updated on: Tue, 17 March 2009 14:51]

Report message to a moderator

Re: using nested tables with CAST as normal table (merged 3) [message #392409 is a reply to message #392392] Tue, 17 March 2009 16:40 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

I'm working with PeopleSoft application.

PeopleSoft uses an Application Server which manages, on a single db connection/session, more than one user by a queuing mechanism.

In fact, it is true, there is a single session, on which db interactions coming from more users, are managed in a queuing mechanism

so, it is very possible a (my) stored procedure to be launced two times once very next to the before(consecutive).

So i need for a thread of my stored procedure to keep a set of records from a main table, based on which i will make other selects.

This set of records differ from one thread to another, and is based on an input parameter of my procedure.

For this set of records i'm trying to make an object, which i will use it as a table to join with the rest of my tables, in order to get the rest of the information needed for that customer(input parameter).

Is there any way to make the second Block to do the insert into vio2 also?


Thanks
















[Updated on: Tue, 17 March 2009 16:41]

Report message to a moderator

Re: using nested tables with CAST as normal table (merged 3) [message #392411 is a reply to message #392409] Tue, 17 March 2009 17:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
If this were just Oracle, I would agree with BlackSwan that you do not need objects, but I am not familiar with PeopleSoft. However, I do not see the problem with the objects either, but perhaps it is due to a difference in versions. Can you post a copy and paste of a complete run, including version information, as I have done below? Please run it directly from SQL*Plus, so that there is no possibility of the problem due to some interface.

SCOTT@orcl_11g> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE	11.1.0.6.0	Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SCOTT@orcl_11g> CREATE TABLE ps_rc_case
  2    (case_id        NUMBER	(15),
  3  	business_unit  VARCHAR2 (15),
  4  	id_cust        NUMBER)
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO ps_rc_case VALUES (220908,'CO0', 789)
  3  INTO ps_rc_case VALUES (220909,'CO0', 789)
  4  INTO ps_rc_case VALUES (220910,'CO0', 789)
  5  INTO ps_rc_case VALUES (220911,'CO0', 789)
  6  INTO ps_rc_case VALUES (220912,'CO0', 789)
  7  INTO ps_rc_case VALUES (220913,'CO0', 789)
  8  INTO ps_rc_case VALUES (220914,'CO0', 789)
  9  INTO ps_rc_case VALUES (221005,'CO0', 789)
 10  INTO ps_rc_case VALUES (221006,'CO0', 789)
 11  INTO ps_rc_case VALUES (221056,'CO0', 789)
 12  INTO ps_rc_case VALUES (221057,'CO0', 789)
 13  INTO ps_rc_case VALUES (221062,'CO0', 789)
 14  INTO ps_rc_case VALUES (221063,'CO0', 789)
 15  SELECT * FROM DUAL
 16  /

13 rows created.

SCOTT@orcl_11g> CREATE TABLE vio2
  2    (case_id        NUMBER	(15),
  3  	business_unit  VARCHAR2 (13))
  4  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE t_cases AS OBJECT
  2    (case_id        NUMBER	(15),
  3  	business_unit  varchar2 (15));
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE t_cases_table AS TABLE OF t_cases;
  2  /

Type created.

SCOTT@orcl_11g> DECLARE
  2    cases_table t_cases_table;
  3  BEGIN
  4    SELECT t_cases (case_id, business_unit)
  5    BULK   COLLECT INTO cases_table
  6    FROM   ps_rc_case
  7    WHERE  id_cust = 789;
  8    --
  9    INSERT INTO vio2
 10    SELECT a.case_id, a.business_unit
 11    FROM   ps_rc_case a,
 12  	      TABLE (CAST (cases_table AS t_cases_table)) b
 13    WHERE  b.case_id = a.case_id
 14    AND    b.business_unit = a.business_unit;
 15    COMMIT;
 16  END;
 17  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM vio2
  2  /

   CASE_ID BUSINESS_UNIT
---------- -------------
    220908 CO0
    220909 CO0
    220910 CO0
    220911 CO0
    220912 CO0
    220913 CO0
    220914 CO0
    221005 CO0
    221006 CO0
    221056 CO0
    221057 CO0
    221062 CO0
    221063 CO0

13 rows selected.

SCOTT@orcl_11g> 

Re: using nested tables with CAST as normal table (merged 3) [message #392412 is a reply to message #392357] Tue, 17 March 2009 19:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>PeopleSoft uses an Application Server which manages, on a single db connection/session, more than one user by a queuing mechanism.

Are you suggesting that Oracle RDBMS is Peoplesoft aware & behaves differently when running Peoplesoft as compared to any other application?


>In fact, it is true, there is a single session, on which db interactions coming from more users, are managed in a queuing mechanism

I am aware that many applications utilize "connection pooling" (CP).
Regardless of whether CP is used or not, there are NEVER any cases where two users connected to the same session at the same time.


>so, it is very possible a (my) stored procedure to be launced two times once very next to the before(consecutive).

I acknowledge that multiple sessions could be invoking the same procedure at the same time.
The reality is that neither of these sessions knows about what is happening in the other session, so nothing needs to be added to prevent interaction.

Once again I challenge you to provide a reproducible test case which shows two users can be "attached" to the same Oracle session at the same time.

If you wish to expend time & energy solving a problem which does not exist, nobody here can prevent you from doing so.

[Updated on: Tue, 17 March 2009 19:38]

Report message to a moderator

Re: using nested tables with CAST as normal table (merged 3) [message #392541 is a reply to message #392412] Wed, 18 March 2009 06:05 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

Hi Barbara,

Thank you for executing on your database.
My version is 10g, and it seems that is not working on 10g.

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


I have executed on sql*plus, and the seame thing:

SQL> DECLARE
  2        cases_table t_cases_table;
  3      BEGIN
  4
  5  select t_cases(case_id, business_unit)
  6  bulk collect into cases_table
  7  from ps_rc_case where id_cust =789;
  8  dbms_output.put_line(cases_table.count);
  9
 10       INSERT INTO vio2
 11      SELECT a.case_id, a.business_unit
 12       FROM   ps_rc_case a,
 13           TABLE (CAST (cases_table AS t_cases_table)) b
 14      WHERE  b.case_id = a.case_id
 15      AND    b.business_unit = a.business_unit;
 16     COMMIT;
 17    END;
 18  /

PL/SQL procedure successfully completed.

SQL> select * from vio2;

no rows selected


Do you have any idea how can i make it works on 10g?

As i was thinking better, if i have 10 users on the same session, and if two of them are executing the same procedure, then one will have to wait for the first to finish, so it my work with global temporary table.

I will make this scenario an test it to see if there are any problems.

Thank you for your time.


Re: using nested tables with CAST as normal table (merged 3) [message #392544 is a reply to message #392357] Wed, 18 March 2009 06:24 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
It works just dandy on my 10g
SQL> SELECT banner FROM v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


which'd be the same version as yours.
Why don't you execute Barabara's full test case and see what happens.

Re: using nested tables with CAST as normal table (merged 3) [message #392566 is a reply to message #392544] Wed, 18 March 2009 07:43 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

I have executed how Barbara said in sql*plus, i will try on other data base.
Maybe this one is different configured.

Re: using nested tables with CAST as normal table (merged 3) [message #392574 is a reply to message #392566] Wed, 18 March 2009 08:22 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

I have tried on other data base version 10.2.0.1 and worked just fine.

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


SQL> CREATE TABLE vio2
(case_id        NUMBER  (15),
        business_unit  VARCHAR2 (13));
  2    3
Table created.

SQL> CREATE TABLE ps_rc_case
(case_id        NUMBER  (15),
business_unit  VARCHAR2 (15),
id_cust        NUMBER);
  2    3    4
Table created.


SQL>  INSERT ALL
  2  INTO ps_rc_case VALUES (220908,'CO0', 789)
  3  INTO ps_rc_case VALUES (220909,'CO0', 789)
  4  INTO ps_rc_case VALUES (220910,'CO0', 789)
  5  INTO ps_rc_case VALUES (220911,'CO0', 789)
  6  INTO ps_rc_case VALUES (220912,'CO0', 789)
  7  INTO ps_rc_case VALUES (220913,'CO0', 789)
  8  INTO ps_rc_case VALUES (220914,'CO0', 789)
  9  INTO ps_rc_case VALUES (221005,'CO0', 789)
 10  INTO ps_rc_case VALUES (221006,'CO0', 789)
 11  INTO ps_rc_case VALUES (221056,'CO0', 789)
 12  INTO ps_rc_case VALUES (221057,'CO0', 789)
 13  INTO ps_rc_case VALUES (221062,'CO0', 789)
 14  INTO ps_rc_case VALUES (221063,'CO0', 789)
 15  SELECT * FROM DUAL;

13 rows created.


SQL> CREATE OR REPLACE TYPE t_cases AS OBJECT (
                                        case_id number(15),
                                business_unit varchar2(15));



Type created.

SQL> create or replace type t_cases_table as table of t_cases;



Type created.


SQL> DECLARE
  2  cases_table t_cases_table;
  3  BEGIN
  4  SELECT t_cases (case_id, business_unit)
  5  BULK   COLLECT INTO cases_table
  6  FROM   ps_rc_case
  7  WHERE  id_cust = 789;
  8  --
  9  INSERT INTO vio2
 10  SELECT a.case_id, a.business_unit
 11  FROM   ps_rc_case a,
 12           TABLE (CAST (cases_table AS t_cases_table)) b
 13  WHERE  b.case_id = a.case_id
 14  AND    b.business_unit = a.business_unit;
 15  COMMIT;
 16  END;
 17  /

PL/SQL procedure successfully completed.

SQL> select * from vio2;

   CASE_ID BUSINESS_UNIT
---------- -------------
    220908 CO0
    220909 CO0
    220910 CO0
    220911 CO0
    220912 CO0
    220913 CO0
    220914 CO0
    221005 CO0
    221006 CO0
    221056 CO0
    221057 CO0

   CASE_ID BUSINESS_UNIT
---------- -------------
    221062 CO0
    221063 CO0

13 rows selected.


I don't know which might be the problem, if there are some possible configurations that are applied t the database in such way that bulk collect is not working.

I have tried to check and i realize that:
select count(*) into countt from table(cast(cases_table as t_cases_table)) where case_id is  null;
dbms_output.put_line(countt);


it returns 13, so when try to read from the collection as a table is reading all values as null.

Any ideas?




Re: using nested tables with CAST as normal table (merged 3) [message #392677 is a reply to message #392574] Wed, 18 March 2009 20:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You need to post a run of the full test case on the database that doesn't work, in the same manner that you posted a run of the full test case on the database that does work.
Re: using nested tables with CAST as normal table (merged 3) [message #392817 is a reply to message #392677] Thu, 19 March 2009 06:17 Go to previous message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

is amazing, now is working.

What i did is to drop types and re-create them in sqlplus again.
Now everything is working just fine.

I will try again to drop it and re-create them in Pl/sql Developer to see if this was the problem.

Thanks.


Previous Topic: Insering data into one single table by taking data from 2 different tables
Next Topic: Order by Dates - Blanks coming first
Goto Forum:
  


Current Time: Fri Dec 09 00:01:13 CST 2016

Total time taken to generate the page: 0.12991 seconds