Home » SQL & PL/SQL » SQL & PL/SQL » CALLING STORED PROCEDURE
CALLING STORED PROCEDURE [message #271739] Tue, 02 October 2007 13:42 Go to next message
loonab
Messages: 7
Registered: October 2007
Junior Member
I am having problems calling a stored hard coded function into a update, where sql statement
I GET errors like invalid col, not a procedure or unDEFINED

#
update tab1
set col x = (select col y from tab2
where
cond1 = cond 2)
where source_system_id = source_system(bis_def.c_srcsys_cpcc)


the function to be called is given below

***************************************************
CREATE OR REPLACE Function Source_System
( p_system_name in varchar2 default 'OTHER')
return integer
is

v_retval Integer := null;


Begin

v_retval := null;

-- added 9/17/2007
if p_system_name = bis_def.c_SRCSYS_BLXRBT then
v_retval := 13;
end if;

-- added 9/20/2007
if p_system_name = bis_def.c_SRCSYS_CPCC then
v_retval := 14;
end if;
.......
.......

Return v_retval;
End Source_System;
/

could someone help me with this asap
Thanks in advance
Re: CALLING STORED PROCEDURE [message #271741 is a reply to message #271739] Tue, 02 October 2007 14:01 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow posting guidelines as stated in the #1 STICKY post found at the very top of this forum.

Use <code tags> along with CUT & PASTE using SQL*Plus to post results of compiling the function back here.
Re: CALLING STORED PROCEDURE [message #271744 is a reply to message #271739] Tue, 02 October 2007 14:06 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Congratulations, you get three messages without ORA codes and line/column location.
You should consult SQL Reference to get correct UPDATE statement syntax.

Interesting, that this is used in asap.
Re: CALLING STORED PROCEDURE [message #271746 is a reply to message #271741] Tue, 02 October 2007 14:16 Go to previous messageGo to next message
loonab
Messages: 7
Registered: October 2007
Junior Member
I havent included the complete function as its a company document.
Yes I will try follow the guideline
Re: CALLING STORED PROCEDURE [message #271816 is a reply to message #271746] Wed, 03 October 2007 01:21 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
loonab wrote on Tue, 02 October 2007 21:16
I havent included the complete function as its a company document.
Yes I will try follow the guideline
Well, you can post the error code, can't you? Or the SQL statement using fake names, like:
UPDATE mytable 
SET    column1 = SYSDATE
WHERE  column2 = 'ABC'
You are right that you don't disclose private information but we need a little bit more information.

MHE
Previous Topic: split data of one column on bases of another column (multi merge of an IM illiterate)
Next Topic: Two snippets doesn't work the same
Goto Forum:
  


Current Time: Thu Dec 08 19:55:53 CST 2016

Total time taken to generate the page: 0.10119 seconds