This discussion is archived
14 Replies Latest reply: Dec 3, 2012 11:14 PM by Girish Sharma RSS

Create Or Replace Function Error

977748 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Good. Please close the thread.

    Regards
    Girish Sharma

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points