We help IT Professionals succeed at work.

Creation of oracle stored procedure which returns values from multi tables.

PrakashRaoBS
PrakashRaoBS asked
on
1,295 Views
Last Modified: 2012-06-21
Hi ,

     I am writing stored procedure for generating reports using crystal reports.Basically i want couple of tables to be joined and fields from both the tables to be used in the reports. I have created a cursor in the package and used the same in the procedure, when i execute the procedure but it is not returning any values ,but message "Unable to resolve fields for REF CURSOR " is displayed.Please help.

Thanks

 







Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
please post the code.
My friend, that's not an Oracle issue. Your problem is TOAD. You need to set you output options in the Set Parameters Window of the Procedure Editor.

Author

Commented:
I have checked and enabled the output options also ..
Here is the code  ...

Package
----------
CREATE OR REPLACE Package Test_Pckg_MPTR
As TYPE MPTR_Type IS REF CURSOR;
End Test_Pckg_MPTR;

Procedure
------------
CREATE OR REPLACE Procedure Test_Proc_MPTR(MPTR_Cursor IN OUT Test_Pckg_MPTR.MPTR_Type)
As  
Begin
Open MPTR_Cursor for
Select emp.EMPNO,emp.ENAME,dept.DNAME,dept.DEPTNO, emp.SAL from emp , dept  where dept.DEPTNO=emp.DEPTNO ;
End Test_Proc_MPTR;

I am getting this error when executing the SP,

ORA-06550: line 3, column 19:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 3, column 19:
PL/SQL: Item ignored






>> PLS-00320: the declaration of the type of this expression is incomplete or malformed

That's a different error now. Can you post how you're executing your procedure?

You package and procedure should compile, see this example:

CREATE OR REPLACE Procedure Test_Proc_MPTR(MPTR_Cursor OUT Test_Pckg_MPTR.MPTR_Type) As
Begin
Open MPTR_Cursor for
Select emp.EMPNO,emp.ENAME,dept.DNAME,dept.DEPTNO, emp.SAL from emp , dept  where dept.DEPTNO=emp.DEPTNO;
end;
/

Procedure created.

exec Test_Proc_MPTR(:refc);

PL/SQL procedure successfully completed.

print refc

     EMPNO ENAME      DNAME              DEPTNO        SAL
---------- ---------- -------------- ---------- ----------
      7369 SMITH      RESEARCH               20        800
      7499 ALLEN      SALES                  30       1600
      7521 WARD       SALES                  30       1250
      7566 JONES      RESEARCH               20       2975
      7654 MARTIN     SALES                  30       1250
      7698 BLAKE      SALES                  30       2850
      7782 CLARK      ACCOUNTING             10       2450
      7788 SCOTT      RESEARCH               20       3000
      7839 KING       ACCOUNTING             10       5000
      7844 TURNER     SALES                  30       1500
      7876 ADAMS      RESEARCH               20       1100
      7900 JAMES      SALES                  30        950
      7902 FORD       RESEARCH               20       3000
      7934 MILLER     ACCOUNTING             10       1300

14 rows selected.

Elapsed: 00:00:00.03

Author

Commented:
i am using TOAD 8.5.0.50 version to write Sp ,compile and execute the same. Once the sp is executed  iam getting the below mentioned code in Set parameter window, on clicking Ok in the window  i am getting the before mentioned error.


code in Set parameter window
-----------------------------------
DECLARE
  MPTR_CURSOR Test_Pckg_MPTR.MPTR_Type;
  MPTR_CURSOR_row MPTR_CURSOR%ROWTYPE;

BEGIN
  -- MPTR_CURSOR := NULL;  Modify the code to initialize this parameter

  ABC_BETA_P4.TEST_PROC_MPTR ( MPTR_CURSOR );

  -- Unable to resolve fields for REF CURSOR MPTR_CURSOR

  COMMIT;
END;
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
paquicuba  .. Excellent the problem is solved ..Thanks. Please let me know is this the only  way we can achieve .. ie by using view , Package then sp ; can't we do it in sp itself as we do in SQL.
First, what do you need the delow declaration for?

>> MPTR_CURSOR_row MPTR_CURSOR%ROWTYPE;

Author

Commented:
Sorry i didn't get your question
Sorry, I meant "below" not "delow"

This RECORD --> MPTR_CURSOR_row MPTR_CURSOR%ROWTYPE;  <-- What do you need it for?

Where are you planning to use RECORD "MPTR_CURSOR_row" ?

Thanks!


Author

Commented:
No , "MPTR_CURSOR_row" it is generated by toad while executing the procedure.
I have problem in accessing the fields  by specifying their name in the procedure.
It's giving error:
PLS-00382: expression is of wrong type .

In our previous example :
PAQUI@PROD > CREATE OR REPLACE Procedure Test_Proc_MPTR(MPTR_Cursor IN OUT Test_Pckg_MPTR.MPTR_Type)
  2  As
  3  Begin
  4  Open MPTR_Cursor for
  5  select * from empdept_vw;  ----> when i say * it works fine but when i give the field names it gives error
  6  end;
  7  /
Hope you got the scenario ...
Can you show me the field names you're giving it, see my example below:

PAQUI@PROD > CREATE OR REPLACE Procedure Test_Proc_MPTR(MPTR_Cursor IN OUT
  2  Test_Pckg_MPTR.MPTR_Type)
  3  As
  4  begin
  5  Open MPTR_Cursor for
  6  Select EMPNO,ENAME,DNAME,DEPTNO,SAL from empdept_vw;   --<<---------- Field Names
  7  end;
  8  /

Procedure created.

Elapsed: 00:00:00.18
PAQUI@PROD > DECLARE
  2  MPTR_CURSOR Test_Pckg_MPTR.MPTR_Type;
  3  MPTR_CURSOR_row MPTR_CURSOR%ROWTYPE;
  4  BEGIN
  5  TEST_PROC_MPTR ( MPTR_CURSOR );
  6  END;
  7  /

PL/SQL procedure successfully completed.     --<<---------- Still compiling successfully

Author

Commented:
yes ,It works fine if i use all the fields defined in the view...

Select EMPNO, ENAME, DNAME, DEPTNO, SAL from empdept_vw ; -------> its working fine

but when i use selecetd fields then its giving problem

Select EMPNO, ENAME, DEPTNO from empdept_vw ; -------->> it gives error

 
Got you!

Your ref_cursor is declared to be of a record type that contains the columns in the view; when you eliminate 1 or more columns, then the ref_cursor doesn't get the whole record, therefore it returns an error. You must either change the view's definition or select null values, see below:

PAQUI@PROD > CREATE OR REPLACE Procedure Test_Proc_MPTR(MPTR_Cursor IN OUT
  2  Test_Pckg_MPTR.MPTR_Type)
  3  As
  4  begin
  5  Open MPTR_Cursor for
  6  Select EMPNO,ENAME,DNAME,NULL,NULL from empdept_vw;   --<<----- Passing NULL, NULL
  7  end;
  8  /

Procedure created.

Elapsed: 00:00:01.84
PAQUI@PROD > VARIABLE REFCUR REFCURSOR
PAQUI@PROD > EXEC Test_Proc_MPTR(:REFCUR);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
PAQUI@PROD > PRINT REFCUR

     EMPNO ENAME      DNAME          N N
---------- ---------- -------------- - -
      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES
      7566 JONES      RESEARCH
      7654 MARTIN     SALES
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING

14 rows selected.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.