Stored Procedure - expand single char to string for reports

[misapena]

Hello all,

I'm very new to this and trying to write a stored procedure that will use the if/then/else control structure to check the single character field in my db and expand it to it's corresponding string for report viewing.

This is my code so far... which doesn't work:

CREATE OR REPLACE PROCEDURE replaceContact
AS
type CHAR;
CURSOR type_cursor IS
SELECT c.CONTACT_TYPE INTO type
FROM CONTACT c;

BEGIN

FOR row IN type_cursor LOOP

IF row.type = 'C' THEN
type := 'CELL';
IF row.type = 'W' THEN
type := 'WORK';
IF row.type = 'P' THEN
type := 'PAGER';
IF row.type = 'E' THEN
type := 'EMAIL';
IF row.type ='H' THEN
type := 'HOME';
END IF;

DBMS_OUTPUT.PUT_LINE(row.type);

END LOOP;

END replaceContact;
/

These are my error messsages:

Warning: Procedure created with compilation errors.

LINE/COL ERROR
-------- -----------------------------------------------------------------
26/5 PLS-00103: Encountered the symbol "LOOP" when expecting one of
the following:
if

28/19 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
end not pragma final instantiable order overriding static
member constructor map

Any help is greatly appreciated =).

-lisa

 

[sem]

I can not get that errors from that code. Though I can tell something:
1. You should remove INTO type from cursor declaration (according to your error you've probably done it). If you need CONTACT_TYPE to be aliased, omit into.
2. Type is reserved word, choose another one.
3. You should use either simple IF.. THEN..ELSE.. END IF; construct or IF..THEN..ELSIF..END IF;
4. row variable is explicitly declared as of type_cursor%rowtype type, so to get its content it should be referenced as row.CONTACT_TYPE. If my assumption in 1 about aliasing is correct, use that (not TYPE!) alias instead of CONTACT_TYPE.


Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Lisa,

There are a few issues with your code, which I shall enumerate here:

1) "Type" is a RESERVED WORD in PL/SQL, which you cannot use in the context that appears in your code.
2) "c" and "c." references in your cursor are acceptable but not necessary.
3) In a CURSOR, you do not use the &quot;INTO <var-list>&quot; construct. &quot;SELECT...INTO...&quot; is for non-CURSOR accesses within PL/SQL.
4) The &quot;IF row.type...&quot; references should become &quot;IF row.contact_type...&quot; references.
5) &quot;TYPE&quot; is a 1-character data item. You are trying to assign 4- and 5-character strings to &quot;TYPE&quot;. That would fail.
6) For the DBMS_OUTPUT.PUT_LINE statement, you want to print the contents of TYPE, which is not part of the ROW cursor. So, remove the &quot;ROW.&quot; reference for TYPE.
7) Once you got rid of the syntax/interpret problems, then the only output you could ever see from your code would be &quot;CELL&quot;. This is because your &quot;IF&quot; statements are nested and will execute only when the first IF is TRUE. When the first IF is TRUE, the other IFs most certainly would evaluate to FALSE. You can resolve this logic error by changing the subsequent IFs to ELSIFs, or you can also get correct results by adding four END IFs: one to terminate each IF.

I have re-coded your script, without error, I hope.

CREATE OR REPLACE PROCEDURE replaceContact
AS
    type_hold    varchar2(10);
    CURSOR type_cursor IS 
        SELECT CONTACT_TYPE
        FROM CONTACT;

BEGIN

FOR row IN type_cursor LOOP

    IF row.contact_type = 'C' THEN
        type_hold := 'CELL';
    ELSIF row.contact_type = 'W' THEN
        type_hold := 'WORK';
    ELSIF row.contact_type = 'P' THEN
        type_hold := 'PAGER';
    ELSIF row.contact_type = 'E' THEN
        type_hold := 'EMAIL';
    ELSIF row.contact_type ='H' THEN
        type_hold := 'HOME';
    END IF;

    DBMS_OUTPUT.PUT_LINE(type_hold);

END LOOP;

END replaceContact;
/

Procedure created.

==========================================
Now, by comparison, the following is alternate code from yours that does the same and a little bit more with a little bit less code:

CREATE OR REPLACE PROCEDURE replaceContact
AS
BEGIN
FOR row IN (SELECT CONTACT_TYPE FROM CONTACT) LOOP
    IF row.contact_type = 'C' THEN
	DBMS_OUTPUT.PUT_LINE('CELL');
    ELSIF row.contact_type = 'W' THEN
	DBMS_OUTPUT.PUT_LINE('WORK');
    ELSIF row.contact_type = 'P' THEN
	DBMS_OUTPUT.PUT_LINE('PAGER');
    ELSIF row.contact_type = 'E' THEN
 	DBMS_OUTPUT.PUT_LINE('EMAIL');
    ELSIF row.contact_type ='H' THEN
	DBMS_OUTPUT.PUT_LINE('HOME');
    ELSE
	DBMS_OUTPUT.PUT_LINE('MISSING or INVALID CONTACT TYPE');
    END IF;
END LOOP;
END replaceContact;
/

Procedure created.

SQL> col contact_type heading "Contact|Type" format a7
SQL> select * from contact;

Contact
Type
-------
C
E
H

P
W
X

7 rows selected.

SQL> exec replaceContact
CELL
EMAIL
HOME
MISSING or INVALID CONTACT TYPE
PAGER
WORK
MISSING or INVALID CONTACT TYPE

PL/SQL procedure successfully completed.

Notice that in the alternative code, it accounts for missing or invalid CONTACT_TYPE such ase NULL and 'X', respectively.

Let us know if this helped.

Mufasa
(aka Dave of Sandy, Utah, USA @ 09:55 (09Dec03) GMT, 02:55 (09Dec03) Mountain Time)

 

[sem]

As an add-on to Dave's exhaustive answer I may note that the same output may be also obtained from

SELECT decode(CONTACT_TYPE
, 'C', 'CELL'
, 'W', 'WORK'
, 'P', 'PAGER'
, 'E', 'EMAIL'
, 'H', 'HOME')
FROM CONTACT

Regards, Dima

http://seminihin.narod.ru

 

[misapena]

Thank you both Dima and Dave, you were both most helpful.

-Lisa