CODEWITHSUNDEEP
oracle-databasestored-procedures
samuelebe
samuelebe

Reputation: 149

SQL to Stored Procedure

I am working on a project and there is a need to write stored procedure instead of SQL query. I have never done this before and I tried now by converting the written sql to a procedure. However, I couldn't get this error free and working. Any suggestions from you folks is very helpful in fixing this..

SQL:

create or replace 
PROCEDURE MS_TST_PROC AS 

BEGIN

DECLARE
  l_organization varchar2(40);
  l_framework varchar2(10);
  l_sub_category_code varchar2(20);
  l_sub_category varchar2(20);
  l_TST_function varchar2(20);
  l_questionnaire_name varchar2(20);
  l_responded_on varchar2(20);
  l_overall_score number(10);
  l_target_score number(10);
  l_maturity number(10,2);
  l_full_name varchar2(20);

  cursor c_get_details 
is

  select 
  ts.organization_name,
  q.framework,
  q.sub_category_code,
  q.sub_category,
  tst.tst_function,
  q.questionnaire_name,
  resp.responded_on ,
  resp.overall_score,
  ts.target_score,
  Round((resp.overall_score / ts.target_score)*100,2) as Maturity,
  users.first_name || ' ' || users.last_name as full_name
  into 
  l_organization,
  l_framework,
  l_sub_category_code,
  l_sub_category,
  l_tst_function,
  l_questionnaire_name,
  l_responded_on,
  l_overall_score,
  l_target_score,
  l_maturity,
  l_full_name

from MS_CMM_QUESTIONNAIRE q
INNER JOIN  MS_CMM_TARGET_SCORE ts
on q.sub_category_code = ts.sub_category_code
INNER JOIN MS_CMM_CSF_FUNCTIONS tst
on tst.sub_category_code = q.sub_category_code
INNER JOIN MS_QSM_QUESTIONNAIRE qsm
on q.QUESTIONNAIRE_NAME = qsm.QUE_NAME
INNER JOIN MS_QSM_QUESTNR_RESP resp
on resp.QUESTIONNAIRE_ID = qsm.QUE_ID
and resp.applies_to_object = ts.organization_name
INNER JOIN SI_USERS_T users
on users.user_name = resp.respondent;

END MS_TST_PROC;

and compilation error says:

Error(60,1): PLS-00103: Encountered the symbol "END" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior

Upvotes: 0

Views: 70

Answers (1)

MT0
MT0

Reputation: 167774

There are a couple of issues:

  • You have a DECLARE statement without a subsequent BEGIN or END statement.
  • You have a CURSOR with an INTO clause; they cannot both be there. If the query returns a single row then just use SELECT ... INTO ... (see below), otherise, if you have multiple rows you need to process then you could use a cursor loop.

Also, it is much easier to read (and to find unmatched DECLARE/BEGIN/END statements) if you format your code and maintain proper levels of indentation.

Something like this:

CREATE PROCEDURE MS_TST_PROC
AS 
  l_organization       MS_CMM_TARGET_SCORE.ORGANIZATION%TYPE;
  l_framework          MS_CMM_QUESTIONNAIRE.FRAMEWORK%TYPE;
  l_sub_category_code  MS_CMM_QUESTIONNAIRE.SUB_CATEGORY_CODE%TYPE;
  l_sub_category       MS_CMM_QUESTIONNAIRE.SUB_CATEGORY%TYPE;
  l_TST_function       MS_CMM_CSF_FUNCTIONS.TST_FUNCTION%TYPE;
  l_questionnaire_name MS_CMM_QUESTIONNAIRE.QUESTIONNAIRE_NAME%TYPE;
  l_responded_on       MS_QSM_QUESTNR_RESP.RESPONDED_ON%TYPE;
  l_overall_score      MS_QSM_QUESTNR_RESP.OVERALL_SCORE%TYPE;
  l_target_score       MS_CMM_TARGET_SCORE.TARGET_SCORE%TYPE;
  l_maturity           number(10,2);
  l_full_name          varchar2(20);
BEGIN    
  SELECT ts.organization_name,
         q.framework,
         q.sub_category_code,
         q.sub_category,
         tst.tst_function,
         q.questionnaire_name,
         resp.responded_on,
         resp.overall_score,
         ts.target_score,
         Round((resp.overall_score / ts.target_score)*100,2),
         users.first_name || ' ' || users.last_name
  INTO   l_organization,
         l_framework,
         l_sub_category_code,
         l_sub_category,
         l_tst_function,
         l_questionnaire_name,
         l_responded_on,
         l_overall_score,
         l_target_score,
         l_maturity,
         l_full_name
  FROM   MS_CMM_QUESTIONNAIRE q
         INNER JOIN  MS_CMM_TARGET_SCORE ts
         on q.sub_category_code = ts.sub_category_code
         INNER JOIN MS_CMM_CSF_FUNCTIONS tst
         on tst.sub_category_code = q.sub_category_code
         INNER JOIN MS_QSM_QUESTIONNAIRE qsm
         on q.QUESTIONNAIRE_NAME = qsm.QUE_NAME
         INNER JOIN MS_QSM_QUESTNR_RESP resp
         on resp.QUESTIONNAIRE_ID = qsm.QUE_ID
            and resp.applies_to_object = ts.organization_name
         INNER JOIN SI_USERS_T users
         on users.user_name = resp.respondent;

  -- Do something with the values.
END MS_TST_PROC;
/

Upvotes: 3

Related Questions