OraFAQ Forum: SQL & PL/SQL » Oracle Queries

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Queries

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Queries [message #10700]

Tue, 10 February 2004 18:55

Ravi Kadu
Messages: 3
Registered: February 2004

Junior Member

Dear Sir,

Thanks a lot for your epoch-making answers to my previous queries.

Please go through following PL/SQL blocks and make correction if necessary

Query No. 1

SET SERVEROUTPUT ON

DECLARE

A VARCHAR2 (10);

B NUMBER (10);

BEGIN

A:=&Student_Name;

B:=&Percentage:

IF

B>=35 AND B<=40;

DBMS_OUTPUT.PUT_LINE(A || �Obtained "C" Grade� );

ELSIF

B>=41 AND B<=59;

DBMS_OUTPUT.PUT_LINE(A || �Obtained "B" Grade� );

ELSIF

B>=60;

DBMS_OUTPUT.PUT_LINE(A || �Obtained "A" Grade� );

ELSIF

B IS NULL;

DBMS_OUTPUT.PUT_LINE(�Sorry, we are unable to execute report of � || A� );

ENDIF;

END;

RUN;

.

In Query No. 1, B IS NULL means skip blank ( as soon as ask enter value for percentage, Just press Enter

�

Query No. 2

SET SERVEROUTPUT ON

DECLARE

A VARCHAR2 (10);

B VARCHAR2 (10);

C VARCHAR2 (10);

BEGIN

A:=&Student_Name;

B:=&Date_of_Birth:

C:=To_Number(months_between(sysdate � B)/12);

DBMS_OUTPUT.PUT_LINE(�The Current age of � || A || �is� || C || �Years� );

END;

RUN;

.

In Query No. 2, It should return calculate age from the Date of birth & Sysdate

�

�

Query No. 3

Is there any provision/ command / syntax to convert Number into words (Character)?

e.g. Number : 30412 In words : Thirty Thousand Four Hundred and Twelve

�

Query No. 4

May I know the example of Converting Functions?

(To_Char to To_Date or vice-versa and To_Char to To_Number or vice-versa)

Query No. 5

Can you please explain me what is the syntax of ALTER User�s Password & DELETE User?

USER NAME : RAVI

PASSWORD : RSK

Query No. 6

For instance, we have table named RAVI, in which there are 5 fields Name (Varchar2 (20), DOB (Date), Address (Varchar2 (20)), Contact (Number (10)), Remarks (Long). I have inserted 2-3 records in the same table. However later, I want to do following modifications / changes.

1. To delete / remove contact field from the table.

2. To increase size of Name field (from 20 to 30)

3. To decrease size of Address field ( from 20 to 15)

Please Provide the Commands / syntaxes for above 3 modifications

Query No. 7

In Query No. 2 (block), Instead of "Enter value for Percentage" for Input

Can it ask us our desirable sentence?

e.g.

" Please enter Student Percentage "

Explain Syntax

Query No. 8

Previously, there was my query regarding elimination / removal of SQL prompt. subsequently i have received following command / syntax for the same

SET SQL PROMPT MYSQL>

however, the result of above command is

"SP2-0158:Unknown SET option "SQL"

�

Regards,

Ravi

Report message to a moderator

Re: Oracle Queries [message #10701 is a reply to message #10700]

Tue, 10 February 2004 23:56

Maaher
Messages: 7065
Registered: December 2001

Senior Member

1. At first glance:
- put quotes around the assignment of A ('&student_name')
- replace the : by a semicolon (;) in the assignment of B
- IF condition THEN ... instead of IF condition;
- ELSIF condition THEN ... instead of ELSIF condition;
- (�Sorry, we are unable to execute report of � || A� , loose the quote at the end.
- ENDIF does not exist, it is END IF; (2 words)
- RUN is no keyword
- If B gets no value the statement becomes: B:=; and it will fail. Replace by:B := To_number('&percentage');

2. Look for the differences:

DECLARE
A VARCHAR2 (10);
B Date;
C Number;
BEGIN
A:='&Student_Name';
B:=to_date('&Date_of_Birth','dd/mm/yyyy');
C:=To_Number(months_between(sysdate,B)/12);
DBMS_OUTPUT.PUT_LINE('The Current age of ' || A || ' is ' || round(C,2) || ' Years' );
END;

3. This is limited to 5373484:

SQL> select to_char( to_date(5373484,'J'),'Jsp') from dual;

TO_CHAR(TO_DATE(5373484,'J'),'JSP')
--------------------------------------------------------------------------
Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four

For a more complex solution, look at this

4. See answer 3 and here

5. ALTER USER some_user IDENTIFIED BY new_password will change the password.
DROP USER will, well...drop him (~= 'delete')

6. Go to tahiti.oracle.com, select the version that is applicable and search for the ALTER TABLE syntax. It's all in there (it requires a free registration). It's no use of copy-paste it in here.

7. Make use of the ACCEPT command:

SET SERVEROUTPUT ON
ACCEPT C PROMPT 'Give Percentage :';
DECLARE
  A VARCHAR2 (10);
  B NUMBER (10);
BEGIN
  A:='&Student_Name';
  B:=&C;

  IF B>=35 AND B<=40 THEN
    DBMS_OUTPUT.PUT_LINE(A || 'Obtained "C" Grade' );
  ELSIF B>=41 AND B<=59 THEN
    DBMS_OUTPUT.PUT_LINE(A || 'Obtained "B" Grade' );
  ELSIF B>=60 THEN
    DBMS_OUTPUT.PUT_LINE(A || 'Obtained "A" Grade' );
  ELSIF B IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Sorry, we are unable to execute report of ' || A );
  END IF;
END;
/

8. SET SQLPROMPT ... instead of SET SQL PROMPT.

MHE

Report message to a moderator

Previous Topic:	Difference between yesterday's rec and today's rec
Next Topic:	Query required

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 15:46:14 CDT 2024