CODEWITHSUNDEEP
sqloracle-databaseplsql
devista
devista

Reputation: 55

PL/SQL query with parameter

I am familiar with MSSQL and using a parameter within the query, but I am not sure how I would do this within PL/SQL. 

DECLARE 
LSITEID  NUMBER := 100001;

BEGIN

SELECT * from invoicehead ih
JOIN sitemaster sm on sm.SITEIID = ih.SITEIID
JOIN invoiceline il on il.invoiceIID = ih.invoiceIID
WHERE 
ih.StartDate BETWEEN '2015-12-01' AND '2016-03-07'
AND SITEIID IN ( LSITEID)


END;

Right now I am testing this within Pl/SQL. But essentially I would be passing in the query with the parameter from MSSQL Linked Server OPENQuery.

How I can run the above query in PL/SQL with the parameter?

Upvotes: 0

Views: 10341

Answers (2)

Brian Leach
Brian Leach

Reputation: 2101

In PL/SQL you just use the name of the argument. In the following example, the argument is P_VALUE, note select statement says where dummy = p_value.

DECLARE
   FUNCTION dummycount (p_value IN DUAL.dummy%TYPE)
      RETURN INTEGER
   AS
      l_ret   INTEGER;
   BEGIN
       SELECT COUNT (*) c
        INTO l_ret
         FROM DUAL
       WHERE dummy = p_value;

       RETURN l_ret;
    END dummycount;
 BEGIN
    DBMS_OUTPUT.put_line ('A: ' || dummycount (p_value => 'A'));
    DBMS_OUTPUT.put_line ('X: ' || dummycount (p_value => 'X'));
END;

This results in the following output:

A: 0
X: 1

Upvotes: 0

Dennis Proksch
Dennis Proksch

Reputation: 260

There is plenty of other resource for finding an answer, e.g. here (Tutorialspoint) or specifically here (plsql-tutorial). But perhaps I have missed your point.

To not remain on merely citing links, your query could look like this:

DECLARE
  LSITEID integer;

BEGIN
  LSITEID := 100001;
  -- dostuff
END;

Two things to note: First, in a declare part (as I have learnt it) you should avoid assigning values. Second, if you intend to pass in different parameters you could/should use a procedure.

Upvotes: 1

Related Questions