How to dynamically create record in PL/Sql based on Table name as Input
0
I want to understand how to create the record type dynamically based on the table name received as input to the procedure.
Ex:
PROCEDURE xxtest(p_table_name IN VARCHAR2)
IS
TYPE t_test_type IS TABLE OF p_table_name%ROWTYPE;
v_test_type t_test_type;
BEGIN
NULL;
END;
oracle plsql plsql-package
edited Nov 26 '18 at 12:34
a_horse_with_no_name
297k46452548
asked Nov 26 '18 at 10:44
SushilSushil
1
add a comment |
0
I want to understand how to create the record type dynamically based on the table name received as input to the procedure.
Ex:
PROCEDURE xxtest(p_table_name IN VARCHAR2)
IS
TYPE t_test_type IS TABLE OF p_table_name%ROWTYPE;
v_test_type t_test_type;
BEGIN
NULL;
END;
oracle plsql plsql-package
edited Nov 26 '18 at 12:34
a_horse_with_no_name
297k46452548
asked Nov 26 '18 at 10:44
SushilSushil
1
Can't be done. Record types have fixed projections. What is the actual problem you're trying to solve? That is, why requirement has lead you to think you need to dynamically create record type variables?
– APC
Nov 26 '18 at 12:31
this was asked to me during an interview and was unable to get the solution. So was curious to know how this can be achieved.
– Sushil
Nov 26 '18 at 14:54
People do ask strange questions in interviews.
– APC
Nov 26 '18 at 15:02
Maybe it was one of those "gotcha" questions... the correct answer is "what is the problem you're trying to solve?" (i.e. perhaps their contrived problem can be solved using SQL without needing a PL/SQL type)
– Jeffrey Kemp
Nov 27 '18 at 2:45
add a comment |
0
0
0
I want to understand how to create the record type dynamically based on the table name received as input to the procedure.
Ex:
PROCEDURE xxtest(p_table_name IN VARCHAR2)
IS
TYPE t_test_type IS TABLE OF p_table_name%ROWTYPE;
v_test_type t_test_type;
BEGIN
NULL;
END;
oracle plsql plsql-package
edited Nov 26 '18 at 12:34
a_horse_with_no_name
297k46452548
asked Nov 26 '18 at 10:44
SushilSushil
1
I want to understand how to create the record type dynamically based on the table name received as input to the procedure.
Ex:
PROCEDURE xxtest(p_table_name IN VARCHAR2)
IS
TYPE t_test_type IS TABLE OF p_table_name%ROWTYPE;
v_test_type t_test_type;
BEGIN
NULL;
END;
oracle plsql plsql-package
oracle plsql plsql-package
edited Nov 26 '18 at 12:34
a_horse_with_no_name
297k46452548
asked Nov 26 '18 at 10:44
SushilSushil
1
edited Nov 26 '18 at 12:34
a_horse_with_no_name
297k46452548
asked Nov 26 '18 at 10:44
SushilSushil
1
edited Nov 26 '18 at 12:34
a_horse_with_no_name
297k46452548
edited Nov 26 '18 at 12:34
a_horse_with_no_name
297k46452548
edited Nov 26 '18 at 12:34
a_horse_with_no_name
297k46452548
297k46452548
asked Nov 26 '18 at 10:44
SushilSushil
1
asked Nov 26 '18 at 10:44
SushilSushil
1
asked Nov 26 '18 at 10:44
SushilSushil
1
1
Can't be done. Record types have fixed projections. What is the actual problem you're trying to solve? That is, why requirement has lead you to think you need to dynamically create record type variables?
– APC
Nov 26 '18 at 12:31
this was asked to me during an interview and was unable to get the solution. So was curious to know how this can be achieved.
– Sushil
Nov 26 '18 at 14:54
People do ask strange questions in interviews.
– APC
Nov 26 '18 at 15:02
Maybe it was one of those "gotcha" questions... the correct answer is "what is the problem you're trying to solve?" (i.e. perhaps their contrived problem can be solved using SQL without needing a PL/SQL type)
– Jeffrey Kemp
Nov 27 '18 at 2:45
add a comment |
Can't be done. Record types have fixed projections. What is the actual problem you're trying to solve? That is, why requirement has lead you to think you need to dynamically create record type variables?
– APC
Nov 26 '18 at 12:31
this was asked to me during an interview and was unable to get the solution. So was curious to know how this can be achieved.
– Sushil
Nov 26 '18 at 14:54
People do ask strange questions in interviews.
– APC
Nov 26 '18 at 15:02
Maybe it was one of those "gotcha" questions... the correct answer is "what is the problem you're trying to solve?" (i.e. perhaps their contrived problem can be solved using SQL without needing a PL/SQL type)
– Jeffrey Kemp
Nov 27 '18 at 2:45
Can't be done. Record types have fixed projections. What is the actual problem you're trying to solve? That is, why requirement has lead you to think you need to dynamically create record type variables?
– APC
Nov 26 '18 at 12:31
Can't be done. Record types have fixed projections. What is the actual problem you're trying to solve? That is, why requirement has lead you to think you need to dynamically create record type variables?
– APC
Nov 26 '18 at 12:31
this was asked to me during an interview and was unable to get the solution. So was curious to know how this can be achieved.
– Sushil
Nov 26 '18 at 14:54
this was asked to me during an interview and was unable to get the solution. So was curious to know how this can be achieved.
– Sushil
Nov 26 '18 at 14:54
People do ask strange questions in interviews.
– APC
Nov 26 '18 at 15:02
People do ask strange questions in interviews.
– APC
Nov 26 '18 at 15:02
Maybe it was one of those "gotcha" questions... the correct answer is "what is the problem you're trying to solve?" (i.e. perhaps their contrived problem can be solved using SQL without needing a PL/SQL type)
– Jeffrey Kemp
Nov 27 '18 at 2:45
Maybe it was one of those "gotcha" questions... the correct answer is "what is the problem you're trying to solve?" (i.e. perhaps their contrived problem can be solved using SQL without needing a PL/SQL type)
– Jeffrey Kemp
Nov 27 '18 at 2:45
add a comment |
1 Answer
1
active
oldest
votes
0
If i was at your place i would try to do simething like that.
create table test_table (f1 number, f2 number);
insert into test_table (f1,f2) values (0,1);
insert into test_table (f1,f2) values (2,3);
select * from test_table
declare
PROCEDURE xxtest(p_table_name IN VARCHAR2) IS
vSql varchar2(4000);
BEGIN
vSql := 'declare ';
vSql := vSql || 'TYPE t_test_type IS TABLE OF '||p_table_name||'%ROWTYPE; ';
vSql := vSql || 'v_test_type t_test_type; ';
vSql := vSql || 'begin ';
vSql := vSql || 'select a1.f1, a1.f2 ';
vSql := vSql || 'bulk collect into v_test_type ';
vSql := vSql || 'from '||p_table_name||' a1; ';
vSql := vSql || 'for i in v_test_type.first..v_test_type.last ';
vSql := vSql || 'loop ';
vSql := vSql || 'dbms_output.put_line(v_test_type(i).f1||'' ''||v_test_type(i).f1); ';
vSql := vSql || 'end loop; ';
vSql := vSql || 'end; ';
dbms_output.put_line(vSQL);
execute immediate vSQL;
END;
begin
xxtest('test_table');
end;
But actually it took almost hour for me and it's real bad question for interview...
May be they don't want to work with you?:)
answered Nov 27 '18 at 14:12
ДмитрийДмитрий
349
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
});
}
});
draft saved
draft discarded
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%2f53479400%2fhow-to-dynamically-create-record-in-pl-sql-based-on-table-name-as-input%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
0
If i was at your place i would try to do simething like that.
create table test_table (f1 number, f2 number);
insert into test_table (f1,f2) values (0,1);
insert into test_table (f1,f2) values (2,3);
select * from test_table
declare
PROCEDURE xxtest(p_table_name IN VARCHAR2) IS
vSql varchar2(4000);
BEGIN
vSql := 'declare ';
vSql := vSql || 'TYPE t_test_type IS TABLE OF '||p_table_name||'%ROWTYPE; ';
vSql := vSql || 'v_test_type t_test_type; ';
vSql := vSql || 'begin ';
vSql := vSql || 'select a1.f1, a1.f2 ';
vSql := vSql || 'bulk collect into v_test_type ';
vSql := vSql || 'from '||p_table_name||' a1; ';
vSql := vSql || 'for i in v_test_type.first..v_test_type.last ';
vSql := vSql || 'loop ';
vSql := vSql || 'dbms_output.put_line(v_test_type(i).f1||'' ''||v_test_type(i).f1); ';
vSql := vSql || 'end loop; ';
vSql := vSql || 'end; ';
dbms_output.put_line(vSQL);
execute immediate vSQL;
END;
begin
xxtest('test_table');
end;
But actually it took almost hour for me and it's real bad question for interview...
May be they don't want to work with you?:)
answered Nov 27 '18 at 14:12
ДмитрийДмитрий
349
add a comment |
0
If i was at your place i would try to do simething like that.
create table test_table (f1 number, f2 number);
insert into test_table (f1,f2) values (0,1);
insert into test_table (f1,f2) values (2,3);
select * from test_table
declare
PROCEDURE xxtest(p_table_name IN VARCHAR2) IS
vSql varchar2(4000);
BEGIN
vSql := 'declare ';
vSql := vSql || 'TYPE t_test_type IS TABLE OF '||p_table_name||'%ROWTYPE; ';
vSql := vSql || 'v_test_type t_test_type; ';
vSql := vSql || 'begin ';
vSql := vSql || 'select a1.f1, a1.f2 ';
vSql := vSql || 'bulk collect into v_test_type ';
vSql := vSql || 'from '||p_table_name||' a1; ';
vSql := vSql || 'for i in v_test_type.first..v_test_type.last ';
vSql := vSql || 'loop ';
vSql := vSql || 'dbms_output.put_line(v_test_type(i).f1||'' ''||v_test_type(i).f1); ';
vSql := vSql || 'end loop; ';
vSql := vSql || 'end; ';
dbms_output.put_line(vSQL);
execute immediate vSQL;
END;
begin
xxtest('test_table');
end;
But actually it took almost hour for me and it's real bad question for interview...
May be they don't want to work with you?:)
answered Nov 27 '18 at 14:12
ДмитрийДмитрий
349
add a comment |
0
0
0
If i was at your place i would try to do simething like that.
create table test_table (f1 number, f2 number);
insert into test_table (f1,f2) values (0,1);
insert into test_table (f1,f2) values (2,3);
select * from test_table
declare
PROCEDURE xxtest(p_table_name IN VARCHAR2) IS
vSql varchar2(4000);
BEGIN
vSql := 'declare ';
vSql := vSql || 'TYPE t_test_type IS TABLE OF '||p_table_name||'%ROWTYPE; ';
vSql := vSql || 'v_test_type t_test_type; ';
vSql := vSql || 'begin ';
vSql := vSql || 'select a1.f1, a1.f2 ';
vSql := vSql || 'bulk collect into v_test_type ';
vSql := vSql || 'from '||p_table_name||' a1; ';
vSql := vSql || 'for i in v_test_type.first..v_test_type.last ';
vSql := vSql || 'loop ';
vSql := vSql || 'dbms_output.put_line(v_test_type(i).f1||'' ''||v_test_type(i).f1); ';
vSql := vSql || 'end loop; ';
vSql := vSql || 'end; ';
dbms_output.put_line(vSQL);
execute immediate vSQL;
END;
begin
xxtest('test_table');
end;
But actually it took almost hour for me and it's real bad question for interview...
May be they don't want to work with you?:)
answered Nov 27 '18 at 14:12
ДмитрийДмитрий
349
If i was at your place i would try to do simething like that.
create table test_table (f1 number, f2 number);
insert into test_table (f1,f2) values (0,1);
insert into test_table (f1,f2) values (2,3);
select * from test_table
declare
PROCEDURE xxtest(p_table_name IN VARCHAR2) IS
vSql varchar2(4000);
BEGIN
vSql := 'declare ';
vSql := vSql || 'TYPE t_test_type IS TABLE OF '||p_table_name||'%ROWTYPE; ';
vSql := vSql || 'v_test_type t_test_type; ';
vSql := vSql || 'begin ';
vSql := vSql || 'select a1.f1, a1.f2 ';
vSql := vSql || 'bulk collect into v_test_type ';
vSql := vSql || 'from '||p_table_name||' a1; ';
vSql := vSql || 'for i in v_test_type.first..v_test_type.last ';
vSql := vSql || 'loop ';
vSql := vSql || 'dbms_output.put_line(v_test_type(i).f1||'' ''||v_test_type(i).f1); ';
vSql := vSql || 'end loop; ';
vSql := vSql || 'end; ';
dbms_output.put_line(vSQL);
execute immediate vSQL;
END;
begin
xxtest('test_table');
end;
But actually it took almost hour for me and it's real bad question for interview...
May be they don't want to work with you?:)
answered Nov 27 '18 at 14:12
ДмитрийДмитрий
349
answered Nov 27 '18 at 14:12
ДмитрийДмитрий
349
answered Nov 27 '18 at 14:12
ДмитрийДмитрий
349
answered Nov 27 '18 at 14:12
ДмитрийДмитрий
349
349
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53479400%2fhow-to-dynamically-create-record-in-pl-sql-based-on-table-name-as-input%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
Can't be done. Record types have fixed projections. What is the actual problem you're trying to solve? That is, why requirement has lead you to think you need to dynamically create record type variables?
– APC
Nov 26 '18 at 12:31
this was asked to me during an interview and was unable to get the solution. So was curious to know how this can be achieved.
– Sushil
Nov 26 '18 at 14:54
People do ask strange questions in interviews.
– APC
Nov 26 '18 at 15:02
Maybe it was one of those "gotcha" questions... the correct answer is "what is the problem you're trying to solve?" (i.e. perhaps their contrived problem can be solved using SQL without needing a PL/SQL type)
– Jeffrey Kemp
Nov 27 '18 at 2:45