Re: Variables in SELECT

James Hedman (t1jamesh_at_tekig7.pen.tek.com) wrote:
: In article <3iktmo$8i1_at_usenet.INS.CWRU.Edu>, fn649_at_cleveland.Freenet.Edu (Jeff Houston) writes:
: >
: > Is it possible to use a parameter input to a procedure to
: > provide a table name for a PL/QSL select statement? The
: > following statement does not substitute for the table name,
: > but prompts the user for input during compilation:
: >
: > SELECT COUNT(*) FROM '&1'
: > WHERE deptno IS NOT NULL
: >
: > Assigning the parameter to a variable, and using the variable
: > in the above statement did not work either. Any thoughts?

: Are you sure that is PL/SQL? It looks like SQL*Plus to me and will work just
: fine WITHOUT the quotes as long as the first parameter is a valid table name.
The problem here is that he wants a stored procedure to use a variable table name. At least, I think that's what he meant. If that's the case, he needs to be using RDBMS version 7.1.3.0.0 with PL/SQL 2.1.3.0.0 (the version with the DBMS_Pare package) to dynamically execute SQL statements with variable table names (or even column names). We had the same probelm and had to upgrade to that version, but it works very well.

: For SQL*Plus try:
 

: select count(*) from &1;
 

: For an anonymous PL/SQL block try:
 

: declare
: result number(9);
: begin
: select count(*) into result from &1;
: end;
: /
 

:

Yes, the previous will work if the block being compiled is not to be a stored procedure.

Jonathan Ingram
jwingram_at_whale.st.usm.edu Received on Mon Mar 06 1995 - 19:43:36 CET