我在Oracle的SQL Developer中使用了这个控制台输出
ORA-01008: no todas las variables han sido enlazadas ORA-06512: en "CIUDADANOS.CIUD_UTILIDADES_PKG", línea 183 01008. 00000 - "not all variables bound" *Cause:
*Action:
问题可能出在以下几行中
OPEN l_temp FOR l_query USING fecha_val, departamento_val, localidad_val, edad_val, destinatario_val;
但是当查看SQL查询中使用的所有变量时
这是完整的功能
FUNCTION OBTENER_NOTIFICATIONS(fecha_val IN DATE, departamento_val IN NUMBER, localidad_val IN NUMBER, edad_val IN NUMBER, destinatario_val IN VARCHAR2)
RETURN VARCHAR2
AS
l_query VARCHAR2(2000);
l_result VARCHAR2(4000);
l_temp SYS_REFCURSOR;
l_row NOTIFICATIONS%ROWTYPE;
BEGIN
l_query := 'SELECT * FROM NOTIFICATIONS WHERE ' ||
'(:fecha_val >= NOTIFICATION_DATE_FROM) AND ' ||
'(:fecha_val < NOTIFICATION_DATE_TO) AND ' ||
'((DEPARTMENT_ID = :departamento_val)) AND ' ||
'((LOCALITY_ID = :localidad_val)) AND ' ||
'(:edad_val >= AGE_FROM) AND ' ||
'(:edad_val < AGE_TO) AND ' ||
'((RECIPIENTS = :destinatario_val) OR (RECIPIENTS = ''both''))';
OPEN l_temp FOR l_query USING fecha_val, departamento_val, localidad_val, edad_val, destinatario_val;
LOOP
FETCH l_temp INTO l_row;
EXIT WHEN l_temp%NOTFOUND;
l_result := l_result || '{"ID": ' || l_row.ID || ', "RECIPIENTS":"' || l_row.RECIPIENTS || '","AGE_FROM":' || l_row.AGE_FROM || ',"AGE_TO":' || l_row.AGE_TO || ',"DEPARTMENT":' || l_row.DEPARTMENT_ID || ',"LOCALITY":' || l_row.LOCALITY_ID || ',"MESSAGE_TITLE":"' || l_row.MESSAGE_TITLE || '","MESSAGE_BODY":"' || l_row.MESSAGE_BODY ||'","ATTACHMENT_TYPE":"' || l_row.ATTACHMENT_TYPE || '","ATTACHMENT":"' || blob_to_base64(l_row.ATTACHMENT) || '","NOTIFICATION_DATE_FROM":"' || l_row.NOTIFICATION_DATE_FROM || '","NOTIFICATION_DATE_TO":"' || l_row.NOTIFICATION_DATE_TO || '","SEND_BY_EMAIL":"' || l_row.SEND_BY_EMAIL || '","CREATED_AT":"' || l_row.CREATED_AT || '","UPDATED_AT":"' || l_row.UPDATED_AT || '","DELETED_AT":"' || l_row.DELETED_AT || '"}';
END LOOP;
IF l_temp%ISOPEN THEN
CLOSE l_temp;
END IF;
RETURN l_result;
END;