CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle-sqldevelopersqlplus
Dave
Dave

Reputation: 57

Can procedures take user run-time input as parameters?

I'm quite new to plsql and I have what I think is a fairly basic question.

I've created the following procedure which works perfectly as I want it:

CREATE or REPLACE PROCEDURE proc_list_similar_recipes(idr_p IN recipe.idr%TYPE)

AS
  idr_row recipe%ROWTYPE;
  v_similarity NUMBER;
  v_title VARCHAR2(200);

BEGIN  
  FOR idr_row IN (SELECT * FROM recipe ORDER BY idr) LOOP
    SELECT recipetitle
    INTO v_title
    from RECIPE
    WHERE IDR = idr_p;
    v_similarity := func_similarity(idr_p, idr_row.idr);
    DBMS_OUTPUT.PUT_LINE('Similarity between ' || v_title || 'idR(' || idr_p || ') and '|| idr_row.recipetitle || '(idR' || idr_row.idr || '): ' || v_similarity);
    END LOOP;
END proc_list_similar_recipes;

And to test it/get output from it I'm making simple anonymous blocks like so:

SET SERVEROUPUT ON
SET VERIFY OFF
ACCEPT prompt_idr PROMPT 'Please enter a valid id'
DECLARE
  prompt_idr number :=&prompt_idr;
BEGIN
  proc_list_similar_recipes(prompt_idr);

END;
/

But the specification of the problem states that my procedure should "prompt the user for an value"? Is it possible to do this without having an additional anonymous block?

Upvotes: 1

Views: 470

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

But the specification of the problem states that my procedure should "prompt the user for an value"?

The fact is that PL/SQL is not interactive.

If your requirement is strictly to prompt and accept values from user, then PL/SQL is not a good solution. A PL/SQL code is compiled and stored in the database, so it is not an interactive.

Also, & is used for variable substitution in SQL*Plus, and it is not a good/feasible idea to provide SQL*Plus as an user interface to the user to input values.

I strongly recommend a frontend application for this kind of requirement. PL/SQL is not interactive, and not suitable here. SQL*Plus as an interface will be a nightmare for a user.

Upvotes: 1

Related Questions