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’)
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
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?
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.
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.
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.
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.