Executing anonymous block PlSQL fail

Question

I want to execute an pl/sql script via terminal but I can't manage to get it to work. It first checks whether an user exists and if he does then it copies data from some table of that user.

Problem arises when there is no user - script doesn't work because it says that table or view does not exist, and that means it somehow precompiles it, while I want it to execute line by line.

Here it is:

DECLARE
    v_count INTEGER := 0;
BEGIN
    SELECT COUNT (1) INTO v_count FROM SYS.DBA_USERS WHERE username = UPPER ('B');

    if v_count = 0 then
        DBMS_OUTPUT.put_line ('Fail');
    else
        insert into A.some_table (some_column)
            select some_column from B.some_table
                where some_column = "x";
    end if;
END;
/

it throws error that table does not exist at line select some_column from B.some_table because while it indeed does not exist (the user does not) the script wouldn't actually go there.

Tony Andrews · Accepted Answer · 2015-06-01 10:04:19Z

3

You need to use dynamic PL/SQL for the insert, so that it is not validated at compile time but only at runtime:

DECLARE
    v_count INTEGER := 0;
BEGIN
    SELECT COUNT (1) INTO v_count FROM SYS.DBA_USERS WHERE username = UPPER ('B');

    if v_count = 0 then
        DBMS_OUTPUT.put_line ('Fail');
    else
        EXECUTE IMMEDIATE
        'insert into A.some_table (some_column)
            select some_column from B.some_table
                where some_column = ''x''';
    end if;
END;
/

answered Jun 1, 2015 at 10:04

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Executing anonymous block PlSQL fail

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related