Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL Script in Oracle database..

Posted on 2012-03-18
Medium Priority
Last Modified: 2012-03-21
I am working on writing a scripts in plsql on Oracle 9i database. In that script it update the table and in that I need validate 3 thing be update the table.
1. The user which run scripts should be eg.SCOTT
2. The tablespace which table is there eg. user should have 500mb of free space.
3. The date which user enter to update the record should not be < than 1 year the sysdate
(eg: sysdate is 2012/03/12 it should be less than 2011/03/11)
The date is enter by the user as a user parameter.
Thanks in advance.
Question by:irsbenz
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 16

Accepted Solution

Wasim Akram Shaik earned 1220 total points
ID: 37736314
you can do something like this as you said, the user will have the parameters, to validate the third condition we would need parameters which user will enter..

probably this can  be done via a trigger on that table, however incase you want to do via a plsql script, then better would you give user an api which will accept all the input parameters for updation and validate the incoming date parameter, also the scott would need to have the access to few dba tables, to query the free space, prepared a rough version without any exception handling, you modify this according to your needs..

check this out

   v_user    VARCHAR2 (100) := 'SCOTT';
   v_free    NUMBER;
   v_total   NUMBER;
   v_date    VARCHAR2 (100) := '01-JAN-1986';
   v_days    NUMBER;
     INTO v_user

   IF v_user = 'SCOTT'
      SELECT *
        INTO v_free, v_total
        FROM (SELECT /* + RULE */ df.BYTES / (1024 * 1024) "Size (MB)",
                        SUM (fs.BYTES) / (1024 * 1024) "Free (MB)"
                  FROM dba_free_space fs,
                       (SELECT   tablespace_name, SUM (BYTES) BYTES
                            FROM dba_data_files
                        GROUP BY tablespace_name) df
                 WHERE fs.tablespace_name(+) = df.tablespace_name
                   AND fs.tablespace_name = 'USERS'
              GROUP BY df.tablespace_name, df.BYTES
              UNION ALL
              SELECT /* + RULE */ fs.BYTES / (1024 * 1024),
                        SUM (df.bytes_free) / (1024 * 1024)
                  FROM dba_temp_files fs,
                       (SELECT   tablespace_name, bytes_free, bytes_used
                            FROM v$temp_space_header
                        GROUP BY tablespace_name, bytes_free, bytes_used) df
                 WHERE fs.tablespace_name(+) = df.tablespace_name
                   AND fs.tablespace_name = 'USERS'
              GROUP BY df.tablespace_name,

      IF v_free > 500
         SELECT SYSDATE - TO_DATE (v_date, 'DD-MON-YYYY')
           INTO v_days
           FROM DUAL;

         IF v_date < 365
                    NULL;--- Replace this null with the update statement, such as update emp set hire_date=v_date;
            DBMS_OUTPUT.put_line ('Date Validation is failed');
         END IF;
         DBMS_OUTPUT.put_line ('TableSpace is not free ');
      END IF;
      DBMS_OUTPUT.put_line ('Script Terminated As User is not SCOTT');
   END IF;

Open in new window


Author Comment

ID: 37750625

Author Closing Comment

ID: 37750627
Excellent straight to point.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question