This content has been marked as final. Show 3 replies
SQL statements cannot access variables or types declared inside PL/SQL code, they can only access types declared on the database itself.
In 12c it's rumoured that this limitation will no longer be the case, but in the meantime, you need to ensure that the SQL can 'see' the types you expect it to use.
The dynamic SQL generated is
PL/SQL engine sends this SQL to SQL engine. And guess what, SQL Engine has no clue what v_fields is. Why? because v_fields a private variable defined in a PL/SQL block for which SQL Engine has zero access.
INSERT INTO TEMP ( COL1,COL2,COL3 ) VALUES ( v_fields(0),v_fields(1),TO_DATE(v_fields(2),'MM/DD/YYYY') )
I personally don't encourage dynamic SQL. Dynamic SQL is always a pain and a result of bad design.
DECLARE v_fields dbms_sql.varchar2a; v_col_str VARCHAR2(200); v_insert VARCHAR2(200); v_idx INTEGER := 0; v_tab VARCHAR2(30) := 'TEMP'; BEGIN FOR i IN (SELECT s.column_name, s.data_type FROM user_tab_cols s WHERE s.table_name = v_tab ORDER BY s.column_id) LOOP v_col_str := v_col_str || i.column_name || ','; IF i.data_type = 'DATE' THEN v_insert := v_insert || 'TO_DATE(:' || v_idx || ',' || '''MM/DD/YYYY''' || '),'; ELSE v_insert := v_insert || ':' || v_idx || ','; END IF; v_idx := v_idx + 1; END LOOP; v_insert := '( ' || RTRIM(v_insert, ',') || ' )'; v_col_str := 'INSERT INTO ' || v_tab || ' ( ' || RTRIM(v_col_str, ',') || ' ) VALUES '; v_fields(0) := 1; v_fields(1) := 'AB'; v_fields(2) := to_char(SYSDATE, 'MM/DD/YYYY'); execute immediate v_col_str || v_insert using v_fields(0), v_fields(1), v_fields(2); END; /
And the most worst thing, the thing that is terrible than dynamic SQL is this
Whats the logic and possible benefit behind this code? Its NONE. Don't do that, NEVER. WHEN OTHERS without a RAISE is a bug in your code. So never do that. And DBMS_OUTPUT is not the correct way to render error message to user.
WHEN OTHERS THEN dbms_output.put_line('Err: ' || SQLERRM); END;
YLN wrote:Nope, I would say it is horrible wrong.
Also the way I am doing,that's right?
Technically, you need to use DBMS_SQL interface, and not execute immediate, in order to use dynamic binding.
Perhaps if you tell us the problem you are trying to solve, we can offer suggestions. But the broken solution you've posted sample code of, is broken. Even if you managed to get it to compile without errors.
Also consider using proper coding standards - like Ada 95 Quality and Style Guide standards. After all, PL/SQL is an implementation of the Ada language. Writing reserved words in upper case is not a standard. It is just silly.