OraFAQ Forum: SQL & PL/SQL » Using variables in a SQL Statement

Home » SQL & PL/SQL » SQL & PL/SQL » Using variables in a SQL Statement - SELECT ONLY

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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]