14 Replies Latest reply: Dec 4, 2012 1:14 AM by Girish Sharma RSS

    Create Or Replace Function Error

    977748
      Hello,

      I'm doing text mining using Oracle SQL Developer: ODMiner.. I imported the data "WEBLOG" into a table.. This weblog data consist of users activity, date, time, url, etc. The first step I took was to use a function to transform date and time that I have in the data table, into a number representing the 40 mins since 01-01-1990. I did this by dividing it by 2400 (seconds in 40 mins). The main purpose is to have a time frame for the sessions.
      I used the following code,

      CREATE OR REPLACE FUNCTION ssnDate(
      DATE IN VARCHAR2 DEFAULT 03-01-18,
      TIME IN VARCHAR2
      ) RETURN NUMBER
      AS
      BEGIN
      RETURN TRUNC((to_date(DATE||' '||TIME, 'DD-MM-YY HH:MM:SS')- to_date('01-JAN-1990','DD-MON-YYYY')) * (86400/2400);
      END ssnDate;

      This was what appeared in the log after running the statement,

      FUNCTION ssnDate compiled
      Warning: execution completed with warning

      After this, I tried to create a VIEW to transform the DATE and TIME with the ssnDate that was created earlier on, and concatenate the CS_URI_STEM (which is the resource accessed), and CS_URI_QUERY (which is the the query, if any, the client was trying to perform)into a new field called WEB_LINK.

      This is the code used,

      CREATE OR REPLACE VIEW WEBLOG_VIEWS("C_IP", "WEB_LINK", "CS_USER_AGENT", "SESSION")
      AS
      SELECT ssnDate(LOG_DATE, LOG_TIME) AS 'SESSION',
      C_IP,
      CS_USER_AGENT,
      (CS_URI_STEM||'?'||CS_URI_QUERY) AS WEB_LINK
      FROM WEBLOG;

      Now from this I got the following error..

      Error starting at line 1 in command:
      CREATE OR REPLACE VIEW WEBLOG_VIEWS("C_IP", "WEB_LINK", "CS_USER_AGENT", "SESSION")
      AS
      SELECT ssnDate(LOG_DATE, LOG_TIME) AS 'SESSION',
      C_IP,
      CS_USER_AGENT,
      (CS_URI_STEM||'?'||CS_URI_QUERY) AS WEB_LINK
      FROM WEBLOG
      Error at Command Line:3 Column:38
      Error report:
      SQL Error: ORA-00923: FROM keyword not found where expected
      00923. 00000 - "FROM keyword not found where expected"
      *Cause:
      *Action:

      I don't get where I'm going wrong with this.. This is the data preparation stage which requires me to prep the data before applying modeling techniques or algorithms.. The next step would be grouping the data, based on the session time, ip and the user agent of each session along with the web_links fields visited by the user in that session.

      I would really be grateful for any inputs on where I'm going wrong and any solutions for that!
        • 1. Re: Create Or Replace Function Error
          rp0428
          Welcome to the forum!

          Whenever you post provide your 4 digit Oracle version.
          >
          CREATE OR REPLACE FUNCTION ssnDate(
          DATE IN VARCHAR2 DEFAULT 03-01-18,
          TIME IN VARCHAR2
          ) RETURN NUMBER
          AS
          BEGIN
          RETURN TRUNC((to_date(DATE||' '||TIME, 'DD-MM-YY HH:MM:SS')- to_date('01-JAN-1990','DD-MON-YYYY')) * (86400/2400);
          END ssnDate;
          >
          You are using 'DATE' and 'TIME' as the names of parameters and in the RETURN statement. Those are reserved words; change them to something else, perhaps P_DATE and P_TIME.

          If you do a sho errr in sql*plus you will see where the actual error is. You are missing a right parentheses just before the semicolon on the RETURN statement. Instead of the line above use
          RETURN TRUNC((to_date(P_DATE||' '||P_TIME, 'DD-MM-YY HH:MM:SS')- to_date('01-JAN-1990','DD-MON-YYYY')) * (86400/2400));
          • 2. Re: Create Or Replace Function Error
            977748
            Thank you for your quick reply! I tried what you suggested, but it keeps displaying the same message in the log.. execution completed with warning.

            Edited by: 974745 on Dec 3, 2012 7:11 PM
            • 3. Re: Create Or Replace Function Error
              sb92075
              fix the error

              How do I ask a question on the forums?
              SQL and PL/SQL FAQ
              • 4. Re: Create Or Replace Function Error
                rp0428
                >
                Thank you for your quick reply! I tried what you suggested, but it keeps displaying the same message in the log.. execution completed with warning
                >
                You can't just say 'I tried what you suggested'. You have to provide information about EXACTLY everything you did, step by step.

                Well you couldn't have because after I added the extra ')' at the end it compiled without any errors for me.

                And you haven't posted your 4 digit Oracle version.
                • 5. Re: Create Or Replace Function Error
                  Girish Sharma
                  Your function code should be something like this :
                  CREATE OR REPLACE FUNCTION ssnDate(
                  P_DATE IN VARCHAR2 DEFAULT '03-01-18',
                  P_TIME IN VARCHAR2
                  ) RETURN NUMBER
                  AS
                  BEGIN
                  RETURN to_number(trunc(to_date(p_date || p_time,'dd-mm-yy HH24:MI:SS')) - to_date('01-JAN-1990','DD-MON-YYYY'))*36;
                  END;
                  /
                  SQL> select ssndate('03-12-12','10:52:22') colA from dual;
                  
                        COLA
                  ----------
                      301392
                  SQL>
                  As Rp said above you should not used Oracle reserve word neither in PL/SQL nor for any objects. You can query V$RESERVED_WORDS for reserved words.

                  So, this is for function part. Now if you gets any error when you use it with creating view let us know what error you now getting.

                  Regards
                  Girish Sharma
                  • 6. Re: Create Or Replace Function Error
                    977748
                    Alright.. like I mentioned before, I imported the weblog.csv data into ODMiner. That resulted in a table named "WEBLOG", where I adjusted the name and data type of the columns which is as follows,

                    1. log_date date
                    2. log_time time
                    3. c_ip character (15)
                    4. cs_username character (1)
                    5. sc_method character (4)
                    6. cs_uri_stem character (56)
                    7. cs_uri_query character (255)
                    8. sc_status integer
                    9. cs_host character (19)
                    10. cs_user_agent character (157)
                    11. cs_cookie character (143)
                    12. cs_referrer character (192)

                    After this, I worked on the ODMiner worksheet where I did the SQL queries. I needed to transform the LOG_DATE and LOG_TIME in the WEBLOG table into a number representing the 40 mins since 1-01-1990. I did this by dividing 2400 (seconds in 40 mins) to get a time frame for a session. For this I did the following SQL query,

                    CREATE OR REPLACE FUNCTION ssnDate(
                    DATE IN VARCHAR2 DEFAULT 03-01-18
                    , TIME IN VARCHAR2
                    ) RETURN NUMBER
                    AS
                    BEGIN
                    RETURN TRUNC((to_date(P_DATE||' '||P_TIME, 'DD-MM-YY HH:MM:SS')- to_date('01-JAN-1990','DD-MON-YYYY')) * (86400/2400));
                    END ssnDate;

                    After running this statement, I got the following message,

                    FUNCTION ssnDate compiled
                    Warning: execution completed with warning

                    After this step, I tried to create a VIEW with the following statement,

                    CREATE OR REPLACE VIEW WEBLOG_VIEWS("C_IP", "WEB_LINK", "CS_USER_AGENT", "SESSION")
                    AS
                    SELECT ssnDate(LOG_DATE, LOG_TIME) AS SESSION,
                    C_IP,
                    CS_USER_AGENT,
                    (CS_URI_STEM||'?'||CS_URI_QUERY) AS WEB_LINK
                    FROM WEBLOG;

                    The error message I got here was,

                    Error at Command Line:3 Column:38
                    Error report:
                    SQL Error: ORA-00923: FROM keyword not found where expected
                    00923. 00000 - "FROM keyword not found where expected"
                    *Cause:   
                    *Action:

                    The Oracle version is 11.2.0.1.0
                    I hope this is understandable enough..
                    • 7. Re: Create Or Replace Function Error
                      977748
                      Thank you, it compiled successfully this time.
                      But I still have the same problem with the VIEW query.
                      • 8. Re: Create Or Replace Function Error
                        Girish Sharma
                        Ok. Post the output of below commands:
                        SQL> set long 999999
                        SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','WEBLOG','WEBLOG_TABLE_OWNER_NAME') FROM DUAL;
                        'WEBLOG_TABLE_OWNER_NAME' is just an example, my intention is here write the name of owner of weblog table in UPPER CASE letters.

                        Regards
                        Girish Sharma
                        • 9. Re: Create Or Replace Function Error
                          977748
                          Ok, not sure I really understand, but I posted the query and this is the output I got..

                          ORA-31603: object "WEBLOG" of type TABLE not found in schema "WEBLOG_TABLE_OWNER_NAME"
                          ORA-06512: at "SYS.DBMS_METADATA", line 2625
                          ORA-06512: at "SYS.DBMS_METADATA", line 2668
                          ORA-06512: at "SYS.DBMS_METADATA", line 2983
                          ORA-06512: at "SYS.DBMS_METADATA", line 3897
                          ORA-06512: at "SYS.DBMS_METADATA", line 5678
                          ORA-06512: at line 1
                          31603. 00000 - "object \"%s\" of type %s not found in schema \"%s\""
                          *Cause:    The specified object was not found in the database.
                          *Action:   Correct the object specification and try the call again.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                          • 10. Re: Create Or Replace Function Error
                            Girish Sharma
                            Ok. Post output of below commands :
                            1. SQL>show user;
                            2. SQL>select owner from dba_tables where table_name='WEBLOG';
                            3. SQL>select table_name from user_tables where table_name='WEBLOG';

                            If you gets the output of 3rd command then remove XYZ from below command the mention output of 1st command and post output of below command :
                            SELECT DBMS_METADATA.GET_DDL('TABLE','WEBLOG','XYZ') FROM DUAL;

                            If you gets the output of 2nd as ORA-00942 then remove XYZ from below command and mention output of 1st command and post output of below command :
                            SELECT DBMS_METADATA.GET_DDL('TABLE','WEBLOG','XYZ') FROM DUAL;

                            If you gets the output of 2nd then remove XYZ from below command and mention output of 2nd command and post output of below command :
                            SELECT DBMS_METADATA.GET_DDL('TABLE','WEBLOG','XYZ') FROM DUAL;

                            I mean, I am just trying to find the name of schema in which WEBLOG table exists. Got it ?

                            Regards
                            Girish Sharma
                            • 11. Re: Create Or Replace Function Error
                              977748
                              Ok..

                              For the first command, the output is,

                              USER is DMUSER

                              For the second command, I got the error ORA-00942, and I tried what you said, and got,

                              "
                              CREATE TABLE "DMUSER"."WEBLOG"
                              (     "LOG_DATE" DATE,
                                   "LOG_TIME" TIMESTAMP (6),
                                   "C_IP" VARCHAR2(15),
                                   "CS_USERNAME" VARCHAR2(1),
                                   "SC_METHOD" VARCHAR2(4),
                                   "CS_URI_STEM" VARCHAR2(56),
                                   "CS_URI_QUERY" VARCHAR2(437),
                                   "SC_STATUS" NUMBER(*,0),
                                   "CS_HOST" CHAR(19),
                                   "CS_USER_AGENT" VARCHAR2(157),
                                   "CS_COOKIE" VARCHAR2(143),
                                   "CS_REFERRER" VARCHAR2(453)
                              ) SEGMENT CREATION DEFERRED
                              PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                              TABLESPACE "USERS"
                              "

                              As for the third command, I got a column with a TABLE_NAME heading and WEBLOG as the first row.
                              • 12. Re: Create Or Replace Function Error
                                Girish Sharma
                                Ok, Good. Post the output of below command. I think it finally created view ..
                                CREATE OR REPLACE VIEW WEBLOG_VIEWS("C_IP", "WEB_LINK", "CS_USER_AGENT", "SESSION")
                                AS
                                SELECT ssnDate(LOG_DATE, LOG_TIME) AS "SESSION",
                                C_IP,
                                CS_USER_AGENT,
                                (CS_URI_STEM||'?'||CS_URI_QUERY) AS "WEB_LINK"
                                FROM WEBLOG
                                /
                                
                                SQL> select * from weblog_views;
                                
                                no rows selected
                                I shall get no rows because there are no rows in my table, while I hope you will get output.

                                Regards
                                Girish Sharma
                                • 13. Re: Create Or Replace Function Error
                                  977748
                                  Yes, it now shows in it's output the 4 columns, C_IP, WEB_LINK, CS_USER_AGENT, and SESSION.

                                  Thank you so much for your help and patience!
                                  • 14. Re: Create Or Replace Function Error
                                    Girish Sharma
                                    Good. Please close the thread.

                                    Regards
                                    Girish Sharma