| 21/85 PL/SQL: ORA-00933: (merged 2 cross-posts) [message #198342] |
Mon, 16 October 2006 13:10  |
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
|
Senior Member |
|
|
hi:
i´m getting the follow error in my procedure
CREATE OR REPLACE PROCEDURE DM08_PROCEDURE_CP_ESTADO IS
v_id_base NUMBER(2);
v_id_orig NUMBER(4);
v_id_sepo NUMBER(2);
v_edo_orig VARCHAR2(30);
v_edo_sepo VARCHAR2(40);
v_abrev_sepo VARCHAR2(5);
v_subcad_ori VARCAHR2(40);
v_subcad_sepo VARCAHR2(40);
v_long_orig NUMBER(3);
CURSOR C_ESTADO_ORIGEN IS
select ID_BASE_ORIGEN, ID_ESTADO, LTRIM(RTRIM(NOM_ESTADO)) from DM08_ESTADO;
CURSOR C_ESTADO_SEPOMEX IS
select ID_ESTADO, LTRIM(RTRIM(ESTADO)), ABREV_ESTADO from DM08_SEPOMEX_ESTADO;
BEGIN
OPEN C_ESTADO_ORIGEN;
FETCH C_ESTADO_ORIGEN INTO v_id_base, v_id_orig, v_edo_orig;
v_edo_orig := UPPER(v_edo_orig);
SELECT NVL(id_estado,0) FROM DM08_SEPOMEX_ESTADO where ESTADO like 'v_edo_sepo' into (v_id_sepo);
IF v_id_sepo <> 0 THEN
INSERT INTO DM08_CP_TEMPORAL_ESTADO VALUES (v_id_base, v_id_orig, v_id_sepo, v_edo_sepo, v_abrev_sepo);
END IF;
EXIT WHEN C_ESTADO_ORIGEN %NOT_FOUND;
CLOSE C_ESTADO_ORIGEN;
EXCEPTION
WHEN OTHERS THEN raise_application_error(-20000, SQLERRM);
END;
/
21/5 PL/SQL: SQL Statement ignored
21/85 PL/SQL: ORA-00933: SQL command not properly ended
I can´t find nothing strange in my select clause...
Can you help me please??
Alex
|
|
|
|
| 21/85 PL/SQL: ORA-00933: [message #198345 is a reply to message #198342] |
Mon, 16 October 2006 13:22  |
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
|
Senior Member |
|
|
hi:
i´m getting the follow error in my procedure
CREATE OR REPLACE PROCEDURE DM08_PROCEDURE_CP_ESTADO IS
v_id_base NUMBER(2);
v_id_orig NUMBER(4);
v_id_sepo NUMBER(2);
v_edo_orig VARCHAR2(30);
v_edo_sepo VARCHAR2(40);
v_abrev_sepo VARCHAR2(5);
v_subcad_ori VARCAHR2(40);
v_subcad_sepo VARCAHR2(40);
v_long_orig NUMBER(3);
CURSOR C_ESTADO_ORIGEN IS
select ID_BASE_ORIGEN, ID_ESTADO, LTRIM(RTRIM(NOM_ESTADO)) from DM08_ESTADO;
CURSOR C_ESTADO_SEPOMEX IS
select ID_ESTADO, LTRIM(RTRIM(ESTADO)), ABREV_ESTADO from DM08_SEPOMEX_ESTADO;
BEGIN
OPEN C_ESTADO_ORIGEN;
FETCH C_ESTADO_ORIGEN INTO v_id_base, v_id_orig, v_edo_orig;
v_edo_orig := UPPER(v_edo_orig);
SELECT NVL(id_estado,0) FROM DM08_SEPOMEX_ESTADO where ESTADO like 'v_edo_sepo' into (v_id_sepo);
IF v_id_sepo <> 0 THEN
INSERT INTO DM08_CP_TEMPORAL_ESTADO VALUES (v_id_base, v_id_orig, v_id_sepo, v_edo_sepo, v_abrev_sepo);
END IF;
EXIT WHEN C_ESTADO_ORIGEN %NOT_FOUND;
CLOSE C_ESTADO_ORIGEN;
EXCEPTION
WHEN OTHERS THEN raise_application_error(-20000, SQLERRM);
END;
/
21/5 PL/SQL: SQL Statement ignored
21/85 PL/SQL: ORA-00933: SQL command not properly ended
I can´t find nothing strange in my select clause...
Can you help me please??
Alex
|
|
|
|
|
|
| Re: 21/85 PL/SQL: ORA-00933: [message #198348 is a reply to message #198345] |
Mon, 16 October 2006 13:29 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Alex, the INTO clause needs to come before the FROM clause.
SELECT NVL(id_estado,0) into v_id_sepo FROM DM08_SEPOMEX_ESTADO where ESTADO like 'v_edo_sepo';
But, there are other problems here. Do you really mean to search on the literal string 'v_edo_sepo'? You have a variable declared with that name, but it has no assigned value.
Also, more importantly, you are doing cursor loops here when this whole procedure should probably be just a single SQL statement - no loops, no variables, no explicit cursors.
|
|
|
|
| Re: 21/85 PL/SQL: ORA-00933: [message #198350 is a reply to message #198342] |
Mon, 16 October 2006 13:40 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| superoscarin@hotmail.com wrote on Mon, 16 October 2006 14:10 |
SELECT NVL(id_estado,0) FROM DM08_SEPOMEX_ESTADO where ESTADO like 'v_edo_sepo' into (v_id_sepo);
|
Proper syntax is:
SELECT NVL(id_estado,0)
into v_id_sepo
FROM DM08_SEPOMEX_ESTADO
where ESTADO = v_edo_sepo;
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
