How to prompt for user input on PLSQL procedure
I'm having trouble getting user input inside of a PLSQL procedure. Everywhere I have looked I've come to the conclusion that is not possible or a PLSQL procedure it not made for user input.
My code:
create or replace
PACKAGE LAB5 AS
daysShow NUMBER;
PROCEDURE show_bizdays2(p_startDate DATE DEFAULT SYSDATE, p_bizDayShow NUMBER);
PROCEDURE show_bizdays2(p_startDate DATE DEFAULT SYSDATE);
FUNCTION Get_Descr(f_sectionId NUMBER) RETURN VARCHAR2;
END LAB5;
Description:
In this code the first procedure accepts two parameters a date and the amount of business days to show preceding that date. Works fine.
The second procedure overloads the first and is supposed to be exactly the same however it takes one parameter and prompts for the user input in the procedure. Or as described in my notes "ONE input parameter - Start Date and will prompt user to enter how many days are needed to show."
My Question(s)
Can you prompt for user input inside of a procedure?
Can I somehow use a global variable in the package that prompts the user when the overloaded procedure is called?
sql oracle stored-procedures plsql
asked Dec 18 '17 at 0:56
Pierre97Pierre97
82
add a comment |
I'm having trouble getting user input inside of a PLSQL procedure. Everywhere I have looked I've come to the conclusion that is not possible or a PLSQL procedure it not made for user input.
My code:
create or replace
PACKAGE LAB5 AS
daysShow NUMBER;
PROCEDURE show_bizdays2(p_startDate DATE DEFAULT SYSDATE, p_bizDayShow NUMBER);
PROCEDURE show_bizdays2(p_startDate DATE DEFAULT SYSDATE);
FUNCTION Get_Descr(f_sectionId NUMBER) RETURN VARCHAR2;
END LAB5;
Description:
In this code the first procedure accepts two parameters a date and the amount of business days to show preceding that date. Works fine.
The second procedure overloads the first and is supposed to be exactly the same however it takes one parameter and prompts for the user input in the procedure. Or as described in my notes "ONE input parameter - Start Date and will prompt user to enter how many days are needed to show."
My Question(s)
Can you prompt for user input inside of a procedure?
Can I somehow use a global variable in the package that prompts the user when the overloaded procedure is called?
sql oracle stored-procedures plsql
asked Dec 18 '17 at 0:56
Pierre97Pierre97
82
5
You can't prompt for user input inside of a procedure. The way to interact with users is to write an application (or use an existing one) that interacts with users (which is NOT an SQL or PL/SQL piece of software), and to call the PL/SQL procedure from that application, passing the user inputs to the procedure.
– mathguy
Dec 18 '17 at 1:46
@mathguy: please make this an answer. All the related questions I checked said "Use the ACCEPT command in SQL*PLUS to get the data, then pass it to the PL/SQL procedure", but that is really answering the wrong question. Your comment is correct: there's no way to get user input directly in PL/SQL. And respond to this comment, if you would, so I can upvote. Thanks.
– Bob Jarvis
Dec 18 '17 at 2:27
@BobJarvis - The reason I posted it as a Comment and not as an Answer is that ... I was simply confirming the (correct!) answer the OP found himself already.
– mathguy
Dec 18 '17 at 2:37
@mathguy Seems like that is the right answer. I have no idea why my prof is asking this (since procedures can't take user input) so I'll have to ask him in class...thanks for the help. If you make an answer I'll mark correct :)
– Pierre97
Dec 18 '17 at 3:55
In that case he may not be distinguishing between PL/SQL and SQL*Plus, and may be thinking that using theACCEPTcommand in SQL*Plus is the same as "getting user input in PL/SQL", in which case you might refer to this question. Best of luck.
– Bob Jarvis
Dec 18 '17 at 14:08
add a comment |
I'm having trouble getting user input inside of a PLSQL procedure. Everywhere I have looked I've come to the conclusion that is not possible or a PLSQL procedure it not made for user input.
My code:
create or replace
PACKAGE LAB5 AS
daysShow NUMBER;
PROCEDURE show_bizdays2(p_startDate DATE DEFAULT SYSDATE, p_bizDayShow NUMBER);
PROCEDURE show_bizdays2(p_startDate DATE DEFAULT SYSDATE);
FUNCTION Get_Descr(f_sectionId NUMBER) RETURN VARCHAR2;
END LAB5;
Description:
In this code the first procedure accepts two parameters a date and the amount of business days to show preceding that date. Works fine.
The second procedure overloads the first and is supposed to be exactly the same however it takes one parameter and prompts for the user input in the procedure. Or as described in my notes "ONE input parameter - Start Date and will prompt user to enter how many days are needed to show."
My Question(s)
Can you prompt for user input inside of a procedure?
Can I somehow use a global variable in the package that prompts the user when the overloaded procedure is called?
sql oracle stored-procedures plsql
asked Dec 18 '17 at 0:56
Pierre97Pierre97
82
I'm having trouble getting user input inside of a PLSQL procedure. Everywhere I have looked I've come to the conclusion that is not possible or a PLSQL procedure it not made for user input.
My code:
create or replace
PACKAGE LAB5 AS
daysShow NUMBER;
PROCEDURE show_bizdays2(p_startDate DATE DEFAULT SYSDATE, p_bizDayShow NUMBER);
PROCEDURE show_bizdays2(p_startDate DATE DEFAULT SYSDATE);
FUNCTION Get_Descr(f_sectionId NUMBER) RETURN VARCHAR2;
END LAB5;
Description:
In this code the first procedure accepts two parameters a date and the amount of business days to show preceding that date. Works fine.
The second procedure overloads the first and is supposed to be exactly the same however it takes one parameter and prompts for the user input in the procedure. Or as described in my notes "ONE input parameter - Start Date and will prompt user to enter how many days are needed to show."
My Question(s)
Can you prompt for user input inside of a procedure?
Can I somehow use a global variable in the package that prompts the user when the overloaded procedure is called?
sql oracle stored-procedures plsql
sql oracle stored-procedures plsql
asked Dec 18 '17 at 0:56
Pierre97Pierre97
82
asked Dec 18 '17 at 0:56
Pierre97Pierre97
82
asked Dec 18 '17 at 0:56
Pierre97Pierre97
82
asked Dec 18 '17 at 0:56
Pierre97Pierre97
82
asked Dec 18 '17 at 0:56
Pierre97Pierre97
82
82
5
You can't prompt for user input inside of a procedure. The way to interact with users is to write an application (or use an existing one) that interacts with users (which is NOT an SQL or PL/SQL piece of software), and to call the PL/SQL procedure from that application, passing the user inputs to the procedure.
– mathguy
Dec 18 '17 at 1:46
@mathguy: please make this an answer. All the related questions I checked said "Use the ACCEPT command in SQL*PLUS to get the data, then pass it to the PL/SQL procedure", but that is really answering the wrong question. Your comment is correct: there's no way to get user input directly in PL/SQL. And respond to this comment, if you would, so I can upvote. Thanks.
– Bob Jarvis
Dec 18 '17 at 2:27
@BobJarvis - The reason I posted it as a Comment and not as an Answer is that ... I was simply confirming the (correct!) answer the OP found himself already.
– mathguy
Dec 18 '17 at 2:37
@mathguy Seems like that is the right answer. I have no idea why my prof is asking this (since procedures can't take user input) so I'll have to ask him in class...thanks for the help. If you make an answer I'll mark correct :)
– Pierre97
Dec 18 '17 at 3:55
In that case he may not be distinguishing between PL/SQL and SQL*Plus, and may be thinking that using theACCEPTcommand in SQL*Plus is the same as "getting user input in PL/SQL", in which case you might refer to this question. Best of luck.
– Bob Jarvis
Dec 18 '17 at 14:08
add a comment |
5
You can't prompt for user input inside of a procedure. The way to interact with users is to write an application (or use an existing one) that interacts with users (which is NOT an SQL or PL/SQL piece of software), and to call the PL/SQL procedure from that application, passing the user inputs to the procedure.
– mathguy
Dec 18 '17 at 1:46
@mathguy: please make this an answer. All the related questions I checked said "Use the ACCEPT command in SQL*PLUS to get the data, then pass it to the PL/SQL procedure", but that is really answering the wrong question. Your comment is correct: there's no way to get user input directly in PL/SQL. And respond to this comment, if you would, so I can upvote. Thanks.
– Bob Jarvis
Dec 18 '17 at 2:27
@BobJarvis - The reason I posted it as a Comment and not as an Answer is that ... I was simply confirming the (correct!) answer the OP found himself already.
– mathguy
Dec 18 '17 at 2:37
@mathguy Seems like that is the right answer. I have no idea why my prof is asking this (since procedures can't take user input) so I'll have to ask him in class...thanks for the help. If you make an answer I'll mark correct :)
– Pierre97
Dec 18 '17 at 3:55
In that case he may not be distinguishing between PL/SQL and SQL*Plus, and may be thinking that using theACCEPTcommand in SQL*Plus is the same as "getting user input in PL/SQL", in which case you might refer to this question. Best of luck.
– Bob Jarvis
Dec 18 '17 at 14:08
5
5
You can't prompt for user input inside of a procedure. The way to interact with users is to write an application (or use an existing one) that interacts with users (which is NOT an SQL or PL/SQL piece of software), and to call the PL/SQL procedure from that application, passing the user inputs to the procedure.
– mathguy
Dec 18 '17 at 1:46
You can't prompt for user input inside of a procedure. The way to interact with users is to write an application (or use an existing one) that interacts with users (which is NOT an SQL or PL/SQL piece of software), and to call the PL/SQL procedure from that application, passing the user inputs to the procedure.
– mathguy
Dec 18 '17 at 1:46
@mathguy: please make this an answer. All the related questions I checked said "Use the ACCEPT command in SQL*PLUS to get the data, then pass it to the PL/SQL procedure", but that is really answering the wrong question. Your comment is correct: there's no way to get user input directly in PL/SQL. And respond to this comment, if you would, so I can upvote. Thanks.
– Bob Jarvis
Dec 18 '17 at 2:27
@mathguy: please make this an answer. All the related questions I checked said "Use the ACCEPT command in SQL*PLUS to get the data, then pass it to the PL/SQL procedure", but that is really answering the wrong question. Your comment is correct: there's no way to get user input directly in PL/SQL. And respond to this comment, if you would, so I can upvote. Thanks.
– Bob Jarvis
Dec 18 '17 at 2:27
@BobJarvis - The reason I posted it as a Comment and not as an Answer is that ... I was simply confirming the (correct!) answer the OP found himself already.
– mathguy
Dec 18 '17 at 2:37
@BobJarvis - The reason I posted it as a Comment and not as an Answer is that ... I was simply confirming the (correct!) answer the OP found himself already.
– mathguy
Dec 18 '17 at 2:37
@mathguy Seems like that is the right answer. I have no idea why my prof is asking this (since procedures can't take user input) so I'll have to ask him in class...thanks for the help. If you make an answer I'll mark correct :)
– Pierre97
Dec 18 '17 at 3:55
@mathguy Seems like that is the right answer. I have no idea why my prof is asking this (since procedures can't take user input) so I'll have to ask him in class...thanks for the help. If you make an answer I'll mark correct :)
– Pierre97
Dec 18 '17 at 3:55
In that case he may not be distinguishing between PL/SQL and SQL*Plus, and may be thinking that using the
ACCEPT command in SQL*Plus is the same as "getting user input in PL/SQL", in which case you might refer to this question. Best of luck.– Bob Jarvis
Dec 18 '17 at 14:08
In that case he may not be distinguishing between PL/SQL and SQL*Plus, and may be thinking that using the
ACCEPT command in SQL*Plus is the same as "getting user input in PL/SQL", in which case you might refer to this question. Best of luck.– Bob Jarvis
Dec 18 '17 at 14:08
add a comment |
1 Answer
1
active
oldest
votes
Similar to sql you can use & operater for getting input from user.
Example :
Declare
Table_nm:='&tblnm';
Begin
Execute immediate ('drop table '¦¦' '¦¦table_nm¦¦' '¦¦' purge') ;
End;
edited Nov 15 '18 at 4:46
Pang
6,9891666105
answered Nov 15 '18 at 4:25
vijayvijay
134
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%2f47860899%2fhow-to-prompt-for-user-input-on-plsql-procedure%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
Similar to sql you can use & operater for getting input from user.
Example :
Declare
Table_nm:='&tblnm';
Begin
Execute immediate ('drop table '¦¦' '¦¦table_nm¦¦' '¦¦' purge') ;
End;
edited Nov 15 '18 at 4:46
Pang
6,9891666105
answered Nov 15 '18 at 4:25
vijayvijay
134
add a comment |
Similar to sql you can use & operater for getting input from user.
Example :
Declare
Table_nm:='&tblnm';
Begin
Execute immediate ('drop table '¦¦' '¦¦table_nm¦¦' '¦¦' purge') ;
End;
edited Nov 15 '18 at 4:46
Pang
6,9891666105
answered Nov 15 '18 at 4:25
vijayvijay
134
add a comment |
Similar to sql you can use & operater for getting input from user.
Example :
Declare
Table_nm:='&tblnm';
Begin
Execute immediate ('drop table '¦¦' '¦¦table_nm¦¦' '¦¦' purge') ;
End;
edited Nov 15 '18 at 4:46
Pang
6,9891666105
answered Nov 15 '18 at 4:25
vijayvijay
134
Similar to sql you can use & operater for getting input from user.
Example :
Declare
Table_nm:='&tblnm';
Begin
Execute immediate ('drop table '¦¦' '¦¦table_nm¦¦' '¦¦' purge') ;
End;
edited Nov 15 '18 at 4:46
Pang
6,9891666105
answered Nov 15 '18 at 4:25
vijayvijay
134
edited Nov 15 '18 at 4:46
Pang
6,9891666105
edited Nov 15 '18 at 4:46
Pang
6,9891666105
edited Nov 15 '18 at 4:46
Pang
6,9891666105
6,9891666105
answered Nov 15 '18 at 4:25
vijayvijay
134
answered Nov 15 '18 at 4:25
vijayvijay
134
answered Nov 15 '18 at 4:25
vijayvijay
134
134
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%2f47860899%2fhow-to-prompt-for-user-input-on-plsql-procedure%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
5
You can't prompt for user input inside of a procedure. The way to interact with users is to write an application (or use an existing one) that interacts with users (which is NOT an SQL or PL/SQL piece of software), and to call the PL/SQL procedure from that application, passing the user inputs to the procedure.
– mathguy
Dec 18 '17 at 1:46
@mathguy: please make this an answer. All the related questions I checked said "Use the ACCEPT command in SQL*PLUS to get the data, then pass it to the PL/SQL procedure", but that is really answering the wrong question. Your comment is correct: there's no way to get user input directly in PL/SQL. And respond to this comment, if you would, so I can upvote. Thanks.
– Bob Jarvis
Dec 18 '17 at 2:27
@BobJarvis - The reason I posted it as a Comment and not as an Answer is that ... I was simply confirming the (correct!) answer the OP found himself already.
– mathguy
Dec 18 '17 at 2:37
@mathguy Seems like that is the right answer. I have no idea why my prof is asking this (since procedures can't take user input) so I'll have to ask him in class...thanks for the help. If you make an answer I'll mark correct :)
– Pierre97
Dec 18 '17 at 3:55
In that case he may not be distinguishing between PL/SQL and SQL*Plus, and may be thinking that using the
ACCEPTcommand in SQL*Plus is the same as "getting user input in PL/SQL", in which case you might refer to this question. Best of luck.– Bob Jarvis
Dec 18 '17 at 14:08