Oracle 7 EXECUTE IMMEDIATE alternative

Question

I am working on a legacy database which is Oracle 7 and I want to use the EXECUTE IMMEDIATE to execute the converted bytes to varchar but I found out the the said function was released on Oracle 10g.

Here is my example:

STORED PROCEDURE:

create or replace procedure QUERY_EXECUTOR
(
    IN_QUERY_STATEMENT IN LONG RAW
)
AS
BEGIN

    declare CONVERTED_QUERY varchar2(32767); 
    cursor c1 is select IN_QUERY_STATEMENT from dual;
    BEGIN  
        for r2 in c1 loop
        converted_query:=UTL_RAW.CAST_TO_VARCHAR2(substr(r2.IN_QUERY_STATEMENT,0)); 
        dbms_output.put_line(converted_query);
        end loop;
        execute immediate converted_query; --THIS LINE
    END;

END;

Look at execute immediate command. When I comment it out. The SP compiled successfully in Oracle 7.

But on Oracle 12g(which we are currently using), the sample code executes just fine.

EXEC QUERY_EXECUTOR('53454c45435420312046524f4d204455414c3b');

When converted is "SELECT 1 FROM DUAL;";

This conversion and execution is for us the best way to handle our current problem so answers will be very much appreciated.

Question

Is there a way or alternative solution if we cannot use the said command in Oracle 7?

EDIT:

Found this topic which is the other utility of executing literal string commands but it is only for DDL commands.

EDIT 2: SOLUTION

The answer by Sir @Wernfried Domscheit did the job for me. I combined the answer with my logic. Although it is not perfect and I think I need to read more of the documentation.

First, it seems that DBMS_SQL.VARCHAR2A is not working in Oracle7.

Then I found this which having a different DBMS_SQL.PARSE method signature. I used it because it says DBMS_SQL.V7 so it might be Oracle 7 but I really don't know. I need to read the documentations tho.

create or replace procedure QUERY_EXECUTOR
(
    IN_QUERY_STATEMENT IN LONG RAW
)
AS
BEGIN
DECLARE
    res INTEGER;
    cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
    CONVERTED_QUERY varchar2(32767);
    cursor c1 is select IN_QUERY_STATEMENT from dual;
    BEGIN
        for r2 in c1 loop
            converted_query:=UTL_RAW.CAST_TO_VARCHAR2(substr(r2.IN_QUERY_STATEMENT,0)); 
        end loop;
        DBMS_SQL.PARSE(cur, converted_query, DBMS_SQL.V7);
        dbms_output.put_line(cur);
        res := DBMS_SQL.EXECUTE(cur);
        DBMS_SQL.CLOSE_CURSOR(cur);
    END;
END;

PS:

I'm not a DBA so please if I'm doing something wrong on the solution part, can you give your opinion for much better solution? Very much appreciated the answer.

This statement/post might help. [exec DBMS_UTILITY.EXEC_DDL_STATEMENT('create table t1 (id number)');](https://stackoverflow.com/questions/6973003/oracle-dbms-utility-exec-ddl-statement-vs-execute-immediate/6973293#6973293) — Arif Sher Khan, Aug 08 '19 at 05:42
@ArifSherKhan, I already added that link at "EDIT" section of my post. — TraxX, Aug 08 '19 at 05:44
Trivial, but it was Oracle 7 not 7i. Oracle 8i was the first Oracle version to have a letter suffix. Also Oracle 8i was the version which introduced EXECUTE IMMEDIATE to a grateful world. — APC, Aug 08 '19 at 07:58

score 3 · Accepted Answer · edited Aug 08 '19 at 07:54

For dynamic prior Oracle 8i you have to use the DBMS_SQL package. Would be like this:

DECLARE
   sqlcmd DBMS_SQL.VARCHAR2A;
   res INTEGER;
   cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;

BEGIN

    sqlcmd(1) := 'SELECT 1';
    sqlcmd(sqlcmd.LAST+1) := 'FROM dual';

    DBMS_SQL.PARSE(cur, sqlcmd, sqlcmd.FIRST, sqlcmd.LAST, TRUE, DBMS_SQL.NATIVE); 
    res := DBMS_SQL.EXECUTE(cur);
    DBMS_SQL.CLOSE_CURSOR(cur);

END;

As far as I remember DBMS_SQL.VARCHAR2A was introduced more recently, perhaps you have to use DBMS_SQL.VARCHAR2_TABLE or DBMS_SQL.VARCHAR2S.

In order to get the result from SELECT you have to use FETCH_ROWS or EXECUTE_AND_FETCH functions, the documentation should help you.

This actually works for me. I will edit my post and will include the solution. Thanks! — TraxX, Aug 08 '19 at 07:33

score 1 · Answer 2 · answered Aug 08 '19 at 05:53

If I understand you right your question is: How can I execute dynamic SQL in Oracle 7.

The pl/sql 'execute' command does not exist in Oracle 7 but the package 'dbms_sql' exists and is described PL/SQL User’s Guide and Reference, Release 2.3, Part No. A32542-1 on page 5-7 of 'Chapter 5: Interaction with Oracle' and in Oracle 7 Server Application Developer's Guide, Release 7.3, Part No. A32536-1 'Chapter 10: Using Dynamic SQL', pp 10-1

Your references help a lot. Thanks! – TraxX Aug 08 '19 at 07:49 — TraxX, Aug 08 '19 at 07:49

Oracle 7 EXECUTE IMMEDIATE alternative

2 Answers2