1

i wanna ask again about my procedure, this is the example:

first,

CREATE OR REPLACE procedure proc10(pnik in varchar2, pposition in varchar2)
IS
v_error_message varchar2(255);
CURSOR cuser IS
    SELECT userid FROM mstuser WHERE nik =  pnik; 
CURSOR crole IS
    SELECT distinct role FROM mstrole;
BEGIN
    FOR cdata IN cuser
    LOOP
        BEGIN
            IF pposition IN crole THEN
                BEGIN
                    .........
                END;
            END IF;
        END;
    END LOOP;
END;

and it has error like this:

[Error] PLS-00405 (11: 29): PLS-00405: subquery not allowed in this context

then i put the subquery into a cursor like this:

CREATE OR REPLACE procedure proc10(pnik in varchar2, pposition in varchar2)
IS
v_error_message varchar2(255);
CURSOR cuser IS
    SELECT userid FROM mstuser WHERE nik =  pnik; 
CURSOR crole IS
    SELECT distinct role FROM mstrole;
BEGIN
    FOR cdata IN cuser
    LOOP
        BEGIN
            IF pposition IN crole THEN
                BEGIN
                    .........
                END;
            END IF;
        END;
    END LOOP;
END;

but when i execute this procedure it has error like this:

[Error] PLS-00103 (13: 29): PLS-00103: Encountered the symbol "CROLE" when expecting one of the following:  (

and i put the parentheses on "CROLE" (advice from @PM 77-1) like this:

CREATE OR REPLACE procedure proc10(pnik in varchar2, pposition in varchar2)
IS
v_error_message varchar2(255);
CURSOR cuser IS
    SELECT userid FROM mstuser WHERE nik =  pnik; 
CURSOR crole IS
    SELECT distinct role FROM mstrole;
BEGIN
    FOR cdata IN cuser
    LOOP
        BEGIN
            IF pposition IN (crole) THEN
                BEGIN
                    .........
                END;
            END IF;
        END;
    END LOOP;
END;

and it has new error like this:

[Error] PLS-00320 (13: 30): PLS-00320: the declaration of the type of this expression is incomplete or malformed

what should i do to fix this? please help thanks before.

Ade Rianto
  • 31
  • 6
  • `IN (crole)` you forgot parentheses – PM 77-1 Apr 08 '16 at 01:42
  • thanks for the answer @PM77-1 i put parentheses there `IF pposition IN (crole) THEN` it has new error like this : `[Error] PLS-00320 (13: 30): PLS-00320: the declaration of the type of this expression is incomplete or malformed` please help – Ade Rianto Apr 08 '16 at 01:56
  • Add your current code to your question. – PM 77-1 Apr 08 '16 at 02:20
  • @PM77-1 the question is updated sir. i put parentheses there : `IF position IN (crole) THEN` – Ade Rianto Apr 08 '16 at 03:51
  • Your cursor variable crole has to be opened and then fetched since you are using explicit cursor and then using it in a if statement. Here's a simple example http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html – cableload Apr 08 '16 at 03:57

0 Answers0