Credit Card Validation using Seeded API Call

Oracle has a seeded API which can be used to validate the Credit Card. Please find below the sample code to use.

DECLARE
-- each character specifies a possible filler characters in the credit
-- card number; i.e. a character that can safely be stripped away
p_fill_chars VARCHAR2(100) := '* -#';
p_cc_number VARCHAR2(200) := '111-111-11-11-1-1';
p_api_version NUMBER := 1.0;
p_init_msg_list VARCHAR2(2000) := ' ';
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
-- will hold the credit card number stripped of all characters except
-- digits; credit card numbers must be of this form for the GetCCType
-- and ValidateCC methods
v_clean_cc VARCHAR2(2000);
-- variable to be set by GetCCType method
v_cc_type IBY_CC_VALIDATE.CCType;
-- variable set by ValidateCC method; indicates if the credit card is
-- still usable
v_cc_valid BOOLEAN;
-- credit card expr date; rolled to the end of the month
-- by the ValidateCC method
v_expr_date DATE := SYSDATE();
BEGIN
-- the credit card number must first be stripped of all non-digits!!
DBMS_OUTPUT.PUT_LINE('Credit card Validation.');
  IBY_CC_VALIDATE.StripCC( p_api_version, p_init_msg_list, p_cc_number,
  p_fill_chars, x_return_status, x_msg_count, x_msg_data,
  v_clean_cc );
  IF x_return_status != FND_API.G_RET_STS_UNEXP_ERROR THEN
   IBY_CC_VALIDATE.GetCCType( p_api_version, p_init_msg_list, v_clean_cc,
   x_return_status, x_msg_count, x_msg_data, v_cc_type);
   IF x_return_status != FND_API.G_RET_STS_UNEXP_ERROR THEN
     IF v_cc_type=IBY_CC_VALIDATE.c_InvalidCC THEN
        DBMS_OUTPUT.PUT_LINE('Credit card number not a valid one.');
     ELSE
        DBMS_OUTPUT.PUT_LINE('Credit card number OK.');
        DBMS_OUTPUT.put_line('Credit Card Type is '||v_cc_type);
     END IF;
     IBY_CC_VALIDATE.ValidateCC( p_api_version, p_init_msg_list, v_clean_cc,
      v_expr_date, x_return_status, x_msg_count, x_msg_data, v_cc_valid);
      IF v_cc_valid THEN
         DBMS_OUTPUT.PUT_LINE('Credit card is valid.');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Credit card number invalid or has expired.');
      END IF;
  END IF;
  END IF;
END;

The v_cc_type returned will be one of the following values.

c_InvalidCC CONSTANT CCType := -1; -- invalid; fits pattern but fails test
c_UnknownCC CONSTANT CCType := 0; -- fits no known pattern
c_McCC CONSTANT CCType := 1; -- Master Card
c_VisaCC CONSTANT CCType := 2; -- Visa
c_AmexCC CONSTANT CCType := 3; -- American Express
c_DClubCC CONSTANT CCType := 4; -- Diner's Club
c_DiscoverCC CONSTANT CCType := 5; -- The Discover Card
c_EnrouteCC CONSTANT CCType := 6; -- Enroute
c_JCBCC CONSTANT CCType := 7; -- JCB

Hope this should help to use within any custom applications that are built in any project.

VI Editor Basics - 2

The Two Modes of VI

The first thing most users learn about the VI editor is that it has two modes: command and insert. The command mode allows the entry of commands to manipulate text. These commands are usually one or two characters long, and can be entered with few keystrokes. The insert mode puts anything typed on the keyboard into the current file.

