CODEWITHSUNDEEP
oracle-databasestored-procedures
Muralidhar A
Muralidhar A

Reputation: 1

How do i run the procedure

exec home_lending_cus('9999999999', 'HOME LENDING', '11111111')

I am trying to run the above statement but it is throwing a error mentioned below.

ORA-00933: SQL Command not properly ended.
ORA-06512: at 'HOME_LENDING_CUS', line 6
ORA-06512: at line1

All the variables of type varchar2

CREATE OR REPLACE PROCEDURE home_lending_cus(
    id_no    VARCHAR2,
    prod_nme VARCHAR2,
    rpt_dte  NUMBER) authid current_user
AS
BEGIN
  EXECUTE immediate 
  ' CREATE TABLE abc AS
  SELECT bt.id,
    ct.cus_id
  FROM bnkr_tbl bt ,
    cus_tbl ct ,
    base_tbl bt
  WHERE bt.id  =ct.id
  AND ct.id    =bt.c_id
  AND bt.pr_nme='||prod_nme|| '
  AND bt.dte   ='||rpt_dte|| '
  AND bt.id    ='||id_no|| '
  GROUP BY bt.id,
    ct.cus_id';
END home_lending_cus;

Upvotes: 0

Views: 70

Answers (1)

Allan
Allan

Reputation: 17429

As @Aramillo pointed out, the quotes around the string variables are wrong, which is making the dynamic SQL not work. However, I would strongly recommend shifting to bind variables, rather than concatenation. Not only will this prevent that kind of error, it will protect you from SQL injection:

CREATE OR REPLACE PROCEDURE home_lending_cus(
    id_no    VARCHAR2,
    prod_nme VARCHAR2,
    rpt_dte  NUMBER) authid current_user
AS
BEGIN
  EXECUTE immediate 
  ' CREATE TABLE abc AS
  SELECT bt.id,
    ct.cus_id
  FROM bnkr_tbl bt ,
    cus_tbl ct ,
    base_tbl bt
  WHERE bt.id  =ct.id
  AND ct.id    =bt.c_id
  AND bt.pr_nme= :1
  AND bt.dte   = :2
  AND bt.id    = :3
  GROUP BY bt.id,
    ct.cus_id' using prod_nme, rpt_dte, id_no;
END home_lending_cus;

Or, rather, it would if you were allowed to use bind variables in DDL. Given that limitation, my inclination would be to split this into two commands.

CREATE OR REPLACE PROCEDURE home_lending_cus(
    id_no    VARCHAR2,
    prod_nme VARCHAR2,
    rpt_dte  NUMBER) authid current_user
AS
BEGIN
  EXECUTE immediate 
  ' CREATE TABLE abc (bt_id number, cus_id number)'
  EXECUTE IMMEDIATE 
  'INSERT INTO abc (bt_id, cus_id)
  SELECT bt.id,
    ct.cus_id
  FROM bnkr_tbl bt ,
    cus_tbl ct ,
    base_tbl bt
  WHERE bt.id  =ct.id
  AND ct.id    =bt.c_id
  AND bt.pr_nme= :1
  AND bt.dte   = :2
  AND bt.id    = :3
  GROUP BY bt.id,
    ct.cus_id' using prod_nme, rpt_dte, id_no;
END home_lending_cus;

There are a couple other issues with this code that you may want to consider:

  1. Your query is also invalid because you're using the alias bt twice in the same FROM clause.
  2. I'd recommend using SQL-99 style joins, rather than the comma-separated list of tables.
  3. Oracle code that creates a table on the fly is always a little suspect. do you really need to create a table, or can you use a global temporary table? The latter is almost always going to be the better option.

Upvotes: 2

Related Questions