Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    37

    Unanswered: Passing table name as a Variable

    Hi,
    I have a stored procedure. Depending on the value of the variable (count), I need to fetch the data from the tables.
    If the count value is 0, I need to fetch it from the remote database, else I need to fetch the data from the database
    where the SP is running(local). The tables have the same structure, same columns except the table name being different.
    I want to pass the table name dynamically to the select query. Please help me on this asap.

    Select x,y,z
    from vTableName
    where id = pInId;

    where x,y, z are the columns present in both the databases...
    vTableName should be passed dynamically
    pinId is what I pass to the function

  2. #2
    Join Date
    Mar 2004
    Location
    Concord, MA
    Posts
    27

    Re: Passing table name as a Variable

    Originally posted by gomes009
    Hi,
    I have a stored procedure. Depending on the value of the variable (count), I need to fetch the data from the tables.
    If the count value is 0, I need to fetch it from the remote database, else I need to fetch the data from the database
    where the SP is running(local). The tables have the same structure, same columns except the table name being different.
    I want to pass the table name dynamically to the select query. Please help me on this asap.

    Select x,y,z
    from vTableName
    where id = pInId;

    where x,y, z are the columns present in both the databases...
    vTableName should be passed dynamically
    pinId is what I pass to the function
    hi,

    you have two options...

    1. Go for dynamic SQL and the build the query at the run time dynamically...

    2. check for the value as 0 or others

    if v_var = 0 then

    select ...
    from table_name@db_link
    where ...

    else

    select ...
    from table_name
    where ...

    end if

    you have to go for pl/sql for this....

    hope this will help...
    ~bagchi

  3. #3
    Join Date
    Feb 2004
    Posts
    37

    Re: Passing table name as a Variable

    I am getting the following error when I have the p_table_name hard coded as 'test_1'. test_1 table is owned by the user that I am currently executing the code
    1 declare
    2 v_count number;
    3 p_table_name varchar2(50);
    4 BEGIN
    5 p_table_name:='test_1';
    6 EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM p_table_name'
    7 INTO v_count ;
    8 dbms_output.put_line(v_count);
    9* END;
    SQL> /
    declare
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at line 6

    Now if the quotes are removed from the table_name(test_1), it gives a different error

    1 declare
    2 v_count number;
    3 p_table_name varchar2(50);
    4 BEGIN
    5 p_table_name:=test_1;
    6 EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM p_table_name'
    7 INTO v_count ;
    8 dbms_output.put_line(v_count);
    9* END;
    SQL> /
    p_table_name:=test_1;
    *
    ERROR at line 5:
    ORA-06550: line 5, column 15:
    PLS-00357: Table,View Or Sequence reference 'TEST_1' not allowed in this
    context
    ORA-06550: line 5, column 1:
    PL/SQL: Statement ignored


    Any help is appreciated

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >5 p_table_name:=test_1;
    Needs to be
    p_table_name:= 'test_1';
    which is the proper way to assign a string to a variable.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    or try
    declare
    v_sql varchar2(500);

    p_table_name:='test_1';

    v_sql := 'SELECT COUNT(1) FROM '||p_table_name;

    EXECUTE IMMEDIATE v_sql;



    HTH
    Gregg

  6. #6
    Join Date
    Mar 2004
    Location
    Concord, MA
    Posts
    27
    this shld work for you...

    declare
    v_count number;
    p_table_name varchar2(50);
    BEGIN
    p_table_name:='test_1';
    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||p_table_name
    INTO v_count ;
    dbms_output.put_line(v_count);
    END;
    /
    ~bagchi

  7. #7
    Join Date
    Feb 2004
    Posts
    37
    Originally posted by bagchi
    this shld work for you...

    declare
    v_count number;
    p_table_name varchar2(50);
    BEGIN
    p_table_name:='test_1';
    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||p_table_name
    INTO v_count ;
    dbms_output.put_line(v_count);
    END;
    /
    Thanks, it worked!

Posting Permissions

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