Home » SQL & PL/SQL » SQL & PL/SQL » Using variables in a SQL Statement - SELECT ONLY
Using variables in a SQL Statement - SELECT ONLY [message #271522] |
Mon, 01 October 2007 17:18  |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
I am coding a program to be compatible with SQL syntax using Oracle 9.2 SQL syntax. I build a full SQL statement based on a number of variables passed by the user. For debugging sake, I do not want to mix languages so I am passing the variables to declared variables at the beginning of the statement with SQL Server. I wish to do the same in Oracle. I have attempted to do this, googled the heck out of it, read the 9.2 documentation, I'm afraid I am just out of my league in this Oracle SQL. Here is my latest attempts. This is for a select only, I have tried two different ways.
SQL> edit
Wrote file afiedt.buf
1 DECLARE
2 p_Account varchar2 := '1002-CASH2';
3 p_AcctgPeriod varchar2 := '2004-01-31';
4 p_CompanyCode varchar2 := 'PC1';
5 p_AcctgBasis varchar2 := 'STATC';
6 SELECT D.*, H.*, A.*, CC.*, CO.*, AC.*,
7 CASE WHEN D.DEBIT_CREDIT_IND = 'C' THEN D.CONVERTED_AMOUNT ELSE 0 END As Credit,
8 CASE WHEN D.DEBIT_CREDIT_IND = 'D' THEN D.CONVERTED_AMOUNT ELSE 0 END As Debit
9 FROM (((BSTRN_HEADER H INNER JOIN BSTRN_DETAIL D ON H.TRAN_ID = D.TRAN_ID
10 INNER JOIN ACCT_BAS_ACCT_BAS A ON D.ACCTG_BASIS_CODE = A.REL_ACCT_BAS_CODE)
11 INNER JOIN COCO CC ON D.COMPANY_CODE = CC.ASSOC_COMPANY_CODE
12 INNER JOIN COMPANY CO ON CC.COMPANY_CODE = CO.COMPANY_CODE)
13 INNER JOIN ACCOUNT AC ON D.COMPANY_CODE = AC.COMPANY_CODE AND D.ACCOUNT_NUMBER = AC.ACCT_NUMBER
14 LEFT JOIN BU_SETS BU ON D.BU_SET_ID = BU.BU_SET_ID WHERE D.ACCOUNT_NUMBER = p_Account
15 AND CC.COMPANY_CODE = p_CompanyCode AND A.ACCTG_BASIS_CODE = p_AcctgBasis
16 AND SUBSTR(D.CAL_ACCTG_PERIOD, 1, 4) =
17 CASE WHEN AC.ACCT_CLASS_IND IN ('I', 'X')
18 THEN SUBSTR(p_AcctgPeriod, 1, 4)
19 ELSE SUBSTR(D.CAL_ACCTG_PERIOD, 1, 4) END
20 AND D.CAL_ACCTG_PERIOD < p_AcctgPeriod
21* AND D.STATUS_IND IN ('A','W');
22 /
SELECT D.*, H.*, A.*, CC.*, CO.*, AC.*,
*
ERROR at line 6:
ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
The symbol "begin" was substituted for "SELECT" to continue.
ORA-06550: line 9, column 9:
PLS-00103: Encountered the symbol "BSTRN_HEADER" when expecting one of the
following:
( select
In that example, SQLPlus seems to assume I want to execure PL/SQL; From everything I have read, I do not want to do this. Because then PL/SQL expects a SELECT INTO as in my attempt below:
SQL> edit
Wrote file afiedt.buf
1 DECLARE
2 p_Account varchar2 := '1002-CASH2';
3 p_AcctgPeriod varchar2 := '2004-01-31';
4 p_CompanyCode varchar2 := 'PC1';
5 p_AcctgBasis varchar2 := 'STATC';
6 BEGIN
7 SELECT D.*, H.*, A.*, CC.*, CO.*, AC.*,
8 CASE WHEN D.DEBIT_CREDIT_IND = 'C' THEN D.CONVERTED_AMOUNT ELSE 0 END As Credit,
9 CASE WHEN D.DEBIT_CREDIT_IND = 'D' THEN D.CONVERTED_AMOUNT ELSE 0 END As Debit
10 FROM (((BSTRN_HEADER H INNER JOIN BSTRN_DETAIL D ON H.TRAN_ID = D.TRAN_ID
11 INNER JOIN ACCT_BAS_ACCT_BAS A ON D.ACCTG_BASIS_CODE = A.REL_ACCT_BAS_CODE)
12 INNER JOIN COCO CC ON D.COMPANY_CODE = CC.ASSOC_COMPANY_CODE
13 INNER JOIN COMPANY CO ON CC.COMPANY_CODE = CO.COMPANY_CODE)
14 INNER JOIN ACCOUNT AC ON D.COMPANY_CODE = AC.COMPANY_CODE AND D.ACCOUNT_NUMBER = AC.ACCT_NUMBER
15 LEFT JOIN BU_SETS BU ON D.BU_SET_ID = BU.BU_SET_ID WHERE D.ACCOUNT_NUMBER = p_Account
16 AND CC.COMPANY_CODE = p_CompanyCode AND A.ACCTG_BASIS_CODE = p_AcctgBasis
17 AND SUBSTR(D.CAL_ACCTG_PERIOD, 1, 4) =
18 CASE WHEN AC.ACCT_CLASS_IND IN ('I', 'X')
19 THEN SUBSTR(p_AcctgPeriod, 1, 4)
20 ELSE SUBSTR(D.CAL_ACCTG_PERIOD, 1, 4) END
21 AND D.CAL_ACCTG_PERIOD < p_AcctgPeriod
22 AND D.STATUS_IND IN ('A','W');
23* END;
SQL> /
p_Account varchar2 := '1002-CASH2';
*
ERROR at line 2:
ORA-06550: line 2, column 11:
PLS-00215: String length constraints must be in range (1 .. 32767)
ORA-06550: line 3, column 15:
PLS-00215: String length constraints must be in range (1 .. 32767)
ORA-06550: line 4, column 15:
PLS-00215: String length constraints must be in range (1 .. 32767)
ORA-06550: line 5, column 14:
PLS-00215: String length constraints must be in range (1 .. 32767)
ORA-06550: line 7, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
SQL>
Any help would be appreciated.
If it helps,
My corresponding SQL from SQL Server -
DECLARE
@Account varchar(15),
@AcctgPeriod varchar(10),
@CompanyCode varchar(5),
@AcctgBasis varchar(5)
SET @Account = '1002-CASH2'
SET @AcctgPeriod = '2004-01-31'
SET @CompanyCode = 'PC1'
SET @AcctgBasis = 'STATC'
SELECT D.*, H.*, A.*, CC.*, CO.*, AC.*,
CASE WHEN D.DEBIT_CREDIT_IND = 'C' THEN D.CONVERTED_AMOUNT ELSE 0 END As Credit,
CASE WHEN D.DEBIT_CREDIT_IND = 'D' THEN D.CONVERTED_AMOUNT ELSE 0 END As Debit
FROM (((BSTRN_HEADER H INNER JOIN BSTRN_DETAIL D ON H.TRAN_ID = D.TRAN_ID
INNER JOIN ACCT_BAS_ACCT_BAS A ON D.ACCTG_BASIS_CODE = A.REL_ACCT_BAS_CODE)
INNER JOIN COCO CC ON D.COMPANY_CODE = CC.ASSOC_COMPANY_CODE
INNER JOIN COMPANY CO ON CC.COMPANY_CODE = CO.COMPANY_CODE)
INNER JOIN ACCOUNT AC ON D.COMPANY_CODE = AC.COMPANY_CODE AND D.ACCOUNT_NUMBER = AC.ACCT_NUMBER)
LEFT JOIN BU_SETS BU ON D.BU_SET_ID = BU.BU_SET_ID
WHERE D.ACCOUNT_NUMBER = @Account
AND CC.COMPANY_CODE = @CompanyCode
AND A.ACCTG_BASIS_CODE = @AcctgBasis
AND SUBSTRING(D.CAL_ACCTG_PERIOD, 1, 4) =
CASE WHEN AC.ACCT_CLASS_IND IN ('I', 'X')
THEN SUBSTRING(@AcctgPeriod, 1, 4)
ELSE SUBSTRING(D.CAL_ACCTG_PERIOD, 1, 4) END
AND D.CAL_ACCTG_PERIOD < @AcctgPeriod
AND D.STATUS_IND IN ('A','W')
[Updated on: Tue, 02 October 2007 14:52] Report message to a moderator
|
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271532 is a reply to message #271522] |
Mon, 01 October 2007 22:28   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | In that example, SQLPlus seems to assume I want to execure PL/SQL; From everything I have read, I do not want to do this.
| So do not issue DECLARE, which ONLY starts declarative part of a PL/SQL block.
Even SQL*Plus has its User's Guide and Reference,found at the same place anacedent posted.
Search there for Substitution/Bind variables.
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271560 is a reply to message #271522] |
Tue, 02 October 2007 01:02   |
tarmenel
Messages: 63 Registered: February 2007 Location: Israel
|
Member |
|
|
Try this:
DECLARE
2 p_Account varchar2(30) := '1002-CASH2';
3 p_AcctgPeriod varchar2(30) := '2004-01-31';
4 p_CompanyCode varchar2(30) := 'PC1';
5 p_AcctgBasis varchar2(30) := 'STATC';
6 BEGIN
You almost had it with the second attempt but as you can see in the error message:
String length constraints must be in range (1 .. 32767)
Oracle can be so subtle sometimes and they lay out the answer but its somehow hidden. I've knocked my head many a time looking for the more complicated answer when it was something simple. Good luck.
|
|
|
|
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271733 is a reply to message #271560] |
Tue, 02 October 2007 13:06   |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
Thanks, it resolved the issue of my varchar2 data types. I thought that varchar were of a variant length and that I could declare them without a size in Oracle. But, I still run into the "INTO expected" message as seen below. I'm afraid that PL/SQL seems to require this to be an INTO statement where I would only like to substitute variables. I apologize if this doesn't conform to the coding standards listed in to the FAQ but the line numbers related to the error message are crucial to helping resolve the error.
SQL> edit
Wrote file afiedt.buf
1 DECLARE
2 p_Account varchar2(30) := '1002-CASH2';
3 p_AcctgPeriod varchar2(30) := '2004-01-31';
4 p_CompanyCode varchar2(30) := 'PC1';
5 p_AcctgBasis varchar2(30) := 'STATC';
6 BEGIN
7 SELECT D.*, H.*, A.*, CC.*, CO.*, AC.*,
8 CASE WHEN D.DEBIT_CREDIT_IND = 'C' THEN D.CONVERTED_AMOUNT ELSE 0 END As Credit,
9 CASE WHEN D.DEBIT_CREDIT_IND = 'D' THEN D.CONVERTED_AMOUNT ELSE 0 END As Debit
10 FROM (((BSTRN_HEADER H INNER JOIN BSTRN_DETAIL D ON H.TRAN_ID = D.TRAN_ID
11 INNER JOIN ACCT_BAS_ACCT_BAS A ON D.ACCTG_BASIS_CODE = A.REL_ACCT_BAS_CODE)
12 INNER JOIN COCO CC ON D.COMPANY_CODE = CC.ASSOC_COMPANY_CODE
13 INNER JOIN COMPANY CO ON CC.COMPANY_CODE = CO.COMPANY_CODE)
14 INNER JOIN ACCOUNT AC ON D.COMPANY_CODE = AC.COMPANY_CODE AND D.ACCOUNT_NUMBER = AC.ACCT_NUMBER
15 LEFT JOIN BU_SETS BU ON D.BU_SET_ID = BU.BU_SET_ID WHERE D.ACCOUNT_NUMBER = p_Account
16 AND CC.COMPANY_CODE = p_CompanyCode AND A.ACCTG_BASIS_CODE = p_AcctgBasis
17 AND SUBSTR(D.CAL_ACCTG_PERIOD, 1, 4) =
18 CASE WHEN AC.ACCT_CLASS_IND IN ('I', 'X')
19 THEN SUBSTR(p_AcctgPeriod, 1, 4)
20 ELSE SUBSTR(D.CAL_ACCTG_PERIOD, 1, 4) END
21 AND D.CAL_ACCTG_PERIOD < p_AcctgPeriod
22 AND D.STATUS_IND IN ('A','W');
23* END;
SQL> /
SELECT D.*, H.*, A.*, CC.*, CO.*, AC.*,
*
ERROR at line 7:
ORA-06550: line 7, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
This statement with hard-coded parameters is valid so I know I am just missing something around parameter declaration, or the answer may simply be that this is not possible.
[Updated on: Tue, 02 October 2007 14:51] Report message to a moderator
|
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271737 is a reply to message #271522] |
Tue, 02 October 2007 13:37   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | I'm afraid that PL/SQL seems to require this to be an INTO statement
| This is right, but you also got other answers not using PL/SQL, including Maaher's demonstration.
More commands you will find in SQL*Plus User's Guide and Reference.
By the way is this all you want to do (just display the resultset) or do you want to process the rows somehow? In the second case, Frank's remark describes the way you shall take.
Do not be afraid to study the documentation. There is a lot of useful examples too. Or you want to save some time not reading it and wait longer for (not very as nobody may know what EXACTLY you need) answers instead.
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271740 is a reply to message #271532] |
Tue, 02 October 2007 13:55   |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
Thanks for the link to the SQL Plus guide, i was able to find the section on substitution once I knew what keyword to search for.
Unfortunately, this substitution feature, from what I can tell, is a native feature of SQLPlus and not something I can use to execute within a program connected to an Oracle database via an Oracle ODBC Driver.
I attempted a few of the examples in this section with no good result. I even went so far as to paste the examples straight into SQLPlus. I know this documentation is for SQLPlus 10g, but I couldn't find a link SQLPlus 9.2 - only a download link - could they be that different?
http://209.85.165.104/search?q=cache:SGzxmUy6VWIJ:www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var9.html+substitution+i n+Oracle+SQL&hl=en&ct=clnk&cd=1&gl=us
SQL> edit
Wrote file afiedt.buf
1* define myv = 'King'
SQL> /
define myv = 'King'
*
ERROR at line 1:
ORA-00900: invalid SQL statement
1 define myv = 'King'
2* select employee_id from employees where last_name = '&myv';
SQL> /
Enter value for myv: King
old 2: select employee_id from employees where last_name = '&myv';
new 2: select employee_id from employees where last_name = 'King';
define myv = 'King'
*
ERROR at line 1:
ORA-00900: invalid SQL statement
above was using the example from the substitution
SQL> edit
Wrote file afiedt.buf
1 define p_Account = '1002-CASH2'
2 define p_AcctgPeriod = '2004-01-31'
3 define p_CompanyCode = 'PC1'
4* define p_AcctgBasis = 'STATC'
5 /
define p_Account = '1002-CASH2'
*
ERROR at line 1:
ORA-00900: invalid SQL statement
my attempt at using this exmaple with my variables
SQL> edit
Wrote file afiedt.buf
1* DEFINE p_Account = '1002-CASH2'
SQL> /
DEFINE p_Account = '1002-CASH2'
*
ERROR at line 1:
ORA-00900: invalid SQL statement
another attempt
SQL> DEFINE
DEFINE _CONNECT_IDENTIFIER = "DV01.***.**********.COM" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000100" (CHAR)
DEFINE _RC = "1" (CHAR)
well, at least this worked, but made me realize this might be specific to SQLPlus an not to be used in ODBC Oracle driver.
My program requirements are that this SQL needs to be executable through the Oracle ODBC Driver and the variables must be declared before the SELECT statement. This SELECT is not a SELECT INTO. Thanks again for your help so far. I appreciate any suggestions.
[Updated on: Tue, 02 October 2007 14:51] Report message to a moderator
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271743 is a reply to message #271737] |
Tue, 02 October 2007 14:06   |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
It takes me awhile to craft these posts, I was in the middle of posting my update to Maher's suggestion as well. I can't comment on my lack of adherence to the posting guidelines other than I followed the "How to get a quick answer to your question?" guidelines before I posted the first time.
Before you decide to open a new topic, you should ask yourself:
First of all: did I try myself? Or am I just hoping that someone else is willing to do my work?
Did I read the documentation? A lot of questions are answered there. Really.
Have I checked the FAQ page?
Did I search the board properly? Have I also tried the Site Search Engine? A large group of questions posted here are just a déjà -répondu of the past.
Did I use Google? Even if your question hasn't been answered here, chances are that on other websites someone already has posted an answer.
Is my question specific to the Oracle database? Now, that may seem obvious since this site is called Oracle FAQs, but we get quite a few questions about Access and MS SQL Server here - and SQL between databases is not always compatible - so please ask any non-Oracle questions elsewhere. It will be to your advantage.
Ok, so you didn't find an answer yet. No sweat, you're welcome to post it here and a lot of people are happy to answer. They can, however, answer only if enough info is provided.
As far as using the
I didn't see any difference in using
--Begin SQLPlus Paste
--End SQLPlus Paste
but I will happily comply.
As far as the line numbers and indentation,
How would have anyone known that what error messages was related to which line if I had formatted the SQLPlus output? I am merely trying "provide enough info."
Look, I'm not trying to upset anyone. I have adhered to the rules as best I could in trying to accomplish my objective, which is determine how to substitue parameters effectvely in Oracle SQL.
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271748 is a reply to message #271737] |
Tue, 02 October 2007 14:31   |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
I have been looking over documents for about approx 12 hours since Monday morning. This seems like such a small syntax problem but I feel like I am getting further from the solution when I read over sections under substitution like:
"9.16 Customizing Parameter Prompts for an iSQL*Plus Dynamic Report for the Web"
I only want to display the resultset. And I answered my own questions about the doc version for SQLPlus, 10G doc applies to all previous versions according to the site, which is hard to believe.
Thanks for your help.
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271751 is a reply to message #271743] |
Tue, 02 October 2007 15:02   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
jblaydes wrote on Tue, 02 October 2007 15:06 |
As far as using the
I didn't see any difference in using
--Begin SQLPlus Paste
--End SQLPlus Paste
but I will happily comply.
|
Not sure what you are saying here as [code] & [/code] will format code, yet I have no idea what --Begin SQLPlus Paste and --End SQLPlus Paste are. And you should be able to tell from your own post (this one I quoted) that there is a difference.
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271752 is a reply to message #271751] |
Tue, 02 October 2007 15:30   |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
I went back and added the to all my previous posts. It still doesn't indent it correctly if you keep the line numbers and I was just pointing out that it would be hard to understand where the error line occurs if you didn't have the line numbers on the left, which breaks the html code tag.
I apologize for any misunderstanding around following forum guidelines, but I stand by my decision to include the line numbers. It already helped resolve the issue of varchar declarations.
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271753 is a reply to message #271522] |
Tue, 02 October 2007 15:39   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | but I couldn't find a link SQLPlus 9.2
|
You were given link to http://tahiti.oracle.com/, where is available documentation for all major versions since 8.1.7.SQL> edit
Wrote file afiedt.buf
1* define myv = 'King'
SQL> /
define myv = 'King'
*
ERROR at line 1:
ORA-00900: invalid SQL statement
Instead of playing with command buffer, could you issue the DEFINE command directly? Maybe you should look at / command into documentation before using it: Quote: | /(slash)
Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.
The buffer has no command history and does not record SQL*Plus commands.
| By the way, there is also VARIABLE command for declaring bind variables.
Quote: | My program requirements are that this SQL needs to be executable through the Oracle ODBC Driver and the variables must be declared before the SELECT statement. This SELECT is not a SELECT INTO.
|
It would be nice if you would stated it in your first post. As I do not know ODBC (just saw JDBC from very far, but I suppose it is similar), I just suppose statement issuing consists from 3 phases:
- prepare statement with parameters replaced with ? (question marks)
- bind variables by parameters
- execute statement
You may use the SELECT directly or create stored procedure/package returning cursor.
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271760 is a reply to message #271753] |
Tue, 02 October 2007 16:41   |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
Thanks, I have like 50 windows open so I thought that was a different link. I found the SQL Plus reference guide for 9.2 and the SQL reference and every othe PDF with the word "SQL".
but the text on this site says that "This document updated in March 2004 for SQL*Plus Version 10.1. However all examples in this document apply to previous versions of SQL*Plus, except where noted."
Quote: | Instead of playing with command buffer, could you issue the DEFINE command directly?
|
What do you mean by directly? You mean without saving it in notepad first? How do you execute SQL in SQLPlus without using the slash? Does the DEFINE command work outside of SQLPlus or is this a feature specific to SQLPlus?
I didn't realize Oracle SQL had features/differences between ODBC and SQLPlus. In my experience, if it executes in SQLPlus, most times it will execute using the Oracle ODBC driver.
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271761 is a reply to message #271522] |
Tue, 02 October 2007 17:05   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Yes, you may issue last SQL or PL/SQL statement from the buffer by /(slash). You may edit the buffer as you did.
However DEFINE is SQL*Plus statement, so it is NOT possible to invoke it this way. You issued DEFINE without parameters in the end. So issue it the same way with parameters (directly from SQL*Plus prompt). But it will NOT be the part of the buffer.
If you want to have the DEFINE commands with SELECT statement together, you may save it to ANY file (script) accessible by SQL*Plus. Then run the script by issuing START (or @) SQL*Plus command.
There should be no difference in issuing the command WITHOUT binds. As I said I do not have practice in this area, however you SHALL use binding wherever possible (at least it is much more performant).
I found some VB coding example here, maybe it will be helpful.
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #271767 is a reply to message #271761] |
Tue, 02 October 2007 18:35   |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
That code sample is very interesting. I was able to compile his sample but only after altering it several times. It's a little cleaner than how I am binding parameters now, but at first look, it WORKS! It is very similiar to what I have already coded but using a different approach within ADO, save for the parameter object.
I have been able to execute the Oracle SQL with the statements 'hard-bound', but was having trouble (as you can gather from my posts) executing the Oracle SQL statement with declared variables.
I just never imagined anyone would be attempting to use Visual Basic 6 and Excel to bind variables through the Oracle ODBC driver using a native ADO object, or that providing this information would be useful for an Oracle syntax problem. Using the keywords 'bind variables' instead of 'substitutions' in association with 'Oracle SQL' has also turned up some interesting reads that may help.
I'll let you know what my final solution is, but this has put me right back on track.
This is how I altered his code from the link
Function ParameterExample()
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm As ADODB.Parameter
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=DV01.******.**********.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 = :acct_no"
cmd.CommandType = adCmdText
' Set up a new parameter for the stored procedure.
Set prm = cmd.CreateParameter("acct_no", adVarChar, adParamInput, 200, "1001-CASH1")
cmd.Parameters.Append prm
' 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
|
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #272049 is a reply to message #271836] |
Wed, 03 October 2007 15:16   |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
Thanks
It helps being able to verify my SQL before putting into code. That way I know if I have a VB code issue or a Oracle SQL syntax issue.
But, It doesn't look like the ADO.Parameters object using the VB code sample is going to bind the parameters in the right way. At first glance, I thought the OracleOLEDB object was matching the named parameters from the ADO.Parameter object (i.e. ":acct_no" to declared ADO.parameter "acct_no") but the replacement is simply ordinal. This is the same for for SQLOLEDB. Ordinal replacement won't work for me as my parameters are used in the JOIN clause sometimes and reused the WHERE clause. So I am back to square one.
There may be another option, but again, I have confirm if this is another ordinal replacement method. The FAQ is on this very site: How does one use bind variables in OO4O?
I do appreciate your help up this point!
By the way, I found some pretty good articles on bind variables here:
Bind Variables - The Key to Performance
Additional article on using Bind Variables
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #272360 is a reply to message #271592] |
Thu, 04 October 2007 13:09   |
jblaydes
Messages: 15 Registered: October 2002
|
Junior Member |
|
|
I Maaher's applied example below, it executes but gives an error. I tried both with VARCHAR datatype and VARCHAR(30) datatype and got the same error, but it still gave me a good result.
ACCEPT acctno VARCHAR PROMPT 'acctno?'
SELECT ACCT_NUMBER
FROM ACCOUNT
WHERE ACCT_NUMBER = &acctno
/
Result
SQL> @c:\orasql\orafaq.sql
SP2-0003: Ill-formed ACCEPT command starting as VARCHAR PROMPT 'acctno?'
Enter value for acctno: '1001-CASH1'
old 3: WHERE ACCT_NUMBER = &acctno
new 3: WHERE ACCT_NUMBER = '1001-CASH1'
ACCT_NUMBER
---------------
1001-CASH1
1001-CASH1
1001-CASH1
1001-CASH1
1001-CASH1
1001-CASH1
1001-CASH1
1001-CASH1
1001-CASH1
1001-CASH1
10 rows selected.
Any way to do this and pass the parameter value without prompting? Would this work outside of SQL Plus?
the way i would do it in SQL Server
DECLARE @Account varchar(15)
SET @Account = '1001-CASH1'
SELECT ACCT_NUMBER from ACCOUNT where ACCT_NUMBER = @Account
I have to believe there is a way to do this in Oracle SQL. It just doesn't seem to be in the cards that you simply can't do it.
[Updated on: Thu, 04 October 2007 13:10] Report message to a moderator
|
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #272406 is a reply to message #271522] |
Thu, 04 October 2007 23:17   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
I am not sure with quotes ('") syntax in Maaher's post.
However, as substitution variables are treated by SQL*Plus commands (ACCEPT or DEFINE), it is quite unlikely that it will work outside SQL*Plus.
I slightly remember JDBC, the parameters are bound there by its position too. Not aware about name matching there.
If I understand you correctly, your problem lies in varying bound parameters when creating SELECT statement in your application. I do not see problem in repeating the parameters as you may bind the same parameter twice (not sure about bind names in SELECT statement).
However, if you are not satisfied with it, you may let the DB developer move the logic into stored PL/SQL FUNCTION (recommended to include it into PACKAGE) returning CURSOR and call this packaged FUNCTION in application.
If you haven't done it yet, you should make a post into some VB forum, as I do not know many DB developers familiar with ODBC/VB (in fact I do not know any).
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #272444 is a reply to message #271522] |
Fri, 05 October 2007 01:34   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Just to adjust my previous post:
Frank has really bigger experience in application than me as he stated:
Quote: | If I am not mistaken, the original poster might be looking for a pl/sql solution, without realising it.
The code he describes in SQLServer is comparable to a ref cursor in Oracle, something to be used in and called from code.
|
If you would be DB, you would rewrite your code to:
DECLARE
p_Account varchar2(30) := :1;
p_AcctgPeriod varchar2(30) := :2;
p_CompanyCode varchar2(30) := :3;
p_AcctgBasis varchar2(30) := :4;
BEGIN
OPEN :c FOR
SELECT <the statement you issued with the usage of local variables>;
END;
/
In fact the stored function would not be very different (just other syntax). You still do not bind by name, however you bind each variable just once.
|
|
|
|
Re: Using variables in a SQL Statement - SELECT ONLY [message #272474 is a reply to message #272447] |
Fri, 05 October 2007 03:24   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
One point that I don't think has been mentioned, but the OP needs to know:
In a T-SQL procedure/block (PL/SQL equivalent on SQL Server), when you run a SELECT statement, the rows of the SELECT are taken as outputs of the procedure and can be used in a client-side program (like VB) as a record set. This does NOT work in PL/SQL.
You have two options with SELECT statements in PL/SQL: INTO and CURSORS.
A bare SELECT must be accompanied by an INTO, selecting into either scalars or a PL/SQL collection. It is difficult (or perhaps impossible?) to pass such a collection back to a calling client like VB.
Alternatively, you can embed the SELECT in a CURSOR. If you do this, you may either consume the rows in PL/SQL using the FETCH command (much like a SELECT INTO), or you can pass a pointer to the cursor (called a REF CURSOR) back to the calling client which can then consume the rows.
Hope that helps.
Ross Leishman
|
|
|
|
|
Goto Forum:
Current Time: Thu Feb 13 08:48:36 CST 2025
|