Re: *HELP* DYNAMIC SQL in PL/SQL, IS POSSIBLE ???

In article <3st29u$3ve_at_masala.cc.uh.edu>, victor_at_hermes.cs.uh.edu ( Victor Rajendra Kumar Addepalli ) writes: ...
> I am trying to do DYNAMIC SQL in PL/SQL and having trouble.
> I tried to pass table name string to the SQL statement in
> the PL/SQL block or procedure and it does not work.
 ...
> My PL/SQL block given below works:
> ---------------------------------
> declare
> my_column varchar2(25);
> my_value varchar2(25);
> begin
> my_column := 'foo';
>
> select decode(my_column,'foo',foo) into my_value
> from foobar;
> end;

> But I tried to bind a table name in the PL/SQL below,
> I get error messages.
> ---------------------------------
> variable my_table varchar2(25);
> declare
> my_value varchar2(25);
> begin
> :my_table := 'foobar';
>
> select foo into my_value
> from :my_table;
> end;
> /
>

This is nothing to do with PL/SQL accepting column names and not table names! That would only be the case if you had tried something like   select :my_column into my_value
  from foobar;
and it had worked!! (Which it would, but not in the way you wanted!!)

Two possibilities I know of are :

1. If your requirement is for the simplest of programs running under SQL*PLUS, try

DEFINE my_table = FOOBAR
DECLARE
  my_value VARCHAR2(25);
BEGIN
  SELECT foo INTO my_value
  FROM &my_table;
end;

2) If not, you should use the dynamic SQL DBMS_SQL package :

SET SERVEROUT ON
DECLARE
    cur INTEGER;
    ret INTEGER;

    my_value varchar2(25); -- for the result
    my_table varchar2(30);                  
    my_col   varchar2(30);

    /* Start the dynamic SQL process */
    cur := dbms_sql.open_cursor;

    /* Ask Oracle to parse your statement, leaving out the INTO clause */     dbms_sql.parse(cur, 'SELECT ' || my_col || ' from ' || my_table,

                   dbms_sql.v7);

    /* Tell the package which column number you are going to want (1)   */
    /* and where it will go (my_value) and its maximum size if varchar2 */
    /* This sets up the INTO part of your statement                     */

    dbms_sql.define_column(cur, 1, my_value, 25);

    /* Execute the request and get one result line /     ret := dbms_sql.execute_and_fetch (cur);

    /* Get the column into my_value */
    /* This executes the INTO clause of your original statement */     dbms_sql.column_value(cur, 1, my_value);

    /* The SQL is finished with so clean up */     dbms_sql.close_cursor(cur);

    /* Use the result (eg. print it) */
    dbms_output.put_line(my_value);
END; See DBMS_SQL documentation for more information. This requires Oracle 7.1.

My_table and My_col can be bind variables, but can't see why you'd want to...

Cheers

Simon MacLoughlin                     Received on Mon Jul 03 1995 - 00:00:00 CEST