CODEWITHSUNDEEP
sqloracle-databaseplsqlplsqldeveloper
Ram
Ram

Reputation: 1064

Oracle Native Dynamic SQL PL/SQL statement without begin and end

I have the problem of creating a backup for a table by creating a series of insert statements.

The input is the table name and each table can have a different number of columns. It is assumed that data types can be varchar2, number, or date only

so I have this line of code:

execute immediate fetchStmt;

where fetchStmt can be:

fetch tableColCursor into valuesArray(1), valuesArray(2), ...,  valuesArray(n)

This just fetches each row from the cursor and puts it into a varray, the statements itself works if it is not in an execute immediate statement.

I do know that an execute immediate can only process SQL queries or PL/SQL blocks. The problems is how would I be able to make this work or what can be a similar solution to the problem?

Note that it is not known during compile time the table and its columns and their data types

Upvotes: 1

Views: 707

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67742

EXECUTE IMMEDIATE can only process full statements, i-e: SQL statements or PLSQL blocks (with [DECLARE]..BEGIN..END).

Furthermore, a block executed this way won't see any variables from the calling block (they don't share the same scope), for instance this won't work:

DECLARE
  l NUMBER := 1;
  k NUMBER := 0;
BEGIN
  EXECUTE IMMEDIATE 'BEGIN l := k; END;';
END;

The above code will produce an error because l and k are not defined in the sub-block. Instead you would need to use input/output variables:

DECLARE
  l NUMBER := 1;
  k NUMBER := 0;
BEGIN
  EXECUTE IMMEDIATE 'BEGIN :P1 := :P2; END;' USING OUT l, k;
  dbms_output.put_line(l); -- return 0
END;

In your case you don't know the number of variables so you won't be able to use EXECUTE IMMEDIATE. You could use DBMS_SQL but I think there are simpler methods: you could find already working code (it exists within Oracle APEX for instance), or you could program it yourself by running a SELECT that would produce the necessary INSERT string. For this method you would have to generate the SQL statement dynamically (dependent upon the table columns). The statement would look like this (for a table TEST(a,b,c) where all columns are integer [needs adapting for other datatypes]):

SELECT 'INSERT INTO test(a,b,c) VALUES ('||a||', '||b||', '||c||');'
  FROM test

Upvotes: 2

Related Questions