ORA-00904: invalid identifier - Not able to run EXECUTE IMMEDIATE in PL/SQL procedure, Not able to execute a query in Oracle11g Options

[kausty88]

I have searched thoroughly various forums and site and I think the mistake I am making in my code is not using Bind Variable. Not sure if that is the only way out, but after lot of research I think that might be it.

Here is the code I am using:

CREATE OR REPLACE
PROCEDURE CUBE_VIEW (VAR_DT IN VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER
AS
START_DATE NUMBER;
END_DATE NUMBER;
VAR DATE;
BEGIN
IF VAR_DT IS NULL THEN
VAR:= SYSDATE;
ELSE
VAR:= TO_DATE(VAR_DT);
END IF;
SELECT TO_NUMBER(TO_CHAR(TRUNC(TRUNC(TO_DATE((SELECT ADD_MONTHS((SELECT TO_DATE(VAR,'DD-MON-YY') + (6-TO_NUMBER(TO_CHAR(TO_DATE(VAR,'DD-MON-YY')+1,'D'))) FROM DUAL),-34) FROM DUAL),'DD-MON-YY'),'MM')-1,'MM'),'YYMMDD')) INTO START_DATE FROM DUAL;
SELECT TO_NUMBER(TO_CHAR(TRUNC(TRUNC(TO_DATE((SELECT ADD_MONTHS((SELECT TO_DATE(VAR,'DD-MON-YY') + (6-TO_NUMBER(TO_CHAR(TO_DATE(VAR,'DD-MON-YY')+1,'D'))) FROM DUAL),-1) FROM DUAL),'DD-MON-YY'),'MM')-1,'MM'),'YYMMDD')) INTO END_DATE FROM DUAL;
DBMS_OUTPUT.PUT_LINE('START DATE IS ' || START_DATE);
DBMS_OUTPUT.PUT_LINE('END DATE IS ' || END_DATE);
--EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW F_SURVEY_YESTERDAY AS SELECT * FROM F_SURVEY_MULTICOL WHERE FLIGHTDATE BETWEEN 090300 AND 111201';
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW RESPONSE_YESTERDAY AS SELECT * FROM RESPONSE_SMALL WHERE FLIGHTDATE BETWEEN START_DATE AND END_DATE';
END CUBE_VIEW;

The code complies successfully but when I am executing the code, here is the error I get. The code works successfully when I manually insert the values (as done in the commented out statement) but when use it from variable, it gives me error. Please help me on this.

Connecting to the database LocalEnvironment.
ORA-00904: "END_DATE": invalid identifier
ORA-06512: at "TESTING.CUBE_VIEW", line 18
ORA-06512: at line 6
START DATE IS 90301
END DATE IS 111201
Process exited.
Disconnecting from the database LocalEnvironment.

[burleson]

Hi,

>> I have searched thoroughly various forums and site and I think the mistake I am making in my code is not using Bind Variable.

Your BIG mistake is not formsatting thsi mess for readability!

PLEASE READ THIS!

http://www.dba-oracle.com/t_sql_statements_formatting.htm

I always put select, from and where alone on separate lines, and indent threee spaces.

As written Albert Eienstein could not figure-out your code!

CODE

CREATE OR REPLACE PROCEDURE
   CUBE_VIEW (VAR_DT IN VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER
AS
START_DATE       NUMBER;
END_DATE          NUMBER;
VAR DATE;
BEGIN
declare cursor c1 as

SELECT
   TO_NUMBER(
      TO_CHAR(
         TRUNC(
            TRUNC(
               TO_DATE(
               (SELECT
                   ADD_MONTHS(
                     (SELECT TO_DATE(VAR,'DD-MON-YY') +
                     (6-TO_NUMBER(TO_CHAR(TO_DATE(VAR,'DD-MON-YY')+1,'D')))
               FROM
               DUAL),-34) FROM DUAL),'DD-MON-YY'),'MM')-1,'MM'),'YYMMDD')) INTO START_DATE FROM DUAL;

create cursor c2 as
SELECT
    TO_NUMBER(TO_CHAR(TRUNC(TRUNC(TO_DATE((SELECT ADD_MONTHS((SELECT TO_DATE(VAR,'DD-MON-YY') + (6-TO_NUMBER(TO_CHAR(TO_DATE(VAR,'DD-MON-YY')+1,'D'))) FROM DUAL),-1) FROM DUAL),'DD-MON-YY'),'MM')-1,'MM'),'YYMMDD')) INTO END_DATE FROM DUAL;
DBMS_OUTPUT.PUT_LINE('START DATE IS ' || START_DATE);
DBMS_OUTPUT.PUT_LINE('END DATE IS ' || END_DATE);
--EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW F_SURVEY_YESTERDAY AS SELECT * FROM F_SURVEY_MULTICOL WHERE FLIGHTDATE BETWEEN 090300 AND 111201';
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW RESPONSE_YESTERDAY AS SELECT * FROM RESPONSE_SMALL WHERE FLIGHTDATE BETWEEN START_DATE AND END_DATE';
END CUBE_VIEW;


IF VAR_DT IS NULL THEN
VAR:= SYSDATE;
ELSE
VAR:= TO_DATE(VAR_DT);
END IF;

[burleson]

Hi again,

If you are learning PL/SQL you MUST bet the Easy PL/SQL book:

http://www.rampant-books.com/book_0501_easy_plsql.htm

*******************************************************
>> ORA-06512: at "TESTING.CUBE_VIEW", line 18

Your error is on line 18 of your original code

>> ORA-00904: "END_DATE": invalid identifier

You need to start by lookuoping up the ora-00904 error:

ORA-00904 string: invalid identifier

Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name.

A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #.

If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

Please read thois to see solution:

http://www.dba-oracle.com/t_ora_00904_stri..._identifier.htm

[burleson]

Does this run OK in SQL*Plus, by itself?

CODE

SELECT TO_NUMBER(TO_CHAR(TRUNC(TRUNC(TO_DATE((SELECT ADD_MONTHS((SELECT TO_DATE(VAR,'DD-MON-YY') + (6-TO_NUMBER(TO_CHAR(TO_DATE(VAR,'DD-MON-YY')+1,'D'))) FROM DUAL),-34) FROM DUAL),'DD-MON-YY'),'MM')-1,'MM'),'YYMMDD')) INTO START_DATE FROM DUAL;
SELECT TO_NUMBER(TO_CHAR(TRUNC(TRUNC(TO_DATE((SELECT ADD_MONTHS((SELECT TO_DATE(VAR,'DD-MON-YY') + (6-TO_NUMBER(TO_CHAR(TO_DATE(VAR,'DD-MON-YY')+1,'D'))) FROM DUAL),-1) FROM DUAL),'DD-MON-YY'),'MM')-1,'MM'),'YYMMDD')) INTO END_DATE FROM DUAL;