parameters in stored procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: parameters in stored procedure

  1. #1
    Join Date
    Jun 2001
    Location
    India
    Posts
    30
    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

  2. #2
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    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.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  4. #4
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    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.

  5. #5
    Join Date
    Jun 2001
    Location
    India
    Posts
    30

    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

  6. #6
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105

    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.

  7. #7
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105

    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
  •  



Click Here to Expand Forum to Full Width