IF var_a = ws_testrec.empid THEN var_result := 'YES' END IF if you typed this exact same string on SQL*Plus command prompt and tried to execute it, you would not be able to.

Thanks, what you really are executing is this:

Var_ifstmnt := 'IF var_a = ws_testrec.'||var_col|| ' THEN var_result := 'YES' END IF ' ĭBMS_OUTPUT.PUT_LINE('string is: '|| var_ifstmnt) ĮXECUTE IMMEDIATE var_ifstmnt USING var_col /* is the syntax correct? */ĭBMS_OUTPUT.PUT_LINE('Result is: '|| var_result) Ws_testrec.empid := 20 /* for simplification lets assign values to the record variables */ Ws_testrec scott.test%ROWTYPE /*test is a tbl with two cloumns */ but the script fails at EXECUTE IMMEDIATE statement. To test this i created a following script. Re: How to give the different values to runtime parameters in a loop?ĭynamic IF statement I am trying to build a dynamic if statement (if it is allowed). If you do have to use SQL*Plus, the following thread shows how: You're much better off using a different front end. SQL*Plus doesn't have good flow-of-0control mechanisms. Pass 'Y' or 'N' in the procedure.Ĭall that procedure in simple pl/sql block during runtime using substituton operator.Ĭreate or replace procedure p1(category_in in varchar2) You can write a genric procedure to achive the desired output. Is there an alternate method to establish this? However i see that this is not possible to do that PROMPT statements and accepting user inputs execute only on the SQL prompt and not inside a PL/SQL block. Now I want that the proc again start executing in the loop from the 1st line i.e. DBMS_OUTPUT.PUT_LINE(' Error Description :' || sErrorDsc) DBMS_OUTPUT.PUT_LINE('Error Code :' || sErrorCd) PROMPT Press y if you want to proceed with the current values, or press n if you want to re-enter the valuesĪCCEPT cChoice CHAR Prompt 'Enter y or n:'ĭBMS_OUTPUT.PUT_LINE('Starting with the process to execute the stored proc') For now, I am using the following logic:ĭBMS_OUTPUT.PUT_LINE('The value of the Category as entered by you is' || cCategory) I am working on a requirement wherein I have to accept values from the user for the various arguments to be supplied to a PL/SQL block and then execute it using these values. Accepting user input and executing a PL/SQL block using it - SQL & PL/SQL Hi All,

Sql prompt input