How to update table1 field by using other table and function

functionsoracleplsqlupdate

I have two tables and one function:

Table1 contains shop_code,batch_id

shop_code| batch_id  | regstriy_id
123 | 100  | 12
124 | 100  |13
125 | 100  |12

Table2 contains shop_code,shop_name

shop_code| shop_name  
123 | need to populate  
124 | need to populate  
125 | need to populate

Function1 take parameter registry_id from table1 and returns shop_name

Table2 shop_name is empty I want to populate against the shop_code.

It will be great if someone can help, I am using Oracle.

I tried this but not working

 update TABLE2 set T2.SHOP_NAME = T.SHOP_NAME
 from(
 select GET_shop_name(t1.regitry_id) as shop_name ,
           t1.shop_code shop_code
           from TABLE1 T1 
                ) t where t.shop_code = t1.shop_code;

Best Answer

There are multiple ways to do it in Oracle. Right of the bat I can think about two.

Straightforward, not Oracle specific (assuming shop_code is unique in table1)

update TABLE2 t2 set SHOP_NAME = 
(
  SELECT GET_shop_name(t1.regitry_id)  
  FROM TABLE1 T1 
  WHERE t2.shop_code = t1.shop_code
);

Oracle specific syntax(requires inline view to be key-preserved - if shop_code is not a PK column in both tables you will very likely get "SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table" )
```
UPDATE
 (
    SELECT GET_shop_name(t1.regitry_id) AS new_name,
           t2.shop_code, t2.shop_name
    FROM TABLE2 t2 
    INNER JOIN t1 ON (t2.shop_code = t1.shop_code)
  )
SET shop_name = new_name;
```

Related Solutions

How to speed up a query with a lot of subqueries

I guess that using subqueries inside functions is the main performance problem.

It seems that the functions are fairly simple and you could drop them and easily rewrite the query with joins only:

SELECT 
  loan.loan_number,
  loan.borrower_name,
  ...
  CASE WHEN loan.date_waiver_ordered IS NULL 
         THEN 'Not Ordered'
       WHEN  MAX(doc.upload_date) >= loan.date_waiver_verified
         THEN 'Not Verified Since Last Upload'
       WHEN  MAX(doc.upload_date) < loan.date_waiver_verified
         THEN 'Verified'
       ELSE NULL
  END  AS get_waiver_status,
  ...

FROM loans_table AS loan

  LEFT JOIN documents_table AS doc             --- joining
    ON  doc.loan_number = loan.loan_number 
    AND doc.document_type = 'waiver' 

WHERE loan.loan_type IN ('A', 'B', 'C')
  AND loan.filing_date >= TO_DATE('30-OCT-12')
  AND loan.filing_date <  TO_DATE('13-NOV-12')

GROUP BY
    loan.PK,                                   --- the Primary Key and
    loan.loan_type,                            --- all the other needed columns
    loan.filing_date,
    loan.loan_number,
    loan.borrower_name,
    loan.date_waiver_verified ;

Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

According to the question, all you need to do is attach a predicate on the query to customer table as shown below.

select * from customer where company_id in(select company_id from user_info where Admin='admin_user');

Right? If yes, you have to create a function which should return a predicate as:

company_id in(select company_id from user_info where Admin='admin_user')

Well, I have done a small demo for your requirement. I have created doramail as an ADMIN user who owns the tables user_info, customer and a function called Hello_Comp_ID.

SQL> conn / as sysdba
SQL> create user doramail identified by doramail;

User Created.

SQL> grant connect, resource to doramail;

Grant succeeded.

SQL> conn doramail/doramail

Connected.

SQL> create table user_info(Company_Id number, Admin varchar2(20));

Table created.

SQL> create table customer(company_id number, name varchar2(20));

Table created.

SQL> insert into user_info values(100, 'MKarki');

1 row created.

SQL> insert into user_info values(200,'VKarki');

1 row created.

SQL> insert into user_info values(300,'CKarki');

1 row created.

SQL> insert into customer values(100,'Joe');

1 row created.

SQL> insert into customer values(200,'Jimmy');

1 row created.

SQL> insert into customer values(300,'Cashy');

1 row created.

SQL> insert into customer values(300,'Tim');

1 row created.

SQL> Commit;

Commit complete.

SQL> select * from user_info;

COMPANY_ID ADMIN                                                                
---------- --------------------                                                 
       100 MKarki                                                               
       200 VKarki                                                               
       300 CKarki                                                               

SQL> select * from customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Ricky                                                                
       100 Joe                                                                  
       200 Jimmy                                                                
       300 Cashy                                                                
       300 Tim  
SQL> Create or Replace FUNCTION Hello_Comp_ID( P_SCHEMA IN varchar2, P_OBJECT IN varchar2) Return varchar2 IS
  2    V_User Varchar2(20);
  3    V_Predicate Varchar2(200);
  4  BEGIN
  5   V_User := SYS_CONTEXT('USERENV','SESSION_USER');
  6    If V_User = 'DORAMAIL' then
  7      V_Predicate := '';
  8    Else
  9     V_Predicate := 'Company_Id in(select company_id from user_info where admin='||q'[']' || V_User || q'[']'||')';
 10    END IF;
 11    RETURN  V_Predicate;
 12  END;
 13  /

Function created.

SQL> exec DBMS_RLS.ADD_POLICY('Doramail','customer','secure_select','doramail','Hello_Comp_ID','SELECT,INDEX');

PL/SQL procedure successfully completed.

SQL> select * from customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Ricky                                                                
       100 Joe                                                                  
       200 Jimmy                                                                
       300 Cashy                                                                
       300 Tim   
SQL> conn / as sysdba
Connected.    
SQL> create user "MKarki" identified by mkarki;

User created.

SQL> grant connect, resource to "MKarki";

Grant succeeded.

SQL> conn doramail/doramail
Connected.
SQL> grant select on customer to "MKarki";

Grant succeeded.

SQL> conn "MKarki"/mkarki
Connected.
SQL> select * from doramail.customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Joe                                                                  
       100 Ricky

Since the user MKarki belongs to company_id 100 he only can see records where company_id is 100.

In your case there might be problem in returned predicate. You can change:

V_Hello:='SYS_CONTEXT('||q'['CompAdmin']'||
','||q'['Company_ID']'||')
='||q'['Company_ID']';

To this:

V_Hello:='SYS_CONTEXT('||q'[']' || CompAdmin || q'[']' ||
','|| q'[']' || Company_ID || q'[']' ||')
='||q'[']' || Company_ID || q'[']';

If you are still having trouble then you can view the predicate being attached to your query using explain plan command. And then you can change the predicate(if there are any logical/syntactical errors in the predicate) in the function.

SQL> explain plan for select * from doramail.customer;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2021122684

--------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     2 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |           |     2 |   100 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMER  |     5 |   125 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| USER_INFO |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COMPANY_ID"="COMPANY_ID")
   3 - filter("ADMIN"='MKarki')

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

Best Answer

Related Solutions

How to speed up a query with a lot of subqueries

Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

Related Question