Skip to main content

PL/SQL How to Return Object Value with PL/SQL Function

--create obj type
   create type          "obj_user_bank_account"                                          as
    object (
            user_id               number(18)  ,
            account_number                varchar2(50),
            bank_name               varchar2(50)
           );

--create pkg spec
    function fnc_user_bank_account_no(p_user_id in number) return obj_user_bank_account;

--create pkg body
    function fnc_user_bank_account_no(p_user_id in number) return obj_user_bank_account is
      objuseraccountbank obj_user_bank_account  :=  obj_user_bank_account (null, null, null);
      accountno                                         table_user_bank_accounts.account_number%type;
      bankname                                        table_banks.adi%type;  
    begin
      select  count(*)
        into  rowcnt
        from  table_user_bank_accounts account
       where  account.user_id = p_user_id
         and  default=1;
   
      if rowcnt >0 then
        select  account.account_number, bank.adi
          into  accountno, bankname
          from  table_user_bank_accounts account,
                table_banks bank
         where  account.user_id=p_user_id
           and  account.bank_id=bank.id
           and  account.default=1;
       
        objuseraccountbank.user_id := p_user_id;
        objuseraccountbank.account_number  := accountno;
        objuseraccountbank.bank_name := bankname;
     
        return objuseraccountbank;
      else
        select  account_number, adi
          into  accountno, bankname
          from( select  account.account_number,
                        bank.adi
                  from  table_user_bank_accounts account,
                        table_banks bank
                 where  account.user_id = p_user_id
                   and  account.bank_id  = bank.id
              order by  account.id desc)
         where  rownum=1;

        objuseraccountbank.user_id := p_user_id;
        objuseraccountbank.account_number  := accountno;
        objuseraccountbank.bank_name := bankname;
     
        return objuseraccountbank;
      end if;
    end;

--create view
    create or replace view test_view
    as
      (select pkg_ort.fnc_user_bank_account_no(knt.id).account_number_no from dual) account_number,
      (select pkg_ort.fnc_user_bank_account_no(knt.id). bank_name from dual)  bank_name
    from table_users knt
    order by knt.id;

Comments

Popular posts from this blog

How to fix Git push error: "RPC failed; curl 56 HTTP/2 stream 7 was reset send-pack: unexpected disconnect while reading sideband packet fatal: the remote end hung up unexpectedly"

Problem Today I saw the following problem when I tried to push my changes to a Git server after doing some work for upgrading an Umbraco v7 project to v8.18.8.  Possible reasons After some investigations, it seems like this could be because of the following reasons; Git is not happy with the amount of changes that are being pushed into the server.  There are possible limitations on the server about the size/amount of files that you can push. Your internet connection is not good and stable enough. Your Git client's version is old. Solution options For me, the easiest option was connecting to another Wifi and trying again. Apparently, this option helped quite a few people, so it is worth giving it a try. Unfortunately, it didn't work for me. A bad internet connection wasn't an option for me either, as my internet is pretty fast (500 Mbps). Similarly, my Git client version was the latest version (git version 2.41.0.windows.3).  On StackOverflow, there were a lot of recommend...

How to fix "Microsoft SQL Error SQL71564: Error validating element [USERNAME]: The element [USERNAME] has been orphaned from its login and cannot be deployed."

I needed to export a database in BACPAC format today in order to restore it somewhere else, and I encountered the following error. To resolve this issue, I deleted all of the users mentioned in the error log. After successfully creating the BACPAC file, I used it to create a new database with no problems. Error: TITLE: Microsoft SQL Server Management Studio ------------------------------ One or more unsupported elements were found in the schema used as part of a data package. Error SQL71564: Error validating element [USER1]: The element [USER1] has been orphaned from its login and cannot be deployed. Error SQL71564: Error validating element [USER2]: The element [USER2] has been orphaned from its login and cannot be deployed. Error SQL71564: Error validating element [USER3]: The element [USER3] has been orphaned from its login and cannot be deployed. Error SQL71564: Error validating element [USER4]: The element [USER4] has been orphaned from its login and cannot be deployed. Error SQL71...

Umbraco lifehack: How to setup anchor/jump links in Umbraco without making code changes

Today one of my clients created a ticket asking whether it is possible to set up anchor/jump links for an Umbraco content page so that they could link different sections of the page together. This is a common scenario for most Content editors and it should be super easy with any CMS platform, right? Houston, we got a problem! Now an anchor or page jump link is a special URL that takes you to a specific place on a page and it normally requires a unique HTML element ID to link different parts of a page.  When I investigated my issue, I realised that the page sections of the Umbraco page didn't have unique IDs.  View Source Code comes to the rescue In order to resolve the issue without any code changes, I decided to update the content using the built-in View Source Code feature of Umbraco CMS. This feature is available on built-in property editors, like Rich Text Editor and is super handy for checking the code that Umbraco generates for your Front-end website and making minor...