Home » SQL & PL/SQL » SQL & PL/SQL » Return names of bind variables ( Version 9.0.4.1644 01.75108 Windows (64-bit) 6.2 Build 9200)
Return names of bind variables [message #680585] Sat, 23 May 2020 06:50 Go to next message
nbs
Messages: 4
Registered: May 2020
Junior Member
Hi.
Is there a way to return the names of all bind-variables in a plsql-script?
I'm calling the script from a C#-application.

Here's an example:

DECLARE
ORDER_NO_ VARCHAR2(20) := :ORDER_NO;
LINE_NO_ VARCHAR2(20) := :LINE_NO;
RELEASE_NO_ VARCHAR2(20) := :RELEASE_NO;

BEGIN
SOLUTION_API.PO_RECEIVE(ORDER_NO_,
LINE_NO_,
RELEASE_NO_);
END;

Friendly regards
nbs

Re: Return names of bind variables [message #680589 is a reply to message #680585] Sat, 23 May 2020 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
Are you sure "9.0.4.1644..." is your Oracle client or database version?

If you want the name of the bind variables in the script test it is more a (custom) syntax analyzer stuff than a PL/SQL one.
It could be as simple as (for the case at most one bind variable per line, this is just an example not a solution):
$ echo 'DECLARE
> ORDER_NO_ VARCHAR2(20) := :ORDER_NO;
> LINE_NO_ VARCHAR2(20) := :LINE_NO;
> RELEASE_NO_ VARCHAR2(20) := :RELEASE_NO;
>
> BEGIN
> SOLUTION_API.PO_RECEIVE(ORDER_NO_,
> LINE_NO_,
> RELEASE_NO_);
> END;' | grep -i ':[A-Z]' | sed -r -e 's/^.*(:[A-Z_#\$]+).*$/\1/i'
:ORDER_NO
:LINE_NO
:RELEASE_NO
Note that at Oracle level, they may not see your bind variable names. See some examples there.

[Updated on: Sat, 23 May 2020 09:51]

Report message to a moderator

Re: Return names of bind variables [message #680600 is a reply to message #680589] Sun, 24 May 2020 08:16 Go to previous messageGo to next message
nbs
Messages: 4
Registered: May 2020
Junior Member
Thank You Michel for Your answer.
I was wrong about the version. It's Oracle Database 12c Release 12.1.0.1.0

Friendly regards
nbs
Re: Return names of bind variables [message #680601 is a reply to message #680600] Sun, 24 May 2020 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you manage your issue?

What is the purpose of the question? may be we can help to find a solution of the main issue.

Re: Return names of bind variables [message #680606 is a reply to message #680601] Mon, 25 May 2020 04:12 Go to previous messageGo to next message
nbs
Messages: 4
Registered: May 2020
Junior Member
Hi Michel
The purpose of the question is, that I'm building a small debugger in C#, and it should show
the bind variables and their values (getting them from the sql-script).
I have not solved it yet.
What is the function of the following line?
| grep -i ':[A-Z]' | sed -r -e 's/^.*(:[A-Z_#\$]+).*$/\1/i' 
Re: Return names of bind variables [message #680609 is a reply to message #680606] Mon, 25 May 2020 09:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

These are *nix shell stuff.
"grep -i ':[A-Z]'" outputs all lines which contain a ":" followed by any alphabetical character.
"sed -r -e 's/^.*(:[A-Z_#\$]+).*$/\1/i'" returns the first pattern starting with ":" and followed by any alphabetical character or "_", "#" or "$".

I should add any digit as digits are allowed in variable names:
sed -r -e 's/^.*(:[A-Z][A-Z0-9_#\$]*).*$/\1/i'

As I said, this was just an example, it would be better to use a scripting language like Perl (my favorite) to handle more complicated cases.

I don't know what you exactly want to do but you can activate the SQL trace when you debug your application to get these information (bind variables, their types, as seen by the db, and their values) as well as what the interface with the database is doing or waiting when your application runs.

Re: Return names of bind variables [message #680615 is a reply to message #680609] Tue, 26 May 2020 02:50 Go to previous message
nbs
Messages: 4
Registered: May 2020
Junior Member
Thank You for Your time Michel.
The shell-stuff was inspiring--> I think the best way in my situation, is get the variables through C#-code.
Friendly regards
nbs
Previous Topic: Missing number in series of numbers
Next Topic: materialized view issue
Goto Forum:
  


Current Time: Wed Sep 23 02:48:56 CDT 2020