Home/Database/Page 5
  1. Asked: June 23, 2016In:

    Error ORA-00933: SQL command not properly ended – update

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    hello, here you can find the cause and action to correct your statement : Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certaRead more

    hello,

    here you can find the cause and action to correct your statement :

    Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order.

    Action: Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete these spaces.

    you can use :

    [code]
    UPDATE employees e
    SET e.last_name = ‘test’
    WHERE e.department_id = (SELECT d.department_id
    FROM departments d
    WHERE d.department_id = e.department_id);
    [/code]

     

    See less
    • 0
  2. Asked: July 30, 2017In:

    How to Create the TKPROF Trace File in oracle ?

    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hello , Solution : For the TKPROF a) tkprof rawtrace.trc output_file explain=apps/apps/sort=(exeela,fchela) sys=no b) rawtrace.trc: Name of trace file output_file: tkprof out file explain: This option provides the explain plan for the SQL statements sort: This provides the sort criteria in which allRead more

    Hello ,

    Solution :

    • For the TKPROF
      a) tkprof rawtrace.trc output_file explain=apps/apps/sort=(exeela,fchela) sys=no
      b) rawtrace.trc: Name of trace file
      output_file: tkprof out file
      explain: This option provides the explain plan for the SQL statements
      sort: This provides the sort criteria in which all SQL statements will be sorted. This will bring the bad SQL at the top of the outputfile.
      sys=no: Disables SQL statements issued by user SYS

    Regards ,

    Mahmoud Morsy

    See less
    • 0
  3. Asked: July 30, 2017In:

    How to check Archive log sequence in Oracle DB 11g?

    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hi, Use the below query : [code] SELECT * FROM v$archived_log ORDER BY stamp DESC [/code] Regards, Mahmoud Morsy

    Hi,

    Use the below query :

    [code]
    SELECT *
    FROM v$archived_log
    ORDER BY stamp DESC
    [/code]

    Regards,

    Mahmoud Morsy

    See less
    • 0
  4. Asked: July 27, 2017In:

    How to kill current session in Oracle (ORA-00027 ) ?

    Mmorsy Junior mahmoudmorsymm1985@gmail.com
    This answer was edited.

    You can use this SQL statement to get the SID, SERIAL# of the current session: SELECT s.sid,s.serial#,spid,TRIM (s.machine) machine,TRIM (s.module) module,statusFROM v$session s, v$process pWHERE paddr = addr AND module IS NOT NULLORDER BY 1, 2 Then with sqlplus Run ALTER SYSTEM KILL SESSION 'sid,seRead more

    You can use this SQL statement to get the SID, SERIAL# of the current session:


    SELECT s.sid,
    s.serial#,
    spid,
    TRIM (s.machine) machine,
    TRIM (s.module) module,
    status
    FROM v$session s, v$process p
    WHERE paddr = addr AND module IS NOT NULL
    ORDER BY 1, 2

    Then with sqlplus Run

    ALTER SYSTEM KILL SESSION 'sid,serial#';

    Or by shell commnad:

    kill -9 spid.

    Regards,

    Mahmoud Morsy.

    See less
    • 0
  5. Asked: July 19, 2017In:

    “What is the difference between Views and Materialized Views in Oracle? “

    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hello Nall, The Materialized views are disk based and are updated periodically based upon the query definition. The Views are virtual only and run the query definition each time they are accessed. Regards, Mahmoud Morsy.

    Hello Nall,

    The Materialized views are disk based and are updated periodically based upon the query definition.
    The Views are virtual only and run the query definition each time they are accessed.

    Regards,

    Mahmoud Morsy.

    See less
    • 0
  6. Asked: June 19, 2016In:

    ORA-29280: utl_file.fopen invalid directory path with directory

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    utl_file.fopen takes the name of a directory, not the path. For example: (you may need to login as SYS to execute these) [code]Windows: CREATE DIRECTORY CUST_DIR AS 'C:'; Linux CREATE DIRECTORY CUST_DIR AS '/usr/tmp'; GRANT READ ON DIRECTORY CUST_DIR TO SCOTT;[/code] Then, you can refer to it in theRead more

    utl_file.fopen takes the name of a directory, not the path. For example:

    (you may need to login as SYS to execute these)

    [code]Windows:
    CREATE DIRECTORY CUST_DIR AS ‘C:’;

    Linux
    CREATE DIRECTORY CUST_DIR AS ‘/usr/tmp’;

    GRANT READ ON DIRECTORY CUST_DIR TO SCOTT;[/code]

    Then, you can refer to it in the call to fopen:

    [code]
    UTL_FILE.FOPEN(‘CUST_DIR’, ‘oraask_test.txt’, ‘W’);
    [/code]

     

    hope this help.

    See less
    • 1
  7. Asked: June 18, 2016In:

    Error: PLS-00103: Encountered the symbol when trying to compile

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    You need to change ELSEIF to ELSIF . your code would be like this : [code]declare CURSOR abc IS select * from all_Objects; begin for rec in abc loop if rec.object_id is null then null; elsif rec.owner is null then null; end if; end loop; end;[/code]

    You need to change ELSEIF to ELSIF .

    your code would be like this :

    [code]declare
    CURSOR abc IS select * from all_Objects;
    begin
    for rec in abc
    loop
    if rec.object_id is null then
    null;
    elsif rec.owner is null then
    null;
    end if;
    end loop;
    end;[/code]

    See less
    • 2
  8. Asked: April 1, 2017In:

    What is the difference between procedure and function in PL/SQL?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    1. Procedure may or may not return value where as function should return one value. 2. Function can be called from SQL statement where as procedure can't be called from the SQL statement. 3. Function are normally used for computation where as procedure are normally used for executing business logic.Read more

    1. Procedure may or may not return value where as function should return one value.

    2. Function can be called from SQL statement where as procedure can’t be called from the SQL statement.

    3. Function are normally used for computation where as procedure are normally used for executing business logic.

    4. Stored procedure is pre-compiled execution plan where as function are not.

    5. We can call function within procedure but we can not call procedure within function.

    6. A FUNCTION must be part of an executable statement, as it cannot be executed independently, whereas procedure represents an independent executable statement.

    and for example of both syntax of function and procedure :

    [code]CREATE OR REPLACE PROCEDURE test_proc
    (p_id IN VARCHAR2) as begin … end

    CREATE OR REPLACE FUNCTION test_func
    (p_id IN VARCHAR2) return varchar2 as begin … end[/code]

     

    hope this help.

    See less
    • 0
  9. Asked: April 3, 2017In:

    ORA-06550: line , column : PLS-00201: identifier must be declared

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    ORA-06550: error causes are:  You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred. in your example, you selected a value inside the variable (v_last) that is not declared in your block. So to correct your block of code, you canRead more

    ORA-06550: error causes are:  You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred.

    in your example, you selected a value inside the variable (v_last) that is not declared in your block.

    So to correct your block of code, you can rewrite it like this:

    DECLARE
    
