Stock Update trigeer

 

--This trigger will update the stock table when the Purchase will happen.

DROP TRIGGER REST.STOCK_INS_UPD_3;

CREATE OR REPLACE TRIGGER REST.stock_ins_upd_3
   AFTER INSERT OR UPDATE
   ON REST.PURCHASE_DETAIL
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
DECLARE
   v                  NUMBER;
   v_product_id       NUMBER;
   v_product_id_old   NUMBER;
BEGIN
   SELECT NVL (MAX (stock_id), 0) + 1 INTO v FROM stock;

   SELECT COUNT (product_id)
     INTO v_product_id
     FROM stock where product_id=:new.product_id;


   IF v_product_id <= 0
   THEN
      INSERT INTO stock (STOCK_ID, PRODUCT_ID, STOCK_QTY)
         SELECT v, :new.PRODUCT_ID,                       --:new.PRODUCT_NAME,
                                   :new.PRODUCT_QUANTITY FROM DUAL;
   -- COMMIT;
   ELSE
      

      UPDATE stock
         SET STOCK_QTY = STOCK_QTY+ :new.PRODUCT_QUANTITY
       WHERE product_id = :new.product_id;

   END IF;
END;
/

Time Schedule Procedure

 CREATE OR REPLACE PROCEDURE time_schedule (in_SCHEDULE_ID NUMBER)

IS
   l_startdt                DATE;
   l_SCHEDULE_ID            NUMBER;
   l_DOCTOR_ID              NUMBER;
   l_SCHEDULE_DT            DATE;
   l_FROM_TIME              DATE;
   l_TO_TIME                DATE;
   l_PERIOD_OF_TIME         NUMBER;
   l_SCHEDULE_SLOT_ID       NUMBER;
   l_loop_END_TIME          DATE;
BEGIN
   SELECT SCHEDULE_ID,
          DOCTOR_ID,
          SCHEDULE_DT,
          FROM_TIME,
          TO_TIME,
          PERIOD_OF_TIME
     INTO l_SCHEDULE_ID,
          l_DOCTOR_ID,
          l_SCHEDULE_DT,
          l_FROM_TIME,
          l_TO_TIME,
          l_PERIOD_OF_TIME
     FROM DOCTOR_SCHEDULE
    WHERE SCHEDULE_ID = in_SCHEDULE_ID;

   l_startdt := l_FROM_TIME;

   WHILE l_startdt < l_TO_TIME AND l_FROM_TIME < l_TO_TIME
   LOOP
      SELECT SCHEDULE_SLOT_ID_SEQ.NEXTVAL
        INTO l_SCHEDULE_SLOT_ID
        FROM DUAL;


      IF NVL (l_PERIOD_OF_TIME, 0) > 0
      THEN
         l_loop_END_TIME := l_startdt + (l_PERIOD_OF_TIME / 24 / 60);

--(l_PERIOD_OF_TIME / 24 / 60) its converted number data to a part of a day.

--that is the exact of period of time.

      END IF;

      INSERT INTO DOCTOR_SCHEDULE_SLOT (SCHEDULE_SLOT_ID,
                                        SCHEDULE_ID,
                                        DOCTOR_ID,
                                        SCHEDULE_DT,
                                        START_TIME,
                                        END_TIME)
           VALUES (l_SCHEDULE_SLOT_ID,
                   l_SCHEDULE_ID,
                   l_DOCTOR_ID,
                   l_SCHEDULE_DT,
                   l_startdt,
                   l_loop_END_TIME);

      IF NVL (l_PERIOD_OF_TIME, 0) > 0
      THEN
         l_startdt := l_startdt + (l_PERIOD_OF_TIME / 24 / 60);
      END IF;



      COMMIT;
   END LOOP;
END;
/
------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE time_schedule (in_SCHEDULE_ID NUMBER:=1,in_doctor_id number:=104)
IS
   l_startdt                DATE;
   l_SCHEDULE_ID            NUMBER;
   l_DOCTOR_ID              NUMBER;
   l_SCHEDULE_DT            DATE;
   l_FROM_TIME              DATE;
   l_TO_TIME                DATE;
   l_PERIOD_OF_TIME         NUMBER;
   l_SCHEDULE_SLOT_ID       NUMBER;
   l_loop_END_TIME          DATE;
   
   v number;
