This discussion is archived
1 Reply Latest reply: Sep 24, 2013 8:25 AM by rp0428 RSS

Passing parameters to get correct table_name

safazaurs Newbie
Currently Being Moderated

Hi.

I'm trying to build one little test case to get an answer how to pass a parameter for table_name, and then work with it.

So I just made overall concept for test case, but my didn't achieve my needs properly, because everything I coded is pretty static.

 

[code]

declare

    v_user varchar2(20) :='&lietotajs';

    v_tabula varchar2(20) :='&tabula';

    v_tabulas_strings varchar2(20);

    v_max number(30);

begin

    v_tabulas_strings := v_user||'.'||v_tabula;

    dbms_output.put_line('PARAMETRI = '||v_user||'.'||v_tabula||'          '||v_tabulas_strings);

    select max(id) into v_max from v_tabulas_strings;

end;

/

[/code]

 

What's the best way, how to pass parameter with user input to get correct table_name and parameter which is dynamic?

Code doesn't work, where line begins with select max(id), because from clause is not properly made, how can i solve this one?    

 

Best regards, Toms.

  • 1. Re: Passing parameters to get correct table_name
    rp0428 Guru
    Currently Being Moderated
    What's the best way, how to pass parameter with user input to get correct table_name and parameter which is dynamic?

    The first step is to post your question in the proper forum. This forum is for sql developer questions and, as the forum title says, NOT for general SQL or PL/SQL questions.

     

    Please mark the thread ANSWERED and repost it in the SQL and PL/SQL forum.

    https://forums.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql

     

    You need to use dynamic SQL to do what you ask.

     

    But dynamic SQL is often the WRONG solution for whatever it is you are trying to do. You should ALWAYS use SQL to get the job done unless it just can't do it. And since the code sample you posted is useless to begin with we can't tell what it is you really need to do. It just doesn't make sense that you would know the column name to use and that it is a numeric column but you don't know the table name and may want to use a different table name the next time.

     

    Dynamic SQL can have some serious security issues, is never as fast as pure SQL, does not scale and is more difficult to maintain.

     

    Before you in the SQL forum you should review the chapter on using dynamic SQL in the PL/SQL language doc

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm

    7 PL/SQL Dynamic SQL

    Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables.

Legend

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