Home » SQL & PL/SQL » SQL & PL/SQL » Need Help on Pl/Sql (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Need Help on Pl/Sql [message #670639] Tue, 17 July 2018 10:28 Go to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Hi All,

My Requirement is to transpose data in a table and then do a join with another table and finally insert the fetched data in to a Table.I'm new to PL/SQL.

I tried writing a little not sure if it is correct.Can Someone help correcting the error in this code .I would like to register this as procedure finally.

Any help would be appreciated and it would help me learn.

DECLARE
V_RINGGOLD_ID;
V_RINGGOLD_NAME;
V_RINGGOLD_SUBJECT;
V_RINGGOLD_SUBJECT_MAPPING;
V_PQ_SUBJECT;
CURSOR RG_SUBJ
IS
SELECT DISTINCT RINGGOLD_ID FROM PQINF.RINGGOLD_IDENTIFY_SUBJECTS;

CURSOR PQ_SUBJ
IS 
 WITH
      ringgold as (
         select RINGGOLD_ID, RINGGOLD_NAME,
                to_char(regexp_substr(SUBJECTS, '[^,]+', 1, column_value)) RINGGOLD_SUBJECT
         from (select * from PQINF.RINGGOLD_IDENTIFY_SUBJECTS where ringgold_id=RG_SUBJ.RINGGOLD_ID),
             table(cast(multiset(select level from dual
                                 connect by level <= regexp_count(SUBJECTS,',')+1)
                    as sys.odciNumberList))
         where rownum > 0
     )
 SELECT  a.RINGGOLD_ID, a.RINGGOLD_NAME,a.RINGGOLD_SUBJECT,z.ringgold_subject ringgold_subject_mapping,z.PQ_SUBJECT
 FROM ringgold a, PQINF.PQ_GOLD_SUBJECTS z
 WHERE a.ringgold_subject=z.ringgold_subject(+)
 ORDER by 1, 2, 3, 4;
 BEGIN 
 OPEN PQ_SUBJ;
 LOOP
 FETCH PQ_SUBJ INTO V_RINGGOLD_ID,V_RINGGOLD_NAME,V_RINGGOLD_SUBJECT,V_RINGGOLD_SUBJECT_MAPPING,V_PQ_SUBJECT;
 EXIT 
 WHEN PQ_SUBJ%NOTFOUND;
 INSERT INTO PQINF.PQ_RINGGOLD_MAPPING(RINGGOLD_ID,RINGGOLD_NAME,RINGGOLD_SUBJECT,RINGGOLD_SUBJECT_MAPPING,PQ_SUBJECT)
 END LOOP;
 CLOSE PQ_SUBJ;
 END;
 
Re: Need Help on Pl/Sql [message #670640 is a reply to message #670639] Tue, 17 July 2018 10:37 Go to previous messageGo to next message
cookiemonster
Messages: 13746
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's hard to fix errors we can't see.
Post the full error code.

That said - you can and should do this as a single insert/select statement - no cursors, no loops.
Write a select that gets the data you want then plug it into an insert statement.
Re: Need Help on Pl/Sql [message #670641 is a reply to message #670640] Tue, 17 July 2018 10:47 Go to previous messageGo to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Hi Cookie Monster,

The Table -PQINF.RINGGOLD_IDENTIFY_SUBJECTS has 22k records and table PQINF.PQ_GOLD_SUBJECTS has 918 records.

The end result is too huge as I need whole matching .So I thought Pl/sql code might help get the data quicker.Please correct me if wrong

Can you tell me how can use the Insert statement along with this SELECT

WITH
      ringgold as (
         select RINGGOLD_ID, RINGGOLD_NAME,
                to_char(regexp_substr(SUBJECTS, '[^,]+', 1, column_value)) RINGGOLD_SUBJECT
         from (select * from PQINF.RINGGOLD_IDENTIFY_SUBJECTS),
             table(cast(multiset(select level from dual
                                 connect by level <= regexp_count(SUBJECTS,',')+1)
                    as sys.odciNumberList))
         where rownum > 0
     )
   select  a.RINGGOLD_ID, a.RINGGOLD_NAME,a.RINGGOLD_SUBJECT,z.ringgold_subject ringgold_subject_mapping, z.PQ_SUBJECT
  from ringgold a, PQINF.PQ_GOLD_SUBJECTS z
 where a.ringgold_subject=z.ringgold_subject(+)
 order by 1, 2, 3, 4
  /
Re: Need Help on Pl/Sql [message #670643 is a reply to message #670641] Tue, 17 July 2018 10:50 Go to previous messageGo to next message
BlackSwan
Messages: 26639
Registered: January 2009
Location: SoCal
Senior Member
>.So I thought Pl/sql code might help get the data quicker.Please correct me if wrong
You are wrong.
a row by row INSERT will be MUCH slower than single SQL statement
Re: Need Help on Pl/Sql [message #670644 is a reply to message #670643] Tue, 17 July 2018 10:56 Go to previous messageGo to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Thanks black swan for correcting.

I'm trying to Insert as Cookie monster has suggested.Will see how it goes.

Re: Need Help on Pl/Sql [message #670648 is a reply to message #670641] Tue, 17 July 2018 11:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
vharish006 wrote on Tue, 17 July 2018 21:17

Can you tell me how can use the Insert statement along with this SELECT
Of course, you can do
INSERT INTO [i]table_name[/i] (<columns_list_goes here>) SELECT <columns_list_goes here> FROM tables/joins .. WHERE clause ..

Or, if you want to use the WITH clause you posted above, you could simply do:

INSERT INTO [i]your_table[/i] ( columns_you_referred_in_select_below)
WITH
      ringgold as (
         select RINGGOLD_ID, RINGGOLD_NAME,
                to_char(regexp_substr(SUBJECTS, '[^,]+', 1, column_value)) RINGGOLD_SUBJECT
         from (select * from PQINF.RINGGOLD_IDENTIFY_SUBJECTS),
             table(cast(multiset(select level from dual
                                 connect by level <= regexp_count(SUBJECTS,',')+1)
                    as sys.odciNumberList))
         where rownum > 0
     )
   select  a.RINGGOLD_ID, a.RINGGOLD_NAME,a.RINGGOLD_SUBJECT,z.ringgold_subject ringgold_subject_mapping, z.PQ_SUBJECT
  from ringgold a, PQINF.PQ_GOLD_SUBJECTS z
 where a.ringgold_subject=z.ringgold_subject(+)
 order by 1, 2, 3, 4
  /
Re: Need Help on Pl/Sql [message #670650 is a reply to message #670648] Tue, 17 July 2018 11:18 Go to previous messageGo to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Thanks Lalith

I ran it with Insert statement 20 min earlier.It is still running as the data is around 22k.

Not sure if there is any way to speed up the process.

Re: Need Help on Pl/Sql [message #670651 is a reply to message #670648] Tue, 17 July 2018 11:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
May be a small example would help you:

SQL> CREATE TABLE t (num NUMBER);

table T created.


SQL> INSERT INTO t (num)
WITH DATA AS
(
  SELECT LEVEL FROM dual
  CONNECT BY LEVEL <= 10
)
SELECT * FROM DATA;

10 rows inserted.


SQL> SELECT * FROM t;

       NUM
----------
         1 
         2 
         3 
         4 
         5 
         6 
         7 
         8 
         9 
        10 

 10 rows selected 

Re: Need Help on Pl/Sql [message #670652 is a reply to message #670650] Tue, 17 July 2018 11:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
vharish006 wrote on Tue, 17 July 2018 21:48
Thanks Lalith

I ran it with Insert statement 20 min earlier.It is still running as the data is around 22k.

Not sure if there is any way to speed up the process.

I see you replied almost around same time I posted an example above. Looks like you already figured out how to do the INSERT.

Coming to your question about making the INSERT more efficient, there are ways to do it, like using APPEND hint with NOLOGGING clause. Direct-path inserts are faster than conventional-path inserts. However, there a lot of factors you need to consider, and number of restrictions.

I recommend you to do more research on this. There are plenty of materials on the Web about this topic. And, test it to understand better before you attempt any of this in PRODUCTION. Of course, once you do your research, and have any outstanding questions, you could always come back here with specific details.

[Updated on: Tue, 17 July 2018 11:33]

Report message to a moderator

Re: Need Help on Pl/Sql [message #670653 is a reply to message #670639] Tue, 17 July 2018 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 66713
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The problem has a background that started in this topic then continue in PM to lead to the SELECT part you have here (without the outer join that was later added by OP).
I hope he will explain you with more details the problem and post the required information for SQL performances question as I asked him in PM.

Re: Need Help on Pl/Sql [message #670654 is a reply to message #670653] Tue, 17 July 2018 11:59 Go to previous messageGo to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
The Outer Join is required as we need to get the values which are not matched in one of table.

The only Index i created is on Ringgold_id in table PQINF.RINGGOLD_IDENTIFY_SUBJECTS
Re: Need Help on Pl/Sql [message #670655 is a reply to message #670653] Tue, 17 July 2018 12:19 Go to previous message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 17 July 2018 22:16

The problem has a background that started in this topic then continue in PM to lead to the SELECT part you have here (without the outer join that was later added by OP).
I hope he will explain you with more details the problem and post the required information for SQL performances question as I asked him in PM.

Ah, OK! This is a continuation of a previous question by OP. Thanks for the details, Michel.
Previous Topic: query performance
Next Topic: Get last record from another table
Goto Forum:
  


Current Time: Fri Dec 06 04:02:27 CST 2019