VI starts out in command mode. There are several commands that put the VI editor into insert mode. The most commonly used commands to get into insert mode are a and i. These two commands are described below. Once you are in insert mode, you get out of it by hitting the escape key. If your terminal does not have an escape key, ^[ should work (control-[). You can hit escape two times in a row and VI would definitely be in command mode. Hitting escape while you are already in command mode doesn't take the editor out of command mode. It may beep to tell you that you are already in that mode.

Some Simple VI Commands

Here is a simple set of commands to get a beginning VI user started. There are many other convenient commands, which will be discussed in later sections.

a

enter insert mode, the characters typed in will be inserted after the current cursor position. If you specify a count, all the text that had been inserted will be repeated that many times.

h

move the cursor to the left one character position.

i

enter insert mode, the characters typed in will be inserted before the current cursor position. If you specify a count, all the text that had been inserted will be repeated that many times.

j

move the cursor down one line.

k

move the cursor up one line.

l

move the cursor to the right one character position.

r

replace one character under the cursor. Specify count to replace a number of characters

u

undo the last change to the file. Typing u again will re-do the change.

x

delete character under the cursor. Count specifies how many characters to delete. The characters will be deleted after the cursor.

VI Editor Basics - 1

Introduction

The VI editor is a screen-based editor used by many Unix users. The VI editor has powerful features to aid programmers, but many beginning users avoid using VI because the different features overwhelm them.

Starting the VI Editor

The VI editor lets a user create new files or edit existing files. The command to start the VI editor is vi, followed by the filename. For example to edit a file called temporary, you would type vi temporary and then return. You can start VI without a filename, but when you want to save your work, you will have to tell VI which filename to save it into later.
When you start VI for the first time, you will see a screen filled with tildes (A tilde looks like this: ~) on the left side of the screen. Any blank lines beyond the end of the file are shown this way. At the bottom of your screen, the filename should be shown, if you specified an existing file, and the size of the file will be shown as well, like this:

"filename" 21 lines, 385 characters

If the file you specified does not exist, then it will tell you that it is a new file, like this:

"newfile" [New file]

If you started VI without a filename, the bottom line of the screen will just be blank when VI starts. If the screen does not show you these expected results, your terminal type may be set wrong. Just type :q and return to get out of VI, and fix your terminal type. If you don't know how, ask a lab monitor.

Getting Out of VI

Now that you know how to get into VI, it would be a good idea to know how to get out of it. The VI editor has two modes and in order to get out of VI, you have to be in command mode. Hit the key labeled "Escape" or "Esc" (If your terminal does not have such a key, then try ^[, or control-[.) to get into command mode. If you were already in the command mode when you hit "Escape", don't worry. It might beep, but you will still be in the command mode.

The command to quit out of VI is :q. Once in command mode, type colon, and 'q', followed by return. If your file has been modified in any way, the editor will warn you of this, and not let you quit. To ignore this message, the command to quit out of VI without saving is :q!. This lets you exit VI without saving any of the changes.

Of course, normally in an editor, you would want to save the changes you have made. The command to save the contents of the editor is :w. You can combine the above command with the quit command, or :wq. You can specify a different file name to save to by specifying the name after the :w. For example, if you wanted to save the file you were working as another filename called filename2, you would type: w filename2 and return.

Performance Tuning Made Easy

I wanted to share an interesting Oracle feature which came across while performance tuning an object.


Please go thru this.


This must be very useful  to tune a sql query, when we start building new programs at least at an initial level.


SQL queries can be auto tuned using the DBMS_SQLTUNE package.


Here are the steps to tune a sample statement.



Let’s take the example of a real life query used.



STEP 1: Create a Tuning Task




DECLARE


my_sqltext CLOB;


task_name VARCHAR2(30);


BEGIN


my_sqltext := < Query >;


task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,


user_name => 'APPS',


scope => 'COMPREHENSIVE',


time_limit => 60,


task_name => 'sql_tuning');



END;


STEP 2: Execute the Task


Begin


dbms_sqltune.execute_tuning_task ( 'sql_tuning');


end;



STEP 3: Check if the task has completed


SELECT task_name, status FROM dba_advisor_log WHERE task_name= 'sql_tuning';



STEP 4: Generate Report


select dbms_sqltune.report_tuning_task('sql_tuning') from dual;


The suggestion below is to analyze the table and then gives the recommendation.

Hope this is useful. You can do further study on this package for the full set of features.