asked on

DECLARE CURSOR IS SELECT BEGIN LOOP END LOOP END

Hello expert,

Found some old SQL scripts that use DECLARE CURSOR IS SELECT BEGIN LOOP END LOOP END
that need updating but am not familiar with this form.

Have looked online for a simple example so I could experiment with this model and figure it out.
Not finding an example that is simple, if it exists.

So have set out to edify myself.

First, created a table using script so it is portable. Attached herewith: PL SQL Create Table explicit.txt
Creates a table with three records and twenty rows.

Second, Have been experimenting with code listed herewith below;
from http://www.oracletutorial.com/plsql-tutorial/plsql-cursor-for-loop/

The Oracle SQL Developer always returns
Error starting at line : 1 in command
which is not too informative.

Think the issue is in the syntax in the PUT_LINE
but can't quite make it work.

Just working to get a simple example to do something
on which a base can be built.

Thanks.

Allen in Dallas

-----------------begin cursor script------------------
DECLARE
CURSOR cur_name
IS
SELECT
CONTACT_ID, FIRST_NAME, LAST_NAME
FROM
TMP_SOURCE_180615
ORDER BY
LAST_NAME DESC;
BEGIN
FOR TMP_SOURCE_180615 IN cur_name
LOOP
DBMS_OUTPUT.PUT_LINE( TMP_SOURCE_180615.FIRST_NAME,
TMP_SOURCE_180615.LAST_NAME);
END LOOP;
END;
PL-SQL-CREATE-TABLE-explicit.txt

Geert G

i'd put a commit at the end of your script ...

why are you reusing a table name inside the cursor ?
just adds to the confusion

i use rec to indicate a record/row in a cursor:

and why are you doing that loop, when this gives the same output

SELECT FIRST_NAME||LAST_NAME  FROM TMP_SOURCE_180615 ORDER BY LAST_NAME DESC;

Open in new window

slightwv (䄆 Netminder)

Your create table has an error.

Try this one:

CREATE TABLE TMP_SOURCE_180615(
	CONTACT_ID NUMBER(6) NOT NULL,
	FIRST_NAME VARCHAR2(50) NOT NULL,
	LAST_NAME VARCHAR2(50) NOT NULL)
;

Open in new window

dbms_output.put_line takes a single string as input. You pass two strings.

Try this:

DECLARE
	CURSOR cur_name
	IS
		SELECT
			CONTACT_ID, FIRST_NAME, LAST_NAME
		FROM
			TMP_SOURCE_180615
		ORDER BY
			LAST_NAME DESC;
BEGIN
	FOR TMP_SOURCE_180615 IN cur_name
	LOOP
		DBMS_OUTPUT.PUT_LINE(TMP_SOURCE_180615.FIRST_NAME || ',' || TMP_SOURCE_180615.LAST_NAME);
	END LOOP;
END;
/

Open in new window

slightwv (䄆 Netminder)

>>and why are you doing that loop,

educational exercise. How else do you learn cursors and loops?

Allen Pitts

ASKER

Hello Expert,

Have continued trial and error on cursor script. Tried

DECLARE
CURSOR cur_name
IS
SELECT
CONTACT_ID, FIRST_NAME, LAST_NAME
FROM
TMP_SOURCE_180615
ORDER BY
LAST_NAME DESC;
BEGIN
FOR TMP_SOURCE_180615 IN cur_name
LOOP
DBMS_OUTPUT.PUT_LINE('Name' || TMP_SOURCE_180615.FIRST_NAME);
END LOOP;
END;

This returns 'PL/SQL procedure successfully completed.'
Was expecting a display of data.
How can one get some display of data from the cursor loop?
Is there some other simple but useful function that can be used?

Thanks.

Allen in Dallas

slightwv (䄆 Netminder)

What tool are you using to execute the code block?

If an older version of sqlplus execute this before the DECLARE:
set serveroutput on size 1000000

Sujith

You need to issue this statement before running your PL/SQL block to see the output.

SET SERVEROUTPUT ON

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Allen Pitts

ASKER

Hello slightw,

The cursor that requires maintenance loops thru a table, say, TMP_SOURCE_180615
and based on criteria in TMP_SOURCE_180617 writes data to TMP_SOURCE_180618.

Will
1. Write scripts to create a populate TMP_SOURCE_180617 and TMP_SOURCE_180618
2. Start new question.

Thanks.

Allen

Allen Pitts

ASKER

Thanks. the SET SERVEROUPUT ON worked.
Will start new question on using Cursor Loop to write data from one table to another based on third table

Allen Pitts

ASKER

Sorry meant to spread the points around to slightWv and sujith. Will be more careful next time.

slightwv (䄆 Netminder)

>> using Cursor Loop to write data from one table to another based on third table

As mentioned before, there are easier ways to do these things.

As long as you are just doing this to learn it is OK. If this is meant for production, there are likely better ways.

>>Sorry meant to spread the points around to slightWv and sujith.

Geert or myself can reopen this if you wish.

Geert G

me ? reopen ?
nope, haven't got that *power*

i once asked to be Delphi Zone Advisor instead of meikl (kretschmar)
but they picked Aikimark instead.
Dunno why, he doesn't understand 10% of Delphi. I think i've gotten to 12% now ...
Gave up on that *power* request