Hi,
Your stored procedure is almost correct. In the stored procedure you created, you are using the input variable names as parameters in the SELECT statement, which will result in a syntax error. Instead, you should specify different names for the variables in the SELECT statement and then assign these variables the values from the input parameters later when you open the cursor.
To illustrate, let's use the following example SELECT statement:
```
SELECT customer_code
FROM bv_orders
WHERE status = 'Closed' OR status = 'Delivered';
```
This is equivalent to the SELECT statement you included:
```
SELECT field_value
FROM "view_name"
WHERE "field_name" = 'FieldName1'
AND "field_value_usage" = 'Value1';
```
To convert this to a stored procedure, you will need parameter variables where dependantField and parentFieldValue will be replaced, for example:
```
(field_value OUT VARCHAR, dependantField IN VARCHAR, parentFieldValue IN VARCHAR)
AS
(
CURSOR cursorData IS
'SELECT customer_code
FROM bv_orders
WHERE status = '':param1'' OR status = '':param2''';
rdata cursorData%ROWTYPE;
)
BEGIN
OPEN cursorData PARAMETERS (param1, param2) VALUES (dependantField, parentFieldValue);
LOOP
FETCH cursorData INTO rdata;
RETURN ROW(field_value) VALUES (rdata.customer_code);
EXIT WHEN cursorData%NOTFOUND;
END LOOP;
CLOSE cursorData;
END;
```
In this stored procedure, we add parameter variables :param1 and :param2 in the SELECT statement. These need to be different from the input variables that will be passed to the stored procedure for execution. In the BEGIN clause, we specify that these variables will hold some values using the PARAMETERS and VALUES functions. Specifically, :param1 and :param2 will be the parameters, and dependantField and parentFieldValue will be the values assigned to these parameters during execution.
Hope this helps!
