I have a working Stored Procedure and would like to improve/simplify it.
CREATE OR REPLACE PROCEDURE DOCSADM.DRILL_COUNTV5 ( IN_TABLE IN VARCHAR2, IN_TYPE IN VARCHAR2, OUT_COUNT OUT NUMBER) AS
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || IN_TABLE
|| ' WHERE TYPEVALUE = ''' || IN_TYPE || ''' '
into OUT_COUNT;
END DRILL_COUNTV5;
/
I would ideally like to make it easier to expand. I would like to rewrite the execute immediate line like
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || IN_TABLE
|| ' WHERE TYPEVALUE = '':IN_TYPE'' '
into OUT_COUNT;
When I use this method, I get 0 as my return value.
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :IN_TABLE WHERE TYPEVALUE = ''' || IN_TYPE
|| ''' ' into OUT_COUNT;
This one gives me "ORA-00903: invalid table name".
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || IN_TABLE
|| ' WHERE TYPEVALUE = '':1'' '
into OUT_COUNT using IN_TYPE;
Gives me "ORA-01006: bind variable does not exist".
I don't mind so much if the table binding doesn't happen because there will be fewer of those, but I really want to be able to do the where binding and not use the concatenation.
The document from Oracle gave me several methods, but when I tried them they just did not work.
What about using USING clause in your procedure:
CREATE TABLE ttt(
typevalue varchar2(10)
);
INSERT INTO ttt VALUES('123');
DECLARE
IN_TABLE varchar2(10) := 'ttt';
typval varchar2(10) := '123';
OUT_COUNT number;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || IN_TABLE || ' WHERE TYPEVALUE = :p '
INTO OUT_COUNT USING IN typval;
DBMS_OUTPUT.PUT_LINE( OUT_COUNT );
END;
/
==============================
dbms_output:
1
Demo: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=1fb7e29017e3c5f4309ed25bbeddf7f6