CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
osfar
osfar

Reputation: 413

call pl/sql from another one in oracle database problem

i have 2 stored procedures 1 is called A with the following impl

PROCEDURE A(p_id IN NUMBER, lic_cat_2  OUT varchar2,lic_cat_1  OUT varchar2,traffic_code  OUT varchar2,lic_type  OUT varchar2,emp_num  OUT varchar2)
// Some LOGIC    
end A ;

and PROCEDURE B which is a wrapper to proc A but i need to get other value with a query 

PROCEDURE B(ph_id IN NUMBER, lic_cat_2  OUT varchar2,lic_cat_1  OUT varchar2,traffic_code  OUT varchar2,lic_type  OUT varchar2,emp_num  OUT varchar2)
    declare number phone_id  
    begin
    select into phone_id   parent_id from per_phones where phone_id= p_id
    exec A(phone_id,lic_cat_2  OUT varchar2,lic_cat_1  OUT varchar2,traffic_code  OUT varchar2,lic_type  OUT varchar2,emp_num  OUT varchar2);
    END B;

but it gives me PLS-00103: Encountered the symbol “CREATE”

Upvotes: 0

Views: 100

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

You're calling the A procedure in a wrong manner:

  • omit EXEC, it is a SQL*Plus command
  • omit parameters' description (IN/OUT, datatype) - pass only values
  • omit DECLARE; you need it in triggers or anonymous PL/SQL blocks, but not in stored procedures
    • by the way, variable name comes first, datatype next (for phone_id)
  • I'd suggest you to prefix parameters and variables with p_ (or par_) and l_ respectively (or any other prefix you want) to distinguish them from column names. Otherwise, it is easy to get confused.
    • also, use table aliases in your queries for the same reason

So:

CREATE OR REPLACE PROCEDURE B (p_ph_id         IN     NUMBER,
                               p_lic_cat_2        OUT VARCHAR2,
                               p_lic_cat_1        OUT VARCHAR2,
                               p_traffic_code     OUT VARCHAR2,
                               p_lic_type         OUT VARCHAR2,
                               p_emp_num          OUT VARCHAR2)
IS
   l_phone_id  NUMBER;
BEGIN
   SELECT p.parent_id
     INTO l_phone_id
     FROM per_phones p
    WHERE p.phone_id = p_ph_id;

   A (l_phone_id,
      p_lic_cat_2,
      p_lic_cat_1,
      p_traffic_code,
      p_lic_type,
      p_emp_num);
END B;

As I don't have your tables, for example (to show how to do it) I used Scott's sample schema:

SQL> create or replace procedure a (par_deptno in number, par_dname out varchar2)
  2  is
  3  begin
  4    select dname into par_dname from dept where deptno = par_deptno;
  5  end;
  6  /

Procedure created.

SQL>
SQL> create or replace procedure b (par_empno in number, par_dname out varchar2) is
  2    l_deptno emp.deptno%type;
  3  begin
  4    select deptno into l_deptno from emp where empno = par_empno;
  5
  6    a(l_deptno, par_dname);
  7  end;
  8  /

Procedure created.

SQL>
SQL> set serveroutput on
SQL> declare
  2    l_dname dept.dname%type;
  3  begin
  4    b (7654, l_dname);
  5    dbms_output.put_line('Dname = ' || l_dname);
  6  end;
  7  /
Dname = SALES

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions