Snowflake Stored Procedure binding argument error

If you run into the below error, when trying to execute a stored procedure in Snowflake,

Execution error in store procedure

Unsupported type for binding argument

One of the causes could be, that the Execute command of the SQL is referring to a Variable that isn’t defined, most likely, misspelt.

Do check if that’s the case.

Hope this helps.

TroubleShoot: impala \d in REGEXP not working

---

Using REGEXP_EXTRACT in impala, to extract a number after a specific text pattern, you can use the below SQL.

Example: Say, The pattern you are looking for is the ‘numeric digits’ that comes after the TEXT/String ‘FBDV’

I’m using a hard-coded text value (‘Sample Text FBBV/3809028;FBDV/436876;FBV/iPhone7,2;FBMD/iPhone]’) for demo the result.

SELECT REGEXP_EXTRACT(c, 'FBDV\/(\\d*?)',1) from (
select 'Sample Text FBBV/3809028;FBDV/436876;FBV/iPhone7,2;FBMD/iPhone]' c) t

Output: 436876

If the value comes from a column in table, you can use:

SELECT REGEXP_EXTRACT({column_name}, 'FBDV\/(\\d*?)',1) from {table_name}

The trick here is: usually \d in REGEX denotes any numeric, but in Impala SQL inside capturing open/close parenthesis, you will have to escape the ‘blakslash’ with a backslash, so it requires \\d instead.

---

Find out how much database space you used up

If you ever want to know, in Oracle database, how much space you have used up so far (in terms of GB) by all of the objects in your schema, you can use the below SQL

SELECT a.owner, tablespace_name ,
sum(a.blocks*16/1024/1024) AS GB
FROM all_tables a
WHERE owner = 'YOUR_SCHEMA_NAME'
group by a.owner, tablespace_name;

Query keeps running, Not sure if it’s done

There would be instances at work when you are querying against a big table, or creating a big table, and you have executed the SQL, and it keeps running for a long time, and status is showing nothing, except it’s running.

And you want to find out how long would it run ? Will it end ever ? or at the minimum, is it even running or the tool just got hung ?

In Oracle database, You can use the below query in another window or most likely another query tool, to see if there are any records. Usually there are records that shows up for active query that run under your user name.

select * from v$session_longops where username = ‘YOUR_ORACLE_SCHEMANAME‘
and (Time_Remaining > 0 or Time_Remaining is null)
ORDER BY START_TIME DESC;

You can use the below query to find the percentage completion, although most times it’s not accurate, because the long running query triggers multiple record reads, and so the percentage may come and go (but on the positive side, you at least know the query is running ,and will complete and the application isn’t hung)

select sum(sofar), sum(totalwork), round(((sum(sofar)/sum(totalwork))*100),2)|| ‘ %’ “Percent Complete”, sum(Time_Remaining) from v$session_longops
where username = ‘YOUR_ORACLE_SCHEMANAME‘
and time_remaining > 0;

Hope this helps 🙂

Execute SQL script using Windows Task Scheduler

Say, you have a SQL script ready, and you want that to run automatically without you kick starting it every single time. You can do this via ‘adding the script to Windows Task Scheduler’, and configuring it.

Example script (saved as test.sql):

set echo on
set time on
set timing on

spool C:\some_folder\logfile\test.log;

drop table test_table;

create table test_table
(name varchar2(30), age int);

insert into test_table (name, age)
select ‘senthamizh selvan’, 20 from dual
union
select ‘Kanmani Anbodu’, 35 from dual;

commit;

spool off
exit;

Note: Spool commands where I’m writing the logs has the ‘Full Path’ of the log file.

We are going to use ‘sqlplus’ – a command line utility for Oracle Database / Query and Windows Task Scheduler to automate this SQL.

You can find ‘Task Scheduler’ application in the ‘Administrative Tools’ section (in Control Panels) for Windows server.

Open ‘Task Scheduler’ and Choose’Create Basic Task’ under Action.

Give a Name to your task, and Click ‘Next’

 

Choose a schedule -daily, weekly, etc ….

And Click Next

I chose ‘Daily’ and gave the ‘Time’ – Click Next once done

Click Next

Enter ‘sqlplus’ and in the arguments add “username/password@databse_name @C:\somefolder\….\test.sql”

Make sure you enter the full path of the location to the file in the arguments.

Click Finish

And your job is ready to run.

Click on ‘Task Scheduler Library’ to confirm if your Job is there in the scheduler

You can ‘right-click’ on the job to ‘Run’ manually once to verigy if its executing to completion or if you want to change any schedule/command/arguments – click on properties, and you will be able to change and Edit as needed.

Also, you can make the job to run in the server, even when you are not logged into the server, by choosing that option from the ‘General’ tab. But to do so, you should be an administrator or have the ‘batch’ access privilege.

See if you can make you ‘batch access’ by doing this

This policy is accessible by opening the Control Panel , Administrative Tools , and then Local Security Policy . In the Local Security Policy window, click Local Policy , User Rights Assignment , and then Logon as batch job

or you will be need to check with your system administrator

 

Also, you can check your log file to confirm you are seeing what you are expecting to !!