Home » SQL & PL/SQL » SQL & PL/SQL » 21/85 PL/SQL: ORA-00933: (merged 2 cross-posts)
21/85 PL/SQL: ORA-00933: (merged 2 cross-posts) [message #198342] Mon, 16 October 2006 13:10 Go to next message
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&acute;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 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

hi:

i&acute;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&acute;t find nothing strange in my select clause...

Can you help me please??
Alex
Re: 21/85 PL/SQL: ORA-00933: [message #198346 is a reply to message #198345] Mon, 16 October 2006 13:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The correct syntax is
select .. into .. from
Re: 21/85 PL/SQL: ORA-00933: [message #198348 is a reply to message #198345] Mon, 16 October 2006 13:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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;

Re: 21/85 PL/SQL: ORA-00933: [message #198351 is a reply to message #198348] Mon, 16 October 2006 13:44 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Thanks Todd:

This is my first step of a procedure that search strings from a field of a table in another, and insert the matches en a third table and look for substrings that matching too...

I guess that the variable shouldn&acute;t have '', right??

I've deleted the '' and my procedure is ok now...

Greetings
Alex



Re: 21/85 PL/SQL: ORA-00933: [message #198353 is a reply to message #198345] Mon, 16 October 2006 13:50 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Oh great, I just wasted my friggin' time answering this question that you also posted in another forum http://www.orafaq.com/forum/t/71029/66800/

Please provide an address where I can send the bill for my time?
Re: 21/85 PL/SQL: ORA-00933: [message #198362 is a reply to message #198353] Mon, 16 October 2006 16:33 Go to previous message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

I&acute;m sorry for that... but i only needed help for the syntax no for the context

Thanks anyway
Alex
Previous Topic: Database Auditing
Next Topic: Viewing sql for a VIEW
Goto Forum:
  


Current Time: Mon Dec 05 09:00:54 CST 2016

Total time taken to generate the page: 0.10205 seconds