V_LAST EMPLOYEES.LAST_NAME%TYPE;

    BEGIN

    SELECT LAST_NAME INTO V_LAST FROM EMPLOYEES;

dbms_output.put_line(‘v_last is :’ || V_LAST );

    END;
    See less
    • 0
  10. Asked: June 23, 2016In:

    how to check if the column value is number or character

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    one example to create a function to return 'Y' if parameter value is number otherwise which is exception in this case to return ('N') ex: [code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE IS l_num NUMBER; BEGIN l_num := TO_NUMBER (p_strinRead more

    one example to create a function to return ‘Y’ if parameter value is number otherwise which is exception in this case to return (‘N’)

    ex:

    [code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
    RETURN VARCHAR2
    DETERMINISTIC
    PARALLEL_ENABLE
    IS
    l_num NUMBER;
    BEGIN
    l_num := TO_NUMBER (p_string);
    RETURN ‘Y’;
    EXCEPTION
    WHEN VALUE_ERROR
    THEN
    RETURN ‘N’;
    END is_number;[/code]

    and here you can call the function created above to identify the value passed is number or not like :

    [code]SELECT (CASE
    WHEN (is_number (mycolumn) = ‘Y’)
    THEN
    ‘your column value is number’
    ELSE
    ‘your column value is not number’
    END)
    FROM myTable;[/code]

    hope this help you.

    See less
    • 0