Calling Procedure with ‘call’ function from within P/L SQL Script Oracle

I am not able to run this simple if/then statement in oracle database. (i think it is 10.x but not sure, seems to be an issue of my sql skill not db version).

Can you help me figure out how to run this simple procedure- email_table_copy() – to send a csv copy of the table only if the table has data in it?

I have to use ‘call’ with the email_table_copy procedure, and it seems this is where the issue occurs.

I would really appreciate your help.

DECLARE
    ncount   NUMBER(10);
    v_sql    LONG;
BEGIN
    SELECT
        COUNT(person_id)
    INTO ncount
    FROM
        test_conditional;

    IF ( ncount > 0 ) THEN
        v_sql := 'call email_table_function(''owner'', ''test_conditional'', ''csv'', ''[e-mail]''
);';
        EXECUTE IMMEDIATE v_sql; 
    END IF;

END;

Answer

Just remove ; from your v_sql:

DECLARE
    ncount   NUMBER(10);
    v_sql    LONG;
BEGIN
    SELECT
        COUNT(person_id)
    INTO ncount
    FROM
        test_conditional;

    IF ( ncount > 0 ) THEN
        v_sql := q'[
           call email_table_function('owner', 'test_conditional', 'csv', '[e-mail]')
        ]';
        EXECUTE IMMEDIATE v_sql; 
    END IF;

END;

Also as you can see I’ve a bit modified your literal to q-literal to make it more readable.

Simple example:

begin
  execute immediate q'[
     call dbms_output.put_line('Test string')
  ]';
end;
/