Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

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;
/
0
thota198
Asked:
thota198
  • 5
  • 3
4 Solutions
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now