Create SQL from parameters including FROM clause
Is it possible to create a stored procedure that calls a SQL statement with a parameter in the select statement and another in the FROM clause?
It sees to me that is not possible, but I don't know much about pl/sql.
The code below is not working of course. Any help would be greatly appreciated.
CREATE OR REPLACE PROCEDURE procedure1 table1 VARCHAR(100)
field1 VARCHAR(100)
IS
BEGIN
FOR I IN (SELECT field1
FROM TABLE (table1)
WHERE field1 IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE ('Field_name : ' || I.FIELD_NAME);
END LOOP;
END procedure1;
plsql oracle11g
edited Jan 22 at 20:33
marc_s
584k13011241270
asked Jan 3 at 19:27
AlbertoAlberto
2514
add a comment |
Is it possible to create a stored procedure that calls a SQL statement with a parameter in the select statement and another in the FROM clause?
It sees to me that is not possible, but I don't know much about pl/sql.
The code below is not working of course. Any help would be greatly appreciated.
CREATE OR REPLACE PROCEDURE procedure1 table1 VARCHAR(100)
field1 VARCHAR(100)
IS
BEGIN
FOR I IN (SELECT field1
FROM TABLE (table1)
WHERE field1 IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE ('Field_name : ' || I.FIELD_NAME);
END LOOP;
END procedure1;
plsql oracle11g
edited Jan 22 at 20:33
marc_s
584k13011241270
asked Jan 3 at 19:27
AlbertoAlberto
2514
1
Yes it is possible with dynamic SQL but you expose yourself to SQL Injection attacks
– Lukasz Szozda
Jan 3 at 19:35
By the way, tables have columns not fields.
– William Robertson
Jan 3 at 23:33
add a comment |
Is it possible to create a stored procedure that calls a SQL statement with a parameter in the select statement and another in the FROM clause?
It sees to me that is not possible, but I don't know much about pl/sql.
The code below is not working of course. Any help would be greatly appreciated.
CREATE OR REPLACE PROCEDURE procedure1 table1 VARCHAR(100)
field1 VARCHAR(100)
IS
BEGIN
FOR I IN (SELECT field1
FROM TABLE (table1)
WHERE field1 IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE ('Field_name : ' || I.FIELD_NAME);
END LOOP;
END procedure1;
plsql oracle11g
edited Jan 22 at 20:33
marc_s
584k13011241270
asked Jan 3 at 19:27
AlbertoAlberto
2514
Is it possible to create a stored procedure that calls a SQL statement with a parameter in the select statement and another in the FROM clause?
It sees to me that is not possible, but I don't know much about pl/sql.
The code below is not working of course. Any help would be greatly appreciated.
CREATE OR REPLACE PROCEDURE procedure1 table1 VARCHAR(100)
field1 VARCHAR(100)
IS
BEGIN
FOR I IN (SELECT field1
FROM TABLE (table1)
WHERE field1 IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE ('Field_name : ' || I.FIELD_NAME);
END LOOP;
END procedure1;
plsql oracle11g
plsql oracle11g
edited Jan 22 at 20:33
marc_s
584k13011241270
asked Jan 3 at 19:27
AlbertoAlberto
2514
edited Jan 22 at 20:33
marc_s
584k13011241270
asked Jan 3 at 19:27
AlbertoAlberto
2514
edited Jan 22 at 20:33
marc_s
584k13011241270
edited Jan 22 at 20:33
marc_s
584k13011241270
edited Jan 22 at 20:33
marc_s
584k13011241270
584k13011241270
asked Jan 3 at 19:27
AlbertoAlberto
2514
asked Jan 3 at 19:27
AlbertoAlberto
2514
asked Jan 3 at 19:27
AlbertoAlberto
2514
2514
1
Yes it is possible with dynamic SQL but you expose yourself to SQL Injection attacks
– Lukasz Szozda
Jan 3 at 19:35
By the way, tables have columns not fields.
– William Robertson
Jan 3 at 23:33
add a comment |
1
Yes it is possible with dynamic SQL but you expose yourself to SQL Injection attacks
– Lukasz Szozda
Jan 3 at 19:35
By the way, tables have columns not fields.
– William Robertson
Jan 3 at 23:33
1
1
Yes it is possible with dynamic SQL but you expose yourself to SQL Injection attacks
– Lukasz Szozda
Jan 3 at 19:35
Yes it is possible with dynamic SQL but you expose yourself to SQL Injection attacks
– Lukasz Szozda
Jan 3 at 19:35
By the way, tables have columns not fields.
– William Robertson
Jan 3 at 23:33
By the way, tables have columns not fields.
– William Robertson
Jan 3 at 23:33
add a comment |
1 Answer
1
active
oldest
votes
I think you can construct the SQL in a variable and execute it. It should work.
CREATE OR REPLACE PROCEDURE CRAZY_PROC (tableName VARCHAR2,
fieldName VARCHAR2)
AS
sqlString VARCHAR2 (1000);
BEGIN
sqlString := 'select ' || fieldName || ' from ' || tableName;
DBMS_OUTPUT.PUT_LINE (sqlString);
END;
/
edited Jan 4 at 14:40
kara
1,98731124
answered Jan 3 at 19:40
praveen Mpraveen M
1
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%2f54028618%2fcreate-sql-from-parameters-including-from-clause%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
I think you can construct the SQL in a variable and execute it. It should work.
CREATE OR REPLACE PROCEDURE CRAZY_PROC (tableName VARCHAR2,
fieldName VARCHAR2)
AS
sqlString VARCHAR2 (1000);
BEGIN
sqlString := 'select ' || fieldName || ' from ' || tableName;
DBMS_OUTPUT.PUT_LINE (sqlString);
END;
/
edited Jan 4 at 14:40
kara
1,98731124
answered Jan 3 at 19:40
praveen Mpraveen M
1
add a comment |
I think you can construct the SQL in a variable and execute it. It should work.
CREATE OR REPLACE PROCEDURE CRAZY_PROC (tableName VARCHAR2,
fieldName VARCHAR2)
AS
sqlString VARCHAR2 (1000);
BEGIN
sqlString := 'select ' || fieldName || ' from ' || tableName;
DBMS_OUTPUT.PUT_LINE (sqlString);
END;
/
edited Jan 4 at 14:40
kara
1,98731124
answered Jan 3 at 19:40
praveen Mpraveen M
1
add a comment |
I think you can construct the SQL in a variable and execute it. It should work.
CREATE OR REPLACE PROCEDURE CRAZY_PROC (tableName VARCHAR2,
fieldName VARCHAR2)
AS
sqlString VARCHAR2 (1000);
BEGIN
sqlString := 'select ' || fieldName || ' from ' || tableName;
DBMS_OUTPUT.PUT_LINE (sqlString);
END;
/
edited Jan 4 at 14:40
kara
1,98731124
answered Jan 3 at 19:40
praveen Mpraveen M
1
I think you can construct the SQL in a variable and execute it. It should work.
CREATE OR REPLACE PROCEDURE CRAZY_PROC (tableName VARCHAR2,
fieldName VARCHAR2)
AS
sqlString VARCHAR2 (1000);
BEGIN
sqlString := 'select ' || fieldName || ' from ' || tableName;
DBMS_OUTPUT.PUT_LINE (sqlString);
END;
/
edited Jan 4 at 14:40
kara
1,98731124
answered Jan 3 at 19:40
praveen Mpraveen M
1
edited Jan 4 at 14:40
kara
1,98731124
edited Jan 4 at 14:40
kara
1,98731124
edited Jan 4 at 14:40
kara
1,98731124
1,98731124
answered Jan 3 at 19:40
praveen Mpraveen M
1
answered Jan 3 at 19:40
praveen Mpraveen M
1
answered Jan 3 at 19:40
praveen Mpraveen M
1
1
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%2f54028618%2fcreate-sql-from-parameters-including-from-clause%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
1
Yes it is possible with dynamic SQL but you expose yourself to SQL Injection attacks
– Lukasz Szozda
Jan 3 at 19:35
By the way, tables have columns not fields.
– William Robertson
Jan 3 at 23:33