英文:
Not all variables bound in PL SQL function
问题
I having this console output in oracle's sql developer
> ORA-01008: not all variables have been bound ORA-06512: in
> "CITIZENS.CITIZEN_UTILITIES_PKG", line 183
> 01008. 00000 - "not all variables bound"
> *Cause:
> *Action:
The problem may be in the following line
OPEN l_temp FOR l_query USING fecha_val, departamento_val, localidad_val, edad_val, destinatario_val;
But when looking at all the variables used in the SQL query
This is the complete function
FUNCTION OBTAIN_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;
英文:
I having this console output in oracle's 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:
The problem may be in the following line
OPEN l_temp FOR l_query USING fecha_val, departamento_val, localidad_val, edad_val, destinatario_val;
But when looking at all the variables used in the SQL query
This is the complete function
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;
答案1
得分: 1
您正在使用位置绑定,因此您需要确保每个绑定都与确切的顺序/位置上的变量相关联。它不关注名称。您需要将 fecha_val 和 edad_val 绑定复制一次:
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, fecha_val, departamento_val, localidad_val, edad_val, edad_val, destinatario_val;
英文:
You are using positional binding, so you need to ensure that every bind has a variable associated with it in the exact order/position. It's not looking at the names. You'll need to double the fecha_val and edad_val binds:
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, fecha_val, departamento_val, localidad_val, edad_val, edad_val, destinatario_val;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论