Home/Database/PL/SQL/Page 2
  1. 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
  2. Asked: June 10, 2017In:

    How to check and test read/write permissions of Oracle directory?

    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Check the below query :SELECT * FROM all_tab_privs WHERE table_name = 'your_directory';

    Check the below query :

    SELECT * FROM all_tab_privs WHERE table_name = ‘your_directory’;

    See less
    • -1
  3. Asked: March 4, 2017In:

    How to execute multiple procedures simultaneously ?

    ashishpandey Explorer

    Hi,   Its better you can create different jobs for each procedure and setting their time same to start execution.

    Hi,

     

    Its better you can create different jobs for each procedure and setting their time same to start execution.

    See less
    • 0
  4. Asked: March 6, 2017In:

    How to use COMMIT or ROLLBACK inside database trigger ?

    ashishpandey Explorer

    Hi, You can creater a procedure to insert into your test_table_log table with parameter of the columns of thie table.   Then on your test_table table trigger call this procedure by passing the values. Commit will be in your calling procedure. So it will work.

    Hi,

    You can creater a procedure to insert into your test_table_log table with parameter of the columns of thie table.

     

    Then on your test_table table trigger call this procedure by passing the values.

    Commit will be in your calling procedure. So it will work.

    See less
    • 0
  5. Asked: June 20, 2016In:

    Return more than one row from stored procedure in pl/sql

    ashishpandey Explorer

    CREATE OR REPLACE PROCEDURE pr_return_multiple_out(p_row_number VARCHAR2) IS CURSOR c0 IS SELECT pol_no, pol_assr_name FROM pgit_policy WHERE ROWNUM <= p_row_number; BEGIN FOR i IN c0 LOOP dbms_output.put_line(i.pol_no||' : '||i.pol_assr_name); END LOOP; EXCEPTION WHEN OTHERS THEN dbms_output.putRead more

    CREATE OR REPLACE PROCEDURE pr_return_multiple_out(p_row_number VARCHAR2) IS
    CURSOR c0 IS
    SELECT pol_no, pol_assr_name FROM pgit_policy WHERE ROWNUM <= p_row_number;
    BEGIN
    FOR i IN c0 LOOP
    dbms_output.put_line(i.pol_no||’ : ‘||i.pol_assr_name);
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    END pr_return_multiple_out;
    /

    —————-Calling by passing number of output we want

    BEGIN
    pr_return_multiple_out(4);
    END;
    /

    See less
    • 1
  6. Asked: June 24, 2016In:

    How to INSERT row when only doesn't exist ?

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

    hello albert, as mr. sandeeptiwari mentioned on your question as comment you can use merge statement and here i will share with you sample example for merge and not exists with regular insert statement first we need to create dummy table to test our code on it : CREATE TABLE testtab ( id NUMBER PRIMRead more

    hello albert,
    as mr. sandeeptiwari mentioned on your question as comment you can use merge statement and here i will share with you sample example for merge and not exists with regular insert statement
    first we need to create dummy table to test our code on it :

    CREATE TABLE testtab
(
 id NUMBER PRIMARY KEY
 ,last_name VARCHAR2 (200)
);

    now we will using not exists clause with regular insert statement but with select like this :

    INSERT INTO testtab
  SELECT 1, 'albert'
  FROM   dual
  WHERE  NOT EXISTS
           (SELECT NULL
            FROM   testtab
            WHERE  last_name = 'albert');

    now the result would be :
    1 row created.
    then trying to execute same statement again and check the result :
    0 rows created.
    that because the row is already exists before

    now we will trying to do the same but in this time we will using merge statement :

    MERGE INTO testtab a
USING      (SELECT 2 id, 'jonny' last_name FROM dual) b
ON         (a.last_name = b.last_name)
WHEN NOT MATCHED THEN
  INSERT     (id, last_name)
  VALUES     (b.id, b.last_name);

    now the result would be :
    1 row merged.
    then trying to execute same statement again and check the result :
    0 row merged.

    hope this help 🙂

    See less
    • 0
  7. Asked: July 31, 2016In:

    How to Raise User-Defined Exception and display Custom SQLERRM ?

    Saly Explorer

    thanks so much to @Stephan Borsodi for this solution

    thanks so much to @Stephan Borsodi for this solution

    See less
    • 0
  8. Asked: July 31, 2016In:

    How to know number of rows updated by UPDATE statement ?

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

    SQL%ROWCOUNT :  return the number of rows fetched/processed by the last DML executed. If the DML fails after fetching 1 row, due to any reason, SQL%ROWCOUNT will return only 1, the number of rows fetched/processed so far basically you can use : BEGIN  UPDATE xx_test_tab  SET    test_col = 777777  WHRead more

    SQL%ROWCOUNT :  return the number of rows fetched/processed by the last DML executed. If the DML fails after fetching 1 row, due to any reason, SQL%ROWCOUNT will return only 1, the number of rows fetched/processed so far
    basically you can use :

    BEGIN
      UPDATE xx_test_tab
      SET    test_col = 777777
      WHERE  col2 LIKE '2%';

      DBMS_OUTPUT.PUT_LINE(TO_Char(SQL%ROWCOUNT)||' rows affected.');
    END;

    hope this help 🙂

    See less
    • 0
  9. Asked: August 1, 2016In:

    Can i use select statement inside if statement ? (if (select) > 0 then) ?

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

    hello Beter, you can't use SQL Statement directly in a PL/SQL expression instead you can use variable to store your count then use your variable into PL/SQL expression like this : DECLARE  v_count   NUMBER;BEGIN  SELECT count (*) INTO v_count FROM dual;  IF v_count >= 1 THEN    dbms_output.put_liRead more

    hello Beter,
    you can’t use SQL Statement directly in a PL/SQL expression instead you can use variable to store your count then use your variable into PL/SQL expression like this :

    DECLARE
      v_count   NUMBER;
    BEGIN
      SELECT count (*) INTO v_count FROM dual;

      IF v_count >= 1 THEN
        dbms_output.put_line ('true');
      ELSE
        dbms_output.put_line ('false');
      END IF;
    END;

    or as you mentioned if you want to use it inside delete statement like this :

    DELETE FROM table_1
    WHERE       (SELECT count (*) FROM table_2) >= 1;

    hope this helpful 🙂

    See less
    • 0
  10. Asked: August 14, 2016In:

    How can i convert CLOBS TO VARCHAR2 column ?

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

    hello Beter, you can use dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte ); and here basic two example : first by using SQL but note the max length is 4000 characters: select dbms_lob.substr( clob_col, 4000, 1 ) from Tablename; second by using PL/SQL max length is 32000 charactersRead more

    hello Beter,
    you can use

    dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );

    and here basic two example :
    first by using SQL but note the max length is 4000 characters:

    select dbms_lob.substr( clob_col, 4000, 1 ) from Tablename;

    second by using PL/SQL max length is 32000 characters :

    DECLARE
  var1   LONG;
BEGIN
  FOR i IN (SELECT clob_col FROM tablename)
  LOOP
    var1 := dbms_lob.substr (i.clob_col, 32000, 1);
  END LOOP;
END;

    hope this may help.
    you can refer to DBMS_LOB from oracle

    See less
    • 0