Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: using parameter from procedure to generate SQL statement

    is it possible to do something like that?

    say if i pass down a array of values, can i dynamically create a SQL from those values to execute?

    now that i think about it....
    maybe ProC will better suited for this?

    Mark

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: using parameter from procedure to generate SQL statement

    Originally posted by mchih
    is it possible to do something like that?

    say if i pass down a array of values, can i dynamically create a SQL from those values to execute?

    now that i think about it....
    maybe ProC will better suited for this?

    Mark
    Sounds like dynamic SQL, but I'd need an example of what you want to do to be sure.

  3. #3
    Join Date
    Nov 2002
    Posts
    98
    for example,

    i will pass down a array of values( the total count of values varies)

    and i will build a generic SQL:

    select value
    from test_table
    where (value = arrary_of_values[1])
    (value = arrary_of_values[2])
    .
    .
    .
    ;

    statement within the parenthesis is what i will need to construct, based on how many values are there.

    Mark

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by mchih
    for example,

    i will pass down a array of values( the total count of values varies)

    and i will build a generic SQL:

    select value
    from test_table
    where (value = arrary_of_values[1])
    (value = arrary_of_values[2])
    .
    .
    .
    ;

    statement within the parenthesis is what i will need to construct, based on how many values are there.

    Mark
    Ah, that doesn't require dynamic SQL, but it uses some fancy stuff:

    SQL> create type numtab_type as table of number;
    2 /

    Type created.

    SQL> declare
    2 array_of_values numtab_type := numtab_type( 51778, 41400, 38460 );
    3 begin
    4 for r in
    5 ( select * from all_objects
    6 where object_id in (select * from table(select cast(array_of_values as numtab_type) from dual))
    7 )
    8 loop
    9 dbms_output.put_line(r.object_name);
    10 end loop;
    11* end;
    SQL> /
    EMP
    DEPT
    SALGRADE

    PL/SQL procedure successfully completed.

    As you can see, I queried from ALL_OBJECTS the 3 rows with OBJECT_IDs 51778, 41400 and 38460 which were stored in the array.
    The array needs to be of a SQL type though (i.e. CREATE TYPE statement), it will not work with PL/SQL collections.

  5. #5
    Join Date
    Nov 2002
    Posts
    98
    thanks for the help!

    so if i want to use numtab_type as a input for my procedure, can i do soemthing like:

    create procedure test123(array_of_num numtab_type in)

    begin
    end test123;
    /

    or execute it like: EXEC test123((11, 22, 33));?


    Thanks,

    Mark

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by mchih
    thanks for the help!

    so if i want to use numtab_type as a input for my procedure, can i do soemthing like:

    create procedure test123(array_of_num numtab_type in)

    begin
    end test123;
    /

    or execute it like: EXEC test123((11, 22, 33));?


    Thanks,

    Mark
    Almost:

    Code:
    SQL> create or replace procedure p
      2  (
      3    array_of_values in numtab_type
      4  )
      5  is
      6  begin
      7    for r in
      8      ( select * from all_objects
      9        where  object_id in (select * from table(select cast(array_of_values as numtab_type) from dual))
     10      )
     11    loop
     12      dbms_output.put_line(r.object_name);
     13    end loop;
     14* end;
    SQL> /
    
    Procedure created.
    
    SQL> exec p( numtab_type( 51778, 41400, 38460 ) )
    EMP
    DEPT
    SALGRADE
    
    PL/SQL procedure successfully completed.

  7. #7
    Join Date
    Sep 2003
    Posts
    2

    Re: using parameter from procedure to generate SQL statement

    Originally posted by mchih
    is it possible to do something like that?

    say if i pass down a array of values, can i dynamically create a SQL from those values to execute?

    now that i think about it....
    maybe ProC will better suited for this?

    Mark
    Actually Im actually doing a program that reads an array of queries and stores the result in an array. In order to archive that ive a pl/sql "The Processor" who receives the array and process each record with the EXECUTE IMMEDIATE command, its something like this:


    EXECUTE IMMEDIATE vCODE INTO vVALOR(vID_REGLA);

    where vVALOR was declared this way:

    TYPE aVALORES IS TABLE OF VARCHAR2(30);
    vVALOR aVALORES:=aVALORES();

    after the DECLARE section you must expand de index of the array in order to get

    vVALOR.EXTEND(9999);


    I HOPE THIS HELP YOU.

    SALUDOS FROM MEXICO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •