Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic sql (oracle 10 g)
Dynamic sql [message #413363] Wed, 15 July 2009 07:02 Go to next message
bond007
Messages: 58
Registered: March 2009
Member


can I write the For loop in the following manner
for rec in cursor_name loop
/*Statements, use rec.col_1 and rec.col_2 */
end loop;
in the procedure test1 . I tried but not able to . Can any one suggest how to do that
----------------------------------------------------
CREATE OR REPLACE PROCEDURE test1 is
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(1000);

v_sbu_code VARCHAR2(20);

v_sbu_number VARCHAR2(20);
BEGIN
query_str := 'SELECT sbu_code,sbu_number from sbu';

OPEN c FOR query_str;
LOOP
FETCH c INTO v_sbu_code ,v_sbu_number;
EXIT WHEN c%NOTFOUND;
-- process row here
dbms_output.put_line(v_sbu_code ||v_sbu_number);
END LOOP;
CLOSE c;
END;
/
SHOW ERRORS;
/
set serveroutput on
------------------------------------------------------------
Re: Dynamic sql [message #413370 is a reply to message #413363] Wed, 15 July 2009 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As requested in your other topics: follow the OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel

[Updated on: Wed, 15 July 2009 07:30]

Report message to a moderator

Re: Dynamic sql [message #413371 is a reply to message #413363] Wed, 15 July 2009 07:32 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't use that syntax with ref cursors.
You can do
DECLARE
  cursor c_cur is SELECT...
BEGIN
  for rec in c_cur loop
    ...
  end loop;
end;
or
BEGIN
  for rec in (select ...) loop
    ...
  end loop;
end;
Previous Topic: Something Like a loop
Next Topic: How to calculate average time
Goto Forum:
  


Current Time: Sat Dec 10 03:00:30 CST 2016

Total time taken to generate the page: 0.06878 seconds