A. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS TYPE cv_pordtyp IS REF CURSOR; cv cv_prodtyp;
v_prodname prod_info.name%TYPE;
v_listprice prod_info.price%TYPE;
v_bind VARCHAR2 (400);
BEGIN
v_bind := ’’’%’ | | p_product_name | | ‘%’’’;
OPEN cv FOR ‘SELECT name, price FROM prod_info WHERE name LIKE ’ | | v_bind; LOOP FETCH cv INTO v_prodname, v_listprice;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPU.PUT_LINE (‘Product Info: ‘ | | v_prodname | | ‘,’ | | v_listprice); END LOOP; CLOSE cv;
END;

B. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS TYPE cv_pordtyp IS REF CURSOR; cv cv_prodtyp;
v_prodname prod_info.name%TYPE;
v_listprice prod_info.price%TYPE;
v_bind VARCHAR2 (400);
BEGIN
v_bind := DBMS_ASSERT.ENQUOTE_LITERAL (‘%’ | | p_product_name | | ‘%’); OPEN cv FOR ‘SELECT name, price FROM prod_info WHERE name LIKE ’ | | v_bind; LOOP FETCH cv INTO v_prodname, v_listprice;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPU.PUT_LINE (‘Product Info: ‘ | | v_prodname | | ‘,’ | | v_listprice); END LOOP; CLOSE cv;
END;

C. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS TYPE cv_pordtyp IS REF CURSOR; cv cv_prodtyp;
v_prodname prod_info.name%TYPE;
v_listprice prod_info.price%TYPE;
v_bind VARCHAR2 (400);
BEGIN
v_bind := ‘%’ | | p_product_name | | ‘%’;
OPEN cv FOR ‘SELECT name, price FROM prod_info WHERE name LIKE ’ | | v_bind; LOOP FETCH cv INTO v_prodname, v_listprice;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPU.PUT_LINE (‘Product Info: ‘ | | v_prodname | | ‘,’ | | v_listprice); END LOOP; CLOSE cv;
END;

D. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS TYPE cv_pordtyp IS REF CURSOR; cv cv_prodtyp;
v_prodname prod_info.name%TYPE;
v_listprice prod_info.price%TYPE;
v_bind VARCHAR2 (400);
BEGIN
v_bind := ‘%’ | | p_product_name | | ‘%’;
OPEN cv FOR ‘SELECT name, price FROM prod_info WHERE name LIKE :b’ USING v_bind; LOOP FETCH cv INTO v_prodname, v_listprice;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPU.PUT_LINE (‘Product Info: ‘ | | v_prodname | | ‘,’ | | v_listprice); END LOOP; CLOSE cv;
END;

E. CREATE PROCEDURE list_products_dynamic (p_product_name VARCHAR2 DEFAULT NULL) AS v_bind VARCHAR2 (400); BEGIN
v_bind := ‘%’ | | p_prodname | | ‘%’;
FOR rec IN (‘SELECT name, price FROM prod_info WHERE name like ‘ | | v_bind) LOOP DBMS_OUTPUT.PUT_LINE (‘Product Info: ’ | | rec.name | | ‘,’ | | rec.price); END LOOP; END;