OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » URGENT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

URGENT [message #39014]

Thu, 06 June 2002 10:41

Raj
Messages: 411
Registered: November 1998

Senior Member

Hi
i have a fincion which should return count, but not returning any value.

please let me know what is wrong with my logic

FUNCTION GetCount
(
HoursFrom IN NUMBER,
HoursTo IN NUMBER,
i_CenterId IN CHAR,
OutString IN VARCHAR
)
RETURN NUMBER
IS
sqlstr VARCHAR2(2000);
l_durationCount NUMBER;
BEGIN

sqlstr := '
SELECT COUNT(a.troubleid) INTO l_durationcount
FROM TROUBLES a, DETAILS b WHERE
a.Troubleid = b.Troubleid AND
((SYSDATE - a.Reportdt)*24 > 4) AND
((SYSDATE - a.Reportdt)*24 < 8) AND
a.ZCODE = '||'''CR'''||' AND
a.CenterId IN ('||i_CenterId||') '||OutString;
EXECUTE IMMEDIATE sqlstr ;

RETURN l_durationCount;

END GetCount;

Report message to a moderator

Re: URGENT [message #39015 is a reply to message #39014]

Thu, 06 June 2002 11:16

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you need make use
execute immediate str into returnvalue;
or
execute immediate str into ldurationcount;

here is a working example.


SQL> get ret
  1  CREATE or replace function ret_count
  2  (tab_name in varchar2)
  3  return number as
  4  ret number;
  5  str varchar2(100);
  6  begin
  7  str:='select count(*) from ' ||tab_name;
  8  execute immediate str into ret;
  9  RETURN ret;
 10* end;
SQL> /

Function created.

SQL> select table_name,ret_count(table_name) from user_tables;

TABLE_NAME                     RET_COUNT(TABLE_NAME)
------------------------------ ---------------------
A                                                  0
AA                                                 0
ASDF                                               2
ASDFA                                              0
ASDFASD                                           26
BONUS                                              0
D                                                  4
DEPT                                               4
EMP                                               14
EMP1                                              40
EMP2                                              13
EMP7                                               0
KK                                                 0
LL                                                 0
PLAN_TABLE                                         5
SALGRADE                                           5
SAMPLE                                             6
TEST10                                            15
TEST2                                              0
TESTOBJ                                            1
TEST_LOAD                                         14

21 rows selected.

Report message to a moderator

Re: URGENT [message #39018 is a reply to message #39014]

Thu, 06 June 2002 18:07

Raj
Messages: 411
Registered: November 1998

Senior Member

Mahesh, thanks, you were helpful,
iam still running into an problem,
iam passing HoursFrom and HoursTo to the function
as 5 and 9
but its giving me an error as invalid column
HoursFrom and HoursTo are not table fields,they are local to the function.
what should i do, for make this work.

FUNCTION GetCount
(
HoursFrom IN NUMBER,
HoursTo IN NUMBER,
i_CenterId IN CHAR,
OutString IN VARCHAR
)
RETURN NUMBER
IS
sqlstr VARCHAR2(2000);
l_durationCount NUMBER;
BEGIN

sqlstr := '
SELECT COUNT(a.troubleid) INTO l_durationcount
FROM TROUBLES a, DETAILS b WHERE
a.Troubleid = b.Troubleid AND
((SYSDATE - a.Reportdt)*24 > HoursFrom) AND
((SYSDATE - a.Reportdt)*24 < HoursTo) AND
a.ZCODE = '||'''CR'''||' AND
a.CenterId IN ('||i_CenterId||') '||OutString;
EXECUTE IMMEDIATE sqlstr ;

RETURN l_durationCount;

END GetCount;

Report message to a moderator

Re: URGENT [message #39022 is a reply to message #39018]

Fri, 07 June 2002 03:38

Rick Cale
Messages: 111
Registered: February 2002

Senior Member

Hi,

Yes they are invalid columns in this case. You have to
build string concatenating the variables HoursFrom and HoursTo. Also you have to remove INTO statement and
put it in the EXECUTE IMMEDIATE stmt.

sqlstr := 'SELECT COUNT(a.troubleid)
FROM TROUBLES a, DETAILS b WHERE
a.Troubleid = b.Troubleid AND
((SYSDATE - a.Reportdt)*24 > ' ||
HoursFrom || ') AND
((SYSDATE - a.Reportdt)*24 < ' ||
HoursTo || ') AND '||
'a.ZCODE = '||'''CR'''||' AND
a.CenterId IN ('||i_CenterId||') '||OutString;

EXECUTE IMMEDIATE sqlstr
INTO l_durationcount;

Report message to a moderator

Re: URGENT [message #39028 is a reply to message #39018]

Fri, 07 June 2002 07:40

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

as rick has told you need to use

EXECUTE IMMEDIATE sqlstr INTO l_durationcount;

Report message to a moderator

Previous Topic:	Re: What is wrong with my logic?
Next Topic:	Error ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:49:37 CDT 2024