Results 1 to 5 of 5

Thread: Noob question

  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Unanswered: Noob question

    I have create a number of *.sql file to add, find record etc. For example:

    ACCEPT id PROMPT 'ENTER PATIENT ID:'
    ACCEPT surname PROMPT 'ENTER PATIENT'S NAME:'

    INSERT INTO PATIENT
    (P_ID, NAME, )
    VALUES('&id', '&name');

    My question is isit possible to create a simple menu in sql say

    1.Add Patient
    2.Search Patient
    3. Remove patient

    and when user press 1 execute Add_Patient.sql and 2 execute Search_Patient.sql etc.

    Thank in advance.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Please, keep in mind that it is Sunday morning here (so I might not think very clearly at the moment).

    Although it, probably, is possible in SQL, why wouldn't you do the decision at the operating system level? This script would, using a simple IF-THEN-ELSE call required .SQL script which would then ask for required parameters and do what it needs to do.

  3. #3
    Join Date
    Oct 2006
    Posts
    2
    Cause this is an assignment, and i can only use sql command, nothing else. So, any help appreciated.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    If you can only use SQL commands, it's going to be tricky. If you can use SQL*Plus commands as well you might be able to do something.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would something like this help?

    First, create two dummy scripts:
    Code:
    REM Script_1.sql
    SELECT 'This is script 1' FROM dual;
    
    REM Script_2.sql
    SELECT 'This is script 2' FROM dual;
    This is a menu script:
    Code:
    REM Menu.sql
    
    ACCEPT answer PROMPT 'What to do: ';
    SET TERMOUT OFF
    SET VERIFY OFF
    SET HEADING OFF
    SET PAUSE OFF
    SET ECHO OFF
    
    COLUMN what_to_do NEWLINE;
    
    SPOOL answer.sql
    
    SELECT DECODE (&answer, 1, '@script_1.sql',
                            2, '@script_2.sql',
                            3, 'Wrong answer'
                  ) what_to_do
    FROM dual;
    
    SPOOL OFF;
    SET TERMOUT ON;
    
    @answer
    Test it and see how it works; tweak it, adjust it according to your needs, but - basically - it should be OK. Of course, if SQL*Plus commands are allowed (just like William said).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •