Solved

SQL Script in Oracle database..

Posted on 2012-03-18
3
356 Views
Last Modified: 2012-03-21
Hi,
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.
0
Comment
Question by:irsbenz
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 305 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


DECLARE
   v_user    VARCHAR2 (100) := 'SCOTT';
   v_free    NUMBER;
   v_total   NUMBER;
   v_date    VARCHAR2 (100) := '01-JAN-1986';
   v_days    NUMBER;
BEGIN
   SELECT USER
     INTO v_user
     FROM DUAL;

   IF v_user = 'SCOTT'
   THEN
      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,
                       fs.BYTES,
                       df.bytes_free,
                       df.bytes_used);

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

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

Open in new window

0
 

Author Comment

by:irsbenz
ID: 37750625
thanks.
0
 

Author Closing Comment

by:irsbenz
ID: 37750627
Excellent straight to point.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query Help 12 52
Pivot Table for a join with partition by and over clause 7 27
Oracle Pivot Question 8 43
Update data using formula 22 19
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now