Home » SQL & PL/SQL » SQL & PL/SQL » Using table type with merge (merged)
Using table type with merge (merged) [message #241987] Thu, 31 May 2007 05:24 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Can i use table type in the using clause of merge statement ?


Attached is the code of the procedure

create or replace procedure fnd_sample as
          cursor fnd_c is select * from fnd_columns;
          type test_t is table of fnd_columns%rowtype;
          fnd_t test_t;
begin
          fetch fnd_c bulk collect into fnd_t;
          for i in 1..fnd_t.count loop
                  merge into sample s using (select * from fnd_t) f
                  on (s.application_id = f.application_id)
                  when matched then
                          update set last_update_date=sysdate
                  when not matched then
                          insert 
(APPLICATION_ID,TABLE_ID,COLUMN_ID,COLUMN_NAME,USER_COLUMN_NAME,COLUMN_SEQUENCE,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
COLUMN_TYPE,WIDTH,NULL_ALLOWED_FLAG,TRANSLATE_FLAG,FLEXFIELD_USAGE_CODE) values
(f.APPLICATION_ID,f.TABLE_ID,f.COLUMN_ID,f.COLUMN_NAME,f.USER_COLUMN_NAME,
f.COLUMN_SEQUENCE,f.LAST_UPDATE_DATE,f.LAST_UPDATED_BY,f.CREATION_DATE,f.CREATED_BY,
f.LAST_UPDATE_LOGIN,f.COLUMN_TYPE,f.WIDTH,f.NULL_ALLOWED_FLAG,f.TRANSLATE_FLAG,f.FLEXFIELD_USAGE_CODE);
          end loop;
exception
          when others then
                  dbms_output.put_line(SQLERRM);
end;
/



I am getting an error saying that

/19 PL/SQL: SQL Statement ignored
8/60 PL/SQL: ORA-00942: table or view does not exist

[Updated on: Thu, 31 May 2007 05:28] by Moderator

Report message to a moderator

Re: Using table type with merge [message #241991 is a reply to message #241987] Thu, 31 May 2007 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Break your lines when you use code tags.
Post the real execution with line numbers.

fnd_t is not a SQL table it is a PL/SQL one and can't be used as it in SQL.
Why not directly use fnd_columns in the merge without any cursor loop?

"When others" should always end with "raise".
Just making dbms_output.put_line(sqlerrm) in "when others" is useless: if you don't put this exception block you get about the same output with a correct coding.

Regards
Michel

Re: Using table type with merge [message #241997 is a reply to message #241987] Thu, 31 May 2007 05:46 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Think so i have made some errors in the code ... its just a sample one

What I want to ask is whether I can use bulk update with merge ?

If I wanna go for bulk update then I need to use a table type or any other type in the merge statement ...whether this can be done ?
Re: Using table type with merge [message #242018 is a reply to message #241997] Thu, 31 May 2007 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But you don't have any bulk update statement in your code.
How can we answer a question that is not asked?

Bulk DML is introduced with "FORALL".
FORALL MERGE does not exist.

Regards
Michel
Re: Using table type with merge [message #242044 is a reply to message #241987] Thu, 31 May 2007 08:38 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Sorry for not being clear

Also is it possible to use a type with merge statement ?
Is there any alternative for using the same ?

create or replace procedure fnd_sample as
          cursor fnd_c is select * from fnd_columns;
          type test_t is table of fnd_columns%rowtype;
          fnd_t test_t;
begin
          fetch fnd_c bulk collect into fnd_t;
          merge into sample s fnd_t..............
Re: Using table type with merge [message #242079 is a reply to message #242044] Thu, 31 May 2007 09:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already gave you an answer: remove all but the merge statement and replace fnd_t with fnd_columns in it.
No PL/SQL. Just MERGE.

Regards
Michel
Using table function [message #242096 is a reply to message #241987] Thu, 31 May 2007 11:04 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I wanna use table function on a table type created inside a procedure .

I am using table function in a merge statement .


1 create or replace procedure fnd_proc as
2          cursor fnd_c is select * from fnd_columns;
3          type test_t is table of fnd_columns%rowtype;
4          fnd_t test_t;
5 begin
6          merge into sample s using (select * from table  (fnd_pkg1.get_records(cursor(select * from fnd_columns)))) f
7          on (s.application_id = f.application_id)
8          when matched then
9                  update set last_update_date=sysdate
10          when not matched then
11                 insert(APPLICATION_ID,TABLE_ID,COLUMN_ID) values(f.APPLICATION_ID,f.TABLE_ID,f.COLUMN_ID);
12 end;
/


create or replace package fnd_pkg1 as
	type fnd_type is table of fnd_columns%rowtype;
	function get_records(p_cursor IN  SYS_REFCURSOR) return fnd_type;
end;
/



create or replace package body fnd_pkg1 as
        function get_records(p_cursor IN  SYS_REFCURSOR) return fnd_type is
                fnd_data fnd_type;
        begin
                fetch p_cursor bulk collect into fnd_data;
                return fnd_data;
        end;
end;
/



When i compile the procedure fnd_proc I get the following error

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/11 PL/SQL: SQL Statement ignored
6/52 PL/SQL: ORA-22905: cannot access rows from a non-nested table
item

6/67 PLS-00642: local collection types not allowed in SQL statements


Let me know what has to be done
Re: Using table function [message #242102 is a reply to message #242096] Thu, 31 May 2007 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still the same question. Why not just?
merge into sample s using (select * from fnd_columns) f

or better:
merge into sample s using fnd_columns f

Regards
Michel

[Updated on: Thu, 31 May 2007 11:35]

Report message to a moderator

Re: Using table type with merge (merged) [message #242106 is a reply to message #241987] Thu, 31 May 2007 11:47 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Instead of using the table directly using a table type improves performance thats y I wanna do it using a table type Smile

I was able to compile the code now after making the pl/sql type as a sql type by creating it in sql instead in package

But i get a runtime error now

ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "APPLSYS.FND_PKG1", line 6
ORA-06512: at line 6
Re: Using table type with merge (merged) [message #242107 is a reply to message #242106] Thu, 31 May 2007 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will not improve the performances.
Even if this was allowed, this will slow down your merge and not just a little bit.

Regards
Michel
Re: Using table type with merge (merged) [message #487554 is a reply to message #241987] Tue, 28 December 2010 15:34 Go to previous messageGo to next message
armoza
Messages: 1
Registered: December 2010
Location: St. Louis
Junior Member
Hello,

Instead of using table type I created a cursor and a merge command for each record using 'select from dual'.
cr2_rec is the fertched record.

MERGE INTO Table2 target
USING
( SELECT
cr2_rec.PK_Field_1
,cr2_rec.Field_3
FROM DUAL)
ON
(
cr2_rec.PK_Field_1=target.PK_Field_1
AND 
cr2_rec.PK_Field_2=target.PK_Field_2
)
WHEN MATCHED THEN UPDATE SET
target.Field_3=cr2_rec.Field_3
WHEN NOT MATCHED THEN INSERT
(
target.PK_Field_1
,target.Field_3
,target.PK_Field_2
)
VALUES
(
cr2_rec.PK_Field_1
,cr2_rec.Field_3
,cr2_rec.PK_Field_2
); 

Hope this helps.
I still would like to know if using a table type is possible with Merge

Armoza.
Re: Using table type with merge (merged) [message #487555 is a reply to message #487554] Tue, 28 December 2010 15:40 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
SQL is SQL.
PL/SQL is PL/SQL
SQL is NOT PL/SQL.

You can NOT use PL/SQL datatype in SQL directly.
Stop trying to mix apples & bricks.
Re: Using table type with merge (merged) [message #487581 is a reply to message #487555] Wed, 29 December 2010 00:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Although I don't know whether you will see any performance improvement, there are two ways to do what you want to do. You can either declare pl/sql types within a package and use a pipelined function within the package to return the type or you can create sql object types and return the object type from a function. I have demonstrated both methods below.

-- pl/sql types within package and pipelined function:
SCOTT@orcl_11gR2> create table fnd_columns
  2    (application_id	  number,
  3  	table_id	  number,
  4  	column_id	  number,
  5  	last_update_date  date)
  6  /

Table created.

SCOTT@orcl_11gR2> insert into fnd_columns values (1, 2, 3, null)
  2  /

1 row created.

SCOTT@orcl_11gR2> insert into fnd_columns values (2, 3, 4, null)
  2  /

1 row created.

SCOTT@orcl_11gR2> create table sample
  2    (application_id	  number,
  3  	table_id	  number,
  4  	column_id	  number,
  5  	last_update_date  date)
  6  /

Table created.

SCOTT@orcl_11gR2> insert into sample values (1, 2, 3, null)
  2  /

1 row created.

SCOTT@orcl_11gR2> --
SCOTT@orcl_11gR2> create or replace package fnd_pkg1
  2  as
  3    type fnd_type is table of fnd_columns%rowtype;
  4    function get_records
  5  	 (p_cursor in sys_refcursor)
  6  	 return       fnd_type pipelined;
  7  end fnd_pkg1;
  8  /

Package created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace package body fnd_pkg1
  2  as
  3    function get_records
  4  	 (p_cursor in sys_refcursor)
  5  	 return       fnd_type pipelined
  6    is
  7  	 fnd_data     fnd_type;
  8    begin
  9  	 fetch p_cursor bulk collect into fnd_data;
 10  	 for i in 1 .. fnd_data.count loop
 11  	   pipe row (fnd_data(i));
 12  	 end loop;
 13  	 close p_cursor;
 14  	 return;
 15    end get_records;
 16  end fnd_pkg1;
 17  /

Package body created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> --
SCOTT@orcl_11gR2> create or replace procedure fnd_proc
  2  as
  3  begin
  4    merge into sample s
  5    using (select *
  6  	      from   table
  7  		       (fnd_pkg1.get_records
  8  			 (cursor (select * from fnd_columns)))) f
  9    on (s.application_id = f.application_id)
 10    when matched then
 11  	 update set last_update_date = sysdate
 12    when not matched then
 13  	 insert (APPLICATION_ID, TABLE_ID, COLUMN_ID)
 14  	 values (f.APPLICATION_ID, f.TABLE_ID, f.COLUMN_ID);
 15  end fnd_proc;
 16  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> exec fnd_proc

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select * from sample
  2  /

APPLICATION_ID   TABLE_ID  COLUMN_ID LAST_UPDA
-------------- ---------- ---------- ---------
             1          2          3 28-DEC-10
             2          3          4

2 rows selected.

SCOTT@orcl_11gR2>


-- sql object types and function that returns object type:
SCOTT@orcl_11gR2> create table fnd_columns
  2    (application_id	  number,
  3  	table_id	  number,
  4  	column_id	  number,
  5  	last_update_date  date)
  6  /

Table created.

SCOTT@orcl_11gR2> insert into fnd_columns values (1, 2, 3, null)
  2  /

1 row created.

SCOTT@orcl_11gR2> insert into fnd_columns values (2, 3, 4, null)
  2  /

1 row created.

SCOTT@orcl_11gR2> create table sample
  2    (application_id	  number,
  3  	table_id	  number,
  4  	column_id	  number,
  5  	last_update_date  date)
  6  /

Table created.

SCOTT@orcl_11gR2> insert into sample values (1, 2, 3, null)
  2  /

1 row created.

SCOTT@orcl_11gR2> --
SCOTT@orcl_11gR2> create or replace type fnd_sql_typ as object
  2    (application_id	  number,
  3  	table_id	  number,
  4  	column_id	  number,
  5  	last_update_date  date);
  6  /

Type created.

SCOTT@orcl_11gR2> create or replace type fnd_sql_type as table of fnd_sql_typ;
  2  /

Type created.

SCOTT@orcl_11gR2> create or replace function get_records
  2    (p_cursor in sys_refcursor)
  3    return	    fnd_sql_type
  4  is
  5    type fnd_type is table of fnd_columns%rowtype;
  6    fnd_data 	fnd_type;
  7    fnd_sql_data	fnd_sql_type := fnd_sql_type();
  8  begin
  9    fetch p_cursor bulk collect into fnd_data;
 10    for i in 1 .. fnd_data.count
 11    loop
 12  	 fnd_sql_data.extend;
 13  	 fnd_sql_data(fnd_sql_data.count) :=
 14  	   fnd_sql_typ
 15  	     (fnd_data(i).application_id,
 16  	      fnd_data(i).table_id,
 17  	      fnd_data(i).column_id,
 18  	      fnd_data(i).last_update_date);
 19    end loop;
 20    close p_cursor;
 21    return fnd_sql_data;
 22  end get_records;
 23  /

Function created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> --
SCOTT@orcl_11gR2> create or replace procedure fnd_proc
  2  as
  3  begin
  4    merge into sample s
  5    using (select *
  6  	      from   table
  7  		       (get_records
  8  			 (cursor (select * from fnd_columns)))) f
  9    on (s.application_id = f.application_id)
 10    when matched then
 11  	 update set last_update_date = sysdate
 12    when not matched then
 13  	 insert (APPLICATION_ID, TABLE_ID, COLUMN_ID)
 14  	 values (f.APPLICATION_ID, f.TABLE_ID, f.COLUMN_ID);
 15  end fnd_proc;
 16  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> exec fnd_proc

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select * from sample
  2  /

APPLICATION_ID   TABLE_ID  COLUMN_ID LAST_UPDA
-------------- ---------- ---------- ---------
             1          2          3 28-DEC-10
             2          3          4

2 rows selected.

SCOTT@orcl_11gR2>




Re: Using table type with merge (merged) [message #566313 is a reply to message #241987] Wed, 12 September 2012 11:11 Go to previous messageGo to next message
sss111ind
Messages: 563
Registered: April 2012
Location: India
Senior Member

Hi All,

Is there any issue if I am using forall merge statement with pl/sql table.

Regards,
Nathan
Re: Using table type with merge (merged) [message #566315 is a reply to message #566313] Wed, 12 September 2012 11:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
sss111ind wrote on Wed, 12 September 2012 09:11


... Is there any issue if I am using forall merge statement with pl/sql table. ...


Michel Cadot wrote on Thu, 31 May 2007 04:46


... FORALL MERGE does not exist. ...


Please see the following link for FORALL syntax, including the DML statement that may be INSERT, or UPDATE, or DELETE, but not MERGE:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/forall_statement.htm#LNPLS01321

You may find some ways to sort of make it work, but not efficiently.

[Updated on: Wed, 12 September 2012 11:37]

Report message to a moderator

Re: Using table type with merge (merged) [message #566322 is a reply to message #566315] Wed, 12 September 2012 12:31 Go to previous messageGo to next message
sss111ind
Messages: 563
Registered: April 2012
Location: India
Senior Member

Hi Barbara,

But it is running successfully while I am testing. And it is not throwing any error.
Should I change the code forall to for loop. Please help me.

Regards,
Nathan


Re: Using table type with merge (merged) [message #566323 is a reply to message #566322] Wed, 12 September 2012 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What "it" is?

Regards
Michel
Re: Using table type with merge (merged) [message #566327 is a reply to message #566322] Wed, 12 September 2012 15:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Please post the code, including any create and insert statements necessary for a test case.
Re: Using table type with merge (merged) [message #566368 is a reply to message #566327] Thu, 13 September 2012 09:04 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
Back to the original question:
Quote:
Can i use table type in the using clause of merge statement ?

Yes, you can.

SET SERVEROUTPUT ON SIZE 900000;

CREATE OR REPLACE TYPE VARCHAR100 AS TABLE OF VARCHAR2(100);

CREATE TABLE test
  (nr INTEGER,
   id VARCHAR2(100));

--TRUNCATE TABLE test;

INSERT 
  INTO test (  nr, id)
     VALUES ( 999,'C');  

DECLARE
   tt VARCHAR100 := VARCHAR100('A','B','C','D'); 
BEGIN

  FOR i IN tt.first .. tt.last
  LOOP
    dbms_output.put_line('tt('||i||')='||tt(i));
  END LOOP;
  
--variant FORALL 
--  FORALL i IN tt.first .. tt.last
--    INSERT INTO test (id) VALUES (tt(i));

  MERGE INTO test t
  USING (SELECT rownum rn, column_value id FROM TABLE(tt)) s
     ON (t.id=s.id) 
   WHEN MATCHED THEN
     UPDATE SET t.nr=rn
   WHEN NOT MATCHED THEN
     INSERT (nr, id) VALUES(rn,s.id);

END;

SELECT * FROM test;

nr     id
------------
3	C
4	D
2	B
1	A

[Updated on: Thu, 13 September 2012 09:05]

Report message to a moderator

Previous Topic: Eliminating Timestamp from third party input
Next Topic: update employee id
Goto Forum:
  


Current Time: Tue Dec 06 16:03:22 CST 2016

Total time taken to generate the page: 0.22890 seconds