What is kamailio ?

Kamailio is open source server and used to make voip call using internet & you can install it on linux,bsd,solaris server.

  • Today i got new word i.e. kamailio its hawaiian word it menas to talk with each other.
  • Kamailio, formerly OpenSER, is a SIP server licensed under the GNU General Public License. It can be configured to act as a SIP registrar.
  • I hope you know about VoIP (voice over internet protocol), if you want to enable this feature in your website then it will be helpful but now question is how to get it ?
  • voip means to make call or to talk over internet. The kamailio is open source server and we can runt in on bsd, linux, solaris
  • you can check more from https://www.kamailio.org/w/

 

 

Advertisements
Posted in Total new | Leave a comment

Define the order of priority when executing sql statements ?

 

Order Evaluated Operator
1 Arithmetic operators (*, /, +, -)
2 Concatenation operators (||)
3 Comparison conditions
4 Is[NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT Logical condition
7 AND logical condition
8 OR logical condition
Posted in oracle, sql | Tagged , , | Leave a comment

What is ROW_NUMBER() function in oracle ?

Before knowing the ROW_NUMBER() function we need to understand the oracle analytical functions: Analytical means to get more control & allowing to operate within a table’s column or data like:

  • Analytical functions also know as windowing functions. it mostly used with window query.
  • Row_number is oracle analytical function that is used to get unique number for each row what is in row or window query.

Here i will discuss one example so we will get better understanding on analytical function:

 

  • suppose we have one table : userid (int),profile_image(blob),image_id(int). the table have profile image based on different userid & data feed is as following.
  • user_id profile_image image_id
    101 hexadeciam imaage–size–20*20 1
    101 hexadeciam imaage–size–40*40 2
    101 hexadeciam imaage–size–60*60 3
    102 hexadeciam imaage–size–20*20 4
    102 hexadeciam imaage–size–40*40 5
    102 hexadeciam imaage–size–60*60 6
    103 hexadeciam imaage–size–20*20 7
    103 hexadeciam imaage–size–40*40 8
    103 hexadeciam imaage–size–60*60 9

    As per above table we have duplicate userid with different size of profile image now my requirement is to get the first image of every userid. so now it’s easy to get the one image of every userid as below:

 

select a.user_id,a.profile_image,a.image_id from
(select b.user_id,b.profile_image,b.image_id, row_number() over(partition by user_id order by image_id ) ana from table b)icp
where icp.ana=1

The above query will return each first profile image for every userid so we have used row_number() analytical function on userid field.

Posted in oracle, sql | Tagged , | Leave a comment

What is difference between dbms_jobs & dbms_scheduler in oracle ?

Simply first, DBMS_JOBS is old version & DBMS_SCHEDULER  is new version having powerful syntax , user restriction to access the scheduler ,features as below:

  • logging of job runs (job history)
  • simple but powerful scheduling syntax (similar to but more powerful than cron syntax)
  • running of jobs outside of the database on the operating system
  • resource management between different classes of jobs
  • use of job arguments including passing of objects into stored procedures
  • privilege-based security model for jobs
  • naming of jobs and comments in jobs
  • stored, reusable schedules
Posted in oracle | Tagged , , | Leave a comment

Oracle most used string functions

S.No Function & Purpose
1 ASCII(x);

Returns the ASCII value of the character x.

2 CHR(x);

Returns the character with the ASCII value of x.

3 CONCAT(x, y);

Concatenates the strings x and y and returns the appended string.

4 INITCAP(x);

Converts the initial letter of each word in x to uppercase and returns that string.

5 INSTR(x, find_string [, start] [, occurrence]);

Searches for find_string in x and returns the position at which it occurs.

6 INSTRB(x);

Returns the location of a string within another string, but returns the value in bytes.

7 LENGTH(x);

Returns the number of characters in x.

8 LENGTHB(x);

Returns the length of a character string in bytes for single byte character set.

9 LOWER(x);

Converts the letters in x to lowercase and returns that string.

10 LPAD(x, width [, pad_string]) ;

Pads x with spaces to the left, to bring the total length of the string up to width characters.

11 LTRIM(x [, trim_string]);

Trims characters from the left of x.

12 NANVL(x, value);

Returns value if x matches the NaN special value (not a number), otherwise x is returned.

13 NLS_INITCAP(x);

Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT.

14 NLS_LOWER(x) ;

Same as the LOWER function except that it can use a different sort method as specified by NLSSORT.

15 NLS_UPPER(x);

Same as the UPPER function except that it can use a different sort method as specified by NLSSORT.

16 NLSSORT(x);

Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used.

17 NVL(x, value);

Returns value if x is null; otherwise, x is returned.

18 NVL2(x, value1, value2);

Returns value1 if x is not null; if x is null, value2 is returned.

19 REPLACE(x, search_string, replace_string);

Searches x for search_string and replaces it with replace_string.

20 RPAD(x, width [, pad_string]);

Pads x to the right.

21 RTRIM(x [, trim_string]);

Trims x from the right.

22 SOUNDEX(x) ;

Returns a string containing the phonetic representation of x.

23 SUBSTR(x, start [, length]);

Returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied.

24 SUBSTRB(x);

Same as SUBSTR except that the parameters are expressed in bytes instead of characters for the single-byte character systems.

25 TRIM([trim_char FROM) x);

Trims characters from the left and right of x.

26 UPPER(x);

Converts the letters in x to uppercase and returns that string.

Posted in oracle | Tagged | Leave a comment

Types of constraints available in oracle

There are five types of constraint available in oracle as following:

NOT NULL: NOT NULL Constraint ensures that the column contains no null values.

UNIQUE KEY: UNIQUE Key Constraint ensures that every value in a column or set of columns must be unique, that is, no two rows of a table can have duplicate values in a specified column or set of columns. If the UNIQUE constraint comprises more than one column, that group of columns is called a Composite Unique Key. There can be more than one Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Key automatically creates index on the column it is created.

PRIMARY KEY: Uniquely identifies each row in the Table. Only one PRIMARY KEY can be created for each table but can have several UNIQUE constraints. PRIMARY KEY ensures that no column can contain a NULL value. A Unique Index is automatically created for a PRIMARY KEY column. PRIMARY KEY is called a Parent key.

FOREIGN KEY: Is also called Referential Integrity Constraint. FOREIGN KEY is one in which a column or set of columns take references of the Primary/Unique key of same or another table. FOREIGN KEY is called a child key. A FOREIGN KEY value must match an existing value in the parent table or be null.

CHECK KEY: Defines a condition that each row must satisfy. A single column can have multiple CHECK Constraints. During CHECK constraint following expressions is not allowed:

Posted in oracle, sql | Tagged , | Leave a comment

What is sys_refcursor in oracle ?

  • SYS_REFCURSOR is a pointer to result set of query where you allow the client to fetch many or a few records as per requirement.
  • You will need the SYS_REFCURSOR whenever it require to fetch records from stored procedures.
  • Using REF CURSOR s is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application. A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database.
  • REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value. A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time.
  • Cursor attributes %FOUND, %OPEN, %NOTFOUND, %ROWCOUNT

Example 1:

DECLARE
REFCURSORVALUE SYS_REFCURSOR;
MYRECORD EMPLOYEE%ROWTYPE;
begin
     OPEN REFCURSORVALUE FOR SELECT * FROM EMPLOYEE WHERE ROWNUM<5;
     LOOP
         FETCH REFCURSORVALUE INTO MYRECORD;
         EXIT WHEN REFCURSORVALUE%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE(TO_CHAR(MYRECORD.EMP_NAME) );
     END LOOP;
    CLOSE REFCURSORVALUE;
END;
/

Example 2:

CREATE OR REPLACE PROCEDURE TEST_TESTP(V_NO VARCHAR2,SVC IN OUT SYS_REFCURSOR) 
IS
BEGIN
OPEN SVC FOR SELECT * FROM EMPLOYEE WHERE NO=V_NO;
END;


DECLARE
REFCURSORVALUE SYS_REFCURSOR;
MYRECORD EMPLOYEE%ROWTYPE;
begin
     WEBR_TESTP('999',REFCURSORVALUE);
     LOOP
         FETCH REFCURSORVALUE INTO MYRECORD;
         EXIT WHEN REFCURSORVALUE%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE(TO_CHAR(MYRECORD.EMP_NAME) );
     END LOOP;
    CLOSE REFCURSORVALUE;
END;
/

Posted in oracle | Tagged | Leave a comment