Home » SQL & PL/SQL » SQL & PL/SQL » Error An INTO Clause (Oracle, Toad, SQL Developer, Windows Server)
Error An INTO Clause [message #654068] |
Tue, 26 July 2016 21:04 |
|
Good day ALL,
Am coming from SQL Server and I use SQL Server Management studio. I don't have issues declare variable and doing a select from a table. But in Oracle different is the case. I ran code below in Toad and SQL Developer
declare
aocode varchar2(40);
miscode varchar2(40);
company varchar2(40);
begin
select excoteamcode, excoaocode, companycode into miscode, aocode, company from mpr_setup where rownum = 1;
SELECT custaccountid, CustNo, a.AccountNo, AccountName, Sector, SubSector, a.teamcode, a.accountofficercode, a.ProductCode, BranchCode, Currency, DateOpened,
MaturityDate, Status, IsDormant, IsJoint, AccountType, SettlementAcct, CompanyCode
, (case a.Teamcode when null then miscode else to_char(a.Teamcode) end ) TeamCodeTemp
,(case a.accountofficercode when null then to_char(aocode) else to_char(a.accountofficercode) end ) AccountOfficerCodeTemp
, ContractRate, Active, Deleted,
CreatedBy, CreatedOn, UpdatedBy, UpdatedOn, RowVersion
FROM mpr_customer_account a;
end;
and an error
Quote:
[Error] Execution (10: 4): ORA-06550: line 10, column 4:
PLS-00428: an INTO clause is expected in this SELECT statement
Kindly help and advice how best I can test my scripts against variables as I enjoyed in SQL Server.
Many thanks.
|
|
|
|
Re: Error An INTO Clause [message #654070 is a reply to message #654069] |
Tue, 26 July 2016 22:01 |
|
I want the result deposited in the data grid.
My undoing is this select excoteamcode, excoaocode, companycode into miscode, aocode, company from mpr_setup where rownum = 1; because I need to declare variables for my Select will make use of. If not I can do normal select and it appears in the data grid.
Thanks.
|
|
|
|
Re: Error An INTO Clause [message #654073 is a reply to message #654072] |
Wed, 27 July 2016 00:11 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I guess that pure SQL (instead of a PL/SQL) might do what you are looking for.
The first SELECT might be used as an inline view (represented by the "x" alias below), and its columns used in the main query (preceded by the "x."). When you execute such a query in TOAD or SQL Developer, you should get the output in the data grid. Have a look:
SELECT a.custaccountid,
a.custno,
a.accountno,
a.accountname,
a.sector,
a.subsector,
a.teamcode,
a.accountofficercode,
a.productcode,
a.branchcode,
a.currency,
a.dateopened,
a.maturitydate,
a.status,
a.isdormant,
a.isjoint,
a.accounttype,
a.settlementacct,
a.companycode,
CASE a.teamcode WHEN NULL THEN x.miscode ELSE TO_CHAR (a.teamcode) END
teamcodetemp,
CASE a.accountofficercode
WHEN NULL THEN TO_CHAR (x.aocode)
ELSE TO_CHAR (a.accountofficercode)
END
accountofficercodetemp,
a.contractrate,
a.active,
a.deleted,
a.createdby,
a.createdon,
a.updatedby,
a.updatedon,
a.rowversion
FROM mpr_customer_account a,
(SELECT s.excoteamcode, s.excoaocode, s.companycode
FROM mpr_setup s
WHERE ROWNUM = 1) x;
Moreover, it appears that you don't even have to select COMPANY from MPR_SETUP table as you don't use its value anywhere in the code.
|
|
|
|
Re: Error An INTO Clause [message #654371 is a reply to message #654068] |
Mon, 01 August 2016 06:53 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
Aside from your immediate question, let me address a point of coding style.
In SQL server the "standard" is to declare objects (table names, column names, variable names, etc) in MixedCase. But in Oracle the default for object names -- as stored in the data dictionary -- is upper case. So your column AccountName (to take an example) is stored in the data dictionary as ACCOUNTNAME. So to get the visual clarity of a mixed case name in a non-mixed-case environemnt, the standard practice in oracle is to use under-score characters. This was done for your table name mpr_customer_account, but likewise your column names should have been defined as 'account_name', 'product_code' etc.
As long as you don't enclose the names in double quotes at the time of object creation,
SQL> create table my_demo (
2 firstname varchar2(20),
3 "FirstName" varchar2(20)
4 )
5 ;
Table created.
SQL> insert into my_demo
2 values ('aaaa','bbbb')
3 ;
1 row created.
SQL> select owner,
2 table_name,
3 column_name,
4 data_type
5 from dba_tab_cols
6 where owner='ESTEVENS'
7 and table_name='MY_DEMO'
8 ;
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE
---------- ---------- --------------- ---------------
ESTEVENS MY_DEMO FIRSTNAME VARCHAR2
ESTEVENS MY_DEMO FirstName VARCHAR2
2 rows selected.
SQL> select owner,
2 table_name,
3 column_name,
4 data_type
5 from dba_tab_cols
6 where owner='ESTEVENS'
7 and table_name='MY_DEMO'
8 and column_name = 'firstname'
9 ;
no rows selected
SQL> select owner,
2 table_name,
3 column_name,
4 data_type
5 from dba_tab_cols
6 where owner='ESTEVENS'
7 and table_name='MY_DEMO'
8 and column_name = 'FIRSTNAME'
9 ;
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE
---------- ---------- --------------- ---------------
ESTEVENS MY_DEMO FIRSTNAME VARCHAR2
1 row selected.
SQL> select * from my_demo
2 ;
FIRSTNAME FirstName
-------------------- --------------------
aaaa bbbb
1 row selected.
SQL> select firstname,
2 FirstName
3 from my_demo
4 ;
FIRSTNAME FIRSTNAME
-------------------- --------------------
aaaa aaaa
1 row selected.
SQL> spo off
Bottom line: Oracle is not MSSQL with a different badge. When in Rome, do as the Romans.
[Updated on: Mon, 01 August 2016 06:55] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:43:46 CDT 2024
|