I'm having trouble when trying to run SELECT statement using Dynamic SQL. Difference from your video is I'm passing variables as parameters to procedure (not using bind variables). Error is - ORA-00936: missing expression CREATE OR REPLACE PROCEDURE P3_GIS_DATA_LOAD ( FEATURECLASS IN VARCHAR2 , PLUSSFEATURECLASS IN VARCHAR2 , TABLENAME IN VARCHAR2 , SITEID IN VARCHAR2 ) IS loadcount integer; sql_stmt VARCHAR2(500); BEGIN sql_stmt := 'SELECT COUNT(*) INTO '||loadcount||' FROM '||tablename||' WHERE PLUSSFEATURECLASS = '||plussfeatureclass||' AND SITEID = '||siteid; dbms_output.put_line(sql_stmt); execute immediate sql_stmt; INSERT INTO MAXCNDVL3.giscountcheck (FEATURECLASS,TABLENAME,COUNT,SITEID,TYPE) VALUES (featureclass,tablename,loadcount,siteid,'MAXIMO'); COMMIT; END P3_GIS_DATA_LOAD;
It is not for improving performance. In real time you would not know the name of the table and will have to perform the operations in run time. That is when this becomes useful.
I'm having trouble when trying to run SELECT statement using Dynamic SQL. Difference from your video is I'm passing variables as parameters to procedure (not using bind variables). Error is - ORA-00936: missing expression
CREATE OR REPLACE PROCEDURE P3_GIS_DATA_LOAD
(
FEATURECLASS IN VARCHAR2
, PLUSSFEATURECLASS IN VARCHAR2
, TABLENAME IN VARCHAR2
, SITEID IN VARCHAR2
) IS
loadcount integer;
sql_stmt VARCHAR2(500);
BEGIN
sql_stmt := 'SELECT COUNT(*) INTO '||loadcount||' FROM '||tablename||' WHERE PLUSSFEATURECLASS = '||plussfeatureclass||' AND SITEID = '||siteid;
dbms_output.put_line(sql_stmt);
execute immediate sql_stmt;
INSERT INTO MAXCNDVL3.giscountcheck (FEATURECLASS,TABLENAME,COUNT,SITEID,TYPE) VALUES (featureclass,tablename,loadcount,siteid,'MAXIMO');
COMMIT;
END P3_GIS_DATA_LOAD;
So how this improve performance of query ?
Please explain
It is not for improving performance. In real time you would not know the name of the table and will have to perform the operations in run time. That is when this becomes useful.