change dynamic sql into pls/sql EXEC IMMEDIATE

[fosa]

Hi every body,

I want to change this sql code

select 'update TABLE_A
set DEBT_CCy='''||ccy_code||'''
where debt_ccy is null
and ticker='''||f.ticker||''';'
from
TABLE_A f,TABLE_B s
where
f.ticker=s.ticker
and debt_ccy is null;

into pl/sql execute immediate

how can I do ?

Thanks

 

[SantaMufasa]

Fosa,

Here is a code model to do what you wanted:

select salary from s_emp where rownum <= 3;

    SALARY
----------
      2500
      1450
      1400

declare
  hold_sql  varchar2(200);
begin
  select 'update s_emp set salary = salary * 1.1 where rownum <= 3' into hold_sql from dual;
  execute immediate hold_sql;
end;
/

select salary from s_emp where rownum <= 3;

    SALARY
----------
      2750
      1595
      1540

Let us know if you have questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[fosa]

when I try this I got en error


1 declare
2 hold_sql varchar2(2000);
3 begin
4 select 'update table_A set CCy_CODE = '''||ccy_code||''' where debt_ccy is null
5 and ticker='''||ticker||''' from
6 table_A f,table_b s
7 where
8 f.ticker=s.ticker
9 and debt_ccy is null' into hold_sql from dual;
10 execute immediate hold_sql;
11* end;
06-NOV-08 : > /
and ticker='''||ticker||''' from
*
ERROR at line 5:
ORA-06550: line 5, column 19:
PL/SQL: ORA-00904: "TICKER": invalid identifier
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

 

[SantaMufasa]

Fosa,

I infer from the error message that "TICKER" is not a column in either "TABLE_A" or "TABLE_B". (Your code implies that "TICKER" is in both tables, so even if "TICKER" does show up in both tables, you will need to qualify "TICKER" with either "f." or "s." when you refer to that column in your SELECT statement.)

So, could you begin by posting a DESCRIBE of both of your tables?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[fosa]

yes I can put desc

desc table_A

TICKER, QUOTE_DATE, DEBT_CCY, DEBT_INF_1Y, DEBT_1Y_3Y, DEBT_3Y_5Y, DEBT_5Y_7Y, DEBT_7Y_10Y, DEBT_SUP_10Y, LEASE

desc table_B

TICKER, CCY_CODE, PAR_ID, ISIN, RIC, SECURITY_NAME, IS_BASKET_OR_INDEX, INSTRUMENT_TYPE, ASSET_TYPE, HAS_COMPOSITION

 

[SantaMufasa]

When you perform a SELECT statement in PL/SQL (not in a CURSOR), you must provide an "INTO" target for the selection:

declare
    hold_sql  varchar2(2000);
begin
    select 'update table_A  set CCy_CODE =  '''||ccy_code||
             ''' where debt_ccy is null and ticker='''||f.ticker||''' [b]into hold_sql[/b]
      From table_A  f,table_b s
     where f.ticker=s.ticker
       and debt_ccy is null' into hold_sql from dual;
    execute immediate hold_sql;
end;
/

I also arbitrarily added "f." to qualify which of the two "tickers" to use in the SELECT.

Let us know if this improves things.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[fosa]

Thnks a lot

I fond a workaroun in SQL and which doesn't use dynamic SQl

update table_A f set debt_ccy =(select ccy_code from table_b s
where s.ticker=f.ticker
and debt_ccy is null)
where debt_ccy is null
and ticker in (select ticker from table_b);