This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Aug 2, 2013 8:47 AM by Greg.Spall RSS

Binding variable type using parameters from procedure

Pappu23 Newbie
Currently Being Moderated

Hi ,

 

Is it possible ? Can anyone please help me in how to do it ?

 

 

create or replace procedure p1 (

p_table_name varchar2,

p_column_name varchar2

) as

l_values   p_table_name.p_column_name%TYPE;

 

begin

.............

............

end;

any help will be appreciated.

 

Thanks

  • 1. Re: Binding variable type using parameters from procedure
    rp0428 Guru
    Currently Being Moderated
    Is it possible ? Can anyone please help me in how to do it ?

    No - and no. You can not declare variables dynamically; they need to exist at compile time.

     

    The fact that you are even wanting to do that means you are trying to use the wrong solution for whatever (unstated) problem you are trying to solve. There are ways to use the DBMS_SQL package to describe columns to determine their type but that package is RARELY needed to solve most business problems.

     

    Start over and tell us the PROBLEM that you have. You also need to provide your 4 digit Oracle version.

  • 2. Re: Binding variable type using parameters from procedure
    Pappu23 Newbie
    Currently Being Moderated

    Thanks rp0428


    Let me explain you what  i am trying


    I will get two parameter

    p_table_name

    p_column_name

    and a array of key value pairs which will be filter (where clauses) conditions.

     

    I have a very simple task in hand .


    get all the record set of p_column_name  values from table p_table_name using filter condition passed. For example

    v_Sql:= select p_column_name from p_table_name where (filter constructed from array passed )

     

    created dynamic sql ,

    open v_Sql using cursor_variable;

     

     

    although i can safely use varchar2 which can fetch date and number values but  for fetching values of recod-set using a sys cursor variable i was trying this thing

    .(l_values   p_table_name.p_column_name%TYPE;)

     


    fetch cursor_variable into l_values;


    after getting the record set , i have to do further check and validation and some processing on the values

     

     

    Please correct me if i am doing wrong

     

    Regards



  • 3. Re: Binding variable type using parameters from procedure
    Greg.Spall Expert
    Currently Being Moderated

    Well, although there is a datatype called "ANYDATA" or something like that, you probably don't need to go there for  this.

     

    This sounds like a simple matter of overloading your procedure.

     

    Create a package, with multiple "copies" of your procedure.

     

    Each handles different data type:

     

    create or replace package your_pkg
    as
       procedure my_proc ( in_table   varchar2, in_column   varchar2,  in_string  varchar2 );
       procedure my_proc ( in_table   varchar2, in_column   varchar2,  in_number  number );
       procedure my_proc ( in_table   varchar2, in_column   varchar2,  in_date   date );
      ... etc.
    end;
    /
  • 4. Re: Binding variable type using parameters from procedure
    Pappu23 Newbie
    Currently Being Moderated

    Ok..

     

    You meant i can use

     

    in_string  varchar2

    in_number  number

    in_date   date


    to bind the values  at fetch time in my_proc ? Correct?


     

    but my_proc is getting called from java .and they have to change the call there also to bind

     

    in_string  varchar2

    in_number  number

    in_date   date

     

    and they will be null or number zero by default

     

     

    Can you tell how to use ANYDATA ? and can it work for all data type

     

    Regards

  • 5. Re: Binding variable type using parameters from procedure
    Greg.Spall Expert
    Currently Being Moderated

    Pappu23 wrote:

     

    Ok..

     

    You meant i can use

     

    in_string  varchar2

    in_number  number

    in_date   date


    to bind the values  at fetch time in my_proc ? Correct?

     

     

    Correct, yes

     

    Pappu23 wrote:

     

    but my_proc is getting called from java .and they have to change the call there also to bind

     

    in_string  varchar2

    in_number  number

    in_date   date

     

    and they will be null or number zero by default

     

     

    So? why's that a problem:

     

    If you really wanted to, you could just declare a single procedure:

     

    procedure my_proc ( in_table   varchar2, in_column  varchar2,  in_string  varchar2, in_number number, in_date date )

     

    So they can call it:

     

    my_proc ( 'table', 'column', 'this', null, null );
    or
    my_proc ( 'table', 'column', null, 123, null );
    ... etc.
    or even:
    my_proc ( in_table => 'table', in_column => 'column', in_date => sysdate )

    ;

     

    whatever ... then you can sort out the logic within your own procedure:

     

    if in_string is not null then
      my_proc ( in_table, in_column, in_string );
    elsif in_number is not null then
    my_proc ( in_table, in_column in _number );
    elsif in_date is not null then
    my_proc ( in_table, in_column, in_date );
    else
      error ??
    end if;

     

    That would give you the most flexibility. You just use that java called proc as a "wrapper" into the other, (properly) overloaded routines.

    If the Java guys can't handle it, do it yourself ..

    lol

  • 6. Re: Binding variable type using parameters from procedure
    Greg.Spall Expert
    Currently Being Moderated

    Pappu23 wrote:

     

    Can you tell how to use ANYDATA ? and can it work for all data type

     

    Regards

    Don't really want to unless we find out we can't handle it another way

  • 7. Re: Binding variable type using parameters from procedure
    Pappu23 Newbie
    Currently Being Moderated

    Thanks Greg for your help .

     

    I get the way to to it . Based on table name and column name that i get , i  can get data type and then can call overloaded procs .

     

     

    Thanks

  • 8. Re: Binding variable type using parameters from procedure
    Greg.Spall Expert
    Currently Being Moderated

    well, not quite what I was getting at, but sure, that'll work too. Just don't hardcode it, I'd try to leverage the view dba_tab_columns to get the proper data type.

  • 9. Re: Binding variable type using parameters from procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Pappu23 wrote:

     

    I will get two parameter

    p_table_name

    p_column_name

    and a array of key value pairs which will be filter (where clauses) conditions.

     

    I have a very simple task in hand .


    get all the record set of p_column_name  values from table p_table_name using filter condition passed. For example

    v_Sql:= select p_column_name from p_table_name where (filter constructed from array passed )

     

    This does NOT make any sense. Who came up with this?

     

    SQL is the interface used to access and manipulate database data. If you want substitute the SQL interface for a client, enabling the client to use an alternative interface, this needs to provide the client with SQL and database abstraction, and provide additional functionality and flexibility.

     

    Creating a primitive interface like you are tasked to do, does neither. It does not make it easier for the client to use SQL and the database. The client still needs to know the database tables and the columns in the tables. It still needs to understand relationships between tables. It still needs to understand the data model. Etc.

     

    All that this interface does is to turn this SQL:

    select col1 from table1 where col2 = 'abc' and col3 = 123

     

    into this type of call:

    begin

      Interface(

        tableName => 'table1',

        columName => 'col1',

        keyValues => TKeyValues(  TKey('col2','abc'), TKey('col3',123) ),

        ..output params..

      );

    end;

    How on earth is this now better and easier and more readable, flexible and maintainable than SQL? How is this an improvement over the SQL interface (language) that is available?

     

    You know what this sounds to me like? Java developers that do not want to use SQL as binding is "difficult" and want to push the concept of using bind variables to someone else - let that party deal with it, and provide these "awesome" Java developers with a single call interface.

     

    It. Is. Stupid.

     

    This is architecturally wrong.

     

    This is not how Oracle is optimally used.

     

    This is not how software should ever be engineered.

     

    Now for my first mug of early morning coffee, to try and wash away the horribly bad taste of poor s/w design....

  • 10. Re: Binding variable type using parameters from procedure
    BluShadow Guru Moderator
    Currently Being Moderated

    I agree with Billy, this 'requirement' is seriously flawed.

     

    What you're essentially asking is "I want a procedure that takes an unknown table and unknown column(s) of unknown datatypes and create a ref cursor from that"

    So how is your calling code going to know what to do with this data it gets through the ref cursor?... for that is must know what the tables are, and what the columns are it's supply and also know the datatypes it expects to be retrieving... in which case that knowledge should be coded into the procedures that the calling code calls, even if that means you have seperate procedures for each table etc.  Sure you can have varying restrictions on the query (where clauses) based on passed in parameters, as that's appropriate stuff to bind in, but having dynamic tables and columns is an immediate indication of a potentially flawed design.

  • 11. Re: Binding variable type using parameters from procedure
    Pappu23 Newbie
    Currently Being Moderated

    Hi ,

     

    Thanks for all the greats advice

     

    Re: Binding variable type using parameters from procedure

    What you're essentially asking is "I want a procedure that takes an unknown table and unknown column(s) of unknown datatypes and create a ref cursor from that"

    So how is your calling code going to know what to do with this data it gets through the ref cursor?.

     

     

     

    Table name will not be unknown . The tables are storing some filter conditions  to be applied on another set of data in other tables.Tables are static and not entirely dynamic.

     

    this is i read on oracle documentation

     

       In PL/SQL, you need dynamic SQL to run:

    • SQL whose text is unknown at compile time

      For example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of subclauses is unknown at compile time.

    • SQL that is not supported as static SQL

      That is, any SQL construct not included in "Description of Static SQL"

       

      .

  • 12. Re: Binding variable type using parameters from procedure
    Greg.Spall Expert
    Currently Being Moderated

    I was kinda wondering that myself, but didn't really want to say anything

  • 13. Re: Binding variable type using parameters from procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Storing filter conditions (code) as data is high questionable. It almost always is the WRONG approach.

     

    Now assuming for a moment that you know what you are doing storing code as data, you need to use DBMS_SQL cursors to turn that data back into executable code.

  • 14. Re: Binding variable type using parameters from procedure
    Pappu23 Newbie
    Currently Being Moderated

    Thanks Greg,

     

    But i will be glad if you clearly specify what you wanted to say ...

     

    Thanks

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points