Problem with SELECT .. INTO 1

[asth01]

Hi,

Inside a procedure, I'm trying to run:

var1 varchar2 (20),
var2 number,
CURSOR C1 IS
SELECT col3
FROM SCHEMA_NAME.REF_TABLE
FOR UPDATE;
....
FOR C1_REC IN C1 LOOP

'SELECT B.COL1, B.COL2 INTO VAR1, VAR2 '||
'FROM SCHEMA1.MASTER_TAB@db23_LINK B ' ||
'WHERE B.COL2 = '''|| TRIM(C1_REC.COL3)|| '''';

EXECUTE IMMEDIATE SQL_STMT;

UPDATE REF_TABLE
SET col1 = var_case_id, col2 = var_country_id,
UPDATE_TIMESTAMP = SYSDATE
WHERE CURRENT OF C1;
....
END LOOP;
...
It gives me error: '...Key word missing..'.

When I try this with:

'SELECT B.COL1, B.COL2 INTO :VAR1, :VAR2 '||
'FROM SCHEMA1.MASTER_TAB@db23_LINK B ' ||
'WHERE B.COL2 = '''|| TRIM(C1_REC.COL3)|| '''';

(with a , I don't get the error BUT I don't get the value either in Update statement as I see all nulls in these 2 columns of REF_TABLE.

What is the problem here?

Any help is appreciated.. (and I want it fast)

Thanks in advance

 

[sem]

....

SQL_STMT := 'SELECT B.COL1, B.COL2 '||
'FROM SCHEMA1.MASTER_TAB@db23_LINK B ' ||
'WHERE B.COL2 = '''|| TRIM(C1_REC.COL3)|| '''';

EXECUTE IMMEDIATE SQL_STMT into INTO VAR1, VAR2;

Or probably better
...
SQL_STMT := 'SELECT B.COL1, B.COL2 '||
'FROM SCHEMA1.MASTER_TAB@db23_LINK B ' ||
'WHERE B.COL2 =:1';

EXECUTE IMMEDIATE SQL_STMT into INTO VAR1, VAR2 using TRIM(C1_REC.COL3);


Regards, Dima

http://seminihin.narod.ru