How to write select procedure in oracle 1

[JawwadLone]

Hello everyone!
Can any one help me in creating a procedure that select some values from table abc. I have tried it using package and package body but it didn't worked for me. It give me compilation error in package body on select query "Not Enough Values" and "SQL Statement Ignored". This is the code which i have tried

PACKAGE        "MAIN_DATA" is
  type cur_main is ref cursor ;
  procedure all_Main_Data( param_cur in out cur_main)  ;
end;

PACKAGE BODY        "MAIN_DATA" as 
  procedure all_Main_Data( param_cur in out cur_main )  
  is  
  Begin
    select  * INTO param_cur from Countries;
  End all_Main_Data;
end Main_Data;

Regards.

 

[sem]

PACKAGE        "MAIN_DATA" is
  type cur_main is ref cursor ;
  procedure all_Main_Data( param_cur in out cur_main)  ;
end;

PACKAGE BODY        "MAIN_DATA" as 
  procedure all_Main_Data( param_cur in out cur_main )  
  is  
  Begin
    [b]open param_cur for select[/b]  *  from Countries;
  End all_Main_Data;
end Main_Data;

Regards, Dima

http://seminihin.narod.ru

 

[JawwadLone]

Thanks Sem for help. it solved my problem. Kindly do me one more favour. I want to execute this procedure in SQL plus. what is the method to execute this SQL plus?

 

[sem]

var c refcursor
exec "MAIN_DATA".all_Main_Data(:c)
print c

BTW why do you use double quotes in package name?


Regards, Dima

http://seminihin.narod.ru

 

[JawwadLone]

It gives an error invalid sql statement

Error:

ORA-00900: Invalid SQL statement

 

[sem]

At what stage? There's no sql in my code
My code is pure sql*plus specific, it can not be called from pl/sql or even another sql querying tool. How dod you call it?

Regards, Dima

http://seminihin.narod.ru

 

[JawwadLone]

Yes i was also treating all three statements as single statement that was why i was getting that error. I have executed each statement individualy and it worked fine. Thanks alot.

Regards,
Jawwad Lone.