BEGIN
select count(1) into v from  DOCTOR_SCHEDULE_SLOT where SCHEDULE_ID = in_SCHEDULE_ID and doctor_id=in_doctor_id;

if v=0 then 

   SELECT SCHEDULE_ID,
          DOCTOR_ID,
          SCHEDULE_DT,
          FROM_TIME,
          TO_TIME,
          PERIOD_OF_TIME
     INTO l_SCHEDULE_ID,
          l_DOCTOR_ID,
          l_SCHEDULE_DT,
          l_FROM_TIME,
          l_TO_TIME,
          l_PERIOD_OF_TIME
     FROM DOCTOR_SCHEDULE
    WHERE SCHEDULE_ID = in_SCHEDULE_ID;

   l_startdt := l_FROM_TIME;

   WHILE l_startdt < l_TO_TIME AND l_FROM_TIME < l_TO_TIME
   LOOP
      SELECT SCHEDULE_SLOT_ID_SEQ.NEXTVAL
        INTO l_SCHEDULE_SLOT_ID
        FROM DUAL;


      IF NVL (l_PERIOD_OF_TIME, 0) > 0
      THEN
         l_loop_END_TIME := l_startdt + (l_PERIOD_OF_TIME / 24 / 60);
      END IF;

      INSERT INTO DOCTOR_SCHEDULE_SLOT (SCHEDULE_SLOT_ID,
                                        SCHEDULE_ID,
                                        DOCTOR_ID,
                                        SCHEDULE_DT,
                                        START_TIME,
                                        END_TIME)
           VALUES (l_SCHEDULE_SLOT_ID,
                   l_SCHEDULE_ID,
                   l_DOCTOR_ID,
                   l_SCHEDULE_DT,
                   l_startdt,
                   l_loop_END_TIME);

      IF NVL (l_PERIOD_OF_TIME, 0) > 0
      THEN
         l_startdt := l_startdt + (l_PERIOD_OF_TIME / 24 / 60);
      END IF;



      COMMIT;
   END LOOP;
   else 
   return;
   end if;
END;
/

Ora-00904 ref invalid identifier with Toad 12.7.0.121

 Problem:

I am trying to access the Script tab of tables in Toad (version 12.7.0.121) and getting ora-00904 ref invalid identifier. The database is 19c.

I don't see this error with 12.2 version of database.
Is there any setting that need to be set in Toad?

Solution:

Yes, Oracle got rid of the "REF" column in DBA_OBJ_AUDIT_OPTS in 19c. The current version of Toad does not include this column in the SQL.

You can avoid the error by unchecking auditing in the script options.

YouTube Link : https://www.youtube.com/c/baizeedrony/videos

Concept Of Oracle Union All...

 /* Formatted on 1/31/2021 12:00:18 PM (QP5 v5.256.13226.35538) */

