create stored procedure in oracle plsql

Question

3.00/5 (2 votes)

See more:

Hi all,

I want to create stored procedure in oracle.

In that,I want to update if record is present in the table else want to insert that record in that table.

Here is my stored proc which I write.

I have very little knowledge about oracle(to be honest no knowledge).

Below is my code and the purpose of this code is to insert/update the data based on the entry what ever the user makes in cognos report studio report view.

SQL

create or replace
PROCEDURE INSERTCOMMENTS 
(
  N_HATID NUMBER  
, N_IN_NUMBER NUMBER  
, N_POINTS VARCHAR2 DEFAULT 255 
, N_QETYPE VARCHAR2 DEFAULT 255 
) AS 
BEGIN

  IF((SELECT COUNT(*) FROM CCM_REPORT_USER.POINTS_QETYPE_COMMENTS 
    WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID)=0)
    THEN (INSERT INTO "CCM_REPORT_USER"."POINTS_QETYPE_COMMENTS" (HATID, IN_NUMBER, POINTS, QETYPE) 
    VALUES (N_HATID , N_IN_NUMBER , N_POINTS , N_QETYPE,));
    ELSE
    (UPDATE "CCM_REPORT_USER"."POINTS_QETYPE_COMMENTS" SET CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.POINTS = N_POINTS 
    AND     CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.QETYPE = N_QETYPE
    WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID) 
    AND     WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.IN_NUMBER = N_IN_NUMBER)
    END IF;
 
END INSERTCOMMENTS;

Thanks a lot in Advance

Posted 25-Jul-12 1:39am

srikshon

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Andrei Straut** · Answer 1 · 2012-07-25T03:23:00

Here, try this one, see if it works (I have nowhere to test an Oracle stored proc right now, so I'm kinda blind. Still, most of the syntax errors should've been corrected)

SQL

create or replace PROCEDURE INSERTCOMMENTS (
    N_HATID IN NUMBER, 
    N_IN_NUMBER IN NUMBER,
    N_POINTS IN VARCHAR2 DEFAULT 255, 
    N_QETYPE IN VARCHAR2 DEFAULT 255)
IS
    count_comments NUMBER := 0;
BEGIN 
  SELECT COUNT(*) INTO count_comments
      FROM CCM_REPORT_USER.POINTS_QETYPE_COMMENTS 
      WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID;
  IF(count_comments = 0) THEN 
    INSERT INTO CCM_REPORT_USER.POINTS_QETYPE_COMMENTS (HATID, IN_NUMBER, POINTS, QETYPE)
      VALUES (N_HATID , N_IN_NUMBER , N_POINTS , N_QETYPE);
  ELSE 
    UPDATE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS
      SET CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.POINTS = N_POINTS,
        CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.QETYPE = N_QETYPE
      WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID
        AND CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.IN_NUMBER = N_IN_NUMBER;
  END IF; 
END;

srikshon · Answer 2 · 2012-07-26T04:22:00

Hi Straut
The below code gets Complied but when i try to execute the code it is gives me the Error saying

CSS

An Error was encountered performing the requested operation:

ORA-06550:line 10, column 3:
PLS-00306:wrong number or types of arguments in call to 'INSERTCOMMENTS_NEW'
ORA-06550:line 10, Column 3:
PL/SQL:Statement ignored
ORA-06512:at line 58

Vender Code 6550

create or replace
PROCEDURE INSERTCOMMENTS_NEW (
N_HATID IN NUMBER,
N_POINTS IN VARCHAR2 DEFAULT 255,
N_QETYPE IN VARCHAR2 DEFAULT 255,
p_cursor out SYS_REFCURSOR)
IS
COUNT_COMMENTS NUMBER := 0;

BEGIN
SELECT COUNT(*) INTO count_comments
FROM CCM_REPORT_USER.POINTS_QETYPE_COMMENTS
WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID;
IF(count_comments = 0) THEN
INSERT INTO CCM_REPORT_USER.POINTS_QETYPE_COMMENTS (HATID, POINTS, QETYPE)
VALUES (N_HATID , N_POINTS , N_QETYPE);
ELSE
UPDATE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS
SET CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.POINTS = N_POINTS,
CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.QETYPE = N_QETYPE
WHERE CCM_REPORT_USER.POINTS_QETYPE_COMMENTS.HATID = N_HATID;

END IF;
open p_cursor for SELECT HATID FROM POINTS_QETYPE_COMMENTS ;
END;

Kindly suggest me on this thanks a lot in advance

create stored procedure in oracle plsql

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0