CODEWITHSUNDEEP
sqloracle-databaseplsqlsqlplus
PlsqlNo
PlsqlNo

Reputation: 21

Executing anonymous block PlSQL fail

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.

Upvotes: 2

Views: 292

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

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;
/

Upvotes: 3

Related Questions