CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqldynamic-sql
Geethanjali Ramaraj
Geethanjali Ramaraj

Reputation: 1

ORA-06561 while executing PL/SQL procedure

I have this procedure:

create or replace
PROCEDURE P_P2
IS
v_str varchar2(1000);
v_file_name varchar2(1000);
BEGIN
P_P1(v_str, 'EXPORT_CSV',v_file_name);
v_str := 'select * from H----)';
v_file_name :=  'H_'||to_char(sysdate,'DD-MM-YYYY')||'.csv';
END;

I am getting error "ORA-06561: given statement is not supported by package DBMS_SQL" when I execute it:

Error starting at line : 165 in command -
exec P_P1
Error report - 
ORA-06561: given statement is not supported by package DBMS_SQL
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at "BIDB.P_P1", line 40
ORA-06512: at "BIDB.P_P2", line 7
ORA-06512: at line 1
06561. 00000 -  "given statement is not supported by package DBMS_SQL"
*Cause:    Attempting to parse an unsupported statement using procedure
           PARSE provided by package DBMS_SQL.
*Action:   Only statements which begin with SELECT, DELETE, INSERT, UPDATE,
           LOCK, BEGIN, DECLARE or << (PL/SQL label delimiter) are supported.

I cannot see why. What am I doing wrong?

Upvotes: 0

Views: 1943

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

You haven't shown what P_P1 is doing, but from what you have shown, your P_P2 procedure may just be calling it too early; you have a call to P_P1 which uses v_str as a parameter, but it's null at that point - you set it after the call that uses it.

That means that somewhere in P_P1 you are probably ending up doing the equivalent of:

 dbms_sql.parse(c, null, dbms_sql.native);

which throws the error you are seeing. You're doing the same with v_filename, which will presumably cause other issues.

So for a start, swap those lines around so the procedure is called last:

create or replace
PROCEDURE P_P2
IS
  v_str varchar2(1000);
  v_file_name varchar2(1000);
BEGIN
  v_str := 'select * from H----)';
  v_file_name :=  'H_'||to_char(sysdate,'DD-MM-YYYY')||'.csv';
  -- call this AFTER populating the variables
  P_P1(v_str, 'EXPORT_CSV',v_file_name);
END;
/

You may have other problems of course - the table name looks odd, both because of the dashes and the closing parenthesis; but you may have just hidden the real name oddly. You seem to have changed the procedure names too (though inconsistently, as your exec seems to be calling the wrong one...).

Upvotes: 2

Related Questions