Error message

Hi
   I was executing a function and was getting the below error
    Execution (2: 8): ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
  while executing i used the statement
   SELECT AE_UTIL.RUN_AE_CHUNKS_BY_MKT('run_600_6', 'Parallel execution is done') from dual
          The function for which the i ran is
   FUNCTION run_ae_chunks_by_mkt (task_name               VARCHAR2,
                                  org_sql_stmt  IN OUT    VARCHAR2)
      RETURN BOOLEAN
   IS
      l_chunk_sql        VARCHAR2 (1000);
      l_sql_stmt         VARCHAR2 (1000);
      l_try              NUMBER;
      l_status           NUMBER;
      v_chunk_sql_stmt   VARCHAR2 (6000);
      v_to_run           varchar2(16000);

      TYPE t_chunk_id IS TABLE OF user_parallel_execute_chunks.chunk_id%TYPE;

      TYPE t_status IS TABLE OF user_parallel_execute_chunks.status%TYPE;

      l_chunk_id         t_chunk_id;
      l_chunk_status     t_status;

      v_chunk_id         user_parallel_execute_chunks.chunk_id%TYPE;
   BEGIN
      -- Create the TASK
      DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name);

      l_chunk_sql := 'select distinct chunk_id, chunk_id from run_chunk where schema_name = user';

      DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL (task_name,
                                                  l_chunk_sql,
                                                  FALSE);

--      v_to_run := 'begin ae_util.run_by_partition(:start_id, :end_id, org_sql_stmt); end; ';
      v_to_run := 'declare
               org_sql_stmt varchar2(16000) := ''' || org_sql_stmt || ''';
               begin
               ae_util.run_by_partition(:start_id, :end_id, org_sql_stmt); end; ';

--      v_to_run := 'begin ae_util.run_test(:start_id, :end_id); end; ';

      dbms_output.put_line('v_to_run is ' || v_to_run);
      dbms_output.put_line('hello');

      DBMS_PARALLEL_EXECUTE.RUN_TASK (task_name,
                                      v_to_run,
                                      DBMS_SQL.NATIVE,
                                      parallel_level   => 8);

      DBMS_OUTPUT.put_line ('Parallel execute done');

      -- If there is error, RESUME it for at most 2 times.
      L_try := 0;
      L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS (task_name);

      WHILE (l_try < 2 AND L_status != DBMS_PARALLEL_EXECUTE.FINISHED)
      LOOP
         L_try := l_try + 1;
         DBMS_PARALLEL_EXECUTE.RESUME_TASK (task_name);
         L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS (task_name);
      END LOOP;


      -- display the status of all the chunks before deleting the task
      SELECT chunk_id, status
        BULK COLLECT INTO l_chunk_id, l_chunk_status
        FROM user_parallel_execute_chunks
       WHERE task_name = task_name;

      FOR i IN l_chunk_id.FIRST .. l_chunk_id.LAST
      LOOP
         DBMS_OUTPUT.put_line (
               'Chunk id : '
            || l_chunk_id (i)
            || ' Status : '
            || l_chunk_status (i));
      END LOOP;


      -- Done with processing; drop the task
      --        DBMS_PARALLEL_EXECUTE.DROP_TASK(task_name);
      DBMS_OUTPUT.put_line ('Statement executed successfully in parallel');
      RETURN TRUE;
   END;
END;
/
thota198Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
>>org_sql_stmt  IN OUT    VARCHAR2

The second parameter needs to be a variable not a literal.

from sqlplus:
var mysql varchar2(100);
exec :mysql :='Parallel execution is done';'

SELECT AE_UTIL.RUN_AE_CHUNKS_BY_MKT('run_600_6', :mysql) from dual;

print mysql

0
 
sdstuberCommented:
the function returns BOOLEAN  which isn't legal in SQL

 boolean is for pl/sql only

0
 
sdstuberCommented:
change your return to string  'Y'/'N'  or numeric 0/1 or some other legal type and value

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
slightwv (䄆 Netminder) Commented:
Agreed on changing the boolean unless this function is part of a larger project and will only be used inside PL/SQL and you are just doing the select to 'test' it.

Then you need an anonymous PL/SQL block not a straight select from dual in sqlplus.
0
 
sdstuberCommented:
once the return values are fixed,   the

ORA-06553: PLS-382: expression is of wrong type  

will go away,  


but then you'll get ORA-6572 due to the out parameter

declaring a variable for it as shown above won't help.  It's simply not legal to have an out parameter in a sql function call
0
 
sdstuberCommented:
to test this function with boolean and an out parameter you'll need a pl/sql block something like this...


DECLARE
    x   VARCHAR2(100) := 'Parallel execution is done';
    z   BOOLEAN;
BEGIN
    z  := run_ae_chunks_by_mkt('run_600_6', x);

    IF z
    THEN
        DBMS_OUTPUT.put_line('true ' || x);
    ELSE
        DBMS_OUTPUT.put_line('false ' || x);
    END IF;
END;
0
 
sdstuberCommented:
on further review of your function,  it appears you don't really need the 2nd parameter to be OUT.

all instances of its use are either commented out, or are used in a read-only fashion.

so,  you could change the return type to string/numeric and change the IN OUT  to just IN

then you could use it in sql
0
 
thota198Author Commented:
ive been trying out but getting all kinds of errors
0
 
slightwv (䄆 Netminder) Commented:
Trying out what?

As explained the function has a LOT of issues.

Post what you have now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.