Hello guys,
First I want to say sorry for any mistake in English, is that I do not speak English very well, I'm Brazilian.
So let's get to my problem:
I have always worked with T-SQL in SQL Server, and now I'm having the need to work with PL / SQL in Oracle, so I'm trying to make a simple script that tests a table exists and, if not, the script should create the table and, if so, it just assigns the value of a field in a variable and display the value of it. Well, the fact is that when I run through the PL / SQL Developer, an error occurs stating that the table does not exist, however, with already informed above, the script should create the table if it does not exist, and that is exactly what I am not understand because in my opinion there is no problem in the script syntax or semantics and yet Oracle is criticizing.
Below is the script with dummy table:
Declare
i number;
v_date date;
v_name VARCHAR2(40);
BEGIN
SELECT count(*) INTO i FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OBJECT_NAME LIKE '%TEST%';
IF I < 1 THEN
DBMS_OUTPUT.PUT_LINE('Entered the IF');
EXECUTE IMMEDIATE 'CREATE TABLE DBO.TEST (NAME VARCHAR2(40))';
ELSE
DBMS_OUTPUT.PUT_LINE('Entered the else');
SELECT name INTO v_name from DBO.TESTE;
END IF;
END;