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 |
|
vharish006
Messages: 70 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 |
cookiemonster
Messages: 13943 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 |
|
vharish006
Messages: 70 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 #670648 is a reply to message #670641] |
Tue, 17 July 2018 11:10 |
Lalit Kumar B
Messages: 3174 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 #670651 is a reply to message #670648] |
Tue, 17 July 2018 11:19 |
Lalit Kumar B
Messages: 3174 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 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
vharish006 wrote on Tue, 17 July 2018 21:48Thanks 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 #670655 is a reply to message #670653] |
Tue, 17 July 2018 12:19 |
Lalit Kumar B
Messages: 3174 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.
|
|
|
Goto Forum:
Current Time: Fri Sep 27 16:55:26 CDT 2024
|