oracle procedure/package help

[bubu2006]

hi,

I have one script which is running fine out side oracle package/procedure.


example
delete table;
commit;

insert into table1(a,b,c)
select
a,
b,
a||b||e||f||d|| as c
from table2);
commit;

the concatenation is not working properly when I am putting this script inside a procedure which is a part of package. I am using Oracle 10g.

 

[SantaMufasa]

BuBu,

Within any PL/SQL block, independent SELECT statements (i.e., outside of some form of CURSOR), you must provide INTO receiving targets for each of your SELECT expressions:

DECLARE
    v_a <data type>;
    v_b <data type>;
    v_c <data type>;
BEGIN
    delete <table>;
    commit;
    insert into table1 [B][I]values[/I][/B] (a,b,c);
    select a, b, a||b||e||f||d
      [B][I]INTO v_a, v_b, v_c[/I][/B]
      from table2);
    commit;
    ...
END;
/

Notice that I also removed the extraneous, error-generating final concatenation operator ("||") from just before the "FROM...".

Let us know if this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cc82394]

It should also work in pl/sql as a 'insert into table as select...'.

declare
--whatever you need
begin
insert into table1
as
select
a,
b,
a||b||e||f||d as c
from table2;
commit;
...
end;
/