Sunday, August 3, 2008

PL/SQL Advance

This section would have Advance Concept of PL/SQL.

AUTHID DEFINER V/S AUTHID CURRENT_USER

How to Select the Nth highest / lowest value from a table

Duplicate rows in the table

Display the number value in Words

Pragma

NOCOPY

DYNAMIC SQL

BULK COLLECT

FORALL
at

2 comments:

  1. moinuddin shaikhFebruary 18, 2009 at 3:12 AM

    explain me how to call this function
    create or replace function updnval(
    tab in varchar2,
    col in varchar2,
    val in number,
    whr in varchar2 := null,
    sch in varchar2 := null)
    return integer is
    begin
    execute immediate
    'update'||nvl(sch,user)||','||tab||
    'set'||col||'=:the_value where'||
    nvl(whr,'1=1') using val;
    return sql%rowcount;
    end;
    /

    ReplyDelete
  2. phaniFebruary 19, 2009 at 12:23 AM

    Hi Shaikh,
    After creating the function in the database.

    Use the following.

    Declare
    xx NUMBER;
    begin
    xx := updnval(tab => ___
    ,col => ___
    ,val => ___
    ,whr => ___
    ,sch => ___
    );
    dbms_output.put_line(xx);
    end;

    ReplyDelete