Home » Developer & Programmer » Forms » ORA-01007 - variable not in select list (Oracle forms 6i)
icon9.gif  ORA-01007 - variable not in select list [message #584185] Thu, 09 May 2013 14:30 Go to next message
flemej
Messages: 5
Registered: May 2013
Location: Brasil
Junior Member

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 #584186 is a reply to message #584185] Thu, 09 May 2013 14:44 Go to previous messageGo to next message
Littlefoot
Messages: 19347
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what Oracle says; does it help?
Oracle

ORA-01007: variable not in select list

Cause: A reference was made to a variable not listed in the SELECT clause. In OCI, this can occur if the number passed for the position parameter is less than one or greater than the number of variables in the SELECT clause in any of the following calls: DESCRIBE, NAME, or DEFINE. In SQL*Forms or SQL*Report, specifying more variables in an INTO clause than in the SELECT clause also causes this error.

Action: Determine which of the problems listed caused the problem and take appropriate action.
Re: ORA-01007 - variable not in select list [message #584188 is a reply to message #584186] Thu, 09 May 2013 14:51 Go to previous messageGo to next message
flemej
Messages: 5
Registered: May 2013
Location: Brasil
Junior Member

In fact, no...
I can´t see the problem. Any query (without "into")return this error.

[Updated on: Thu, 09 May 2013 14:53]

Report message to a moderator

Re: ORA-01007 - variable not in select list [message #584189 is a reply to message #584188] Thu, 09 May 2013 14:55 Go to previous messageGo to next message
Littlefoot
Messages: 19347
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I never wrote code as you did, so I'm not familiar with some of these statements. If you don't mind, could you explain what would you want to do?
Re: ORA-01007 - variable not in select list [message #584190 is a reply to message #584188] Thu, 09 May 2013 15:04 Go to previous messageGo to next message
flemej
Messages: 5
Registered: May 2013
Location: Brasil
Junior Member

Sure! I´m creating an form with one field called "txtquery". The user can write a query on this field and click on button with this code. The form will create a text file with the result of the query writing. I managed to create a function in oracle and it worked perfectly but I want to solve without creating any object in the database.
Sorry for my bad english!
Re: ORA-01007 - variable not in select list [message #584191 is a reply to message #584190] Thu, 09 May 2013 15:20 Go to previous messageGo to next message
Littlefoot
Messages: 19347
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Understood, thank you!

OK then, have a look at BluShadow's message in this OTN forum discussion (it is the second message). He used a stored procedure with UTL_FILE to create the result file. That procedure accepts a query (P_SQL parameter) which might be OK with you (that would be contents of your TXTQUERY item).

Kind of a drawback is the fact that the result file is located on a database server (so your user has to have access to it).

Unfortunately, I'm not good at what you are trying to do so ... can't help much. Hopefully, someone else will be able to assist.

Best of luck!
Re: ORA-01007 - variable not in select list [message #584192 is a reply to message #584191] Thu, 09 May 2013 15:33 Go to previous messageGo to next message
flemej
Messages: 5
Registered: May 2013
Location: Brasil
Junior Member

Thank you for the fast response!
The BlueShadow made the same code i wrote in database function i have created, but the diference is, on my code i declared the table fields automaticaly. I would not want to create database object.
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 messageGo to next message
flemej
Messages: 5
Registered: May 2013
Location: Brasil
Junior Member

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;

Re: ORA-01007 - variable not in select list [message #584275 is a reply to message #584270] Fri, 10 May 2013 15:28 Go to previous message
Littlefoot
Messages: 19347
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad you made it work!

Thank you for taking some time and sharing the solution with us; I'm sure someone will find it useful in the future.
Previous Topic: software for developer suit 6i
Next Topic: acrobat shortcut
Goto Forum:
  


Current Time: Fri Aug 01 05:33:40 CDT 2014

Total time taken to generate the page: 0.27172 seconds