Toad World® Forums

Help passing select statement to procedure


#1

FROM SECURITY_USER

AND ((LRT_EMAIL_INDICATOR)=1));

Thanks in advance!
Mike

First of all I’m very new to TOAD/Oracle so please bear with me. I am using Toad PL/SQL and trying to write a stored procedure (SP) that will pass a Select statement as a text string to another SP. How can I assign it to a variable and pass it to the SP? The issue is the Select statement will have singe quotes in the string incasing text values. Below is an example - notice the text value ‘Analyst’ inside the Select statement.

INNER JOIN LRT_EMAIL_INDICATOR ON SECURITY_USER.USER_ID = LRT_EMAIL_INDICATOR.USER_ID WHERE (((SECURITY_USER.LRT_SECURITY)=‘Analyst’)

Message was edited by: MThomas


#2

In SQL, any time you want to embed a single quote in a quoted string you double
it. Try something as simple as

SELECT ‘That’‘s all, folks!’ FROM DUAL ;

Note that that is two single quotes, not one double quote. So your first
procedure calls the second one with something like

do_the_select ( ‘SELECT … WHERE (((SECURITY_USER.LRT_SECURITY)=’‘Analyst’’)
… ’ );

TOAD’s formatting is your friend here. When everything is red,
you’ve (probably) got the quotes correct: it will all be interpreted as
one big character string.

Nate Schroeder

US Seed & Trait Commercial IT - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592


#3

The simple answer is double the apostrophes. Ie
‘’Analyst’’

But I don’t think what you want will work.

I assume you are trying something like:

Procedure 1 generates a select statement that it passes to procedure 2 which
will then do an execute immediate using the passed text string.

This will not work because you need an INTO in that case

Or something like

Procedure 1 generates a select that it passes as a cursor to procedure 2. That
also probably will not work.


#4

Just concatenate the string together, i.e. ‘’’ ||
‘Analyst’ || ‘’’.

James A. Davis

mailto:james.davis@alaska.gov

5455 Dr. Martin Luther King Jr. Ave

Anchorage, AK 99507

WORK : (907) 334-2152

FAX : (907) 334-2161


#5

The issue is the Select statement will have singe

quotes in the string incasing text values.

Hey Mike;

It would help if you could clarify a bit. Your example only shows a sql
statement, not pl/sql.

I’d have to research how inner joins are formed again to know if
there’s anything wrong with that part but the rest of your query looks
just fine so I’m at a bit of a loss to know what your question is.

… SECURITY_USER . LRT_SECURITY )= ‘Analyst’ …

That’s a properly structured statement so perhaps you could follow up with
just what kind of error message you are receiving while compiling your
procedure. Of course… with your use of “security_user”,
I’m concluding that security_user is a table or view that points to a
table as that is how you are using it. Perhaps security_user is the second
procedure you mentioned?

Roger S.


#6

…i cannot locate that entity locally…

SQL> select * from ALL_TABLES where table_name LIKE ‘%SECURITY_USER%’;
no rows selected

a package name perhaps
?
Martin Gainty


Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l’information seulement et n’aura pas n’importe
quel effet légalement obligatoire. Étant donné que les email peuvent
facilement être sujets à la manipulation, nous ne pouvons accepter aucune
responsabilité pour le contenu fourni.


#7

It would be a plus if Toad could generate automaticaly an embeded code placing
the single quotes where needed. A button to remove them would be welcome too.

Etienne


#8

i cannot locate that entity locally

a package name perhaps

It could also be a personalized table/view for his particular application
(rather than an Oracle defined object).

However, I think the rest of the responders answered his question correctly. On
re-review of the original post in light of the responders, I do see a possible
question in there now where I didn’t before.

Roger S.


#9

yep… i think i missed that

as they said in the movie airplane “Roger Roger”

have a safe weekend all,
Martin Gainty


Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l’information seulement et n’aura pas n’importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.