Home » Developer & Programmer » Forms » ORA-01007 - variable not in select list (Oracle forms 6i)
ORA-01007 - variable not in select list [message #584185] |
Thu, 09 May 2013 14:30 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/dc1b2f036aefbc3a109b50fe436d6109?s=64&d=mm&r=g) |
flemej
Messages: 5 Registered: May 2013 Location: Brasil
|
Junior Member |
![flemej@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
Hi!
I wrote the following code:
DECLARE
VISCONNECTED BOOLEAN;
VCONEXAO EXEC_SQL.CONNTYPE;
VARQUIVO_SAIDA TEXT_IO.FILE_TYPE;
VCURSOR EXEC_SQL.CURSTYPE;
VCOLUMNVALUE VARCHAR2(2000);
VSTATUS PLS_INTEGER;
VNUMCOLUNAS NUMBER DEFAULT 0;
VSEPARADOR VARCHAR2(10) DEFAULT ';';
VCONTADOR NUMBER DEFAULT 0;
BEGIN
VCONEXAO := EXEC_SQL.DEFAULT_CONNECTION;
VISCONNECTED := EXEC_SQL.IS_CONNECTED;
IF NOT VISCONNECTED THEN
MSG_ALERT('Não conectado.', 'E', TRUE);
ELSE
VCURSOR := EXEC_SQL.OPEN_CURSOR;
END IF;
BEGIN
EXEC_SQL.PARSE(VCONEXAO, VCURSOR, :BLK.TXTQUERY, EXEC_SQL.V7);
EXCEPTION
WHEN OTHERS THEN
MSG_ALERT('Ocorreu o erro ' || SQLERRM || ' executando parse da query!', 'E', TRUE);
END;
BEGIN
IF TEXT_IO.IS_OPEN(VARQUIVO_SAIDA) THEN
TEXT_IO.FCLOSE(VARQUIVO_SAIDA);
END IF;
VARQUIVO_SAIDA := TEXT_IO.FOPEN(:BLK.TXTDIRECTORY || :BLK.TXTFILENAME, 'w');
EXCEPTION
WHEN OTHERS THEN
MSG_ALERT('Ocorreu o erro ' || SQLERRM || ' criando arquivo no disco!', 'E', TRUE);
END;
BEGIN
FOR I IN 1 .. 255 LOOP
BEGIN
EXEC_SQL.DEFINE_COLUMN(VCURSOR, I, VCOLUMNVALUE, 2000);
VNUMCOLUNAS := I;
EXCEPTION
WHEN OTHERS THEN
IF (SQLCODE = -1007) THEN
EXIT;
ELSE
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
MSG_ALERT('Ocorreu o erro ' || SQLERRM || ' executando define_column!', 'E', TRUE);
END;
EXEC_SQL.DEFINE_COLUMN(VCURSOR, 1, VCOLUMNVALUE, 20000);
BEGIN
VSTATUS := EXEC_SQL.EXECUTE(VCURSOR); -- ----------------------->> ERROR HERE!!!!!!!!
EXCEPTION
WHEN OTHERS THEN
MSG_ALERT('Ocorreu o erro ' || EXEC_SQL.LAST_ERROR_MESG || ' fazendo execute para a query!', 'E', TRUE);
END;
BEGIN
LOOP
EXIT WHEN(EXEC_SQL.FETCH_ROWS(VCURSOR) <= 0);
VSEPARADOR := '';
FOR I IN 1 .. VNUMCOLUNAS LOOP
EXEC_SQL.COLUMN_VALUE(VCURSOR, I, VCOLUMNVALUE);
TEXT_IO.PUT_LINE(VARQUIVO_SAIDA, VSEPARADOR || VCOLUMNVALUE);
VSEPARADOR := :BLK.TXTSEPARATOR;
END LOOP;
TEXT_IO.NEW_LINE(VARQUIVO_SAIDA);
VCONTADOR := VCONTADOR + 1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
MSG_ALERT('Ocorreu o erro ' || SQLERRM || ' criando linhas no arquivo texto!', 'E', TRUE);
END;
BEGIN
EXEC_SQL.CLOSE_CURSOR(VCURSOR);
EXCEPTION
WHEN OTHERS THEN
MSG_ALERT('Ocorreu o erro ' || SQLERRM || ' fechando cursor!', 'E', TRUE);
END;
BEGIN
TEXT_IO.FCLOSE(VARQUIVO_SAIDA);
EXCEPTION
WHEN OTHERS THEN
MSG_ALERT('Ocorreu o erro ' || SQLERRM || ' fechando arquivo!', 'E', TRUE);
END;
END;
------------------------------------------
But, on line "VSTATUS := EXEC_SQL.EXECUTE(VCURSOR);" i get the error (ORA-01007 - VARIABLE NOT IN SELECT LIST). Whats is wrong?
Thanks a lot!!!
|
|
|
|
|
|
|
|
|
Re: ORA-01007 - variable not in select list [message #584270 is a reply to message #584185] |
Fri, 10 May 2013 14:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/dc1b2f036aefbc3a109b50fe436d6109?s=64&d=mm&r=g) |
flemej
Messages: 5 Registered: May 2013 Location: Brasil
|
Junior Member |
![flemej@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
Hi, all!
I finally got it!
now running perfectly!
Sharing.....
DECLARE
TYPE TCOLUMNDEFS IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
VARQUIVO_SAIDA TEXT_IO.FILE_TYPE;
VDEFCOLUNAS TCOLUMNDEFS;
VCURSOR EXEC_SQL.CURSTYPE;
VLINHASSELECIONADAS PLS_INTEGER;
VNUMCOLS PLS_INTEGER := 0;
VTIPODADO PLS_INTEGER;
VTAMCOL PLS_INTEGER;
VNOMECOLUNA VARCHAR2(255);
VSTRING VARCHAR2(32767);
VCONTINUA BOOLEAN := TRUE;
VVALORCOLUNA VARCHAR2(255);
VCONEXAO EXEC_SQL.CONNTYPE;
VVEZ NUMBER(1) := 1;
VTEM NUMBER(10);
VBARRA BOOLEAN := FALSE;
BEGIN
IF :BLK.TXTFILENAME IS NULL THEN
:BLK.TXTFILENAME := 'C:\EXTRACAO_' || TO_CHAR(SYSDATE, 'RRRRMMDDHH24MISS') || '.CSV';
SYNCHRONIZE;
END IF;
IF :BLK.TXTQUERY IS NULL THEN
MSG_ALERT('É obrigatório informar a query!', 'W', TRUE);
END IF;
IF LENGTH(:BLK.TXTQUERY) > 32767 THEN
MSG_ALERT('O tamanho da query deve estar entr 1 e 32767 caracteres!', 'W', TRUE);
END IF;
SELECT INSTR(UPPER(:BLK.TXTQUERY), 'INSERT') INTO VTEM FROM DUAL;
IF VTEM <> 0 THEN
VBARRA := TRUE;
END IF;
IF NOT VBARRA THEN
SELECT INSTR(UPPER(:BLK.TXTQUERY), 'UPDATE') INTO VTEM FROM DUAL;
IF VTEM <> 0 THEN
VBARRA := TRUE;
END IF;
END IF;
IF NOT VBARRA THEN
SELECT INSTR(UPPER(:BLK.TXTQUERY), 'DELETE') INTO VTEM FROM DUAL;
IF VTEM <> 0 THEN
VBARRA := TRUE;
END IF;
END IF;
IF NOT VBARRA THEN
SELECT INSTR(UPPER(:BLK.TXTQUERY), 'CREATE') INTO VTEM FROM DUAL;
IF VTEM <> 0 THEN
VBARRA := TRUE;
END IF;
END IF;
IF NOT VBARRA THEN
SELECT INSTR(UPPER(:BLK.TXTQUERY), 'ALTER') INTO VTEM FROM DUAL;
IF VTEM <> 0 THEN
VBARRA := TRUE;
END IF;
END IF;
IF VBARRA THEN
MSG_ALERT('Não são permitidos comandos de manipulação de dados!', 'W', TRUE);
END IF;
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'BUSY');
:BLK.TXTQUERY := REPLACE(:BLK.TXTQUERY, ';', '');
VCONEXAO := EXEC_SQL.DEFAULT_CONNECTION;
VCURSOR := EXEC_SQL.OPEN_CURSOR(VCONEXAO);
EXEC_SQL.PARSE(VCONEXAO, VCURSOR, :BLK.TXTQUERY);
VLINHASSELECIONADAS := EXEC_SQL.EXECUTE(VCONEXAO, VCURSOR);
BEGIN
IF TEXT_IO.IS_OPEN(VARQUIVO_SAIDA) THEN
TEXT_IO.FCLOSE(VARQUIVO_SAIDA);
END IF;
VARQUIVO_SAIDA := TEXT_IO.FOPEN(:BLK.TXTFILENAME, 'w');
EXCEPTION
WHEN OTHERS THEN
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT');
MSG_ALERT('Ocorreu o erro ' || SQLERRM || ' criando arquivo no disco!', 'E', TRUE);
END;
WHILE VCONTINUA LOOP
VSTRING := NULL;
BEGIN
WHILE TRUE LOOP
VNUMCOLS := VNUMCOLS + 1;
EXEC_SQL.DESCRIBE_COLUMN(VCONEXAO, VCURSOR, VNUMCOLS, VNOMECOLUNA, VTAMCOL, VTIPODADO);
VDEFCOLUNAS(VNUMCOLS) := GET_DISPLAY_LENGTH(VTIPODADO, VTAMCOL);
VNOMECOLUNA := RPAD(VNOMECOLUNA, VDEFCOLUNAS(VNUMCOLS));
VSTRING := VSTRING || LTRIM(RTRIM(VNOMECOLUNA)) || ';';
END LOOP;
EXCEPTION
WHEN EXEC_SQL.INVALID_COLUMN_NUMBER THEN
VNUMCOLS := VNUMCOLS - 1;
END;
IF VNUMCOLS = 0 THEN
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT');
MSG_ALERT('A query não retornou resultados!', 'W', FALSE);
VCONTINUA := FALSE;
EXIT;
END IF;
IF VVEZ = 1 THEN
TEXT_IO.PUT_LINE(VARQUIVO_SAIDA, VSTRING);
VVEZ := 2;
END IF;
FOR COL IN 1 .. VNUMCOLS LOOP
EXEC_SQL.DEFINE_COLUMN(VCONEXAO, VCURSOR, COL, VVALORCOLUNA, 255);
END LOOP;
WHILE EXEC_SQL.FETCH_ROWS(VCONEXAO, VCURSOR) > 0 LOOP
VSTRING := NULL;
FOR COL IN 1 .. VNUMCOLS LOOP
EXEC_SQL.COLUMN_VALUE(VCONEXAO, VCURSOR, COL, VVALORCOLUNA);
IF VVALORCOLUNA IS NOT NULL THEN
VVALORCOLUNA := LTRIM(VVALORCOLUNA);
ELSE
VVALORCOLUNA := ';';
END IF;
VSTRING := VSTRING || LTRIM(RTRIM(VVALORCOLUNA)) || ';';
END LOOP;
TEXT_IO.PUT_LINE(VARQUIVO_SAIDA, VSTRING);
END LOOP;
IF NOT EXEC_SQL.MORE_RESULT_SETS(VCONEXAO, VCURSOR) THEN
VCONTINUA := FALSE;
END IF;
END LOOP;
EXEC_SQL.CLOSE_CURSOR(VCONEXAO, VCURSOR);
TEXT_IO.FCLOSE(VARQUIVO_SAIDA);
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT');
MSG_ALERT('Arquivo gerado com sucesso!', 'I', FALSE);
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT');
HANDLE_ERROR(VCONEXAO);
END;
FUNCTION GET_DISPLAY_LENGTH(DTYPE IN PLS_INTEGER,
DLEN IN PLS_INTEGER) RETURN NUMBER IS
RETVAL PLS_INTEGER;
BEGIN
IF DTYPE = EXEC_SQL.VARCHAR2_TYPE OR
DTYPE = EXEC_SQL.CHAR_TYPE THEN
RETVAL := DLEN;
ELSIF DTYPE = EXEC_SQL.NUMBER_TYPE OR
DTYPE = EXEC_SQL.FLOAT_TYPE THEN
RETVAL := 15;
ELSIF DTYPE = EXEC_SQL.DATE_TYPE THEN
RETVAL := 15;
ELSIF DTYPE = EXEC_SQL.RAW_TYPE OR
DTYPE = EXEC_SQL.LONG_RAW_TYPE THEN
RETVAL := DLEN * 2;
ELSIF DTYPE = EXEC_SQL.ROWID_TYPE THEN
RETVAL := 20;
ELSE
RETVAL := 30;
END IF;
RETURN RETVAL;
END GET_DISPLAY_LENGTH;
PROCEDURE HANDLE_ERROR(V_CONHANDLE IN EXEC_SQL.CONNTYPE) IS
ALERT_RETVAL NUMBER;
BEGIN
MSG_ALERT('Erro ' || TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(V_CONHANDLE)) || ': ' ||
EXEC_SQL.LAST_ERROR_MESG(V_CONHANDLE),
'W',
FALSE);
RETURN;
EXCEPTION
WHEN EXEC_SQL.PACKAGE_ERROR THEN
MSG_ALERT('Ocorreu o erro ' || SQLERRM || ' na execução do pacote EXEC_SQL', 'E', TRUE);
WHEN OTHERS THEN
MSG_ALERT('Ocorreu o erro ' || SQLERRM || ' no tratamento de exceções', 'E', TRUE);
END;
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 02:37:36 CDT 2024
|