Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 109 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Error in SQL Query
Hello,
I am not a SQL-Developer but have to create some SQL Queries as part of my job. I want to create a Procedure but something is going wrong. Can you please help me identify the error. Thank you very much.
CREATE OR REPLACE PROCEDURE CONTRACT_PARTNERS
(
USER_INPUT IN VARCHAR2
, RELATION IN VARCHAR2
, OUTPUT OUT VARCHAR2
) AS
v_filter nvarchar2(30);
v_Value NUMBER(10);
BEGIN
v_Value :=
CASE WHEN p_relation= 'eingehend' THEN 'D'
WHEN p_relation = 'ausgehend' THEN 'K'
END
SELECT into OUTPUT
no, name, street,' ' as ADDITION, zipcode,city,' ' as State,country_code, 'Vertragspartner' as CONTLIST
FROM sol_vendor WHERE (company_code like p_userinput OR lower(no) like lower(p_userinput) OR lower(name) like lower(p_userinput)) AND no LIKE 'p_userinput%';
END CONTRACT_PARTNERS;
Answers
-
Hi,
What, exactly, is the error? Post the complete error message(s), including line numbers.
-
We don't know your data model or what you are trying to do, so "something's wrong" is beyond vague.
What do you want to return in your output?
-
Your approach to using SQL via PL/SQL is flawed. Oracle is nothing like SQL-Server in this regard, and approaches and techniques used in T-SQL simply do not work in SQL and PL/SQL.
What exact business requirement are you trying to meet?
-
Hi,
SELECT into OUTPUT
no, name, street,' ' as ADDITION, zipcode,city,' ' as State,country_code, 'Vertragspartner' as CONTLIST
FROM sol_vendor WHERE (company_code like p_userinput OR lower(no) like lower(p_userinput) OR lower(name) like lower(p_userinput)) AND no LIKE 'p_userinput%';
Review the syntax of SELECT ... INTO. The INTO clause comes after the SELECT clause, right before FROM. If you;re SELECTing into one VARCHAR2 variable (and nothing else), then the SELECT clause must contain one string column (and nothing else), e.g.
SELECT no || ' ' || name || ' ' || street || ... INTO output FROM ...
-
Sorry, I am also new to this Community. I need some time to learn to ask questions properly.
I have got a table sol_vendor where we have contract partners. Based on the relation to the partner the Company name has a prefix "D" or "K". We are using a software called ELO to create contracts. When creating a new contract the employees choose the option for contract relation ("ingoing" or "outgoing"). Based on the selection I want to filter the listed contract partners. If "ingoing" is selected, I want to list all companies with the prefix "D" like, "D - Apple".
To get data from database ELO is using an interface to create SQL queries and to pass variables to the query.
Currently working SQL Query is:
SELECT no, name, street,' ' as ADDITION, zipcode,city,' ' as State,country_code, 'Vertragspartner' as CONTLIST
FROM sol_vendor WHERE company_code like ? OR lower(no) like lower(?) OR lower(name) like lower(?)
? is the user-input
To filter the query Output I could just modify the SQL Query or try something new. The ELO Team told me that i can create procedere.
-
For starters
CREATE OR REPLACE PROCEDURE CONTRACT_PARTNERS ( USER_INPUT IN VARCHAR2 , RELATION IN VARCHAR2 , OUTPUT OUT VARCHAR2 ) AS v_filter nvarchar2(30); -- do you really need Nvarchar2? v_Value NUMBER(10); BEGIN v_Value := CASE WHEN p_relation= 'eingehend' THEN 'D' -- parameter name is relation, not p_relation WHEN p_relation = 'ausgehend' THEN 'K' END; -- missing semi-colon SELECT into OUTPUT -- INTO clause must follow select list, not preceed it. no, name, street,' ' as ADDITION, zipcode,city,' ' as State,country_code, 'Vertragspartner' as CONTLIST FROM sol_vendor WHERE (company_code like p_userinput OR lower(no) like lower(p_userinput) OR lower(name) like lower(p_userinput)) AND no LIKE 'p_userinput%'; END CONTRACT_PARTNERS; --parameter name is USER_INPUT not p_userinput
SY.
-
To filter the query Output I could just modify the SQL Query or try something new. The ELO Team told me that i can create procedere.
Wrong.
In Oracle the best method for accessing/processing data is SQL. For high performance SQL (minimal CPU and memory spend on parsing) is SQL with bind variables.
Your SQL used currently, seems to be a SQL using bind variables. This is the correct approach.
-
I will try out your answers once i get acces to the server again. Thank you very much for all suggestions. Have a nice weekend.
