Pl Sql Personal Marco Melara

Front 1

Test Sync Iphone

Back 1

I worked

Front 2

Sintax PRAGMA Exeption_init ()

Back 2

Declare
seuErro EXCEPTION;
PRAGMA EXCEPTION_INIT (seuErro , -2292)
-- ONDE -2292 É O NUMERO DO ERRO ORACLE

Front 3

Delta Between treated and non treated execeptions.

Back 3

Treated exceptions are declared in the EXEPTION block of a PL SQL it permits the PL sql to continue processing even though and exeption occured. A non treated exception can be ppre defined and non predefined and cause oracle to stop the PL/Sql code.

Front 4

Delta between Predefined and non predefined exeptions.

Back 4

Predefined exceptions are default oracle exeptions and non predefined are exceptions created by the user.

Front 5

Cite 5 examples of predefined exceptions:

Back 5

ACCESS_INTO_NULL
CASE_NOT_FOUND
COLLECTION_IS_NULL
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGIN_DENIED
NO_DATA_FOUND
NOT_LOGGED_ON
PROGRAM_ERROR
ROWTYPE_MISMATCH
SELF_IS_NULL
STORAGE_ERROR
SUBSCRIPT_BEYOND_COUNT
SUBSCRIPT_OUTSIDE_LIMIT
SYS_INVALID_ROWID
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
VALUE_ERROR
ZERO_DIVIDE

Front 6

What is SQLCODE?

Back 6

Returns the numeric value of and error code.

Front 7

What is SQLERRM?

Back 7

Returns the text ossociated of the error code.

Front 8

What is the function of the RAISE command?

Back 8

The raise command raises an exception under a user defined condition.

IF SQL%NOTFOUND THEN
RAISE USER_DECLARED_EXEPTION.

Front 9

How do you declare an exception?

Back 9

DECLARE
user_exception EXCEPTION;
BEGIN ...

Front 10

Cite the syntax of the IF command.

Back 10

IF <condition>
THEN
<code>
ELSE
IF <condition>
<code>
END IF;
END IF;

Front 11

Syntax of the CASE command.

Back 11

CASE
WHEN <condition> THEN <result>
WHEN <condition> THEN <result>
...
<ELSE> <result>
END;

Front 12

What are the three types of LOOP commands?

Back 12

BASIC
FOR
WHEN

Front 13

Syntax of a basic LOOP command.

Back 13

LOOP
<code>
EXIT WHEN <exit condition>
END LOOP;

Front 14

Syntax of a WHILE loop.

Back 14

WHILE <condition>
<code>
END LOOP;

Front 15

Syntax of the FOR loop using IN.

Back 15

FOR i in 1...<n> LOOP
<code>
END LOOP;

Front 16

Show the sintax of a FOR...Loop using Select.

Back 16

for rec in (select col_1, col_2 from table_a) loop
/*Statements, use rec.col_1 and rec.col_2 */
end loop;

Front 17

Quais os diferentes tipos de cursores?

Back 17

Implicitos
Explicitos

Front 18

Declare a Basic Cursor sintax

Back 18

CURSOR c1
IS
SELECT course_number
from courses_tbl;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;
close c1;

Front 19

Create loop with a cursor.

Back 19

OPEN c1;
LOOP
FETCH c1 INTO var;
EXIT WHEN c1%NOTFOUND;

Front 20

Cite the 4 attributes of an explicit cursor.

Back 20

%ISOPEN
%NOFOUND
%FOUND
%ROWCOUNT

Front 21

when you use the EXECUTE IMMEDIATE statment trying to a run a ddl and you get the folowing error:

ERRO na linha 1:
ORA-01031: privilégios insuficientes
ORA-06512: em "PLSQL10.MARCO", line 7
ORA-06512: em line 1

what do you need to do to your procedure?

Back 21

you need to add AUTHID CURRENT_USER to the declaration of the procedure EX:

create or replace procedure Marco
(
nomeTab varchar2 ,
nomeCol1 varchar2
) AUTHID CURRENT_USER is
begin
EXECUTE IMMEDIATE 'CREATE TABLE ' || nomeTab || '( ' || nomeCol1 || ' ) ' ;
end;
/