Home » SQL & PL/SQL » SQL & PL/SQL » Using variables in a SELECT statement - Special Offer!
Using variables in a SELECT statement - Special Offer! [message #272381] Thu, 04 October 2007 15:58 Go to next message
jblaydes
Messages: 15
Registered: October 2002
Junior Member
This post is a continuation of
“Using variables in a SQL Statement - SELECT ONLY [message #271522]” from the
“SQL & PL/SQL for Newbie’s” forum

I am continuing my search for a way to create an Oracle v9.2 SQL statement that fulfills the following conditions:

1. Declare a named variable and its data type in the top of a SELECT statement.
2. Pass the value to a named variable later in the same statement without prompting before the execution of the SELECT statement.
3. Execute the SELECT statement with the substituted variables. The SELECT statement cannot contain an INSERT or UPDATE.
4. The parameter substitution method cannot be ordinal.
5. This entire statement can be executed in one command using VB6 implementing ADO against the OraOLEDB Provider.
6. The statement must be compiled at runtime, meaning it cannot be pre-compiled as a stored procedure on the server side. I don't really mind if it's PL/SQL or just SQL solution, but this restriction may rule out using PL/SQL.

Almost all basic Oracle SQL Syntax works using the OraOLEDB provider, but I have reviewed the 1,668 page SQL Reference in PDF format provided with the v9.2 documentation and found no example or method of implementing this type of variable substitution in Oracle SQL.

This is an example of the equivalent SQLServer SQL Statement:

DECLARE @Account varchar(15)
SET @Account = '1001-CASH1'
SELECT ACCT_NUMBER from ACCOUNT where ACCT_NUMBER = @Account


I execute the SQLServer SQL Statement in the following manner within VB6:

Function ParameterExampleSQLServerDeclare(strVars As String)
   Dim cmd As New ADODB.Command
   Dim rs As New ADODB.Recordset
   
   'SQL Strings
   Dim strSetVars As String
   Dim strDeclare As String
   Dim strBaseSQL As String
   
   'variable declaration
   strDeclare = "DECLARE @Account varchar(15) "
   
   'set variables
   strSetVars = "SET @Account ='" & strVars & "' "
   
   'set Base SELECT
   strBaseSQL = "Select ACCT_NUMBER from ACCOUNT  where ACCOUNT.ACCT_NUMBER = @Account"
   
   Set cnnConn = New ADODB.Connection
         With cnnConn
           .ConnectionString = "Provider=SQLOLEDB;Data Source=*******;Initial Catalog=*******;User Id=******;Password=******"
           .Open
         End With
         
   ' Set the command's connection using a connection string.
   cmd.ActiveConnection = cnnConn
   ' Set the command's text, and specify that it
   ' is an SQL statement.
   
   cmd.CommandText = strDeclare & strSetVars & strBaseSQL
   cmd.CommandType = adCmdText

   ' Create a recordset by executing the command.
   Set rs = cmd.Execute
   
   ' Loop through the recordset and print the first field.
   Do While Not rs.EOF
      Debug.Print rs(0)
      rs.MoveNext
   Loop
   
   ' Close the recordset.
   rs.Close

End Function



If you are saavy in VB, you might say, well why don't you just put the variable here (see example below) instead and forget all of the DECLARE and SET statements in the SQL.

   strBaseSQL = "Select ACCT_NUMBER from ACCOUNT  where ACCOUNT.ACCT_NUMBER = '" & strVars & "'


While this is a very basic example, some of the SQL contains up to 30 variables and a variable replacement of the last example method makes debugging the application very, very difficult, especially when some of these variables are used in the JOIN and the WHERE clause so you could potentially have 60 string replacements to manage all over one SQL statement. Using the declaration method, I can ensure a level of control and automation over the variable replacement section of the code. There are 2,784 unique SQL statements that pass through this section of code similar to this basic example.

To make this a little more fun and interesting, I will offer a 35 US$ gift certificate on Amazon.com to the first person who can come up with a working Oracle SQL example for this problem. Once I have confirmed your Oracle SQL example to work within the code example and it follows the requirements, I will send the gift certificate to the e-mail address of your choice. If 35 US$ dollars isn't enough money, there's always bragging rights and knowing that you helped someone!

This offer expires Friday October 5, 2007 @ 8:00am CDT (Central Daylight Time)

[Updated on: Thu, 04 October 2007 16:19]

Report message to a moderator

Re: Using variables in a SELECT statement - Special Offer! [message #272404 is a reply to message #272381] Thu, 04 October 2007 23:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't know VB, so I can't give you the full VB code. But I can give you the PL/SQL code and some pointers on how to do it in VB.

First, create this package on your database:
CREATE OR REPLACE PACKAGE vars AS

        SUBTYPE identifier IS VARCHAR2(30);

        TYPE vchashtype  IS TABLE OF VARCHAR2(2000) INDEX BY IDENTIFIER;
        TYPE numhashtype IS TABLE OF NUMBER INDEX BY IDENTIFIER;
        TYPE dthashtype IS TABLE OF DATE INDEX BY IDENTIFIER;

        vchash  VCHASHTYPE;
        numhash NUMHASHTYPE;
        dthash  DTHASHTYPE;

        ------------------------------------------------
        -- SET(): Overloaded procedure
        --
        -- Loads a value into an associative array.
        --
        -- Different versions of the procedure exist for each possible data type
        ------------------------------------------------
        PROCEDURE set (
                nam     IDENTIFIER
        ,       val     VARCHAR2
        );

        PROCEDURE set (
                nam     IDENTIFIER
        ,       val     NUMBER
        );

        PROCEDURE set (
                nam     IDENTIFIER
        ,       val     DATE
        );


        FUNCTION getvc (
                nam     IDENTIFIER
        ) RETURN VARCHAR2;

        FUNCTION getnum (
                nam     IDENTIFIER
        ) RETURN NUMBER;

        FUNCTION getdt (
                nam     IDENTIFIER
        ) RETURN DATE;

END vars;
/

And now create this package body:
CREATE OR REPLACE PACKAGE BODY vars AS

        PROCEDURE set (
                nam     IDENTIFIER
        ,       val     VARCHAR2
        ) IS
        BEGIN
                vchash(nam) := val;
        END set;


        PROCEDURE set (
                nam     IDENTIFIER
        ,       val     NUMBER
        ) IS
        BEGIN
                numhash(nam) := val;
        END set;

        PROCEDURE set (
                nam     IDENTIFIER
        ,       val     DATE
        ) IS
        BEGIN
                dthash(nam) := val;
        END set;


        FUNCTION getvc (
                nam     IDENTIFIER
        ) RETURN VARCHAR2 IS
        BEGIN
                RETURN(vchash(nam));
        END;

        FUNCTION getnum (
                nam     IDENTIFIER
        ) RETURN NUMBER IS
        BEGIN
                RETURN(numhash(nam));
        END;

        FUNCTION getdt (
                nam     IDENTIFIER
        ) RETURN DATE IS
        BEGIN
                RETURN(dthash(nam));
        END;
END vars;
/


When you call VARS.SET, it saves the variable you supply (a VARCHAR2, NUMBER, or DATE) in a persistent hash using the name you supply.

When you call GETVC, it retrieves the saved VARCHAR2 variable using the name you supply. GETNUM and GETDT are equivalents to return numbers and dates. It was not possible to overload these, hence the separate function names.

How to use it:

Here is an example in SQL*Plus:
EXEC vars.set('TABLE_NAME', 'ALL_TABLES');
EXEC vars.set('COLUMN_ID', 15);


SELECT  object_name
FROM    dba_objects
WHERE   object_name = vars.getvc('TABLE_NAME')
/

SELECT  table_name, column_name
FROM    dba_tab_columns
WHERE   table_name = vars.getvc('TABLE_NAME')
AND     column_id = vars.getnum('COLUMN_ID')
/

with the results:
PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


OBJECT_NAME
--------------------------------------------------------------------------------
ALL_TABLES
ALL_TABLES


TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
ALL_TABLES                     PCT_INCREASE


In VB, you will want to return record sets. They do not work the same way as SQL Server. Oracle uses REF CURSORS. If you search on the web you will find examples of how to return a REF CURSOR as a record set.

Here is a REF CURSOR example in SQL*Plus once again:
VAR rc1 REFCURSOR
VAR rc2 REFCURSOR

BEGIN
        vars.set('TABLE_NAME', 'ALL_TABLES');
        vars.set('COLUMN_ID', 15);


        OPEN :rc1 FOR
        SELECT  object_name
        FROM    dba_objects
        WHERE   object_name = vars.getvc('TABLE_NAME');

        OPEN :rc2 FOR
        SELECT  table_name, column_name
        FROM    dba_tab_columns
        WHERE   table_name = vars.getvc('TABLE_NAME')
        AND     column_id = vars.getnum('COLUMN_ID');

END;
/

print :rc1
print :rc2

and the results:
PL/SQL procedure successfully completed.


OBJECT_NAME
--------------------------------------------------------------------------------
ALL_TABLES
ALL_TABLES


TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
ALL_TABLES                     PCT_INCREASE


For your purposes, you would probably make a separate call to initialise each variable, and then a final call to execute the SELECT:

Something like this (I'm guessing the syntax - I really have no idea - just domonstrating that you can probably avoid using a PL/SQL block).
cmd.CommandText = "call vars.set('TABLE_NAME', & myvc & ')'
cmd.CommandType = adCmdText
cmd.Execute

cmd.CommandText = "call vars.set('COLUMN_ID', & mynum & ')'
cmd.CommandType = adCmdText
cmd.Execute

cmd.CommandText = "SELECT ....vars.getvc('TABLE_NAME') ...."
cmd.CommandType = adCmdText
set rs = cmd.Execute



This is a pretty simple implementation. The trick is to use persistent associative arrays to store variables and functions to retrieve them in SQL code. You can make it more complex by adding a DECLARE function as specified in your requirement, but I don't think it will give you anything new.

I can't think of a neat way to overload the GET function to avoid the three separate function names. You could probably use object types to make the syntax look funkier (eg. VARS.GET('NAME').VC or something similar).

Ross Leishman

[Updated on: Thu, 04 October 2007 23:26]

Report message to a moderator

Re: Using variables in a SELECT statement - Special Offer! [message #272570 is a reply to message #272404] Fri, 05 October 2007 10:08 Go to previous messageGo to next message
jblaydes
Messages: 15
Registered: October 2002
Junior Member
I appreciate the suggestion, but unfortunately my requirements do not allow me to be maintain and server-side code.
Re: Using variables in a SELECT statement - Special Offer! [message #272575 is a reply to message #272381] Fri, 05 October 2007 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
There is straight forward but inefficient solution.
Create/compose the SQL statements as text strings in VB & the use EXECUTE IMMEDIATE to invoke them.
Re: Using variables in a SELECT statement - Special Offer! [message #272579 is a reply to message #272404] Fri, 05 October 2007 10:31 Go to previous messageGo to next message
jblaydes
Messages: 15
Registered: October 2002
Junior Member
Quote:
In VB, you will want to return record sets. They do not work the same way as SQL Server. Oracle uses REF CURSORS. If you search on the web you will find examples of how to return a REF CURSOR as a record set.


I can return a valid recordset from Oracle using the approach i described in the earlier post, I just would just rather use one method for both back-end databases.

Working example of returning an Oracle recordset in VB 6

Function ParameterExampleOracleCurrentMethod(strAccount As String)
   Dim cmd As New ADODB.Command
   Dim rs As New ADODB.Recordset
   
   Set cnnConn = New ADODB.Connection
         With cnnConn
           .ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=**********.COM;User Id=*****;Password=*****"
           .Open
         End With
         
   ' Set the command's connection using a connection string.
   cmd.ActiveConnection = cnnConn
   ' Set the command's text, and specify that it
   ' is an SQL statement.
   cmd.CommandText = "Select ACCT_NUMBER from ACCOUNT  where ACCOUNT.ACCT_NUMBER = '" & strAccount & "'"
   cmd.CommandType = adCmdText


   ' Create a recordset by executing the command.
   Set rs = cmd.Execute
   
   ' Loop through the recordset and print the first field.
   Do While Not rs.EOF
      Debug.Print rs(0)
      rs.MoveNext
   Loop
   
   ' Close the recordset.
   rs.Close

End Function



The only real difference between the SQL Server example and the Oracle example is where I append the variables using string replacement. Because of the size of my SQL statements, the number of variables, and restriction of not being to use server-side objects, It looks as though I will just have to use the current method "ParameterExampleOracleCurrentMethod" and abandon my new approach "Function ParameterExampleSQLServerDeclare" altogether as I don't want to maintain to different sets of code for each database type.

Thanks for the suggestions, but I may have been asking for something that simply can't be done.

[Updated on: Fri, 05 October 2007 10:31]

Report message to a moderator

Re: Using variables in a SELECT statement - Special Offer! [message #272659 is a reply to message #272579] Fri, 05 October 2007 18:50 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The fundamental difference here is that SQL Server allows you to package up multiple SQL statements into a single execution (T-SQL I think), returning a recordset from the last SQL executed. Oracle does not.

Oracle allows a single statement to be executed. It may be either a SQL Statement (which may be returned as a recordset) or it may be a PL/SQL statement (which may return NOTHING except bind variables).

So, if you choose to execute a SQL statement, it must stand on its own. It may reference anything already on the database (like the persistent hash in my previous example) and it may reference bind variables. You have already ruled creating any storage structure on the server, so that leaves you with bind variables. Bind variables can be referenced by name in most Oracle client interfaces (I suspect VB is no exception), but they must be explicitly bound in the client. You want to paste the variable settings at the top of the command text, so that rules out bind variables.

Unless I'm missing something, we've exhausted the SQL statement option. Now we're left with PL/SQL. Again, there are two options: call a stored procedure or an anonymous (BEGIN/END) block. You don't want to create server-side permanent code, so stored proc is ruled out.

When you execute PL/SQL, you cannot return a recordset in the same manner as a SQL statement; you MUST return a REF CURSOR. Can VB call an anonymous block and return a REF CURSOR? I don't know. I know you can call a stored proc and return a REF CURSOR, and this page describes how to execute an anonymous block.

The block that you construct in your command string would look something like this:

DECLARE
    param_account VARCHAR2(10) := '12345';
BEGIN
    OPEN :rc FOR
    SELECT col1, col2, col3
    FROM   the_table
    WHERE  account_no = param_account;
END;


So you would roll this PL/SQL block into a string, bind :RC (for output) to an appropriate structure in VB, and then execute the string.

Hope that helps.

Ross Leishman
Previous Topic: value returned from a function
Next Topic: Using variables in a SQL Statement - SELECT ONLY
Goto Forum:
  


Current Time: Thu Dec 08 02:23:02 CST 2016

Total time taken to generate the page: 0.12389 seconds