parameters in stored procedure

[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

[nishant]

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.

[pando]

I believe you really mean

my_proc('x','y','a,b,c')

if that was the case I did something similar

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

[nishant]

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.

[amit_ahuja]

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

[Highlander]

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.

[Highlander]

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