Home » SQL & PL/SQL » SQL & PL/SQL » Update in PL/SQL procedure (Oracle 9i)
Update in PL/SQL procedure [message #410694] Mon, 29 June 2009 16:59 Go to next message
kon786
Messages: 10
Registered: June 2009
Junior Member
Hello,
I am trying to for a bit of code in a procedure.
I want to update a column in Table A with same column value concatenate with select column from table B.
Update Table A set A.column=A.Column (+ or concatenate) select B.column from table B.
Please let me know the correct way to update.

Thanks
Re: Update in PL/SQL procedure [message #410708 is a reply to message #410694] Mon, 29 June 2009 20:39 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Update in PL/SQL procedure [message #410710 is a reply to message #410694] Mon, 29 June 2009 20:49 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Try something like this:

update tab_a tab_a
set tab_a.col_1 = tab_a.col_1 || (select tab_b.col_1
                                  from tab_b tab_b
				  where tab_b.col_3 = 2)


Hope it help
Re: Update in PL/SQL procedure [message #410830 is a reply to message #410694] Tue, 30 June 2009 08:46 Go to previous messageGo to next message
kon786
Messages: 10
Registered: June 2009
Junior Member
Hi I have tried this already and also using + instead of || but still getting error
PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

PLS-00103: Encountered the symbol "WHERE" when expecting one of
the following:
; return returning and or

any help would be appreciated.Thanks
Re: Update in PL/SQL procedure [message #410831 is a reply to message #410830] Tue, 30 June 2009 08:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Hi I have tried this already and also using + instead of || but still getting error

Why? Just in case picking syntax out of the air will help?
In Oracle, concatenation is done with ||.

If you post the SQL that you're trying to run, then we might be able to help.

The syntax you've been provided with is valid.
Re: Update in PL/SQL procedure [message #410835 is a reply to message #410694] Tue, 30 June 2009 09:30 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
I don`t have 9i database but maybe you could try this also


NF@dev > update tab_a tab_a
  2  set tab_a.col_1 = (select tab_a.col_1||tab_b.col_1
  3                     from  tab_b tab_b
  4                     where tab_b.col_3 = 2);

1 row updated.


Re: Update in PL/SQL procedure [message #410836 is a reply to message #410831] Tue, 30 June 2009 09:34 Go to previous messageGo to next message
kon786
Messages: 10
Registered: June 2009
Junior Member
Yes this query is working when I execute seperately but when I try to execute in a procedure
CREATE OR REPLACE PROCEDURE test
IS
declare ----
BEGIN
update tab_a tab_a
set tab_a.col_1 = tab_a.col_1 || (select tab_b.col_1
from tab_b tab_b
where tab_b.col_3 = 2)
EXCEPTION
exception_section
END;


It throws the error
PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

PLS-00103: Encountered the symbol "WHERE" when expecting one of
the following:
; return returning and or

Very much confused with this.
Re: Update in PL/SQL procedure [message #410837 is a reply to message #410694] Tue, 30 June 2009 10:01 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Put the concatenation inside the subselect.
Re: Update in PL/SQL procedure [message #410838 is a reply to message #410836] Tue, 30 June 2009 10:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That is not the code that you are running. That code would result in a

PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:

   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor external language
The symbol "begin" was substituted for "DECLARE" to continue.


Stop faking the errors and the output, and copy/paste what you REALLY do.
Re: Update in PL/SQL procedure [message #410844 is a reply to message #410694] Tue, 30 June 2009 10:44 Go to previous messageGo to next message
kon786
Messages: 10
Registered: June 2009
Junior Member
Thanks for your suggestion I could have already pasted the query I am running if I had chance.But I really dont want to post the exact data so I have used an example.I was just trying to show that I am using correct PL/SQL block.Declare.. gives error I understand that.Declare.. means there are some Variables declared in that statement thats it.Hope this helps.
I was expecting senior members do understand this.
Thanks again.
Re: Update in PL/SQL procedure [message #410846 is a reply to message #410694] Tue, 30 June 2009 10:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thing is you've got a syntax error. It's very difficult to diagnose syntax errors without seeing the exact syntax used.

However I did test this myself and older versions of oracle apparently don't like that syntax in procedures (8i gives the error, 10g works, I don't have 9i handy but I suspect it's the same as 8i).

So as I said before:
Put the concatenation inside the subselect.
Re: Update in PL/SQL procedure [message #410850 is a reply to message #410694] Tue, 30 June 2009 11:40 Go to previous messageGo to next message
kon786
Messages: 10
Registered: June 2009
Junior Member
Thank you for helping me out.
I have tried to replicate exact query
CREATE OR REPLACE PROCEDURE test IS
v_sql_err_code NUMBER;
v_sql_err_text VARCHAR2(512);
BEGIN
UPDATE TableA A
SET A.column1 = A.Column1 ||','|| (SELECT distinct b.column1 FROM TableB B
WHERE B.column2 = A.Column2
and ltrim(rtrim(B.column3)) = ltrim(rtrim(A.column3))) where A.column4 is not null;
commit;
EXCEPTION
WHEN OTHERS THEN
BEGIN
v_sql_err_code := SQLCODE;
v_sql_err_text := SQLERRM;
INSERT INTO TABLE_ERRORS VALUES(
user, 'NULL', NULL, 'TEST',
v_sql_err_code, v_sql_err_text,SYSDATE, USER);
COMMIT;
END;
end;
/

Re: Update in PL/SQL procedure [message #410851 is a reply to message #410837] Tue, 30 June 2009 11:49 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 30 June 2009 16:01
Put the concatenation inside the subselect.


And this:
EXCEPTION
WHEN OTHERS THEN
BEGIN 
v_sql_err_code := SQLCODE;
v_sql_err_text := SQLERRM;
INSERT INTO TABLE_ERRORS VALUES(
user, 'NULL', NULL, 'TEST',
v_sql_err_code, v_sql_err_text,SYSDATE, USER);
COMMIT; 
END;


Is a really bad idea.
Never do an exception when others that doesn't re-raise the error or you won't know it failed.
Never, ever do an exception when others that does a commit.
If you really want to log the errors like this (and it's generally not necessary) create a seperate procedure to do the insert into the log table and make it an autonomous_transaction.
Re: Update in PL/SQL procedure [message #410861 is a reply to message #410850] Tue, 30 June 2009 14:01 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you didn't understand what Cookiemonster has said (or don't know what it means):
SET A.column1 = A.Column1 ||','|| (SELECT distinct b.column1 FROM TableB B
should be rewritten as
SET A.column1 = (SELECT distinct A.Column1 ||','|| b.column1 FROM TableB B

What happens in that case?

Also, if it is not a problem, try to format your code and enclose it into the [code]tags to improve readability. If you don't know how to do that, it is described in the OraFAQ Forum Guide.
Re: Update in PL/SQL procedure [message #410863 is a reply to message #410861] Tue, 30 June 2009 14:15 Go to previous messageGo to next message
kon786
Messages: 10
Registered: June 2009
Junior Member
Thank you so much Cookiemonster and Littlefoot.I have corrected my code.I did know how to concatenate inside the subselect.

[Updated on: Tue, 30 June 2009 14:15]

Report message to a moderator

Re: Update in PL/SQL procedure [message #410898 is a reply to message #410863] Tue, 30 June 2009 23:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Kaeluan already posted that in his/her second reply, so you could have seen it.
Re: Update in PL/SQL procedure [message #411020 is a reply to message #410694] Wed, 01 July 2009 08:45 Go to previous message
kon786
Messages: 10
Registered: June 2009
Junior Member
Oh I am so sorry Kaeluan.Frank is right.You had already given the solution but some how I missed the correction you suggested.
If I would have noticed your query correction properly I could have solved my problem quickly.
Thanks again every one for helping and giving solutions to juniors.
Previous Topic: Could you please help me in joining these tables
Next Topic: Finding entries where only 1 entry is available (merged 3)
Goto Forum:
  


Current Time: Fri Dec 09 09:19:54 CST 2016

Total time taken to generate the page: 0.18642 seconds