Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE HELP
EXECUTE IMMEDIATE HELP [message #582153] Sun, 14 April 2013 12:11 Go to next message
ayomigs13
Messages: 2
Registered: April 2013
Location: United States
Junior Member
Hello,
I am trying to create a procedure using the EXECUTE IMMEDIATE. I have been having problems calling it from an anonymous block
My code

CREATE OR REPLACE PROCEDURE homework
(p_table_name VARCHAR2)
IS
v_department_id departments.department_id%TYPE;
BEGIN
EXECUTE IMMEDIATE
'SELECT department_id INTO v_department_id
FROM ' || p_table_name;
DBMS_OUTPUT.PUT_LINE('The department_id for table ' ||p_table_name|| ' is ' || v_department_id);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple rows, please use a cursor');
END;

I called the procedure from an anonymous block

BEGIN
homework('Employees');
END;

It gives me an error
ORA-00905: missing keyword

Am I missing anything? Thank you
Re: EXECUTE IMMEDIATE HELP [message #582154 is a reply to message #582153] Sun, 14 April 2013 12:34 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Wrong syntax. Execute immediate's INTO is supposed to be used differently than in static SQL. Here's an example:
SQL> create or replace procedure homework (p_table_name in varchar2)
  2  is
  3    v_department_id dept.deptno%type;
  4  begin
  5    execute immediate ('select deptno from ' || p_table_name ||
  6                       ' where rownum = 1') into v_department_id;
  7    dbms_output.put_line('Deptno is ' || v_department_id);
  8  end;
  9  /

Procedure created.

SQL> exec homework('dept');
Deptno is 10

PL/SQL procedure successfully completed.

SQL>
Re: EXECUTE IMMEDIATE HELP [message #582156 is a reply to message #582153] Sun, 14 April 2013 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

2 good books for you you may consult before using any feature like EXECUTE IMMEDIATE and which will give you the answer for any syntax error:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

And also Database SQL Reference.

Regards
Michel

Re: EXECUTE IMMEDIATE HELP [message #582158 is a reply to message #582156] Sun, 14 April 2013 14:08 Go to previous message
ayomigs13
Messages: 2
Registered: April 2013
Location: United States
Junior Member
Thanks!
Previous Topic: sql distinct row query
Next Topic: Issue with Parallel Pipelined
Goto Forum:
  


Current Time: Sat Oct 25 07:15:57 CDT 2014

Total time taken to generate the page: 0.07928 seconds