-
i want to create a procedure, where i can pass values like:
my_proc('x','y',('a','b','c'))
how it can be done? has anybody worked on this kind of problem?
thanks and regards,
Amit Ahuja
-
Originally posted by amit_ahuja
i want to create a procedure, where i can pass values like:
my_proc('x','y',('a','b','c'))
how it can be done? has anybody worked on this kind of problem?
thanks and regards,
Amit Ahuja
Hi,
I just want to know that what is the use of sending the parameters like this..u can send them as separate parameters.
Any special task by sending like this.
Take Care.
-
I believe you really mean
my_proc('x','y','a,b,c')
if that was the case I did something similar
Code:
create or replace package body ins_media
as
procedure media_semana(p_ori_owner varchar2,
p_ori_tab varchar2,
p_avg_col varchar2,
p_predicate varchar2,
p_group_col varchar2,
p_des_owner varchar2,
p_des_tab varchar2)
is
l_sql_str varchar2(2000);
begin
l_sql_str := 'insert into ' || p_des_owner || '.' || p_des_tab
|| ' select ' || p_group_col
|| ', avg(' || replace(p_avg_col,',','),avg(')
|| ') from ' || p_ori_owner
|| '.' || p_ori_tab || ' where ' || p_predicate
|| ' between trunc(sysdate) - 7500 and trunc(sysdate) group by '
|| p_group_col;
execute immediate l_sql_str;
dbms_output.put_line(sql%rowcount);
dbms_output.put_line(l_sql_str);
end media_semana;
example executing
EXEC INS_MEDIA.MEDIA_MES('LSC', 'EMP', 'COMM, SAL', 'HIREDATE', 'DEPTNO', 'LSC', 'EMP_TEST')
the key part is replace(p_avg_col,',','),avg(')
I have not pasted the whole code
-
Hi,
Could u tell me the special advantage of using it.Can't we take 2 parameters rather using this clause.Any special benefit.
Thanks.
-
this is the actual problem....
hi everybody!
thanks a lot for your concern and suggesting me the solution.
the actual problem that i'm facing is:
We have a stored procedure in which we want to pass a comma delimited list as one of the parameters. Inside the stored procedure I would like to use that list as part of a where clause using "in (commalist)". Howver
this raises an invalid number error.
The only two methods we can think of for such a
small query are:
1) Loop through the list to build some sort of a cursor containing each value in its own row then using that cursor in the query.
2) Build dynamic SQL and EXECUTTE IMMEDIATE it into a temporary table.
Then OPEN CURSOR FOR Select from that table.
If you can think of a less painful method please let me know.
Thanks and regards,
Amit Ahuja
-
Re: this is the actual problem....
Amit,
You can use an array to pass comma delimited values, then, with a simple loop asign them to a string and execute your query dynamic.
Let me know if you need some example.
-
Re: this is the actual problem....
A little example would not hurt:
declare an array type:
TYPE T_ARRAY_TYPE IS VARRAY(100) OF VARCHAR2(4000);
T_ARRAY T_ARRAY_TYPE;
Your proc:
MY_PROC (NUM IN NUMBER,
COM_DELIMIT IN T_ARRAY_TYPE)
FOR I IN COM_DELIMIT LOOP
VAR := VAR ||', '||COM_DELIMIT(I);
END LOOP;
Execute your statment:
EXECUTE IMMEDIATE 'SELECT *
FROM MY_TABLE
WHERE VAL = :VAR' USING VAR;
Let me know if it works
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|