Home » SQL & PL/SQL » SQL & PL/SQL » Error An INTO Clause (Oracle, Toad, SQL Developer, Windows Server)
icon5.gif  Error An INTO Clause [message #654068] Tue, 26 July 2016 21:04 Go to next message
abacusdotcom
Messages: 15
Registered: June 2010
Location: Lagos
Junior Member

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 #654069 is a reply to message #654068] Tue, 26 July 2016 21:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
to where should the results of the SELECT .... FROM MPR_CUSTOMER_ACCOUNT be deposited?
You have included "INTO" in the first SELECT & is required for every SELECT in PL/SQL procedure.
Re: Error An INTO Clause [message #654070 is a reply to message #654069] Tue, 26 July 2016 22:01 Go to previous messageGo to next message
abacusdotcom
Messages: 15
Registered: June 2010
Location: Lagos
Junior Member



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 #654072 is a reply to message #654070] Tue, 26 July 2016 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
abacusdotcom wrote on Tue, 26 July 2016 20: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.


I do not know what you mean by "data grid" & neither does Oracle therefore error gets thrown.
Within PL/SQL procedures, the SELECTED values must go INTO somewhere.

When SELECT statement exists within PL/SQL procedure, only 1 row can be returned.
Re: Error An INTO Clause [message #654073 is a reply to message #654072] Wed, 27 July 2016 00:11 Go to previous messageGo to next message
Littlefoot
Messages: 21678
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 #654360 is a reply to message #654073] Mon, 01 August 2016 05:15 Go to previous messageGo to next message
abacusdotcom
Messages: 15
Registered: June 2010
Location: Lagos
Junior Member

Thanks all.. Your views works. @LittleFoot Thanks.
Re: Error An INTO Clause [message #654371 is a reply to message #654068] Mon, 01 August 2016 06:53 Go to previous message
EdStevens
Messages: 1321
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

Previous Topic: Same record with Space and Without Space
Next Topic: Sorting
Goto Forum:
  


Current Time: Mon Jun 21 17:10:04 CDT 2021