-1

I have a table like this:

CST_ID CST_NAME CST_TYPE PYMENT_MODE TOTAL_PAYMENT
----------------------------------------------------
   105 Jyothy   Regular  Full                15900
   101 Alveena  Regular  half                15800
   102 Nizam    Regular  Full                15500
   100 Hari     Regular  Full                14500
   103 Sharath  Partime  Full                17500
   104 Jipsy    Shifted  Full                18000

I created a stored procedure for getting the row that I called from the procedure with the primary key valued field (here cst_id) as 

CREATE or replace PROCEDURE sp_slct (cst_id NUMBER) AS
tot_id NUMBER;
--ls_name varchar2,
--ls_type varchar2,
--ls_mode varchar2,
--ls_pymnt int;
BEGIN 
    select 
        cst_id, cst_name, cst_type, payment_mode, total_payment into 
        ls_name, ls_type, ls_mode, ls_pymnt from tbl_cst 
        where
        tbl_cst.cst_id = sp_slct.cst_id;
        END sp_slct;

but I'm getting the errors in compilation

How do I get the result to select a row with the ID that I given as the input to the procedure?

Nizam Muhammed
  • 11
  • 6
  • **What** errors do you get in compilation? You have for some reason commented out the variables you select into (though you haven't give the varchar2s sizes, and they should have semicolons between them not commas), and have an extra one `tot_id` which you don't use. And your `into` and `from` are the wrong way round. What so you plan to do with `ls_name` etc. once you have them? – Alex Poole Nov 01 '16 at 10:21
  • I included the ls_ fields to store the correspondents from the table but when I include it its shows that compiled with compilation error. I need the result as one row and that row is selected by which cst_id is queried with the procedure that I call with the procedure statement – Nizam Muhammed Nov 01 '16 at 10:30
  • Have you investigated the actual errors? You can use `show errors` after compilation in SQL\*Plus and SQL Developer; you can also query the `user_errors` view to see all outstanding PL/SQL errors. – Alex Poole Nov 01 '16 at 10:41
  • This is the last entered query in oracle CREATE or replace PROCEDURE sp_slct (cst_id NUMBER,ls_name varchar2 out, ls_type varchar2 out, ls_mode varchar2 out, ls_pymnt int out) AS tot_id NUMBER; BEGIN select cst_id, cst_name,cst_type,pyment_mode,total_payment from tbl_cst into ls_name,ls_type,ls_mode,ls_pymnt WHERE tbl_cst.cst_id = sp_slct.cst_id; END sp_slct; I cant create the procedure with this – Nizam Muhammed Nov 01 '16 at 10:53
  • Look at `user_errors` to see why that won't compile. Please don't post code in comments. Edit the question to show the code you run and the errors that version generates. (You have basic syntax errors. To start with, the IN/OUT flag comes before the data type, not after it; you still have `into` in the wrong place, etc.). – Alex Poole Nov 01 '16 at 10:58
  • Possible duplicate of [Search All Fields In All Tables For A Specific Value (Oracle)](http://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle) – Nizam Muhammed Nov 03 '16 at 05:18

2 Answers2

0

You miss, at least, the IN keyword in the procedure parameter list, here how it should be. Maybe you should declare also the vars in the INTO clause

CREATE or replace PROCEDURE sp_slct (cst_id IN NUMBER) AS
Massimo Petrus
  • 1,881
  • 2
  • 13
  • 26
  • [The parameter direction flag is optional](https://docs.oracle.com/cloud/latest/db112/LNPLS/parameter_declaration.htm#LNPLS1271), and the default is iN. – Alex Poole Nov 01 '16 at 13:24
  • guys!! I want to retrieve a row by the id value of that row, but this all done by a S procedure, I corrected the orders in regular form, But still I cant select the fields by a key value that I entered in the call query – Nizam Muhammed Nov 02 '16 at 06:31
0

It would be great if you tell us your error Messages, that would improve the possibility to help. Until now your procedure doesnt do anything than to start a query.

You have two ways to use the result of a query. You could use cursors (if you want to search in some rows) or you can use "into" if you needs only one value. (as you did)

And if you want to get a result that matches to your Parameter list you must put your Parameters into the where- clause (as you did).

The only mistake I can see is the order: SELECT ... INTO ... FROM ...

Oracle documentation

And, of course, you must declare the variables (undo your comments)

am2
  • 380
  • 5
  • 21