UPDATE TABLE A FROM TABLE B IN ORACLE

Hi all

A little big problem, i always forget in oracle how to do an update on a table from data on another table, so i fix it in my blog :

update table1

set field = (

select field

from table2

where table2.id = table1.id

);

Stay tuned.

SP-552 oracle bind variable not declared

Hi all,

About this error we have lost many time. I hope this post can help someone.
My script sql is :


set verify off

set heading off

set linesize 400

spool RIL_388_OP_INS_AnagD4

COLUMN indGruppoiInG NEW_VALUE indGruppoiInG

SELECT max(substr(nome_elemento_associazione,4,4))+1 indGruppoiInG FROM SCTELEM_ASSOC WHERE TIPO_ASSOCIAZIONE = 'GRUPPI_IN' AND NOME_ELEMENTO_ASSOCIAZIONE LIK
E 'GIG%';

SELECT '@INFO AGGIUNTO GRUPPO IN ' || 'GIG' || lpad(to_char(&indGruppoiInG),4,'0') || ' IN CATEGORIA LA UNDELIVERED SERV >' || 'CATG0010' || '<' from dual; COLUMN indGruppoiInG NEW_VALUE indGruppoiInG SELECT max(substr(nome_elemento_associazione,4,4))+1 indGruppoiInG FROM SCTELEM_ASSOC WHERE TIPO_ASSOCIAZIONE = 'GRUPPI_IN' AND NOME_ELEMENTO_ASSOCIAZIONE LIK E 'GIG%'; SELECT '@INFO AGGIUNTO GRUPPO IN ' || 'GIG' || lpad(to_char(&indGruppoiInG),4,'0') || ' IN CATEGORIA LA UNDELIVERED SERV >' || 'CATG0010' || '<' from dual;

spool off

When we have executed this script alone it was OK. But when it was executed after another script, was showed this error :

SP-552 oracle bind variable "indGruppoiInG" not declared

After many controls we have discovered that a previously script seated "set define #", this command (see this link causes previously error.
Solution for this error was put at beginning my script sql, this command :

set define on

To avoid previously different define setting.

Stay tuned.