0

I'm trying to run a script of oracle functions to add them to my database, but even though I've tried both Navicat and SQL Developer to add them correctly, the functions are all added in the same function. I tried to finish each function with GO, but it has not worked either. What can you recommend?

Example Script

<code> REATE
FUNCTION "ASCII_RAW" (
    p_raw VARCHAR2)
  RETURN NUMBER
IS
  v_result NUMBER := 0;
BEGIN
  FOR i IN 1..LENGTH(p_raw)
  LOOP
    v_result := v_result * 256 + ASCII(SUBSTR(p_raw, i, 1));
  END LOOP;
  RETURN v_result;
END;;

CREATE
FUNCTION "BITXOR" (
    p_dec1 NUMBER,
    p_dec2 NUMBER)
  RETURN VARCHAR2
IS
BEGIN
  RETURN ASCII_RAW(utl_raw.cast_to_varchar2(utl_raw.bit_xor( raw_ascii(p_dec1), raw_ascii(p_dec2))));
END;;

CREATE
FUNCTION "CHAR_TO_HEX" (STR_IN VARCHAR2) RETURN VARCHAR2 IS

  V_RESULT VARCHAR2(10);

BEGIN
  V_RESULT := CASE
                WHEN STR_IN = '0' THEN '0000'
                WHEN STR_IN = '1' THEN '0001'
                WHEN STR_IN = '2' THEN '0010'
                WHEN STR_IN = '3' THEN '0011'
                WHEN STR_IN = '4' THEN '0100'
                WHEN STR_IN = '5' THEN '0101'
                WHEN STR_IN = '6' THEN '0110'
                WHEN STR_IN = '7' THEN '0111'
                WHEN STR_IN = '8' THEN '1000'
                WHEN STR_IN = '9' THEN '1001'
                WHEN STR_IN = 'A' THEN '1010'
                WHEN STR_IN = 'B' THEN '1011'
                WHEN STR_IN = 'C' THEN '1100'
                WHEN STR_IN = 'D' THEN '1101'
                WHEN STR_IN = 'E' THEN '1110'
                WHEN STR_IN = 'F' THEN '1111'                
                ELSE '-1'
              END;              
  RETURN V_RESULT;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);  
END;;</code>
Alberto Tormos
  • 1
  • 1
    I don't recognize the ";;" at the end of the sql block; perhaps that is a Navicat convention? Oracle requires a single semi-colon to end a statement. Oracle scripts also require the "/" (left-justified on a line by itself) to end a named code block and compile the buffer content. And if you use SQL*Plus, empty lines can become problematic when appearing between code blocks. – Stilgar Jul 17 '18 at 21:02
  • The ;; It is a Navicat convention when a comparison of the data in the database is made. By default it is double ;; at the end of each sentence. But thank you very much Stilgar, the / to finish the blocks is the solution. – Alberto Tormos Jul 18 '18 at 14:39

0 Answers0