Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> ORA-00904: invalid identifier - Not able to run EXECUTE IMMEDIATE in PL/SQL procedure, Not able to execute a query in Oracle11g
kausty88
post Feb 4 2012, 04:01 PM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 4-February 12
Member No.: 46,713



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.
Go to the top of the page
 
+Quote Post
burleson
post Feb 4 2012, 04:53 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,314
Joined: 26-January 04
Member No.: 13



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;


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
burleson
post Feb 4 2012, 04:59 PM
Post #3


Advanced Member
***

Group: Members
Posts: 11,314
Joined: 26-January 04
Member No.: 13



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:

QUOTE
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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
burleson
post Feb 4 2012, 05:01 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,314
Joined: 26-January 04
Member No.: 13



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;


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd July 2014 - 02:22 AM