Sql prompt input how to#
Sql prompt input code#

It will ask for you to input parameters and will show default values if any i did the same as told but its giving me an error. An anonymous block will look like this.Д®ND going through the schema browser > procedures> Put your procedure in a anonymous block and execute it. I want to execute my procedure with manually giving the parameters. I am trying to understand any possibility of doing this in PL/SQL. This is why I am trying to see if I can build a dynamic if statement. When I am referring the cursor-rec.fldname i can not hardcode the fldname because it is actually coming from tableA. Once I built the dynamic_cursor_rec then I need to validate each cursor rec fieldvalues (after each fetch) against the above mentioned fldvalues from the table (tableA values stored in tableA_cursor_rec).Д®xample: if tableA_cursor_rec.fldvalue1 = dynamic_cursor_rec.fldname1 (hope I am making the correct assumption on building the dynamic cursor based on fldnames which are coming from tableA. the same table also has field values for the respective columns.ДЇldname1, fldvalue1, fldname2, fldvalue2, etc.,ДЇirst I need to build the cursor based on the fld names from the above table, which I guess I can do it through dynamic sql. What is the problem you are trying to solve with this dynamic condition? I am building a cursor based on field values from a table. PL/SQL block before you can execute it using execute immediate. You would have to create an entire DECLARE. ORA-06512: at line 19it should either be a valid SQL statement or a valid PL/SQL block for you to be able to execute successfully. You are sending this string to the database asking it to execute it.

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.

Sql prompt input how to#

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)

Sql prompt input code#

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,