Not all variables bound in PL/SQL function.

huangapple go评论62阅读模式
英文:

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 := &#39;SELECT * FROM NOTIFICATIONS WHERE &#39; || 
               &#39;(:fecha_val &gt;= NOTIFICATION_DATE_FROM) AND &#39; ||
               &#39;(:fecha_val &lt; NOTIFICATION_DATE_TO) AND &#39; ||
               &#39;((DEPARTMENT_ID = :departamento_val)) AND &#39; ||
               &#39;((LOCALITY_ID = :localidad_val)) AND &#39; ||
               &#39;(:edad_val &gt;= AGE_FROM) AND &#39; ||
               &#39;(:edad_val &lt; AGE_TO) AND &#39; ||
               &#39;((RECIPIENTS = :destinatario_val) OR (RECIPIENTS = &#39;&#39;both&#39;&#39;))&#39;;
    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 || &#39;{&quot;ID&quot;: &#39; || l_row.ID || &#39;, &quot;RECIPIENTS&quot;:&quot;&#39; || l_row.RECIPIENTS || &#39;&quot;,&quot;AGE_FROM&quot;:&#39; || l_row.AGE_FROM || &#39;,&quot;AGE_TO&quot;:&#39; || l_row.AGE_TO || &#39;,&quot;DEPARTMENT&quot;:&#39; || l_row.DEPARTMENT_ID || &#39;,&quot;LOCALITY&quot;:&#39; || l_row.LOCALITY_ID || &#39;,&quot;MESSAGE_TITLE&quot;:&quot;&#39; || l_row.MESSAGE_TITLE || &#39;&quot;,&quot;MESSAGE_BODY&quot;:&quot;&#39; || l_row.MESSAGE_BODY ||&#39;&quot;,&quot;ATTACHMENT_TYPE&quot;:&quot;&#39; || l_row.ATTACHMENT_TYPE || &#39;&quot;,&quot;ATTACHMENT&quot;:&quot;&#39; || blob_to_base64(l_row.ATTACHMENT) || &#39;&quot;,&quot;NOTIFICATION_DATE_FROM&quot;:&quot;&#39; || l_row.NOTIFICATION_DATE_FROM || &#39;&quot;,&quot;NOTIFICATION_DATE_TO&quot;:&quot;&#39; || l_row.NOTIFICATION_DATE_TO || &#39;&quot;,&quot;SEND_BY_EMAIL&quot;:&quot;&#39; || l_row.SEND_BY_EMAIL || &#39;&quot;,&quot;CREATED_AT&quot;:&quot;&#39; || l_row.CREATED_AT || &#39;&quot;,&quot;UPDATED_AT&quot;:&quot;&#39; || l_row.UPDATED_AT || &#39;&quot;,&quot;DELETED_AT&quot;:&quot;&#39; || l_row.DELETED_AT || &#39;&quot;}&#39;;
    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 := &#39;SELECT * FROM NOTIFICATIONS WHERE &#39; || 
               &#39;(:fecha_val &gt;= NOTIFICATION_DATE_FROM) AND &#39; ||
               &#39;(:fecha_val &lt; NOTIFICATION_DATE_TO) AND &#39; ||
               &#39;((DEPARTMENT_ID = :departamento_val)) AND &#39; ||
               &#39;((LOCALITY_ID = :localidad_val)) AND &#39; ||
               &#39;(:edad_val &gt;= AGE_FROM) AND &#39; ||
               &#39;(:edad_val &lt; AGE_TO) AND &#39; ||
               &#39;((RECIPIENTS = :destinatario_val) OR (RECIPIENTS = &#39;&#39;both&#39;&#39;))&#39;;
    OPEN l_temp FOR l_query USING fecha_val, fecha_val, departamento_val, localidad_val, edad_val, edad_val, destinatario_val;

huangapple
  • 本文由 发表于 2023年4月17日 00:05:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/76028853.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定