OraFAQ Forum: SQL & PL/SQL » Dynamic SQL Select query (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL Select query (merged) (PL/SQL)

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

Dynamic SQL Select query (merged) [message #651867]

Thu, 26 May 2016 11:52

richard_oak
Messages: 8
Registered: May 2016

Junior Member

Hello,

I'm just learning PL/SQL and been trying to resolve my issue by myself but with no success yet.

This is what I'm trying to do:

CREATE OR REPLACE PROCEDURE "EMPLOYEES_BY_DEPT"
IS
V_EMP_ID NUMBER;

CURSOR GET_EMP_IP IS
SELECT EMP_ID FROM DEPARTMENTS;

BEGIN
OPEN GET_EMP_IP FOR
SELECT * FROM EMPLOYEES WHERE EMP_ID = GET_EMP_IP;

END;

I need to pull out all employees from the employees table where the emp_id exists in the department table.

Report message to a moderator

Re: Dynamic SQL Select query (merged) [message #651869 is a reply to message #651867]

Thu, 26 May 2016 11:56

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I need to pull out all employees from the employees table where the emp_id exists in the department table.

what do you mean by this?
The department table does not contain any employee id.
Do you mean where a department id is equal to an employee id?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Report message to a moderator

Re: Dynamic SQL Select query (merged) [message #651870 is a reply to message #651869]

Thu, 26 May 2016 12:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

never do in PL/SQL that which can be done in plain SQL

Report message to a moderator

Re: Dynamic SQL Select query (merged) [message #651873 is a reply to message #651870]

Thu, 26 May 2016 12:11

richard_oak
Messages: 8
Registered: May 2016

Junior Member

I'm just trying to use a LOOP for a SELECT statement where a variable is passed. I get errors that an INTO was expected.

Report message to a moderator

Re: Dynamic SQL Select query (merged) [message #651874 is a reply to message #651873]

Thu, 26 May 2016 12:13

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste your session.
And don't forget to format as specified above.

Report message to a moderator

Re: Dynamic SQL Select query (merged) [message #651875 is a reply to message #651873]

Thu, 26 May 2016 12:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

richard_oak wrote on Thu, 26 May 2016 10:11

I'm just trying to use a LOOP for a SELECT statement where a variable is passed. I get errors that an INTO was expected.

when using SELECT inside PL/SQL code it must include INTO clause & must return only single row (at a time).

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=pl%2Fsql+select+into

Report message to a moderator

Previous Topic:	Different values of count(*) and num_rows of table
Next Topic:	Indexes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 15:44:38 CDT 2024