1 Reply Latest reply: May 10, 2013 10:33 AM by InoL RSS

    ORA-01007 - variable not in select list - Oracle forms 6i

    1008247
      Hi!

      I´m creating an form with one field called "txtquery". The user can write a query on this field and click on button with code above. 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!
      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!!!