Re: SQL Variable

On 28 Apr 2007 10:20:56 -0700, William Robertson
<williamr2019@xxxxxxxxxxxxxx> wrote:

On Apr 28, 12:03 pm, sybra...@xxxxxxxxx wrote:
On 28 Apr 2007 02:11:49 -0700, William Robertson

<williamr2...@xxxxxxxxxxxxxx> wrote:
Actually you do need quotes, otherwise the substitution will resolve
to

strTblName := TB_B4000;

Actually he doesn't need quotes as his original code was reading
strTblname := '&1';

--
Sybrand Bakker
Senior Oracle DBA

I meant he does need the quotes around &1 in the anonymous non-SQL
block, in case there are any others I am missing.

SQL> def 1
DEFINE 1 = "TB_B4000" (CHAR)
SQL> r
1 DECLARE
2 strTblName VARCHAR2(30);
3 s NUMBER;
4 b DATE;
5 BEGIN
6 b := sysdate;
7 strTblName := '&1';
8* END;
old 7: strTblName := '&1';
new 7: strTblName := 'TB_B4000';

PL/SQL procedure successfully completed.

SQL> ed
Wrote file /Users/williamr/SQL/afiedt.buf

1 DECLARE
2 strTblName VARCHAR2(30);
3 s NUMBER;
4 b DATE;
5 BEGIN
6 b := sysdate;
7 strTblName := &1;
8* END;
SQL> /
old 7: strTblName := &1;
new 7: strTblName := TB_B4000;
strTblName := TB_B4000;
*
ERROR at line 7:
ORA-06550: line 7, column 19:
PLS-00201: identifier 'TB_B4000' must be declared
ORA-06550: line 7, column 5:
PL/SQL: Statement ignored

His FULL code was

declare
strTblName char;
s number;
b date;
BEGIN
b:=sysdate;
strTblName := '&1';
END;

So WHICH quotes does he need?
There was never a DEFINE 1 (which was made up by you).

--

Sybrand Bakker
Senior Oracle DBA
.

Relevant Pages

  • Re: SQL Variable
    ... Sybrand Bakker ... Senior Oracle DBA ... SQL> r ... I meant the quotes around the quotes around &1 in the anonymous block. ...
    (comp.databases.oracle.misc)
  • Re: SQL Variable
    ... I meant he does need the quotes around &1 in the anonymous non-SQL ... SQL> def 1 ... DECLARE ... b:= sysdate; ...
    (comp.databases.oracle.misc)
  • Re: using VBA to insert records from access database to sql server
    ... In SQL you 'escape' single quotes by doubling them. ... > Dim oConn As ADODB.Connection ... > Dim strInsert As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: using VBA to insert records from access database to sql server
    ... "Brendan Reynolds" wrote: ... > In SQL you 'escape' single quotes by doubling them. ... >> Dim oConn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)
  • SQL CONTAINS clause parameter substitution problem
    ... I'm in the process of parameterising the SQL SELECT statements in my ... result from parameters within quotes not being substitued. ... single and multiple search strings: ... My problems began when I modified the code to use an SqlCommand ...
    (microsoft.public.dotnet.framework.adonet)