SELECT TYPE,

       item_code,

       item_name,

       unit,

       qc_refno,

       qcdate,

       mrrno,

       mrrdate,

       typecode,

       typename,

       qty,

       VALUE,

       ccode

  FROM ( (SELECT 'Import' TYPE,

                 item_code,

                 item_name,

                 unit,

                 qc_refno,

                 qcdate,

                 mrrno,

                 mrrdate,

                 typecode,

                 typename,

                 importqty qty,

                 importvalue VALUE,

                 ccode

            FROM STOCK_DETAIL_REPORT)

        UNION ALL

        (SELECT 'Local' TYPE,

                item_code,

                item_name,

                unit,

                qc_refno,

                qcdate,

                mrrno,

                mrrdate,

                typecode,

                typename,

                localqty qty,

                localvalue VALUE,

                ccode

           FROM STOCK_DETAIL_REPORT)

        UNION ALL

        (SELECT 'Other Receive' TYPE,

                item_code,

                item_name,

                unit,

                qc_refno,

                qcdate,

                mrrno,

                mrrdate,

                typecode,

                typename,

                orecqty qty,

                orecvalue VALUE,

                ccode

           FROM STOCK_DETAIL_REPORT)

        UNION ALL

        (SELECT 'Return' TYPE,

                item_code,

                item_name,

                unit,

                qc_refno,

                qcdate,

                mrrno,

                mrrdate,

                typecode,

                typename,

                retqty qty,

                retvalue VALUE,

                ccode

           FROM STOCK_DETAIL_REPORT)

        UNION ALL

        (SELECT 'Issue' TYPE,

                item_code,

                item_name,

                unit,

                qc_refno,

                qcdate,

                mrrno,

                mrrdate,

                typecode,

                typename,

                issueqty qty,

                issuevalue VALUE,

                ccode

           FROM STOCK_DETAIL_REPORT)

        UNION ALL

        (SELECT 'Other Issue' TYPE,

                item_code,

                item_name,

                unit,

                qc_refno,

                qcdate,

                mrrno,

                mrrdate,

                typecode,

                typename,

                oissueqty qty,

                oissuevalue VALUE,

                ccode

           FROM STOCK_DETAIL_REPORT))

          WHERE     TYPE = :p_type

          AND ccode = :p_ccode

         AND (NVL (qty, 0)) > 0

         AND typecode = :p_itemtype

\

Note :In the above query. I define some columns which I manipulate in the UNION ALL query.

Firstly you should understood it that how many columns I defined in the first select statement   all the UNION ALL query the same column we will define .with out defining all the column we will get an error like " ORA-01789: query block has incorrect number of result columns ".

After all the where clause column are mentioned from the first select statement.

Thanks  

Definition of Oracle EXISTS Operator

 The Oracle EXISTS operator is a Boolean operator that return true or false. To check the existence of     rows in a table the EXISTS operator is frequently used with a subquery.

STRUCTURE:

SELECT *

  FROM table

   WHERE EXISTS

              (Subquery)

Note: If the Subquery returns any rows then the EXISTS operator returns true ,otherwise returns false.

The processing of the Subquery terminated by the EXISTS operator if the Subquery return first row.

Oracle EXISTS examples

Let’s take some examples of using EXISTS operator to see how it works.

Oracle EXISTS with SELECT statement example

See the following customers and orders tables in the sample database:

The following example uses the EXISTS operator to find all customers who have the order.

SELECT
    name
FROM
    customers a
WHERE
    EXISTS (
        SELECT
            1
        FROM
            orders
        WHERE
            customer_id = a.customer_id
    )
ORDER BY
    name;
For each customer in the customers table, the subquery checks whether the customer appears on the orders table. If yes, then the EXISTS operator returns true and stops scanning the orders table. Otherwise, the EXISTS operator returns false if the subquery does not find the customer in the orders table.
The result if the  EXISTS operator is used by the WHERE clause to retrieve the customer that makes the subquery returns any rows.
Note that Oracle ignores the select list in the subquery so you can use any column, literal value, expression, etc. In the query above, we used literal number 1.

How to generate primary key with character and number

 Code:

CREATE OR REPLACE TRIGGER article_trig

before insert on articles

REFERENCING NEW AS NEW OLD AS OLD

for each row

declare

v_id number;

--extra varchar2(26):='p';

begin

select nvl(max(to_number(id)),0)+1 into v_id from articles;

:new.id:='python'||'-'|| LPAD(v_id,2,0);

--:new.id:=v_id;

end;

/

How to Modify a Column in Oracle Database

ALTER TABLE articles

MODIFY ID varchar2(100);

NOTE:Here I modify my primary key column.But remember that if you have value in this table you 

will not able to modify your column.

Thanks

Baizeed Rony

Create a basic PL/SQL block

Solution:

======

write the keyword BEGIN and END.Place your code between BEGIN and END.Example is below

======================================================================

BEGIN

here write your executable code;

END;

===========

If you want to introduce variables for your PL/SQL block, you must precede your block with a

DECLARE section. Here’s an example:

DECLARE

One or more variable declarations

BEGIN

One or more PL/SQL statements

END;