Re: Variables in SELECT
Date: 24 Feb 95 22:24:55 GMT
Message-ID: <Spencer.H.Moore.12.2F4E5CB7_at_tek.com>
In article <3iktmo$8i1_at_usenet.INS.CWRU.Edu> fn649_at_cleveland.Freenet.Edu (Jeff Houston) writes:
>From: fn649_at_cleveland.Freenet.Edu (Jeff Houston)
>Subject: Variables in SELECT
>Date: 24 Feb 1995 15:24:08 GMT
>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?
I'm doing something very similar right now: I'm dynamically creating the database link for the select on the fly.
You can't do what you tried to do above unless you want to specify the table at compile time (entirely possible if you want to use an anonymous PL/SQL block from the operating system rather than a stored procedure). Nor can you use a PL/SQL variable. What you may want to use is dynamic SQL (type 3?).
Check out the install file for dynamic SQL. On my system (Sun Solaris 2.3 running Oracle 7.1.3) the file is: $ORACLE_HOME/rdbms/admin/dbmssql.sql. The file has its own documentation. If you have done dynamic SQL in Pro*C or something this will be pretty similar.
Spencer Moore Received on Fri Feb 24 1995 - 23:24:55 CET