PL/SQL query with parameter

Question

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?

OPENQUERY doesn't seem to allow parameters, so presumably you'd have to construct the statement with the variable embedded in it? Your PL/SQL block isn't selecting into anything so isn't valid, but I'm not sure you want PL/SQL at all really. You should also not be replying on implicit date conversion or NLS settings; looks like you can use date literals though, since you'll also have to embed those. — Alex Poole
– Alex Poole, Commented Mar 7, 2016 at 17:16

Dennis Proksch · Accepted Answer · 2016-03-07 17:20:34Z

1

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.

answered Mar 7, 2016 at 17:20

Dennis Proksch

2602 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian Leach · Accepted Answer · 2016-03-07 17:23:17Z

0

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

answered Mar 7, 2016 at 17:23

Brian Leach

2,1111 gold badge14 silver badges17 bronze badges

Collectives™ on Stack Overflow

PL/SQL query with parameter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related