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 Go to next message
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 #271524 is a reply to message #271522] Mon, 01 October 2007 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow coding guidelines as stated in the #1 STICKY post as found at the top of this forum.

Both the SQL & the PL/SQL Reference Guides can be found at http://tahiti.oracle.com.

If you can't construct statements with valid syntax, you'll never be able to produce a functioning program.

Re: Using variables in a SQL Statement - SELECT ONLY [message #271532 is a reply to message #271522] Mon, 01 October 2007 22:28 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #271565 is a reply to message #271560] Tue, 02 October 2007 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've knocked my head many a time looking for the more complicated answer when it was something simple

And you still do.
OP seems to ask for non PL/SQL solution and you provided a PL/SQL solution.
There is no need of PL/SQL. Keep it simple.

Regards
Michel
Re: Using variables in a SQL Statement - SELECT ONLY [message #271581 is a reply to message #271522] Tue, 02 October 2007 02:06 Go to previous messageGo to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Ahh! Well I'm sure it won't be the last time Razz

Is there then not a way to use the '&' symbol to pass variables?

AND D.CAL_ACCTG_PERIOD < &p_AcctgPeriod

[Updated on: Tue, 02 October 2007 02:08]

Report message to a moderator

Re: Using variables in a SQL Statement - SELECT ONLY [message #271592 is a reply to message #271581] Tue, 02 October 2007 02:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You're right. You are looking for a substitution variable. It works a little bit like demonstrated below. For more information, look here.

My orafaq.sql script:
ACCEPT empno NUMBER PROMPT 'employee? '

SELECT employee_id  
     , last_name
FROM   employees
WHERE  employee_id = &empno
/


When I run it, it show this:
SQL> @orafaq
employee? 101
old   4: WHERE  employee_id = &empno
new   4: WHERE  employee_id =        101

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        101 Kochhar
MHE
Re: Using variables in a SQL Statement - SELECT ONLY [message #271597 is a reply to message #271592] Tue, 02 October 2007 02:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
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.
Re: Using variables in a SQL Statement - SELECT ONLY [message #271733 is a reply to message #271560] Tue, 02 October 2007 13:06 Go to previous messageGo to next message
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 #271736 is a reply to message #271733] Tue, 02 October 2007 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you refuse to follow the forum rules, do you expect we still help you?

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Using variables in a SQL Statement - SELECT ONLY [message #271737 is a reply to message #271522] Tue, 02 October 2007 13:37 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #271836 is a reply to message #271522] Wed, 03 October 2007 02:05 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Thank you for feedback.

If semicolon (:) is used for binds, you may run the query in SQL*Plus after directly (from the prompt) declaring bind variables by SQL*Plus VARIABLE command (you can do it only once).
Re: Using variables in a SQL Statement - SELECT ONLY [message #272049 is a reply to message #271836] Wed, 03 October 2007 15:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #272382 is a reply to message #272360] Thu, 04 October 2007 15:59 Go to previous messageGo to next message
jblaydes
Messages: 15
Registered: October 2002
Junior Member
I have added a continuation to this orginal post to the SQL Experts forum with a special offer attached - please see here
Re: Using variables in a SQL Statement - SELECT ONLY [message #272406 is a reply to message #271522] Thu, 04 October 2007 23:17 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #272447 is a reply to message #272406] Fri, 05 October 2007 01:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The quotes are not necessary if your prompt is a single word. Otherwise use (single or double) quotes.

But have a look at this:
SQL> help accept

 ACCEPT
 ------

 Reads a line of input and stores it in a given substitution variable.
 In iSQL*Plus, displays the Input Required screen for you to enter a
 value for the substitution variable.

 ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
 [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
MHE
Re: Using variables in a SQL Statement - SELECT ONLY [message #272474 is a reply to message #272447] Fri, 05 October 2007 03:24 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Using variables in a SQL Statement - SELECT ONLY [message #272477 is a reply to message #272474] Fri, 05 October 2007 03:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I did mention it, albeit a lot more cryptic than your clear explanation.
Re: Using variables in a SQL Statement - SELECT ONLY [message #272660 is a reply to message #272477] Fri, 05 October 2007 18:54 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's what I get for skimming Wink Apols Frank
Previous Topic: Using variables in a SELECT statement - Special Offer!
Next Topic: Not a single-group function
Goto Forum:
  


Current Time: Fri Dec 02 22:57:54 CST 2016

Total time taken to generate the page: 0.12572 seconds