[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

How do I incorporate a PL/SQL parameter as a partial table name?

I am using a PL/SQL combination to test the migration of data between one source and two targets. There are a large number of SQL scripts required.

The SQL statements themselves are quite complex, but the difference between executing against the second target is literally a prefix on the table name, like this:
   SELECT * FROM DB1_CUSTOMER
   SELECT * FROM DB2_CUSTOMER

The SQL statements are stored within "test case" files, with each test case file containing at least one SQL statement.

I'm planning to run the SQL from a vbs pl/sql command line "controller", which looks like this:
   SQL> C:\SQL\Library\TestCase1.sql

I want to include a run time parameter into the SQL, so that the SQL only needs to be written/updated once and the controller can dictate at run time, which target the SQL will execute against.

So the SQL will look like:
   SELECT * FROM &1_CUSTOMER

Hence, the PL/SQL command looks like this (to execute against DB1):
   SQL> C:\SQL\Library\TestCase1.sql DB1

But, I get a "that table doesn't exist" error. (Probably) because the implementation above does not disseminate the &1 parameter from the rest of the table's actual name.

I've tried using () and | etc, but can't seem to get it right.

I'm considering resorting to including a Find/Replace function in my vbs as a pre-processor for each SQL script, but this work-around will add processing overhead to what is already a performance intensive exercise. This is why I would like to utilise an execution parameter.

Appreciate any help that can be provided.
Thanks
0
Flipp
Asked:
Flipp
  • 5
  • 4
  • 2
  • +2
2 Solutions
 
flow01Commented:
The &1 substitution is normal done by sqlplus.
Does your controller file call sqlplus ?
If so ensure to have the sqlplus setting scan on (set scan on) else substitution wil not take place.
0
 
keyuCommented:
Do one thing pass it inside string and execute it....



DECLARE @sqkstr as varchar(1000)

 @sqkstr ='select * from '+@sqkstr +'_CUSTOMER'

EXEC (@sqkstr )
0
 
sdstuberCommented:
I don't see any pl/sql above.  Can you post some of your script so we can see where pl/sql might be involved?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
slightwv (䄆 Netminder) Commented:
This works for me form a command line given the sqlplus script below and having a table called tab1

If you can post your vbs script maybe we can help.

sqlplus user/password @q hello 1
select '&1' from tab&2;
select '&1' from tab&2;
exit

Open in new window

0
 
FlippAuthor Commented:
Thank you for the responses thus far.

Apologies for misleading, I need to clarify:

1. For starters - there's no pl/sql at play. It's all SQL*Plus.
2. The vbs script shouldn't come into play as it is simply executing the SQL from the SQL Plus command line prompt. The vbs just allows me to iterate sub folders and execute SQL contained therein. It's a work in progress, but for anyone interested (or perhaps I'm mistaken as to its relevance), I'll post it shortly.

@slightwv - yes, the appending parameter works for me too, but not when I need to prepend the parameter value (which is a bit of a tease!).

Feedback from other sources have been confident that I can't do what I want to do, with the technology that I want to use. The only option they provided was to use two variables (one for the prefix and one for the table name  (SELECT * FROM &1&2), which won't work for me due to the large number of table variations in the scripts.

So my options appear to either incorporate PL/SQL or incorporate a Find/Replace subroutine into my vbs script. As vbs is my strength, whereas pl/sql and SQL*Plus are not - I'm likely to go with the vbs pre-processing option.

I'll leave the Q open a little longer, just in case I've overlooked something.
ExecuteThisSuite fs.getFolder ("H:\SQL_Developer\SQL\Library")

Sub ExecuteThisSuite(Folder)
   For Each Subfolder In Folder.SubFolders     
      Set files = SubFolder.Files     
 
      For Each file In files
         WshShell.Sendkeys "@" & subFolder.path & "\" & file.Name  & " " & strOutput &"~"
         wscript.sleep (500)
 
      Next
      ExecuteThisSuite(SubFolder)

   Next
end sub

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Please provide a bigger picture of what you have and what you need.

In the original question you stated "above does not disseminate the &1 parameter". Then you mention it does work from sqlplus.

This implies the error is in how you pass it into sqlplus.

I'm not seeing the big picture here.
0
 
FlippAuthor Commented:
slightwv - thanks for your quick response and your patience in seeking to understand the problem I face.

Whilst the appended parameter does work, the prepended parameter (which is what I need) isn't working.

When I use a prepended parameter like this...
   SELECT * FROM &1_CUSTOMER

...SQL asks me to provide a value for the parameter "&1_CUSTOMER", as opposed to asking me for the value of "&1".

My interpretation of this result, is that the SQL engine does not see my parameter as &1. It sees my parameter as "&1_CUSTOMER". That's why I'm saying that SQL does not identify my parameter in the manner that I intend. A SQL*Plus parameter appears to be defined by the & symbol and extends through to the next space char.

What I need is to be able to prepend a table name with a runtime parameter that is provided to the SQL script via a SQL*Plus command line.
0
 
slightwv (䄆 Netminder) Commented:
Apologies.  I missed prepend.

I think the trick is add a '.'.  Not sure why it does but tends too get ignored with variables in sqlplus scripts.

Try:

 SELECT * FROM &1._CUSTOMER;
0
 
FlippAuthor Commented:
Yes - I had tried a few things with the '.' but it seems I didn't try the following! which works, as required.

Table name is: DB1_CUSTOMER
ParameterTest.sql is: SELECT * FROM &1._C30_POLCY_CVR
SQL*Plus command line is: SQL> @H:\SQL_Developer\SQL\TEMP\ParameterTest.sql DB1


Many thanks... additional vbs not required!
0
 
FlippAuthor Commented:
Table name is: DB1_CUSTOMER
ParameterTest.sql is: SELECT * FROM &1._C30_POLCY_CVR
SQL*Plus command line is: SQL> @H:\SQL_Developer\SQL\TEMP\ParameterTest.sql DB1
0
 
FlippAuthor Commented:
A slight adjustment/amendment is required to the closing comment, replacing with the following:

Table name is: DB1_CUSTOMER
ParameterTest.sql is: SELECT * FROM &1._CUSTOMER
SQL*Plus command line is: SQL> @H:\SQL_Developer\SQL\TEMP\ParameterTest.sql DB1
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.  I forget where I picked up that little trick but it does come in handy from time to time.

I'm sure it is in the docs somewhere if you care to dig it up.
0
 
sdstuberCommented:
just to help clarify about the (.)

you don't need the period if the substitution variable is standalone...

     select * from &1

but you do need it if you want to append other characters to the variable

    select * from &1._tab

it's been available since at least 7.3
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now