CODEWITHSUNDEEP
sqloracle-databaseplsql
Div
Div

Reputation: 1

SQL command not ended properly at pkg_test

I have to write a stored procedure that starts copying the data from a table 'company' into a staging table 'company_stg' if no records for that date are present in it.

I have the following code :

CREATE OR REPLACE 
PACKAGE BODY PKG_TEST AS

PROCEDURE SP_BILLING AS
BEGIN 

EXECUTE IMMEDIATE 'SELECT * FROM COMPANY INTO COMPANY_STG 
                  WHERE NOT EXISTS (SELECT * FROM COMPANY_STG WHERE AS_OF_DATE = "2023-02-08")';

END;
END PKG_TEST;

I AM GETTING THE ERROR "SQL COMMAND NOT PROPERLY ENDED" company * company_stg have as_of_date as a column. rest all are same. please help me with this

I have also tried

if not exists  (SELECT * FROM COMPANY_STG WHERE AS_OF_DATE = "2023-02-08")
then 
select from company into company_stg

Upvotes: 0

Views: 58

Answers (2)

Bogdan Dincescu
Bogdan Dincescu

Reputation: 728

So many things look bad in that piece of code... First, why use dynamic SQL execute immediate? It's best to avoid dynamic SQL as much as possible because it leads to runtime errors and requires pretty much instrumentation so that it may be debugged. Generally you use dynamic SQL when you do not know beforehand the name of a table it will operate on, which is not the case for you. You definitely know you have to work with tables COMPANY and COMPANY_STG. Is it not so? Then, it doesn't look like you have read the manual to see an insert select. When you insert into a table, it's best to give the list of columns into which you actually insert data. If one alters that table and adds one or more than one column, the insert which does not have the list of columns will crash. Thus, to insert into COMPANY_STG data from COMPANY, the SQL should look like below:

insert into company_stg(
 ... ---- here should be the list of columns you insert data into
)
select 
 ... --- here should the source columns you are willing to insert
from company c
where not exists (
  select 1
  from company_stg cs
  where cs.as_of_date= --- what is the condition??? I did not understand
)

;

You have not given the structures for those tables, so that I can't give you the columns to select and to insert into. Nor did I really understand what the condition for inserting data should be.

Upvotes: 1

MT0
MT0

Reputation: 168578

SELECT does not perform a copy and SELECT * FROM COMPANY INTO COMPANY_STG is not valid syntax. You want to use an INSERT statement to do that (and check if there is any row first):

CREATE OR REPLACE PACKAGE BODY PKG_TEST AS

  PROCEDURE SP_BILLING
  AS
  BEGIN
    DECLARE
      v_staged_count NUMBER;
    BEGIN
      SELECT 1
      INTO   v_staged_count
      FROM   COMPANY_STG
      WHERE  AS_OF_DATE = DATE '2023-02-08'
      FETCH FIRST ROW ONLY; -- We don't care how many rows so stop after finding
                            -- the first one.

      -- Stop as rows have been found.
      RETURN;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- Continue
        NULL;
    END;

    INSERT INTO company_stg
    SELECT *
    FROM   COMPANY;
  END;
END PKG_TEST;
/

fiddle

Upvotes: 0

Related Questions