DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to use boolean in pl/sql

  1. #1
    Join Date
    Mar 2001
    Posts
    77
    I am trying check whether or not a department number I am passing from the calling environment is valid. The return value must be boolean. Something is wrong with this program here. Can anyone explaine it to me? Thanks!!

    CREATE OR REPLACE FUNCTION valid_deptno
    (v_deptno DEPT.DEPTNO%TYPE)
    RETURN BOOLEAN
    IS

    v_valid DEPT.DEPTNO%TYPE;

    BEGIN

    SELECT deptno
    INTO v_valid
    FROM dept
    WHERE deptno = v_deptno;

    IF SQL%NOTFOUND THEN
    RETURN FALSE;
    ELSIF SQL%FOUND THEN
    RETURN TRUE;
    END IF;

    END;
    /

  2. #2
    Join Date
    Feb 2002
    Posts
    11
    Begin
    If valid_deptno(10) then
    dbms_output.put_line('True');
    else
    dbms_output.put_line('false');
    end if;
    end;

    above syntax could be used for your function, which returns a boolean.
    If valid_deptno(10) then
    or
    If not valid_deptno(10) then -> to check for -return false value

    hope it helps
    bye
    murugs


  3. #3
    Join Date
    Mar 2001
    Posts
    77
    Thanks Indian2000!

    Well, I am using this function VALID_DEPTNO to check the deptno for a procedure. There are two things I don't understand:
    1. How can I execute VALID_DEPTNO as a stand alone function in sql*plus so that I won't get the following errors?

    SQL> VARIABLE g_deptno NUMBER;
    SQL> EXECUTE :g_deptno := valid_deptno (10);
    BEGIN :g_deptno := valid_deptno (10); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 20:
    PLS-00382: expression is of wrong type
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored


    2. When I am executing the following procedure, is there a way I can avoid hard-coding the department number? Can anybody help me on this? Thanks!!
    e.g. valid_deptno (40)

    CREATE OR REPLACE PROCEDURE new_emp
    (v_ename emp.ename%TYPE)
    IS

    v_job emp.job%TYPE DEFAULT 'SALESMAN';
    v_mgr emp.mgr%TYPE DEFAULT 7839;
    v_sal emp.sal%TYPE DEFAULT 1000;
    v_comm emp.comm%TYPE DEFAULT 0;
    v_deptno emp.deptno%TYPE DEFAULT 30;

    BEGIN

    IF valid_deptno (40) THEN

    INSERT INTO emp (empno, ename, job, mgr, sal, comm, deptno)
    VALUES (seq_empno.nextval, v_ename, v_job, v_mgr, v_sal, v_comm, v_deptno);

    END IF;

    END;
    /

  4. #4
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    sql*plus command "VARIABLE" only returns

    NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
    NCHAR | NCHAR (n) | NVARCHAR2 (n) |
    CLOB | NCLOB | REFCURSOR ]


    so, as your function returns BOOLEAN, you cannot use a variable which type is NUMBER to hold the result. Try returning a varchar2. Generally a function is used in IF statements and SQL ...

    F.

  5. #5
    Join Date
    Mar 2001
    Posts
    77
    Mnemonical,

    If I understand you correctly, you suggested I use VARCHAR2 this way?

    SQL> VARIABLE g_deptno VARCHAR2(6);
    SQL> EXECUTE :g_deptno := valid_deptno (10);
    BEGIN :g_deptno := valid_deptno (10); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 20:
    PLS-00382: expression is of wrong type
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    If so, still doesn't seem to execute.

  6. #6
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282


    No, I suggested you to return varchar2 (from function) for using with VARIABLE command when declaring a varchar2 variable in sql*plus. But it's not necessary, as I told you, functions generally are used in IF statements and SQL, why do you want to declare a sql*plus variable for holding a function return? Your first script works fine the way indian2000 suggested.

    F.

  7. #7
    Join Date
    Feb 2002
    Posts
    11
    hi oraka6
    for your first question, you cannot directly execute the function from SQL> execute....... like executing the procedure. but there is way to execute the function using Dbms package(sorry I dont remember it).
    For your 2nd question, it depends where you are going to use the procedure. Like forms/report. you can call the procedure from the form assigning values from the FORMS. But when you run the from PL/SQL, you can do like this

    CREATE OR REPLACE PROCEDURE new_emp
    (v_ename emp.ename%TYPE)
    IS

    v_job emp.job%TYPE DEFAULT 'SALESMAN';
    v_mgr emp.mgr%TYPE DEFAULT 7839;
    v_sal emp.sal%TYPE DEFAULT 1000;
    v_comm emp.comm%TYPE DEFAULT 0;
    v_deptno emp.deptno%TYPE DEFAULT 30;
    a number :=&a;

    BEGIN

    IF valid_deptno (a) THEN

    INSERT INTO emp (empno, ename, job, mgr, sal, comm, deptno)
    VALUES (seq_empno.nextval, v_ename, v_job, v_mgr, v_sal, v_comm, v_deptno);

    END IF;

    END;

    this is only temporary solution or you call this proce' and pass the parameter to deptno. hope it helps
    Murugs

  8. #8
    Join Date
    Mar 2001
    Posts
    77
    O.K. Got it. I am not allow to test function with booleans in sql*plus!!

    However, is there another way to pass deptno without using ampersend (&)? Tks!

  9. #9
    Join Date
    Feb 2002
    Posts
    11
    ok you can do like this, add one in parameter on your procedure for your Deptno

    CREATE OR REPLACE PROCEDURE new_emp
    (v_ename emp.ename%TYPE,a number )
    IS

    v_job emp.job%TYPE DEFAULT 'SALESMAN';
    v_mgr emp.mgr%TYPE DEFAULT 7839;
    v_sal emp.sal%TYPE DEFAULT 1000;
    v_comm emp.comm%TYPE DEFAULT 0;
    v_deptno emp.deptno%TYPE DEFAULT 30;

    BEGIN

    IF valid_deptno (a) THEN

    INSERT INTO emp (empno, ename, job, mgr, sal, comm, deptno)
    VALUES (seq_empno.nextval, v_ename, v_job, v_mgr, v_sal, v_comm, v_deptno);

    END IF;

    END;

    and then call the procedure where ever you want and pass valriable name, deptno(In parameters) OR execute it in SQL
    sql> execute new_emp('XZY',10);

    bye
    murugs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width