Home » SQL & PL/SQL » SQL & PL/SQL » Cursor - character to number conversion error in params
Cursor - character to number conversion error in params [message #211068] Tue, 26 December 2006 01:25 Go to next message
smora
Messages: 59
Registered: May 2006
Member
Hi

I need to create a cursor that takes an optional parameter (p_game_id). If the value passed for that param is zero, then i run the Report11Cursor. However, if the value passed for p_game_id is not zero, then I run the Report11Cursor_game, which uses that param in the where clause.

create or replace function report11
(p_startdate IN t.view_date%TYPE,
p_enddate IN t.view_date%TYPE,
p_game_id IN VARCHAR2
)
RETURN report11_table

AS
p_game_id_mod VARCHAR2(400);
.
.
CURSOR Report11Cursor IS
select game_id, sub_id, game_placement, view_date as view_date
from t
where view_date is not null
and trans_date >= p_startdate and to_date(to_char(trans_date, 'YYYYMMDD')) <= p_enddate
order by game_id, sub_id, view_date asc;

CURSOR Report11Cursor_game IS
select game_id, sub_id, game_placement, view_date as view_date
from t
where game_id in (p_game_id_mod)
and view_date is not null
and trans_date >= p_startdate and to_date(to_char(trans_date, 'YYYYMMDD')) <= p_enddate
order by game_id, sub_id, view_date asc;

BEGIN
-- Replace
select REPLACE(p_game_id, '''','') into p_game_id_mod from dual;

LOOP
EXIT WHEN INSTR(p_game_id_mod,',') = 0;
v_gid := SUBSTR(p_game_id_mod,1,INSTR(p_game_id_mod,','));
p_game_id_mod := SUBSTR(p_game_id_mod,INSTR(p_game_id_mod,',')+1);

dbms_output.put_line(v_gid);
END LOOP;
.
.
.
Question 1: Can I write the cursor in such a way so that i only use 1 cursor for both cases, where a param is passed and where it is missing.

Question 2: The format of p_game_id is a list of comma separated numbers (11,22,33,44). I specify the type of this param as varchar2, but when i place it into the where clause in Report11Cursor_game, it gives a "Exception: -6502 ORA-06502: PL/SQL: numeric or value error: character to number conversion error". Im guessing its expecting a comma separated list of numbers, and does not convert the string passed to it into the required format.

Any ideas on how to workaround this would be appreciated.
Thank you.
Shaman
Re: Cursor - character to number conversion error in params [message #211087 is a reply to message #211068] Tue, 26 December 2006 03:41 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
for your question no 2 read this
http://tkyte.blogspot.com/2006/06/varying-in-lists.html

for question 1 put filter condition like this and try

where case when nvl(trim(REPLACE(replace(p_game_id,',',''), '''','')),'0')='0' then 1=1 else game_id in <<function that will convert p_gameid as comma seperated values as per above url link>>
end

[Updated on: Tue, 26 December 2006 04:53]

Report message to a moderator

Re: Cursor - character to number conversion error in params [message #211097 is a reply to message #211068] Tue, 26 December 2006 05:51 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
I tried the solution to Question 1, using a simplified statement to check if the 'case' within where clause works...

select game_id, sub_id, game_placement, view_date as view_date
from transactions
where view_date is not null
and CASE WHEN p_game_id = 0 THEN 1=1 ELSE game_id = p_game_id END
order by game_id, sub_id, view_date asc

SQL> /
and CASE WHEN p_game_id = 0 THEN 1=1 ELSE game_id = p_game_id END
*
ERROR at line 4:
ORA-00905: missing keyword


Is it possible to use case within where in Oracle 10g?

Thanks
Shaman
Re: Cursor - character to number conversion error in params [message #211098 is a reply to message #211097] Tue, 26 December 2006 06:02 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Unfortunately I do not have access to Oracle right now to check this but can you try this check if works
select game_id, sub_id, game_placement, view_date as view_date,
CASE WHEN p_game_id = 0 THEN 1  when game_id = p_game_id then 1 else 0 END as cond0
from transactions
where view_date is not null
and cond0=1
order by game_id, sub_id, view_date asc

Re: Cursor - character to number conversion error in params [message #211102 is a reply to message #211068] Tue, 26 December 2006 06:25 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
That doesnt work either:

1 select game_id, sub_id, game_placement, view_date as view_date,
2 CASE WHEN p_game_id = 0 THEN 1 when game_id = p_game_id then 1 else 0 END as cond0
3 from transactions
4 where view_date is not null
5 and cond0=1
6* order by game_id, sub_id, view_date asc
SQL> /
and cond0=1
*
ERROR at line 5:
ORA-00904: "COND0": invalid identifier
Re: Cursor - character to number conversion error in params [message #211103 is a reply to message #211102] Tue, 26 December 2006 06:31 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Sorry do not have access to Oracle else I would have tested before posting here

but Can you try this?

SQL> variable p_game_id varchar2(4000);
SQL> select * from (
select game_id, sub_id, game_placement, view_date as view_date,
CASE WHEN :p_game_id = 0 THEN 1  when game_id = p_game_id then 1 else 0 END as cond0
from transactions)
where view_date is not null
and cond0=1
order by game_id, sub_id, view_date asc
/


Re: Cursor - character to number conversion error in params [message #211402 is a reply to message #211068] Thu, 28 December 2006 07:16 Go to previous message
itspurushoth
Messages: 1
Registered: December 2006
Location: Bangalore
Junior Member

For Question no:- 1

You Can Use Decode Statement in the Cursor
if the p_game_id is 0 its like Report11Cursor
else
its like Report11Cursor_game

CURSOR Report11Cursor IS
select game_id, sub_id, game_placement, view_date as view_date
from t
where game_id = Decode(P_game_id,0,game_id,P_game_id)
view_date is not null
and trans_date >= p_startdate and to_date(to_char(trans_date, 'YYYYMMDD')) <= p_enddate
order by game_id, sub_id, view_date asc;

For Question no :- 2
I Update it after 1/2 hrs
Previous Topic: Passing column name and getting column value
Next Topic: Queryoutput from 2 tables
Goto Forum:
  


Current Time: Wed Dec 07 08:50:33 CST 2016

Total time taken to generate the page: 0.12469 seconds