Needed help to totally remove Temp tables oracle procedures - Oracle - PL/SQL
CREATE OR REPLACE PROCEDURE myDemoStoreProc
(
inputVariable1 IN NUMBER DEFAULT 0 ,
v_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
INSERT INTO temptable1(
SELECT DISTINCT FROM TABLE1
WHERE Col1 = 'logic1' );
INSERT INTO temptable2(
SELECT col2 ,
NVL(( SELECT col1
FROM temptable1 tt1
WHERE sm.col1 = tt1.col1), 0) col3,
col4
FROM table2 sm);
DELETE temptable2
WHERE col4 IN ( 'logic2','logic3' )
OR col4 IS NULL;
IF NVL(inputVariable1 , 0) = 1 THEN
DELETE temptable2
WHERE col1!= 'logic4';
END IF;
OPEN v_cursor FOR
SELECT col1,
col2,
col3,
col4
FROM temptable2;
DBMS_SQL.RETURN_RESULT(v_cursor) ;
END;
As you can see , that there is two temp tables getting used in this stored procedure, how I can remove the dependency of temp tables, can rewrite the whole stored procedure without temp tables
I don't need full code, maybe a pseudo code to adjust the last delete and If logic.
I was trying like a big select query but its not very convenient to do so.
oracle plsql
edited Jan 10 at 21:44
marc_s
578k12911161261
asked Nov 14 '18 at 16:13
ShaswataShaswata
79111
add a comment |
CREATE OR REPLACE PROCEDURE myDemoStoreProc
(
inputVariable1 IN NUMBER DEFAULT 0 ,
v_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
INSERT INTO temptable1(
SELECT DISTINCT FROM TABLE1
WHERE Col1 = 'logic1' );
INSERT INTO temptable2(
SELECT col2 ,
NVL(( SELECT col1
FROM temptable1 tt1
WHERE sm.col1 = tt1.col1), 0) col3,
col4
FROM table2 sm);
DELETE temptable2
WHERE col4 IN ( 'logic2','logic3' )
OR col4 IS NULL;
IF NVL(inputVariable1 , 0) = 1 THEN
DELETE temptable2
WHERE col1!= 'logic4';
END IF;
OPEN v_cursor FOR
SELECT col1,
col2,
col3,
col4
FROM temptable2;
DBMS_SQL.RETURN_RESULT(v_cursor) ;
END;
As you can see , that there is two temp tables getting used in this stored procedure, how I can remove the dependency of temp tables, can rewrite the whole stored procedure without temp tables
I don't need full code, maybe a pseudo code to adjust the last delete and If logic.
I was trying like a big select query but its not very convenient to do so.
oracle plsql
edited Jan 10 at 21:44
marc_s
578k12911161261
asked Nov 14 '18 at 16:13
ShaswataShaswata
79111
add a comment |
CREATE OR REPLACE PROCEDURE myDemoStoreProc
(
inputVariable1 IN NUMBER DEFAULT 0 ,
v_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
INSERT INTO temptable1(
SELECT DISTINCT FROM TABLE1
WHERE Col1 = 'logic1' );
INSERT INTO temptable2(
SELECT col2 ,
NVL(( SELECT col1
FROM temptable1 tt1
WHERE sm.col1 = tt1.col1), 0) col3,
col4
FROM table2 sm);
DELETE temptable2
WHERE col4 IN ( 'logic2','logic3' )
OR col4 IS NULL;
IF NVL(inputVariable1 , 0) = 1 THEN
DELETE temptable2
WHERE col1!= 'logic4';
END IF;
OPEN v_cursor FOR
SELECT col1,
col2,
col3,
col4
FROM temptable2;
DBMS_SQL.RETURN_RESULT(v_cursor) ;
END;
As you can see , that there is two temp tables getting used in this stored procedure, how I can remove the dependency of temp tables, can rewrite the whole stored procedure without temp tables
I don't need full code, maybe a pseudo code to adjust the last delete and If logic.
I was trying like a big select query but its not very convenient to do so.
oracle plsql
edited Jan 10 at 21:44
marc_s
578k12911161261
asked Nov 14 '18 at 16:13
ShaswataShaswata
79111
CREATE OR REPLACE PROCEDURE myDemoStoreProc
(
inputVariable1 IN NUMBER DEFAULT 0 ,
v_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
INSERT INTO temptable1(
SELECT DISTINCT FROM TABLE1
WHERE Col1 = 'logic1' );
INSERT INTO temptable2(
SELECT col2 ,
NVL(( SELECT col1
FROM temptable1 tt1
WHERE sm.col1 = tt1.col1), 0) col3,
col4
FROM table2 sm);
DELETE temptable2
WHERE col4 IN ( 'logic2','logic3' )
OR col4 IS NULL;
IF NVL(inputVariable1 , 0) = 1 THEN
DELETE temptable2
WHERE col1!= 'logic4';
END IF;
OPEN v_cursor FOR
SELECT col1,
col2,
col3,
col4
FROM temptable2;
DBMS_SQL.RETURN_RESULT(v_cursor) ;
END;
As you can see , that there is two temp tables getting used in this stored procedure, how I can remove the dependency of temp tables, can rewrite the whole stored procedure without temp tables
I don't need full code, maybe a pseudo code to adjust the last delete and If logic.
I was trying like a big select query but its not very convenient to do so.
oracle plsql
oracle plsql
edited Jan 10 at 21:44
marc_s
578k12911161261
asked Nov 14 '18 at 16:13
ShaswataShaswata
79111
edited Jan 10 at 21:44
marc_s
578k12911161261
asked Nov 14 '18 at 16:13
ShaswataShaswata
79111
edited Jan 10 at 21:44
marc_s
578k12911161261
edited Jan 10 at 21:44
marc_s
578k12911161261
edited Jan 10 at 21:44
marc_s
578k12911161261
578k12911161261
asked Nov 14 '18 at 16:13
ShaswataShaswata
79111
asked Nov 14 '18 at 16:13
ShaswataShaswata
79111
asked Nov 14 '18 at 16:13
ShaswataShaswata
79111
79111
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Try below query and see if able to achieve same functionality :
IF NVL(inputVariable1 , 0) = 1 THEN
OPEN v_cursor FOR
select col1,col2,NVL(col1,0) col3,col4
from TABLE1
JOIN TABLE 2
ON TABLE2.col1=TABLE1.COL1
AND (TABLE2.COL4 NOT IN ('logic2','logic3') OR TABLE2.COL4 IS NOT NULL)
AND col1!= 'logic4';
DBMS_SQL.RETURN_RESULT(v_cursor) ;
END IF;
answered Nov 14 '18 at 16:55
kanagarajkanagaraj
35417
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304493%2fneeded-help-to-totally-remove-temp-tables-oracle-procedures-oracle-pl-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try below query and see if able to achieve same functionality :
IF NVL(inputVariable1 , 0) = 1 THEN
OPEN v_cursor FOR
select col1,col2,NVL(col1,0) col3,col4
from TABLE1
JOIN TABLE 2
ON TABLE2.col1=TABLE1.COL1
AND (TABLE2.COL4 NOT IN ('logic2','logic3') OR TABLE2.COL4 IS NOT NULL)
AND col1!= 'logic4';
DBMS_SQL.RETURN_RESULT(v_cursor) ;
END IF;
answered Nov 14 '18 at 16:55
kanagarajkanagaraj
35417
add a comment |
Try below query and see if able to achieve same functionality :
IF NVL(inputVariable1 , 0) = 1 THEN
OPEN v_cursor FOR
select col1,col2,NVL(col1,0) col3,col4
from TABLE1
JOIN TABLE 2
ON TABLE2.col1=TABLE1.COL1
AND (TABLE2.COL4 NOT IN ('logic2','logic3') OR TABLE2.COL4 IS NOT NULL)
AND col1!= 'logic4';
DBMS_SQL.RETURN_RESULT(v_cursor) ;
END IF;
answered Nov 14 '18 at 16:55
kanagarajkanagaraj
35417
add a comment |
Try below query and see if able to achieve same functionality :
IF NVL(inputVariable1 , 0) = 1 THEN
OPEN v_cursor FOR
select col1,col2,NVL(col1,0) col3,col4
from TABLE1
JOIN TABLE 2
ON TABLE2.col1=TABLE1.COL1
AND (TABLE2.COL4 NOT IN ('logic2','logic3') OR TABLE2.COL4 IS NOT NULL)
AND col1!= 'logic4';
DBMS_SQL.RETURN_RESULT(v_cursor) ;
END IF;
answered Nov 14 '18 at 16:55
kanagarajkanagaraj
35417
Try below query and see if able to achieve same functionality :
IF NVL(inputVariable1 , 0) = 1 THEN
OPEN v_cursor FOR
select col1,col2,NVL(col1,0) col3,col4
from TABLE1
JOIN TABLE 2
ON TABLE2.col1=TABLE1.COL1
AND (TABLE2.COL4 NOT IN ('logic2','logic3') OR TABLE2.COL4 IS NOT NULL)
AND col1!= 'logic4';
DBMS_SQL.RETURN_RESULT(v_cursor) ;
END IF;
answered Nov 14 '18 at 16:55
kanagarajkanagaraj
35417
answered Nov 14 '18 at 16:55
kanagarajkanagaraj
35417
answered Nov 14 '18 at 16:55
kanagarajkanagaraj
35417
answered Nov 14 '18 at 16:55
kanagarajkanagaraj
35417
35417
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304493%2fneeded-help-to-totally-remove-temp-tables-oracle-procedures-oracle-pl-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
