Home/Database/Page 3
  1. Asked: January 31, 2021In:

    ORA-29275 partial multibyte character

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

    Hello Rain , Oracle Descritption for this error is given below: ORA-29275: partial multibyte character Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input. Action: Ensure that the complete multibyte character is sent from theRead more

    Hello Rain ,

    Oracle Descritption for this error is given below:
    ORA-29275:
    partial multibyte character
    Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input.
    Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW.

    Mainly reason for this type or ora error is that there is a junk/invisible or special character available ,but unfortunately the cause and action that oracle give it to us is not that much descriptive, so to investigate more and figure out the problem and fix it let’s try this:

    First try to select columns individually like below example:

    SELECT COL1 FROM TABLENAME;
SELECT COL2 FROM TABLENAME;

    until reach to the column that causing your problem and raise this ORA error.

    so far so good for now that we identified the column that has junk/invisible character we can fetch the column data and overcome this issue by using CONVERT function in oracle like below example :

    SELECT CONVERT(COL1, ‘US7ASCII’, ‘WE8ISO8859P1’) FROM TABLENAME;

    Hope that this answer was helpful. 

    See less
    • 1
  2. Asked: September 3, 2019In:

    What is the difference between searched case and simple case in oracle pl/sql?

    praveen

    1 difference I can c is, when using    case <expression> when ….(i.e simple case), u'll do an exact comparison (like a=b).  e.g case <expression> when value1 then... when value2 then...etc (i.e ur checking if expression = value1 or expression = value2 & so on) for case when <expreRead more

    1 difference I can c is, when using    case <expression> when ….(i.e simple case), u’ll do an exact comparison (like a=b).  e.g case <expression> when value1 then… when value2 then…etc (i.e ur checking if expression = value1 or expression = value2 & so on)

    for case when <expression> then .. (i.e searched case), u’ll do at least 2 comparisons i.e >= x and <=y case when variable >= x & <=y then.. when variable between a and b then…

    there may b other differences.. eager 2 know

     

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

    Using between on sysdate instead of TRUNC

    Ahmed_Shmes Junior ahmedshmes21@gmail.com

    Use TO_DATE Function. SELECT sum(column3) from table1 where column1 = ‘XXXXXAA12’ and column2 between to_date (<yor val>,<your format>) and to_date (<yor val>,<your format>);

    Use TO_DATE Function.

    SELECT sum(column3)
    from table1
    where column1 = ‘XXXXXAA12’
    and column2 between to_date (<yor val>,<your format>) and to_date (<yor val>,<your format>);

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

    How to order by number inside of character for a query like ’25 AB’ ,’30 MT’ ?

    Ahmed_Shmes Junior ahmedshmes21@gmail.com
    This answer was edited.

    Use REGEXP_SUBSTR  Syntax : REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] ) code : SELECT t.*, REGEXP_SUBSTR (COL1, '(d+)') as sort_by_col FROM test_table t Order by sort_by_col ; Result : application2 25 AB 30 AB 3125 50 50000 AS740TRead more

    Use REGEXP_SUBSTR 

    Syntax :
REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )
code :
SELECT t.*, REGEXP_SUBSTR (COL1, '(d+)') as sort_by_col
  FROM test_table t
Order by sort_by_col ;
Result :
application2
25 AB
30 AB
3125
50
50000
AS740TRN
QAS897ZCS
oraask
test
    See less
    • 0
  5. Asked: March 4, 2017In:

    How to convert text with comma separated to array in pl/sql ?

    Ahmed_Shmes Junior ahmedshmes21@gmail.com

    Use REGEXP_SUBSTR  to Extract desired info REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] ) And CONNECT BY  to loop over the string searching for the "," and use it as a delimiter. SELECT REGEXP_SUBSTR ('text1, text2, text3', '[^,]+',Read more

    Use REGEXP_SUBSTR  to Extract desired info

    REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )

    And CONNECT BY  to loop over the string searching for the “,” and use it as a delimiter.

    SELECT REGEXP_SUBSTR (‘text1, text2, text3’,
    ‘[^,]+’,
    1,
    LEVEL),LEVEL
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR (‘text1, text2, text3’,
    ‘[^,]+’,
    1,
    LEVEL)
    IS NOT NULL;

    See less
    • 0
  6. Asked: March 18, 2019In:

    Which symbol is used for concatenation? in plsql

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

    its very simple you can use || symbol to concatenate two string or using concat function and here you can find full explanation with examples about how to use oracle || - oracle concatenation in knowledge base section. have a nice day.

    its very simple you can use || symbol to concatenate two string or using concat function and here you can find full explanation with examples about how to use oracle || – oracle concatenation in knowledge base section.

    have a nice day.

    See less
    • 2
  7. Asked: March 4, 2019In:

    ORA-12705 invalid or unknown NLS parameter value specified error when connect to oracle 11g from a php application

    Googler Explorer

    Hello Waqas, ORA-12705 invalid or unknown NLS parameter value specified Cause: There are two possible causes: Either an attempt was made to issue an ALTER SESSION statement with an invalid NLS parameter or value; or the NLS_LANG environment variable contains an invalid language, territory, or characRead more

    Hello Waqas,

    ORA-12705 invalid or unknown NLS parameter value specified

    Cause: There are two possible causes: Either an attempt was made to issue an ALTER SESSION statement with an invalid NLS parameter or value; or the NLS_LANG environment variable contains an invalid language, territory, or character set.

    Action: Check the syntax of the ALTER SESSION command and the NLS parameter, correct the syntax and retry the statement, or specify correct values in the NLS_LANG environment variable. For more information about the syntax of the ALTER SESSION command, see Oracle8i SQL Reference.

    See less
    • 0
  8. Asked: October 21, 2018In:

    How to create synonym in oracle ?

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

    Hello Amira, synonyms is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schemaRead more

    Hello Amira,

    synonyms is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

    You generally use synonyms when you are granting access to an object from another schema and you don’t want the users to have to worry about knowing which schema owns the object.

    and here is the syntax to create synonym

    CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name
FOR [schema .] object_name [@ dblink];

    Example:

    CREATE PUBLIC SYNONYM emps
FOR app.employees;

    Hope this help.

    See less
    • 1
  9. Asked: October 14, 2018In:

    Is there SQL Function to add space inside string value?

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

    You can use RPAD or LPAD functions with spaces select 'Ora' || rpad(' ',5,' ') || 'Ask' from dual;  

    You can use RPAD or LPAD functions with spaces

    select 'Ora' || rpad(' ',5,' ') || 'Ask'
from dual;

     

    See less
    • 0
  10. Asked: April 30, 2017In:

    What is the difference between binary_integer and pls_integer in pl/sql?

    Googler Explorer

    Hi, binary_integer and pls_integer both are same. Both are PL/SQL datatypes with range -2,147,648,467 to 2,147,648,467. Compared to integer and binary_integer pls_integer very fast in excution. Because pls_intger operates on machine arithmetic and binary_integer operes on library arithmetic. pls_intRead more

    Hi,

    binary_integer and pls_integer both are same. Both are PL/SQL datatypes with range -2,147,648,467 to 2,147,648,467.

    Compared to integer and binary_integer pls_integer very fast in excution. Because pls_intger operates on machine arithmetic and binary_integer operes on library arithmetic.

    pls_integer comes from oracle10g.

    binary_integer allows indexing integer for assocative arrays prior to oracle9i.

    See less
    • 0