• Author / Uploaded
• Rajesh Gupta

Citation preview

c. DECLARE v_birthdate DATE NOT NULL; Not legal because if we declare not null then must assign a value. d. DECLARE v_in_stock BOOLEAN := 1; Not correct if its boolean then it shoul be boolean:= True. 2. In each of the following assignments, indicate whether the statement is valid and what the valid data type of the result will be. a. v_days_to_go := v_due_date - SYSDATE; Its a valid statement and the data type of the result would be numeric. b. v_sender := USER || ': ' || TO_CHAR(v_dept_no); Its incorrect because user could be an nonstring value. c. v_sum := $100,000 + $250,000; this is not correct because $ is not allowed. d. v_flag := TRUE; its correct and datatype would be boolean. e. v_n1 := v_n2 > (2 * v_n3); Its correct and datatype would be boolean(TRUE OR False). f. v_value := NULL; return type could be anytype but its incorrect because null can not assign to variable. 3. Create an anonymous block to output the phrase My PL/SQL Block Works een.

to the scr

variable v_message varchar2(30) begin :v_message := 'MY PL/SQL Block Works'; end; / print v_message V_MESSAGE --------------------------------------------------------------------------------

my pl/sql block works 4. Create a block that declares two variables. Assign the value of these PL/SQL variables to iSQL*Plus host variables and print the results of the PL/SQL variables to the screen. Execute your PL/SQL block. Save your PL/SQL block in a file named p1q4.sql, by clicki ng the Save Script button. Remember to save the script with a .sql extension. V_CHAR Character (variable length) V_NUM Number Assign values to these variables as follows: Variable Value -------- ------------------------------------V_CHAR The literal '42 is the answer' V_NUM The first two characters from V_CHAR AnsSQL> SQL> 2 3 4 5 6 7 8 9 10

variable g_char varchar2(50) variable g_num number declare v_char varchar2(50); v_num number(2); begin v_char:='42 is the answer'; :g_char:=v_char; v_num:=substr(v_char,1,2); :g_num:=v_num; end; /

PL/SQL procedure successfully completed. SQL> print g_char; G_CHAR -------------------------------------------------------------------------------42 is the answer SQL> print g_num; G_NUM ---------42